From 51c44b2c497d52ccc3f2c0de09347d994aa0eb11 Mon Sep 17 00:00:00 2001 From: "okbob@github.com" Date: Thu, 6 Jul 2023 08:29:21 +0200 Subject: [PATCH 2/9] Storage for session variables and SQL interface Session variables are stored in session memory in dedicated hash table. The write access is done by LET command. The read access is by SELECT command. The access rights should be checked. Support for session variables from PL/pgSQL included. The execution of LET command in PL/pgSQL is different than other utility commands. The reason for this difference is possibility to evaluate expression directly by exec_eval_expr (see exec_eval_simple_expr). It requires few tens lines more, but the execution of usually expected expressions can be significantly faster. The identifiers of session variables should be shadowed by possible column's identifiers always. This is by design feature. We don't want to break an application by creating some session variable that is badly named. Unfortunately then can be hard to identify this situation, so there is new warning (controlled by session_variables_ambiguity_warning GUC), that is raised when in context when session variables are allowed is some available session variable, but another column reference is used. This patch doesn't solve memory cleaning. It will be solved by next patches. --- doc/src/sgml/catalogs.sgml | 13 + doc/src/sgml/config.sgml | 60 ++ doc/src/sgml/ddl.sgml | 44 + doc/src/sgml/event-trigger.sgml | 24 + doc/src/sgml/plpgsql.sgml | 12 + doc/src/sgml/ref/allfiles.sgml | 1 + doc/src/sgml/ref/alter_variable.sgml | 1 + doc/src/sgml/ref/create_variable.sgml | 1 + doc/src/sgml/ref/drop_variable.sgml | 1 + doc/src/sgml/ref/let.sgml | 97 ++ doc/src/sgml/reference.sgml | 1 + src/backend/catalog/dependency.c | 5 + src/backend/catalog/namespace.c | 327 +++++++ src/backend/catalog/pg_variable.c | 2 + src/backend/commands/Makefile | 1 + src/backend/commands/explain.c | 31 +- src/backend/commands/meson.build | 1 + src/backend/commands/prepare.c | 2 +- src/backend/commands/session_variable.c | 721 ++++++++++++++ src/backend/executor/Makefile | 1 + src/backend/executor/execExpr.c | 77 ++ src/backend/executor/execExprInterp.c | 16 + src/backend/executor/execMain.c | 59 ++ src/backend/executor/execParallel.c | 147 ++- src/backend/executor/meson.build | 1 + src/backend/executor/spi.c | 3 + src/backend/executor/svariableReceiver.c | 212 +++++ src/backend/jit/llvm/llvmjit_expr.c | 6 + src/backend/nodes/nodeFuncs.c | 10 + src/backend/optimizer/plan/planner.c | 8 + src/backend/optimizer/plan/setrefs.c | 137 ++- src/backend/optimizer/prep/prepjointree.c | 3 + src/backend/optimizer/util/clauses.c | 74 +- src/backend/parser/analyze.c | 281 +++++- src/backend/parser/gram.y | 63 +- src/backend/parser/parse_agg.c | 9 + src/backend/parser/parse_cte.c | 8 + src/backend/parser/parse_expr.c | 265 +++++- src/backend/parser/parse_func.c | 2 + src/backend/parser/parser.c | 3 +- src/backend/rewrite/rewriteHandler.c | 1 + src/backend/tcop/dest.c | 7 + src/backend/tcop/pquery.c | 3 + src/backend/tcop/utility.c | 16 + src/backend/utils/adt/ruleutils.c | 46 + src/backend/utils/cache/plancache.c | 41 +- src/backend/utils/fmgr/fmgr.c | 10 +- src/backend/utils/misc/guc_tables.c | 10 + src/bin/psql/tab-complete.c | 12 +- src/include/catalog/namespace.h | 2 + src/include/catalog/pg_proc.dat | 7 + src/include/catalog/pg_variable.h | 7 + src/include/commands/explain.h | 3 +- src/include/commands/session_variable.h | 32 + src/include/executor/execExpr.h | 10 + src/include/executor/execdesc.h | 4 + src/include/executor/svariableReceiver.h | 25 + src/include/nodes/execnodes.h | 19 + src/include/nodes/parsenodes.h | 19 + src/include/nodes/pathnodes.h | 5 + src/include/nodes/plannodes.h | 4 +- src/include/nodes/primnodes.h | 12 +- src/include/optimizer/planmain.h | 2 + src/include/parser/kwlist.h | 1 + src/include/parser/parse_expr.h | 1 + src/include/parser/parse_node.h | 4 + src/include/parser/parser.h | 6 +- src/include/tcop/cmdtaglist.h | 1 + src/include/tcop/dest.h | 3 +- src/pl/plpgsql/src/Makefile | 2 +- .../src/expected/plpgsql_session_variable.out | 412 ++++++++ src/pl/plpgsql/src/meson.build | 1 + src/pl/plpgsql/src/pl_exec.c | 55 ++ src/pl/plpgsql/src/pl_funcs.c | 24 + src/pl/plpgsql/src/pl_gram.y | 28 +- src/pl/plpgsql/src/pl_reserved_kwlist.h | 1 + src/pl/plpgsql/src/plpgsql.h | 14 +- .../src/sql/plpgsql_session_variable.sql | 315 ++++++ .../regress/expected/session_variables.out | 900 ++++++++++++++++++ src/test/regress/sql/session_variables.sql | 587 ++++++++++++ src/tools/pgindent/typedefs.list | 6 + 81 files changed, 5337 insertions(+), 51 deletions(-) create mode 100644 doc/src/sgml/ref/let.sgml create mode 100644 src/backend/commands/session_variable.c create mode 100644 src/backend/executor/svariableReceiver.c create mode 100644 src/include/commands/session_variable.h create mode 100644 src/include/executor/svariableReceiver.h create mode 100644 src/pl/plpgsql/src/expected/plpgsql_session_variable.out create mode 100644 src/pl/plpgsql/src/sql/plpgsql_session_variable.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 2f7208fc47..9a08f3d80f 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -9718,6 +9718,19 @@ SCRAM-SHA-256$<iteration count>:&l + + + varcreate_lsn XLogRecPtr + + + LSN of the transaction where variable was created. It is used + (in combination with oid) as everytime + unique identifier. Only oid cannot be + used for this purpose, because unused oid + can be reused. + + + varname name diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 694d667bf9..017c7c3d51 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -10374,6 +10374,66 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' + + session_variables_ambiguity_warning (boolean) + + session_variables_ambiguity_warning configuration parameter + + + + + When on, a warning is raised when any identifier in a query could be + used as both a column identifier, routine variable or a session + variable identifier. The default is off. + + + Session variables can be shadowed by column references in a query, this + is an expected behavior. Previously working queries shouldn't error out + by creating any session variable, so session variables are always shadowed + if an identifier is ambiguous. Variables should be referenced using + anunambiguous identifier without any possibility for a collision with + identifier of other database objects (column names or record fields names). + The warning messages emitted when enabling session_variables_ambiguity_warning + can help finding such identifier collision. + +CREATE TABLE foo(a int); +INSERT INTO foo VALUES(10); +CREATE VARIABLE a int; +LET a = 100; +SELECT a FROM foo; + + + + a +---- + 10 +(1 row) + + + +SET session_variables_ambiguity_warning TO on; +SELECT a FROM foo; + + + +WARNING: session variable "a" is shadowed +LINE 1: SELECT a FROM foo; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + a +---- + 10 +(1 row) + + + + This feature can significantly increase log size, so it's disabled by + default. For testing or development environments it's recommended to + enable it if you use session variables. + + + + standard_conforming_strings (boolean) stringsstandard conforming diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 8d48055e1b..79d38a1716 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -5148,6 +5148,50 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01'; commands. A session variable can be created by the CREATE VARIABLE command. + + + The value of a session variable is set with the LET SQL + command. While session variables share properties with tables, their value + cannot be updated with an UPDATE command. The value of a + session variable may be retrieved by the SELECT SQL + command. + +CREATE VARIABLE var1 AS date; +LET var1 = current_date; +SELECT var1; + + + or + + +CREATE VARIABLE public.current_user_id AS integer; +GRANT READ ON VARIABLE public.current_user_id TO PUBLIC; +LET current_user_id = (SELECT id FROM users WHERE usename = session_user); +SELECT current_user_id; + + + + + The value of a session variable is local to the current session. Retrieving + a variable's value returns either a NULL or a default + value, unless its value has been set to something else in the current + session using the LET command. The content of a variable + is not transactional. This is the same as regular variables in PL languages. + The session variables can be persistent or can be temporary. In both cases, + the content of session variables is temporary and not shared (like an + content of temporary tables). + + + + The session variables can be shadowed by column references in a query. When + a query contains identifiers or qualified identifiers that could be used as + both a session variable identifiers and as column identifier, then the + column identifier is preferred every time. Warnings can be emitted when + this situation happens by enabling configuration parameter . User can explicitly + qualify the source object by syntax table.column or + variable.column. + diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index cac5f9ff94..ff9e1cb60d 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -413,6 +413,14 @@ - + + ALTER VARIABLE + X + X + - + - + + ALTER VIEW X @@ -709,6 +717,14 @@ - + + CREATE VARIABLE + X + X + - + - + + CREATE VIEW X @@ -1005,6 +1021,14 @@ - + + DROP VARIABLE + X + X + X + - + + DROP VIEW X diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index f55e901c7e..0f93647382 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -5970,6 +5970,18 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE; + + + <command>Session variables</command> + + + The PL/pgSQL language has no packages, and + therefore no package variables or package constants. + PostgreSQL has session variables and immutable + session variables. Session variables can be created by CREATE + VARIABLE, as described in . + + diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index 35f20e42ce..fa295e5d77 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -158,6 +158,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_variable.sgml b/doc/src/sgml/ref/alter_variable.sgml index d87570e7d8..d2036351e5 100644 --- a/doc/src/sgml/ref/alter_variable.sgml +++ b/doc/src/sgml/ref/alter_variable.sgml @@ -173,6 +173,7 @@ ALTER VARIABLE boo SET SCHEMA private; + diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml index 04901c5510..faed05e22b 100644 --- a/doc/src/sgml/ref/create_variable.sgml +++ b/doc/src/sgml/ref/create_variable.sgml @@ -151,6 +151,7 @@ SELECT var1; + diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml index 5bdb3560f0..67988b5fcd 100644 --- a/doc/src/sgml/ref/drop_variable.sgml +++ b/doc/src/sgml/ref/drop_variable.sgml @@ -111,6 +111,7 @@ DROP VARIABLE var1; + diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml new file mode 100644 index 0000000000..63968eddd9 --- /dev/null +++ b/doc/src/sgml/ref/let.sgml @@ -0,0 +1,97 @@ + + + + + LET + + + + session variable + changing + + + + LET + 7 + SQL - Language Statements + + + + LET + change a session variable's value + + + + +LET session_variable = sql_expression + + + + + Description + + + The LET command assigns a value to the specified session + variable. + + + + + + Parameters + + + + session_variable + + + The name of the session variable. + + + + + + sql_expression + + + An SQL expression (can be subquery in parenthesis). The result must + be of castable to the same data type as the session variable (in + implicit or assignment context). + + + + + + + + Example: + +CREATE VARIABLE myvar AS integer; +LET myvar = 10; +LET myvar = (SELECT sum(val) FROM tab); + + + + + + Compatibility + + + The LET is a PostgreSQL + extension. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index 885f293b71..4256a488f8 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -186,6 +186,7 @@ &grant; &importForeignSchema; &insert; + &let; &listen; &load; &lock; diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index 050df918d3..7794b8f35f 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -1914,6 +1914,11 @@ find_expr_references_walker(Node *node, { Param *param = (Param *) node; + /* A variable parameter depends on the session variable */ + if (param->paramkind == PARAM_VARIABLE) + add_object_address(OCLASS_VARIABLE, param->paramvarid, 0, + context->addrs); + /* A parameter must depend on the parameter's datatype */ add_object_address(OCLASS_TYPE, param->paramtype, 0, context->addrs); diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index 8cd57fbae1..f390fd168f 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -2952,6 +2952,333 @@ LookupVariable(const char *nspname, return varoid; } +/* + * The input list contains names with indirection expressions used as the left + * part of LET statement. The following routine returns a new list with only + * initial strings (names) - without indirection expressions. + */ +List * +NamesFromList(List *names) +{ + ListCell *l; + List *result = NIL; + + foreach(l, names) + { + Node *n = lfirst(l); + + if (IsA(n, String)) + { + result = lappend(result, n); + } + else + break; + } + + return result; +} + +/* + * IdentifyVariable - try to find variable identified by list of names. + * + * Before this call we don't know, how these fields should be mapped to + * schema name, variable name and attribute name. In this routine + * we try to apply passed names to all possible combinations of schema name, + * variable name and attribute name, and we count valid combinations. + * + * Returns oid of identified variable. When last field of names list is + * identified as an attribute, then output attrname argument is set to + * an string of this field. + * + * When there is not any valid combination, then we are sure, so the + * list of names cannot to identify any session variable. In this case + * we return InvalidOid. + * + * We can find more valid combination than one. + * Example: users can have session variable x in schema y, and + * session variable y with attribute x inside some schema from + * search path. In this situation the meaning of expression "y"."x" + * is ambiguous. In this case this routine returns oid of variable + * x in schema y, and the output parameter "not_unique" is set to + * true. In this case this variable is locked. + * + * The AccessShareLock is created on related session variable. The lock + * will be kept for the whole transaction. + * + * Note: the out attrname should be used only when the session variable + * is identified. When the session variable is not identified, then this + * output variable can hold reference to some string, but isn't sure + * about its semantics. + * + * When we use this routine for identification of shadowed variable, + * we don't want to raise any error. Shadowing column reference is correct, + * and we don't want to break execution due shadowing check. + */ +Oid +IdentifyVariable(List *names, char **attrname, bool *not_unique, bool noerror) +{ + Node *field1 = NULL; + Node *field2 = NULL; + Node *field3 = NULL; + Node *field4 = NULL; + char *a = NULL; + char *b = NULL; + char *c = NULL; + char *d = NULL; + Oid varid = InvalidOid; + Oid old_varid = InvalidOid; + uint64 inval_count; + bool retry = false; + + /* + * DDL operations can change the results of a name lookup. Since all such + * operations will generate invalidation messages, we keep track of + * whether any such messages show up while we're performing the operation, + * and retry until either (1) no more invalidation messages show up or (2) + * the answer doesn't change. + */ + for (;;) + { + Oid varoid_without_attr = InvalidOid; + Oid varoid_with_attr = InvalidOid; + + *not_unique = false; + *attrname = NULL; + varid = InvalidOid; + + inval_count = SharedInvalidMessageCounter; + + switch (list_length(names)) + { + case 1: + field1 = linitial(names); + + Assert(IsA(field1, String)); + + varid = LookupVariable(NULL, strVal(field1), true); + break; + + case 2: + field1 = linitial(names); + field2 = lsecond(names); + + Assert(IsA(field1, String)); + a = strVal(field1); + + if (IsA(field2, String)) + { + /* when both fields are of string type */ + b = strVal(field2); + + /* + * a.b can mean "schema"."variable" or "variable"."field". + * Check both variants, and returns InvalidOid with + * not_unique flag, when both interpretations are + * possible. + */ + varoid_without_attr = LookupVariable(a, b, true); + varoid_with_attr = LookupVariable(NULL, a, true); + } + else + { + /* The last field of list can be star too. */ + Assert(IsA(field2, A_Star)); + + /* + * In this case, the field1 should be variable name. But + * direct unboxing of composite session variables is not + * supported now, and then we don't need to try lookup + * related variable. + * + * Unboxing is supported by syntax (var).* + */ + return InvalidOid; + } + + if (OidIsValid(varoid_without_attr) && OidIsValid(varoid_with_attr)) + { + *not_unique = true; + varid = varoid_without_attr; + } + else if (OidIsValid(varoid_without_attr)) + { + varid = varoid_without_attr; + } + else if (OidIsValid(varoid_with_attr)) + { + *attrname = b; + varid = varoid_with_attr; + } + break; + + case 3: + { + bool field1_is_catalog = false; + + field1 = linitial(names); + field2 = lsecond(names); + field3 = lthird(names); + + Assert(IsA(field1, String)); + Assert(IsA(field2, String)); + + a = strVal(field1); + b = strVal(field2); + + if (IsA(field3, String)) + { + c = strVal(field3); + + /* + * a.b.c can mean catalog.schema.variable or + * schema.variable.field. + * + * Check both variants, and set not_unique flag, when + * both interpretations are possible. + * + * When third node is star, only possible + * interpretation is schema.variable.*, but this + * pattern is not supported now. + */ + varoid_with_attr = LookupVariable(a, b, true); + + /* + * check pattern catalog.schema.variable only when + * there is possibility to success. + */ + if (strcmp(a, get_database_name(MyDatabaseId)) == 0) + { + field1_is_catalog = true; + varoid_without_attr = LookupVariable(b, c, true); + } + } + else + { + Assert(IsA(field3, A_Star)); + return InvalidOid; + } + + if (OidIsValid(varoid_without_attr) && OidIsValid(varoid_with_attr)) + { + *not_unique = true; + varid = varoid_without_attr; + } + else if (OidIsValid(varoid_without_attr)) + { + varid = varoid_without_attr; + } + else if (OidIsValid(varoid_with_attr)) + { + *attrname = c; + varid = varoid_with_attr; + } + + /* + * When we didn't find variable, we can (when it is + * allowed) raise cross-database reference error. + */ + if (!OidIsValid(varid) && !noerror && !field1_is_catalog) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cross-database references are not implemented: %s", + NameListToString(names)))); + } + break; + + case 4: + { + field1 = linitial(names); + field2 = lsecond(names); + field3 = lthird(names); + field4 = lfourth(names); + + Assert(IsA(field1, String)); + Assert(IsA(field2, String)); + Assert(IsA(field3, String)); + + a = strVal(field1); + b = strVal(field2); + c = strVal(field3); + + /* + * In this case, "a" is used as catalog name - check it. + */ + if (strcmp(a, get_database_name(MyDatabaseId)) != 0) + { + if (!noerror) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cross-database references are not implemented: %s", + NameListToString(names)))); + } + else + { + if (IsA(field4, String)) + { + d = strVal(field4); + } + else + { + Assert(IsA(field4, A_Star)); + return InvalidOid; + } + + *attrname = d; + varid = LookupVariable(b, c, true); + } + } + break; + + default: + if (!noerror) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("improper qualified name (too many dotted names): %s", + NameListToString(names)))); + return InvalidOid; + } + + /* + * If, upon retry, we get back the same OID we did last time, then the + * invalidation messages we processed did not change the final answer. + * So we're done. + * + * If we got a different OID, we've locked the variable that used to + * have this name rather than the one that does now. So release the + * lock. + */ + if (retry) + { + if (old_varid == varid) + break; + + if (OidIsValid(old_varid)) + UnlockDatabaseObject(VariableRelationId, old_varid, 0, AccessShareLock); + } + + /* + * Lock the variable. This will also accept any pending invalidation + * messages. If we got back InvalidOid, indicating not found, then + * there's nothing to lock, but we accept invalidation messages + * anyway, to flush any negative catcache entries that may be + * lingering. + */ + if (!OidIsValid(varid)) + AcceptInvalidationMessages(); + else if (OidIsValid(varid)) + LockDatabaseObject(VariableRelationId, varid, 0, AccessShareLock); + + if (inval_count == SharedInvalidMessageCounter) + break; + + retry = true; + old_varid = varid; + varid = InvalidOid; + } + + return varid; +} + /* * DeconstructQualifiedName * Given a possibly-qualified name expressed as a list of String nodes, diff --git a/src/backend/catalog/pg_variable.c b/src/backend/catalog/pg_variable.c index d68ad8bd19..10b6187bbe 100644 --- a/src/backend/catalog/pg_variable.c +++ b/src/backend/catalog/pg_variable.c @@ -26,6 +26,7 @@ #include "parser/parse_type.h" #include "utils/builtins.h" #include "utils/lsyscache.h" +#include "utils/pg_lsn.h" #include "utils/syscache.h" static ObjectAddress create_variable(const char *varName, @@ -101,6 +102,7 @@ create_variable(const char *varName, varid = GetNewOidWithIndex(rel, VariableObjectIndexId, Anum_pg_variable_oid); values[Anum_pg_variable_oid - 1] = ObjectIdGetDatum(varid); + values[Anum_pg_variable_varcreate_lsn - 1] = LSNGetDatum(GetXLogInsertRecPtr()); values[Anum_pg_variable_varname - 1] = NameGetDatum(&varname); values[Anum_pg_variable_varnamespace - 1] = ObjectIdGetDatum(varNamespace); values[Anum_pg_variable_vartype - 1] = ObjectIdGetDatum(varType); diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile index 48f7348f91..1cfaeca51e 100644 --- a/src/backend/commands/Makefile +++ b/src/backend/commands/Makefile @@ -50,6 +50,7 @@ OBJS = \ schemacmds.o \ seclabel.o \ sequence.o \ + session_variable.o \ statscmds.o \ subscriptioncmds.o \ tablecmds.o \ diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 8570b14f62..b637879bf5 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -19,6 +19,7 @@ #include "commands/defrem.h" #include "commands/prepare.h" #include "executor/nodeHash.h" +#include "executor/svariableReceiver.h" #include "foreign/fdwapi.h" #include "jit/jit.h" #include "nodes/extensible.h" @@ -416,7 +417,7 @@ ExplainOneQuery(Query *query, int cursorOptions, } /* run it (if needed) and produce output */ - ExplainOnePlan(plan, into, es, queryString, params, queryEnv, + ExplainOnePlan(plan, into, query->resultVariable, es, queryString, params, queryEnv, &planduration, (es->buffers ? &bufusage : NULL)); } } @@ -501,6 +502,25 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, else ExplainDummyGroup("Notify", NULL, es); } + else if (IsA(utilityStmt, LetStmt)) + { + LetStmt *letstmt = (LetStmt *) utilityStmt; + List *rewritten; + Query *query; + + if (es->format == EXPLAIN_FORMAT_TEXT) + appendStringInfoString(es->str, "SET SESSION VARIABLE\n"); + else + ExplainDummyGroup("Set Session Variable", NULL, es); + + rewritten = QueryRewrite(castNode(Query, copyObject(letstmt->query))); + + Assert(list_length(rewritten) == 1); + query = linitial_node(Query, rewritten); + ExplainOneQuery(query, + CURSOR_OPT_PARALLEL_OK, NULL, es, + queryString, params, queryEnv); + } else { if (es->format == EXPLAIN_FORMAT_TEXT) @@ -524,8 +544,8 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, * to call it. */ void -ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, - const char *queryString, ParamListInfo params, +ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, Oid targetvar, + ExplainState *es, const char *queryString, ParamListInfo params, QueryEnvironment *queryEnv, const instr_time *planduration, const BufferUsage *bufusage) { @@ -568,6 +588,11 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, */ if (into) dest = CreateIntoRelDestReceiver(into); + else if (OidIsValid(targetvar)) + { + dest = CreateVariableDestReceiver(); + SetVariableDestReceiverVarid(dest, targetvar); + } else dest = None_Receiver; diff --git a/src/backend/commands/meson.build b/src/backend/commands/meson.build index 42cced9ebe..404be3d22b 100644 --- a/src/backend/commands/meson.build +++ b/src/backend/commands/meson.build @@ -38,6 +38,7 @@ backend_sources += files( 'schemacmds.c', 'seclabel.c', 'sequence.c', + 'session_variable.c', 'statscmds.c', 'subscriptioncmds.c', 'tablecmds.c', diff --git a/src/backend/commands/prepare.c b/src/backend/commands/prepare.c index 18f70319fc..9643ec8f71 100644 --- a/src/backend/commands/prepare.c +++ b/src/backend/commands/prepare.c @@ -639,7 +639,7 @@ ExplainExecuteQuery(ExecuteStmt *execstmt, IntoClause *into, ExplainState *es, PlannedStmt *pstmt = lfirst_node(PlannedStmt, p); if (pstmt->commandType != CMD_UTILITY) - ExplainOnePlan(pstmt, into, es, query_string, paramLI, queryEnv, + ExplainOnePlan(pstmt, into, InvalidOid, es, query_string, paramLI, queryEnv, &planduration, (es->buffers ? &bufusage : NULL)); else ExplainOneUtility(pstmt->utilityStmt, into, es, query_string, diff --git a/src/backend/commands/session_variable.c b/src/backend/commands/session_variable.c new file mode 100644 index 0000000000..a02a17b6c1 --- /dev/null +++ b/src/backend/commands/session_variable.c @@ -0,0 +1,721 @@ +/*------------------------------------------------------------------------- + * + * session_variable.c + * session variable creation/manipulation commands + * + * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/commands/session_variable.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "catalog/pg_variable.h" +#include "commands/session_variable.h" +#include "executor/svariableReceiver.h" +#include "funcapi.h" +#include "miscadmin.h" +#include "rewrite/rewriteHandler.h" +#include "storage/lmgr.h" +#include "storage/proc.h" +#include "tcop/tcopprot.h" +#include "utils/builtins.h" +#include "utils/datum.h" +#include "utils/inval.h" +#include "utils/lsyscache.h" +#include "utils/snapmgr.h" +#include "utils/syscache.h" + +/* + * Values of session variables are stored in the backend local memory + * inside sessionvars hash table in binary format inside a dedicated memory + * context SVariableMemoryContext. The hash key is oid + * of related entry in pg_variable table. But unambiguity of oid is + * not guaranteed (in long time). There can be long time not active + * session with stored value identified by one oid, and in other session + * related variable can be dropped, assigned oid can be released, and + * theoreticaly this oid can be assigned to different session variable. + * At the end, the reading of value stored in old session should to fail, + * because related entry in pg_variable will not be consistent with + * stored value. This is reason why we do check consistency between stored + * value and catalog by create_lsn value. + * + * Before any usage (not only read in transaction) we need to check consistency + * with pg_variable entry. When there is not entry with stored oid, the related + * variable was dropped, and stored value is not consistent. When entry with + * known oid, but lsn number is different, entry of pg_variable was created + * for different variable and stored value is not consistent again. + */ +typedef struct SVariableData +{ + Oid varid; /* pg_variable OID of the variable (hash key) */ + XLogRecPtr create_lsn; + + bool isnull; + bool freeval; + Datum value; + + Oid typid; + int16 typlen; + bool typbyval; + + bool is_domain; + void *domain_check_extra; + LocalTransactionId domain_check_extra_lxid; + + /* + * Stored value and type description can be outdated when we receive + * sinval message. We have to check always if the stored data are + * trustful. + */ + bool is_valid; + + uint32 hashvalue; /* used for pairing sinval message */ +} SVariableData; + +typedef SVariableData *SVariable; + +static HTAB *sessionvars = NULL; /* hash table for session variables */ + +static MemoryContext SVariableMemoryContext = NULL; + +/* + * Callback function for session variable invalidation. + * + * Note: originally we enhanced a list xact_recheck_varids here. Unfortunately + * it was not safe and a little bit too complex, because the sinval callback + * function can be called when we iterate over xact_recheck_varids list. + * Another issue was the possibility of being out of memory when we enhanced + * the list. So now we just switch flag in related entry sessionvars hash table. + * We need to iterate over hash table on every sinval message, so extra two + * iteration over this hash table is not significant overhead (and we skip + * entries that don't require recheck). Now we do not have any memory allocation + * in the sinval handler (This note can be removed before commit). + */ +static void +pg_variable_cache_callback(Datum arg, int cacheid, uint32 hashvalue) +{ + HASH_SEQ_STATUS status; + SVariable svar; + + /* + * There is no guarantee of sessionvars being initialized, even when + * receiving an invalidation callback, as DISCARD [ ALL | VARIABLES ] + * destroys the hash table entirely. + */ + if (!sessionvars) + return; + + elog(DEBUG1, "pg_variable_cache_callback %u %u", cacheid, hashvalue); + + /* + * When the hashvalue is not specified, then we have to recheck all + * currently used session variables. Since we can't guarantee the exact + * session variable from its hashValue, we also have to iterate over all + * items of the sessionvars hash table. + */ + hash_seq_init(&status, sessionvars); + + while ((svar = (SVariable) hash_seq_search(&status)) != NULL) + { + if (hashvalue == 0 || svar->hashvalue == hashvalue) + { + svar->is_valid = false; + } + } +} + +/* + * Release stored value, free memory + */ +static void +free_session_variable_value(SVariable svar) +{ + /* Clean current value */ + if (!svar->isnull) + { + if (svar->freeval) + { + pfree(DatumGetPointer(svar->value)); + svar->freeval = false; + } + + svar->isnull = true; + } + + svar->value = (Datum) 0; + svar->freeval = false; +} + +/* + * Returns true when the entry in pg_variable is consistent with + * the given session variable. + */ +static bool +is_session_variable_valid(SVariable svar) +{ + HeapTuple tp; + bool result = false; + + Assert(OidIsValid(svar->varid)); + + tp = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(svar->varid)); + + if (HeapTupleIsValid(tp)) + { + /* + * In this case, the only oid cannot be used as unique identifier, + * because the oid counter can wraparound, and the oid can be used for + * new other session variable. We do a second check against 64bit + * unique identifier. + */ + if (svar->create_lsn == ((Form_pg_variable) GETSTRUCT(tp))->varcreate_lsn) + result = true; + + ReleaseSysCache(tp); + } + + return result; +} + +/* + * Update attributes cached in svar + */ +static void +setup_session_variable(SVariable svar, Oid varid) +{ + HeapTuple tup; + Form_pg_variable varform; + + Assert(OidIsValid(varid)); + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for session variable %u", varid); + + varform = (Form_pg_variable) GETSTRUCT(tup); + + svar->varid = varid; + svar->create_lsn = varform->varcreate_lsn; + + svar->typid = varform->vartype; + + get_typlenbyval(svar->typid, &svar->typlen, &svar->typbyval); + + svar->is_domain = (get_typtype(varform->vartype) == TYPTYPE_DOMAIN); + svar->domain_check_extra = NULL; + svar->domain_check_extra_lxid = InvalidLocalTransactionId; + + svar->isnull = true; + svar->freeval = false; + svar->value = (Datum) 0; + + svar->is_valid = true; + + svar->hashvalue = GetSysCacheHashValue1(VARIABLEOID, + ObjectIdGetDatum(varid)); + + ReleaseSysCache(tup); +} + +/* + * Assign some content to the session variable. It's copied to + * SVariableMemoryContext if necessary. + * + * If any error happens, the existing value shouldn't be modified. + */ +static void +set_session_variable(SVariable svar, Datum value, bool isnull) +{ + Datum newval; + SVariableData locsvar, + *_svar; + + Assert(svar); + Assert(!isnull || value == (Datum) 0); + + /* + * Use typbyval, typbylen from session variable only when they are + * trustable (the invalidation message was not accepted for this variable). + * When the variable is possibly invalid, force setup. + * + * Do not it against passed svar, it should be unchanged, when an assignment + * is not successful (the datumCopy can fail). + */ + if (!svar->is_valid) + { + setup_session_variable(&locsvar, svar->varid); + _svar = &locsvar; + } + else + _svar = svar; + + if (!isnull) + { + MemoryContext oldcxt = MemoryContextSwitchTo(SVariableMemoryContext); + + newval = datumCopy(value, _svar->typbyval, _svar->typlen); + + MemoryContextSwitchTo(oldcxt); + } + else + newval = value; + + free_session_variable_value(svar); + + /* We can overwrite old variable now. No error expected */ + if (svar != _svar) + memcpy(svar, _svar, sizeof(SVariableData)); + + svar->value = newval; + svar->isnull = isnull; + svar->freeval = newval != value; + + /* + * XXX While unlikely, an error here is possible. It wouldn't leak memory + * as the allocated chunk has already been correctly assigned to the + * session variable, but would contradict this function contract, which is + * that this function should either succeed or leave the current value + * untouched. + */ + elog(DEBUG1, "session variable \"%s.%s\" (oid:%u) has new value", + get_namespace_name(get_session_variable_namespace(svar->varid)), + get_session_variable_name(svar->varid), + svar->varid); +} + +/* + * Create the hash table for storing session variables. + */ +static void +create_sessionvars_hashtables(void) +{ + HASHCTL vars_ctl; + + Assert(!sessionvars); + + if (!SVariableMemoryContext) + { + /* Read sinval messages */ + CacheRegisterSyscacheCallback(VARIABLEOID, + pg_variable_cache_callback, + (Datum) 0); + + /* We need our own long lived memory context */ + SVariableMemoryContext = + AllocSetContextCreate(TopMemoryContext, + "session variables", + ALLOCSET_START_SMALL_SIZES); + } + + memset(&vars_ctl, 0, sizeof(vars_ctl)); + vars_ctl.keysize = sizeof(Oid); + vars_ctl.entrysize = sizeof(SVariableData); + vars_ctl.hcxt = SVariableMemoryContext; + + sessionvars = hash_create("Session variables", 64, &vars_ctl, + HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); +} + +/* + * Search a seesion variable in the hash table given its oid. If it + * doesn't exist, then insert it there. + * + * Caller is responsible for doing permission checks. + * + * As side effect this function acquires AccessShareLock on + * related session variable until the end of the transaction. + */ +static SVariable +get_session_variable(Oid varid) +{ + SVariable svar; + bool found; + + /* Protect used session variable against drop until transaction end */ + LockDatabaseObject(VariableRelationId, varid, 0, AccessShareLock); + + if (!sessionvars) + create_sessionvars_hashtables(); + + svar = (SVariable) hash_search(sessionvars, &varid, + HASH_ENTER, &found); + + if (found) + { + if (!svar->is_valid) + { + /* + * The variable can be flagged as invalid by processing invalidation + * message, but can be validated by recheck against system catalog. + */ + if (is_session_variable_valid(svar)) + svar->is_valid = true; + else + /* + * When the value cannot be validated, we can safely throw it. + * There is oid in catalog, but it is related to different + * session variable (different create_lsn). + */ + free_session_variable_value(svar); + } + } + else + svar->is_valid = false; + + /* + * Force setup for not yet initialized variables or variables that cannot + * be validated. + */ + if (!svar->is_valid) + { + setup_session_variable(svar, varid); + + elog(DEBUG1, "session variable \"%s.%s\" (oid:%u) has assigned entry in memory (emitted by READ)", + get_namespace_name(get_session_variable_namespace(varid)), + get_session_variable_name(varid), + varid); + } + + /* Ensure so returned data is still correct domain */ + if (svar->is_domain) + { + /* + * Store domain_check extra in TopTransactionContext. When we are in + * other transaction, the domain_check_extra cache is not valid + * anymore. + */ + if (svar->domain_check_extra_lxid != MyProc->lxid) + svar->domain_check_extra = NULL; + + domain_check(svar->value, svar->isnull, + svar->typid, &svar->domain_check_extra, + TopTransactionContext); + + svar->domain_check_extra_lxid = MyProc->lxid; + } + + return svar; +} + +/* + * Store the given value in an SVariable, and cache it if not already present. + * + * Caller is responsible for doing permission checks. + * + * As side effect this function acquires AccessShareLock on + * related session variable until the end of the transaction. + */ +void +SetSessionVariable(Oid varid, Datum value, bool isNull) +{ + SVariable svar; + bool found; + + /* Protect used session variable against drop until transaction end */ + LockDatabaseObject(VariableRelationId, varid, 0, AccessShareLock); + + if (!sessionvars) + create_sessionvars_hashtables(); + + svar = (SVariable) hash_search(sessionvars, &varid, + HASH_ENTER, &found); + + if (!found) + { + setup_session_variable(svar, varid); + + elog(DEBUG1, "session variable \"%s.%s\" (oid:%u) has assigned entry in memory (emitted by WRITE)", + get_namespace_name(get_session_variable_namespace(svar->varid)), + get_session_variable_name(svar->varid), + varid); + } + + /* + * This should either succeed or fail without changing the currently + * stored value. + */ + set_session_variable(svar, value, isNull); +} + +/* + * Wrapper around SetSessionVariable after checking for correct permission. + */ +void +SetSessionVariableWithSecurityCheck(Oid varid, Datum value, bool isNull) +{ + AclResult aclresult; + + /* + * Is caller allowed to update the session variable? + */ + aclresult = object_aclcheck(VariableRelationId, varid, GetUserId(), ACL_UPDATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, get_session_variable_name(varid)); + + SetSessionVariable(varid, value, isNull); +} + +/* + * Returns a copy of the value of the session variable (in current memory + * context) specified by its oid. Caller is responsible for doing permission + * checks. + */ +Datum +GetSessionVariable(Oid varid, bool *isNull, Oid *typid) +{ + SVariable svar; + Datum result; + + svar = get_session_variable(varid); + + /* + * Although svar is freshly validated in this point, the svar->is_valid can + * be false, due possible accepting invalidation message inside domain + * check. Now, the validation is done after lock, that can also accept + * invalidation message, so validation should be trustful. + * + * For now, we don't need to repeat validation. Only svar should be valid + * pointer. + */ + Assert(svar); + + *typid = svar->typid; + + /* force copy of non NULL value */ + if (!svar->isnull) + { + result = datumCopy(svar->value, svar->typbyval, svar->typlen); + *isNull = false; + } + else + { + result = (Datum) 0; + *isNull = true; + } + + return (Datum) result; +} + +/* + * Returns a copy of ths value of the session variable specified by its oid + * with a check of the expected type. Like previous GetSessionVariable, the + * caller is responsible for doing permission checks. + */ +Datum +GetSessionVariableWithTypeCheck(Oid varid, bool *isNull, Oid expected_typid) +{ + SVariable svar; + Datum result; + + svar = get_session_variable(varid); + + Assert(svar && svar->is_valid); + + if (expected_typid != svar->typid) + elog(ERROR, "type of variable \"%s.%s\" is different than expected", + get_namespace_name(get_session_variable_namespace(varid)), + get_session_variable_name(varid)); + + if (!svar->isnull) + { + result = datumCopy(svar->value, svar->typbyval, svar->typlen); + *isNull = false; + } + else + { + result = (Datum) 0; + *isNull = true; + } + + return (Datum) result; +} + +/* + * Assign result of evaluated expression to session variable + */ +void +ExecuteLetStmt(ParseState *pstate, + LetStmt *stmt, + ParamListInfo params, + QueryEnvironment *queryEnv, + QueryCompletion *qc) +{ + Query *query = castNode(Query, stmt->query); + List *rewritten; + DestReceiver *dest; + AclResult aclresult; + PlannedStmt *plan; + QueryDesc *queryDesc; + Oid varid = query->resultVariable; + + Assert(OidIsValid(varid)); + + /* + * Is it allowed to write to session variable? + */ + aclresult = object_aclcheck(VariableRelationId, varid, GetUserId(), ACL_UPDATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, get_session_variable_name(varid)); + + /* Create dest receiver for LET */ + dest = CreateDestReceiver(DestVariable); + SetVariableDestReceiverVarid(dest, varid); + + /* run rewriter - can be used for replacement of DEFAULT node */ + query = copyObject(query); + + rewritten = QueryRewrite(query); + + Assert(list_length(rewritten) == 1); + + query = linitial_node(Query, rewritten); + Assert(query->commandType == CMD_SELECT); + + /* plan the query */ + plan = pg_plan_query(query, pstate->p_sourcetext, + CURSOR_OPT_PARALLEL_OK, params); + + /* + * Use a snapshot with an updated command ID to ensure this query sees + * results of any previously executed queries. (This could only matter if + * the planner executed an allegedly-stable function that changed the + * database contents, but let's do it anyway to be parallel to the EXPLAIN + * code path.) + */ + PushCopiedSnapshot(GetActiveSnapshot()); + UpdateActiveSnapshotCommandId(); + + /* Create a QueryDesc, redirecting output to our tuple receiver */ + queryDesc = CreateQueryDesc(plan, pstate->p_sourcetext, + GetActiveSnapshot(), InvalidSnapshot, + dest, params, queryEnv, 0); + + /* call ExecutorStart to prepare the plan for execution */ + ExecutorStart(queryDesc, 0); + + /* + * Run the plan to completion. The result should be only one row. For an + * check too_many_rows we need to read two rows. + */ + ExecutorRun(queryDesc, ForwardScanDirection, 2L, true); + + /* save the rowcount if we're given a qc to fill */ + if (qc) + SetQueryCompletion(qc, CMDTAG_LET, queryDesc->estate->es_processed); + + /* and clean up */ + ExecutorFinish(queryDesc); + ExecutorEnd(queryDesc); + + FreeQueryDesc(queryDesc); + + PopActiveSnapshot(); +} + +/* + * pg_session_variables - designed for testing + * + * This is a function designed for testing and debugging. It returns the + * content of sessionvars as-is, and can therefore display entries about + * session variables that were dropped but for which this backend didn't + * process the shared invalidations yet. + */ +Datum +pg_session_variables(PG_FUNCTION_ARGS) +{ +#define NUM_PG_SESSION_VARIABLES_ATTS 8 + + elog(DEBUG1, "pg_session_variables start"); + + InitMaterializedSRF(fcinfo, 0); + + if (sessionvars) + { + ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo; + HASH_SEQ_STATUS status; + SVariable svar; + + hash_seq_init(&status, sessionvars); + + while ((svar = (SVariable) hash_seq_search(&status)) != NULL) + { + Datum values[NUM_PG_SESSION_VARIABLES_ATTS]; + bool nulls[NUM_PG_SESSION_VARIABLES_ATTS]; + HeapTuple tp; + bool var_is_valid = false; + + memset(values, 0, sizeof(values)); + memset(nulls, 0, sizeof(nulls)); + + values[0] = ObjectIdGetDatum(svar->varid); + values[3] = ObjectIdGetDatum(svar->typid); + + /* check if session variable is visible in system catalog */ + tp = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(svar->varid)); + + /* + * Sessionvars can hold data of variables removed from catalog, + * (and not purged) and then namespacename and name cannot be read + * from catalog. + */ + if (HeapTupleIsValid(tp)) + { + Form_pg_variable varform = (Form_pg_variable) GETSTRUCT(tp); + + /* When we see data in catalog */ + if (svar->create_lsn == varform->varcreate_lsn) + { + /* and when when these data are not out of date */ + values[1] = CStringGetTextDatum( + get_namespace_name(varform->varnamespace)); + + values[2] = CStringGetTextDatum(NameStr(varform->varname)); + values[4] = CStringGetTextDatum(format_type_be(svar->typid)); + values[5] = BoolGetDatum(false); + + values[6] = BoolGetDatum( + object_aclcheck(VariableRelationId, svar->varid, + GetUserId(), ACL_SELECT) == ACLCHECK_OK); + + values[7] = BoolGetDatum( + object_aclcheck(VariableRelationId, svar->varid, + GetUserId(), ACL_UPDATE) == ACLCHECK_OK); + + var_is_valid = true; + } + + ReleaseSysCache(tp); + } + + if (!var_is_valid) + { + /* + * When session variable was removed from catalog, but we + * haven't processed the invlidation yet. In this case, we can + * display only few oids. Other data are not available + * (without Form_pg_variable record), or can be lost (because + * there is not protection by dependency (more). + */ + nulls[1] = true; + nulls[2] = true; + nulls[4] = true; + nulls[6] = true; + nulls[7] = true; + + values[5] = BoolGetDatum(true); + } + + tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls); + } + } + + elog(DEBUG1, "pg_session_variables end"); + + return (Datum) 0; +} diff --git a/src/backend/executor/Makefile b/src/backend/executor/Makefile index 11118d0ce0..71248a34f2 100644 --- a/src/backend/executor/Makefile +++ b/src/backend/executor/Makefile @@ -76,6 +76,7 @@ OBJS = \ nodeWindowAgg.o \ nodeWorktablescan.o \ spi.o \ + svariableReceiver.o \ tqueue.o \ tstoreReceiver.o diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 2c62b0c9c8..5a4c3a4e16 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -34,6 +34,7 @@ #include "catalog/objectaccess.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "executor/execExpr.h" #include "executor/nodeSubplan.h" #include "funcapi.h" @@ -984,6 +985,82 @@ ExecInitExprRec(Expr *node, ExprState *state, scratch.d.param.paramtype = param->paramtype; ExprEvalPushStep(state, &scratch); break; + + case PARAM_VARIABLE: + { + int es_num_session_variables = 0; + SessionVariableValue *es_session_variables = NULL; + + if (state->parent && state->parent->state) + { + es_session_variables = state->parent->state->es_session_variables; + es_num_session_variables = state->parent->state->es_num_session_variables; + } + + if (es_session_variables) + { + SessionVariableValue *var; + + /* + * Use buffered session variables when the + * buffer with copied values is avaiable + * (standard query executor mode) + */ + + /* Parameter sanity checks. */ + if (param->paramid >= es_num_session_variables) + elog(ERROR, "paramid of PARAM_VARIABLE param is out of range"); + + var = &es_session_variables[param->paramid]; + + if (var->typid != param->paramtype) + elog(ERROR, "type of buffered value is different than PARAM_VARIABLE type"); + + /* + * In this case, pass the value like a + * constant. + */ + scratch.opcode = EEOP_CONST; + scratch.d.constval.value = var->value; + scratch.d.constval.isnull = var->isnull; + ExprEvalPushStep(state, &scratch); + } + else + { + AclResult aclresult; + Oid varid = param->paramvarid; + Oid vartype = param->paramtype; + + /* + * When the expression is evaluated directly + * without query executor start (plpgsql + * simple expr evaluation), then the array + * es_session_variables is null. In this case + * we need to use direct access to session + * variables. The values are not protected by + * using copy, but it is not problem (we don't + * need to emulate stability of the value). + * + * In this case we should to do aclcheck, + * because usual aclcheck from + * standard_ExecutorStart is not executed in + * this case. Fortunately it is just once per + * transaction. + */ + aclresult = object_aclcheck(VariableRelationId, varid, + GetUserId(), ACL_SELECT); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, + get_session_variable_name(varid)); + + scratch.opcode = EEOP_PARAM_VARIABLE; + scratch.d.vparam.varid = varid; + scratch.d.vparam.vartype = vartype; + ExprEvalPushStep(state, &scratch); + } + } + break; + case PARAM_EXTERN: /* diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 24c2b60c62..8605c0e4d1 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -59,6 +59,7 @@ #include "access/heaptoast.h" #include "catalog/pg_type.h" #include "commands/sequence.h" +#include "commands/session_variable.h" #include "executor/execExpr.h" #include "executor/nodeSubplan.h" #include "funcapi.h" @@ -449,6 +450,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_PARAM_EXEC, &&CASE_EEOP_PARAM_EXTERN, &&CASE_EEOP_PARAM_CALLBACK, + &&CASE_EEOP_PARAM_VARIABLE, &&CASE_EEOP_CASE_TESTVAL, &&CASE_EEOP_MAKE_READONLY, &&CASE_EEOP_IOCOERCE, @@ -1087,6 +1089,20 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_PARAM_VARIABLE) + { + /* + * Direct access to session variable (without buffering). Because + * returned value can be used (without an assignement) after the + * referenced session variables is updated, we have to use an copy + * of stored value every time. + */ + *op->resvalue = GetSessionVariableWithTypeCheck(op->d.vparam.varid, + op->resnull, + op->d.vparam.vartype); + EEO_NEXT(); + } + EEO_CASE(EEOP_CASE_TESTVAL) { /* diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index 4c5a7bbf62..ec42a11146 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -46,7 +46,9 @@ #include "catalog/namespace.h" #include "catalog/partition.h" #include "catalog/pg_publication.h" +#include "catalog/pg_variable.h" #include "commands/matview.h" +#include "commands/session_variable.h" #include "commands/trigger.h" #include "executor/execdebug.h" #include "executor/nodeSubplan.h" @@ -201,6 +203,63 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags) Assert(queryDesc->sourceText != NULL); estate->es_sourceText = queryDesc->sourceText; + /* + * The executor doesn't work with session variables directly. Values of + * related session variables are copied to dedicated array, and this array + * is passed to executor. + */ + if (queryDesc->num_session_variables > 0) + { + /* + * When a parallel query needs to access query parameters (including + * related session variables), then related session variables are + * restored (deserialized) in queryDesc already. So just push pointer + * of this array to executor's estate. + */ + Assert(IsParallelWorker()); + estate->es_session_variables = queryDesc->session_variables; + estate->es_num_session_variables = queryDesc->num_session_variables; + } + else if (queryDesc->plannedstmt->sessionVariables) + { + ListCell *lc; + int nSessionVariables; + int i = 0; + + /* + * In this case, the query uses session variables, but we have to + * prepare the array with passed values (of used session variables) + * first. + */ + Assert(!IsParallelWorker()); + nSessionVariables = list_length(queryDesc->plannedstmt->sessionVariables); + + /* Create the array used for passing values of used session variables */ + estate->es_session_variables = (SessionVariableValue *) + palloc(nSessionVariables * sizeof(SessionVariableValue)); + + /* Fill the array */ + foreach(lc, queryDesc->plannedstmt->sessionVariables) + { + AclResult aclresult; + Oid varid = lfirst_oid(lc); + + aclresult = object_aclcheck(VariableRelationId, varid, GetUserId(), ACL_SELECT); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, + get_session_variable_name(varid)); + + estate->es_session_variables[i].varid = varid; + estate->es_session_variables[i].value = GetSessionVariable(varid, + &estate->es_session_variables[i].isnull, + &estate->es_session_variables[i].typid); + + i++; + } + + estate->es_num_session_variables = nSessionVariables; + } + /* * Fill in the query environment, if any, from queryDesc. */ diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c index cc2b8ccab7..64e3b66e73 100644 --- a/src/backend/executor/execParallel.c +++ b/src/backend/executor/execParallel.c @@ -12,8 +12,9 @@ * workers and ensuring that their state generally matches that of the * leader; see src/backend/access/transam/README.parallel for details. * However, we must save and restore relevant executor state, such as - * any ParamListInfo associated with the query, buffer/WAL usage info, and - * the actual plan to be passed down to the worker. + * any ParamListInfo associated with the query, buffer/WAL usage info, + * session variables buffer, and the actual plan to be passed down to + * the worker. * * IDENTIFICATION * src/backend/executor/execParallel.c @@ -66,6 +67,7 @@ #define PARALLEL_KEY_QUERY_TEXT UINT64CONST(0xE000000000000008) #define PARALLEL_KEY_JIT_INSTRUMENTATION UINT64CONST(0xE000000000000009) #define PARALLEL_KEY_WAL_USAGE UINT64CONST(0xE00000000000000A) +#define PARALLEL_KEY_SESSION_VARIABLES UINT64CONST(0xE00000000000000B) #define PARALLEL_TUPLE_QUEUE_SIZE 65536 @@ -140,6 +142,12 @@ static bool ExecParallelRetrieveInstrumentation(PlanState *planstate, /* Helper function that runs in the parallel worker. */ static DestReceiver *ExecParallelGetReceiver(dsm_segment *seg, shm_toc *toc); +/* Helper functions that can pass values of session variables */ +static Size EstimateSessionVariables(EState *estate); +static void SerializeSessionVariables(EState *estate, char **start_address); +static SessionVariableValue *RestoreSessionVariables(char **start_address, + int *num_session_variables); + /* * Create a serialized representation of the plan to be sent to each worker. */ @@ -598,6 +606,7 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate, char *pstmt_data; char *pstmt_space; char *paramlistinfo_space; + char *session_variables_space; BufferUsage *bufusage_space; WalUsage *walusage_space; SharedExecutorInstrumentation *instrumentation = NULL; @@ -607,6 +616,7 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate, int instrumentation_len = 0; int jit_instrumentation_len = 0; int instrument_offset = 0; + int session_variables_len = 0; Size dsa_minsize = dsa_minimum_size(); char *query_string; int query_len; @@ -662,6 +672,11 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate, shm_toc_estimate_chunk(&pcxt->estimator, paramlistinfo_len); shm_toc_estimate_keys(&pcxt->estimator, 1); + /* Estimate space for serialized session variables. */ + session_variables_len = EstimateSessionVariables(estate); + shm_toc_estimate_chunk(&pcxt->estimator, session_variables_len); + shm_toc_estimate_keys(&pcxt->estimator, 1); + /* * Estimate space for BufferUsage. * @@ -756,6 +771,11 @@ ExecInitParallelPlan(PlanState *planstate, EState *estate, shm_toc_insert(pcxt->toc, PARALLEL_KEY_PARAMLISTINFO, paramlistinfo_space); SerializeParamList(estate->es_param_list_info, ¶mlistinfo_space); + /* Store serialized session variables. */ + session_variables_space = shm_toc_allocate(pcxt->toc, session_variables_len); + shm_toc_insert(pcxt->toc, PARALLEL_KEY_SESSION_VARIABLES, session_variables_space); + SerializeSessionVariables(estate, &session_variables_space); + /* Allocate space for each worker's BufferUsage; no need to initialize. */ bufusage_space = shm_toc_allocate(pcxt->toc, mul_size(sizeof(BufferUsage), pcxt->nworkers)); @@ -1403,6 +1423,7 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc) SharedJitInstrumentation *jit_instrumentation; int instrument_options = 0; void *area_space; + char *sessionvariable_space; dsa_area *area; ParallelWorkerContext pwcxt; @@ -1428,6 +1449,14 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc) area_space = shm_toc_lookup(toc, PARALLEL_KEY_DSA, false); area = dsa_attach_in_place(area_space, seg); + /* Reconstruct session variables. */ + sessionvariable_space = shm_toc_lookup(toc, + PARALLEL_KEY_SESSION_VARIABLES, + false); + queryDesc->session_variables = + RestoreSessionVariables(&sessionvariable_space, + &queryDesc->num_session_variables); + /* Start up the executor */ queryDesc->plannedstmt->jitFlags = fpes->jit_flags; ExecutorStart(queryDesc, fpes->eflags); @@ -1496,3 +1525,117 @@ ParallelQueryMain(dsm_segment *seg, shm_toc *toc) FreeQueryDesc(queryDesc); receiver->rDestroy(receiver); } + +/* + * Estimate the amount of space required to serialize a session variable. + */ +static Size +EstimateSessionVariables(EState *estate) +{ + int i; + Size sz = sizeof(int); + + if (estate->es_session_variables == NULL) + return sz; + + for (i = 0; i < estate->es_num_session_variables; i++) + { + SessionVariableValue *svarval; + Oid typeOid; + int16 typLen; + bool typByVal; + + svarval = &estate->es_session_variables[i]; + + typeOid = svarval->typid; + + sz = add_size(sz, sizeof(Oid)); /* space for type OID */ + + /* space for datum/isnull */ + Assert(OidIsValid(typeOid)); + get_typlenbyval(typeOid, &typLen, &typByVal); + + sz = add_size(sz, + datumEstimateSpace(svarval->value, svarval->isnull, typByVal, typLen)); + } + + return sz; +} + +/* + * Serialize a session variables buffer into caller-provided storage. + * + * We write the number of parameters first, as a 4-byte integer, and then + * write details for each parameter in turn. The details for each parameter + * consist of a 4-byte type OID, and then the datum as serialized by + * datumSerialize(). The caller is responsible for ensuring that there is + * enough storage to store the number of bytes that will be written; use + * EstimateSessionVariables to find out how many will be needed. + * *start_address is updated to point to the byte immediately following those + * written. + * + * RestoreSessionVariables can be used to recreate a session variable buffer + * based on the serialized representation; + */ +static void +SerializeSessionVariables(EState *estate, char **start_address) +{ + int nparams; + int i; + + /* Write number of parameters. */ + nparams = estate->es_num_session_variables; + memcpy(*start_address, &nparams, sizeof(int)); + *start_address += sizeof(int); + + /* Write each parameter in turn. */ + for (i = 0; i < nparams; i++) + { + SessionVariableValue *svarval; + Oid typeOid; + int16 typLen; + bool typByVal; + + svarval = &estate->es_session_variables[i]; + typeOid = svarval->typid; + + /* Write type OID. */ + memcpy(*start_address, &typeOid, sizeof(Oid)); + *start_address += sizeof(Oid); + + Assert(OidIsValid(typeOid)); + get_typlenbyval(typeOid, &typLen, &typByVal); + + datumSerialize(svarval->value, svarval->isnull, typByVal, typLen, + start_address); + } +} + +static SessionVariableValue * +RestoreSessionVariables(char **start_address, int *num_session_variables) +{ + SessionVariableValue *session_variables; + int i; + int nparams; + + memcpy(&nparams, *start_address, sizeof(int)); + *start_address += sizeof(int); + + *num_session_variables = nparams; + session_variables = (SessionVariableValue *) + palloc(nparams * sizeof(SessionVariableValue)); + + for (i = 0; i < nparams; i++) + { + SessionVariableValue *svarval = &session_variables[i]; + + /* Read type OID. */ + memcpy(&svarval->typid, *start_address, sizeof(Oid)); + *start_address += sizeof(Oid); + + /* Read datum/isnull. */ + svarval->value = datumRestore(start_address, &svarval->isnull); + } + + return session_variables; +} diff --git a/src/backend/executor/meson.build b/src/backend/executor/meson.build index 65f9457c9b..b34b383b07 100644 --- a/src/backend/executor/meson.build +++ b/src/backend/executor/meson.build @@ -64,6 +64,7 @@ backend_sources += files( 'nodeWindowAgg.c', 'nodeWorktablescan.c', 'spi.c', + 'svariableReceiver.c', 'tqueue.c', 'tstoreReceiver.c', ) diff --git a/src/backend/executor/spi.c b/src/backend/executor/spi.c index 33975687b3..097f22d666 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -2967,6 +2967,9 @@ _SPI_error_callback(void *arg) case RAW_PARSE_PLPGSQL_ASSIGN3: errcontext("PL/pgSQL assignment \"%s\"", query); break; + case RAW_PARSE_PLPGSQL_LET: + errcontext("LET statement \"%s\"", query); + break; default: errcontext("SQL statement \"%s\"", query); break; diff --git a/src/backend/executor/svariableReceiver.c b/src/backend/executor/svariableReceiver.c new file mode 100644 index 0000000000..db3a73c7e3 --- /dev/null +++ b/src/backend/executor/svariableReceiver.c @@ -0,0 +1,212 @@ +/*------------------------------------------------------------------------- + * + * svariableReceiver.c + * An implementation of DestReceiver that stores the result value in + * a session variable. + * + * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/executor/svariableReceiver.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" +#include "miscadmin.h" + +#include "access/detoast.h" +#include "catalog/pg_variable.h" +#include "commands/session_variable.h" +#include "executor/svariableReceiver.h" +#include "storage/lock.h" +#include "utils/builtins.h" +#include "utils/lsyscache.h" +#include "utils/syscache.h" + +typedef struct +{ + DestReceiver pub; + Oid varid; + Oid typid; + int32 typmod; + int typlen; + int slot_offset; + int rows; +} SVariableState; + +/* + * Prepare to receive tuples from executor. + */ +static void +svariableStartupReceiver(DestReceiver *self, int operation, TupleDesc typeinfo) +{ + SVariableState *myState = (SVariableState *) self; + int natts = typeinfo->natts; + int outcols = 0; + int i; + + /* Receiver should be initialized by SetVariableDestReceiverVarid */ + Assert(OidIsValid(myState->varid)); + + for (i = 0; i < natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(typeinfo, i); + Oid typid; + Oid collid; + int32 typmod; + + if (attr->attisdropped) + continue; + + if (++outcols > 1) + continue; + + get_session_variable_type_typmod_collid(myState->varid, + &typid, + &typmod, + &collid); + + /* + * double check - the type and typmod of target variable should be + * same as type and typmod of assignment expression. It should be, the + * expression is wrapped by cast to target type and typmod. + */ + if (attr->atttypid != typid || + (attr->atttypmod >= 0 && + attr->atttypmod != typmod)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("target session variable is of type %s" + " but expression is of type %s", + format_type_with_typemod(typid, typmod), + format_type_with_typemod(attr->atttypid, + attr->atttypmod)))); + + myState->typid = attr->atttypid; + myState->typmod = attr->atttypmod; + myState->typlen = attr->attlen; + myState->slot_offset = i; + } + + if (outcols != 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg_plural("assignment expression returned %d column", + "assignment expression returned %d columns", + outcols, + outcols))); + + myState->rows = 0; +} + +/* + * Receive a tuple from the executor and store it in session variable. + */ +static bool +svariableReceiveSlot(TupleTableSlot *slot, DestReceiver *self) +{ + SVariableState *myState = (SVariableState *) self; + Datum value; + bool isnull; + bool freeval = false; + + /* Make sure the tuple is fully deconstructed */ + slot_getallattrs(slot); + + value = slot->tts_values[myState->slot_offset]; + isnull = slot->tts_isnull[myState->slot_offset]; + + if (myState->typlen == -1 && !isnull && VARATT_IS_EXTERNAL(DatumGetPointer(value))) + { + value = PointerGetDatum(detoast_external_attr((struct varlena *) + DatumGetPointer(value))); + freeval = true; + } + + myState->rows += 1; + + if (myState->rows > 1) + ereport(ERROR, + (errcode(ERRCODE_TOO_MANY_ROWS), + errmsg("expression returned more than one row"))); + + SetSessionVariable(myState->varid, value, isnull); + + if (freeval) + pfree(DatumGetPointer(value)); + + return true; +} + +/* + * Clean up at end of an executor run + */ +static void +svariableShutdownReceiver(DestReceiver *self) +{ + if (((SVariableState *) self)->rows == 0) + ereport(ERROR, + (errcode(ERRCODE_NO_DATA_FOUND), + errmsg("expression returned no rows"))); +} + +/* + * Destroy receiver when done with it + */ +static void +svariableDestroyReceiver(DestReceiver *self) +{ + pfree(self); +} + +/* + * Initially create a DestReceiver object. + */ +DestReceiver * +CreateVariableDestReceiver(void) +{ + SVariableState *self = (SVariableState *) palloc0(sizeof(SVariableState)); + + self->pub.receiveSlot = svariableReceiveSlot; + self->pub.rStartup = svariableStartupReceiver; + self->pub.rShutdown = svariableShutdownReceiver; + self->pub.rDestroy = svariableDestroyReceiver; + self->pub.mydest = DestVariable; + + /* + * Private fields will be set by SetVariableDestReceiverVarid and + * svariableStartupReceiver. + */ + return (DestReceiver *) self; +} + +/* + * Set parameters for a VariableDestReceiver. + * Should be called right after creating the DestReceiver. + */ +void +SetVariableDestReceiverVarid(DestReceiver *self, Oid varid) +{ + SVariableState *myState = (SVariableState *) self; + LOCKTAG locktag PG_USED_FOR_ASSERTS_ONLY; + + Assert(myState->pub.mydest == DestVariable); + Assert(OidIsValid(varid)); + Assert(SearchSysCacheExists1(VARIABLEOID, varid)); + +#ifdef USE_ASSERT_CHECKING + + SET_LOCKTAG_OBJECT(locktag, + MyDatabaseId, + VariableRelationId, + varid, + 0); + + Assert(LockHeldByMe(&locktag, AccessShareLock)); + +#endif + + myState->varid = varid; +} diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index 00d7b8110b..1353777b1a 100644 --- a/src/backend/jit/llvm/llvmjit_expr.c +++ b/src/backend/jit/llvm/llvmjit_expr.c @@ -1073,6 +1073,12 @@ llvm_compile_expr(ExprState *state) LLVMBuildBr(b, opblocks[opno + 1]); break; + case EEOP_PARAM_VARIABLE: + build_EvalXFunc(b, mod, "ExecEvalParamVariable", + v_state, op, v_econtext); + LLVMBuildBr(b, opblocks[opno + 1]); + break; + case EEOP_PARAM_CALLBACK: { LLVMTypeRef v_functype; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index c03f4f23e2..87986793ab 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -4101,6 +4101,16 @@ raw_expression_tree_walker_impl(Node *node, return true; } break; + case T_LetStmt: + { + LetStmt *stmt = (LetStmt *) node; + + if (WALK(stmt->target)) + return true; + if (WALK(stmt->query)) + return true; + } + break; case T_PLAssignStmt: { PLAssignStmt *stmt = (PLAssignStmt *) node; diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index 44efb1f4eb..978ad3e79d 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -321,6 +321,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, glob->lastPlanNodeId = 0; glob->transientPlan = false; glob->dependsOnRole = false; + glob->sessionVariables = NIL; /* * Assess whether it's feasible to use parallel mode for this query. We @@ -556,6 +557,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions, result->paramExecTypes = glob->paramExecTypes; /* utilityStmt should be null, but we might as well copy it */ result->utilityStmt = parse->utilityStmt; + result->sessionVariables = glob->sessionVariables; result->stmt_location = parse->stmt_location; result->stmt_len = parse->stmt_len; @@ -722,6 +724,12 @@ subquery_planner(PlannerGlobal *glob, Query *parse, */ pull_up_subqueries(root); + /* + * Check if some subquery uses session variable. Flag hasSessionVariables + * should be true if query or some subquery uses any session variable. + */ + pull_up_has_session_variables(root); + /* * If this is a simple UNION ALL query, flatten it into an appendrel. We * do this now because it requires applying pull_up_subqueries to the leaf diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c index 97fa561e4e..eb153bd267 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -210,7 +210,9 @@ static List *set_returning_clause_references(PlannerInfo *root, static List *set_windowagg_runcondition_references(PlannerInfo *root, List *runcondition, Plan *plan); - +static bool pull_up_has_session_variables_walker(Node *node, + PlannerInfo *root); +static void record_plan_variable_dependency(PlannerInfo *root, Oid varid); /***************************************************************************** * @@ -1293,6 +1295,50 @@ set_plan_refs(PlannerInfo *root, Plan *plan, int rtoffset) return plan; } +/* + * Search usage of session variables in subqueries + */ +void +pull_up_has_session_variables(PlannerInfo *root) +{ + Query *query = root->parse; + + if (query->hasSessionVariables) + { + root->hasSessionVariables = true; + } + else + { + (void) query_tree_walker(query, + pull_up_has_session_variables_walker, + (void *) root, 0); + } +} + +static bool +pull_up_has_session_variables_walker(Node *node, PlannerInfo *root) +{ + if (node == NULL) + return false; + if (IsA(node, Query)) + { + Query *query = (Query *) node; + + if (query->hasSessionVariables) + { + root->hasSessionVariables = true; + return false; + } + + /* Recurse into subselects */ + return query_tree_walker((Query *) node, + pull_up_has_session_variables_walker, + (void *) root, 0); + } + return expression_tree_walker(node, pull_up_has_session_variables_walker, + (void *) root); +} + /* * set_indexonlyscan_references * Do set_plan_references processing on an IndexOnlyScan @@ -1941,8 +1987,9 @@ copyVar(Var *var) * This is code that is common to all variants of expression-fixing. * We must look up operator opcode info for OpExpr and related nodes, * add OIDs from regclass Const nodes into root->glob->relationOids, and - * add PlanInvalItems for user-defined functions into root->glob->invalItems. - * We also fill in column index lists for GROUPING() expressions. + * add PlanInvalItems for user-defined functions and session variables into + * root->glob->invalItems. We also fill in column index lists for GROUPING() + * expressions. * * We assume it's okay to update opcode info in-place. So this could possibly * scribble on the planner's input data structures, but it's OK. @@ -2032,15 +2079,28 @@ fix_expr_common(PlannerInfo *root, Node *node) g->cols = cols; } } + else if (IsA(node, Param)) + { + Param *p = (Param *) node; + + if (p->paramkind == PARAM_VARIABLE) + record_plan_variable_dependency(root, p->paramvarid); + } } /* * fix_param_node * Do set_plan_references processing on a Param + * Collect session variables list and replace variable oid by + * index to collected list. * * If it's a PARAM_MULTIEXPR, replace it with the appropriate Param from * root->multiexpr_params; otherwise no change is needed. * Just for paranoia's sake, we make a copy of the node in either case. + * + * If it's a PARAM_VARIABLE, then we collect used session variables in + * list root->glob->sessionVariable. We should to assign Param paramvarid + * too, and it is position of related session variable in mentioned list. */ static Node * fix_param_node(PlannerInfo *root, Param *p) @@ -2059,6 +2119,41 @@ fix_param_node(PlannerInfo *root, Param *p) elog(ERROR, "unexpected PARAM_MULTIEXPR ID: %d", p->paramid); return copyObject(list_nth(params, colno - 1)); } + + if (p->paramkind == PARAM_VARIABLE) + { + ListCell *lc; + int n = 0; + bool found = false; + + /* We will modify object */ + p = (Param *) copyObject(p); + + /* + * Now, we can actualize list of session variables, and we can + * complete paramid parameter. + */ + foreach(lc, root->glob->sessionVariables) + { + if (lfirst_oid(lc) == p->paramvarid) + { + p->paramid = n; + found = true; + break; + } + n += 1; + } + + if (!found) + { + root->glob->sessionVariables = lappend_oid(root->glob->sessionVariables, + p->paramvarid); + p->paramid = n; + } + + return (Node *) p; + } + return (Node *) copyObject(p); } @@ -2120,7 +2215,10 @@ fix_alternative_subplan(PlannerInfo *root, AlternativeSubPlan *asplan, * replacing Aggref nodes that should be replaced by initplan output Params, * choosing the best implementation for AlternativeSubPlans, * looking up operator opcode info for OpExpr and related nodes, - * and adding OIDs from regclass Const nodes into root->glob->relationOids. + * adding OIDs from regclass Const nodes into root->glob->relationOids, + * and assigning paramvarid to PARAM_VARIABLE params, and collecting + * of OIDs of session variables in root->glob->sessionVariables list + * (paramvarid is an position of related session variable in this list). * * 'node': the expression to be modified * 'rtoffset': how much to increment varnos by @@ -2142,7 +2240,8 @@ fix_scan_expr(PlannerInfo *root, Node *node, int rtoffset, double num_exec) root->multiexpr_params != NIL || root->glob->lastPHId != 0 || root->minmax_aggs != NIL || - root->hasAlternativeSubPlans) + root->hasAlternativeSubPlans || + root->hasSessionVariables) { return fix_scan_expr_mutator(node, &context); } @@ -3498,6 +3597,25 @@ record_plan_type_dependency(PlannerInfo *root, Oid typid) } } +/* + * Record dependency on a session variable. The variable can be used as a + * session variable in expression list, or target of LET statement. + */ +static void +record_plan_variable_dependency(PlannerInfo *root, Oid varid) +{ + PlanInvalItem *inval_item = makeNode(PlanInvalItem); + + /* paramid is still session variable id */ + inval_item->cacheId = VARIABLEOID; + inval_item->hashValue = GetSysCacheHashValue1(VARIABLEOID, + ObjectIdGetDatum(varid)); + + /* Append this variable to global, register dependency */ + root->glob->invalItems = lappend(root->glob->invalItems, + inval_item); +} + /* * extract_query_dependencies * Given a rewritten, but not yet planned, query or queries @@ -3565,10 +3683,11 @@ extract_query_dependencies_walker(Node *node, PlannerInfo *context) if (query->commandType == CMD_UTILITY) { /* - * Ignore utility statements, except those (such as EXPLAIN) that - * contain a parsed-but-not-planned query. + * Ignore utility statements, except those (such as EXPLAIN or + * LET) */ query = UtilityContainsQuery(query->utilityStmt); + if (query == NULL) return false; } @@ -3589,6 +3708,10 @@ extract_query_dependencies_walker(Node *node, PlannerInfo *context) lappend_oid(context->glob->relationOids, rte->relid); } + /* Record dependency on targer variable of LET command */ + if (OidIsValid(query->resultVariable)) + record_plan_variable_dependency(context, query->resultVariable); + /* And recurse into the query's subexpressions */ return query_tree_walker(query, extract_query_dependencies_walker, (void *) context, 0); diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 73ff40721c..91df8b1578 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -1265,6 +1265,9 @@ pull_up_simple_subquery(PlannerInfo *root, Node *jtnode, RangeTblEntry *rte, /* If subquery had any RLS conditions, now main query does too */ parse->hasRowSecurity |= subquery->hasRowSecurity; + /* If subquery had session variables, now main query does too */ + parse->hasSessionVariables |= subquery->hasSessionVariables; + /* * subquery won't be pulled up if it hasAggs, hasWindowFuncs, or * hasTargetSRFs, so no work needed on those flags diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index da258968b8..d92e649043 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -26,6 +26,7 @@ #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "commands/session_variable.h" #include "executor/executor.h" #include "executor/functions.h" #include "funcapi.h" @@ -840,16 +841,17 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context) /* * We can't pass Params to workers at the moment either, so they are also - * parallel-restricted, unless they are PARAM_EXTERN Params or are - * PARAM_EXEC Params listed in safe_param_ids, meaning they could be - * either generated within workers or can be computed by the leader and - * then their value can be passed to workers. + * parallel-restricted, unless they are PARAM_EXTERN or PARAM_VARIABLE + * Params or are PARAM_EXEC Params listed in safe_param_ids, meaning they + * could be either generated within workers or can be computed by the + * leader and then their value can be passed to workers. */ else if (IsA(node, Param)) { Param *param = (Param *) node; - if (param->paramkind == PARAM_EXTERN) + if (param->paramkind == PARAM_EXTERN || + param->paramkind == PARAM_VARIABLE) return false; if (param->paramkind != PARAM_EXEC || @@ -2306,6 +2308,7 @@ convert_saop_to_hashed_saop_walker(Node *node, void *context) * value of the Param. * 2. Fold stable, as well as immutable, functions to constants. * 3. Reduce PlaceHolderVar nodes to their contained expressions. + * 4. Current value of session variable can be used for estimation too. *-------------------- */ Node * @@ -2428,6 +2431,29 @@ eval_const_expressions_mutator(Node *node, } } } + else if (param->paramkind == PARAM_VARIABLE && + context->estimate) + { + int16 typLen; + bool typByVal; + Datum pval; + bool isnull; + + get_typlenbyval(param->paramtype, + &typLen, &typByVal); + + pval = GetSessionVariableWithTypeCheck(param->paramvarid, + &isnull, + param->paramtype); + + return (Node *) makeConst(param->paramtype, + param->paramtypmod, + param->paramcollid, + (int) typLen, + pval, + isnull, + typByVal); + } /* * Not replaceable, so just copy the Param (no need to @@ -4815,21 +4841,43 @@ substitute_actual_parameters_mutator(Node *node, { if (node == NULL) return NULL; + + /* + * SQL functions can contain two different kind of params. The nodes with + * paramkind PARAM_EXTERN are related to function's arguments (and should + * be replaced in this step), because this is how we apply the function's + * arguments for an expression. + * + * The nodes with paramkind PARAM_VARIABLE are related to usage of session + * variables. The values of session variables are not passed to expression + * by expression arguments, so it should not be replaced here by + * function's arguments. Although we could substitute params related to + * immutable session variables with default expression by this default + * expression, it is safer to not do it. This way we don't have to run + * security checks here. There can be some performance loss, but an access + * to session variable is fast (and the result of default expression is + * immediately materialized and can be reused). + */ if (IsA(node, Param)) { Param *param = (Param *) node; - if (param->paramkind != PARAM_EXTERN) + if (param->paramkind != PARAM_EXTERN && + param->paramkind != PARAM_VARIABLE) elog(ERROR, "unexpected paramkind: %d", (int) param->paramkind); - if (param->paramid <= 0 || param->paramid > context->nargs) - elog(ERROR, "invalid paramid: %d", param->paramid); - /* Count usage of parameter */ - context->usecounts[param->paramid - 1]++; + if (param->paramkind == PARAM_EXTERN) + { + if (param->paramid <= 0 || param->paramid > context->nargs) + elog(ERROR, "invalid paramid: %d", param->paramid); + + /* Count usage of parameter */ + context->usecounts[param->paramid - 1]++; - /* Select the appropriate actual arg and replace the Param with it */ - /* We don't need to copy at this time (it'll get done later) */ - return list_nth(context->args, param->paramid - 1); + /* Select the appropriate actual arg and replace the Param with it */ + /* We don't need to copy at this time (it'll get done later) */ + return list_nth(context->args, param->paramid - 1); + } } return expression_tree_mutator(node, substitute_actual_parameters_mutator, (void *) context); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 7a1dfb6364..632b71a65c 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -25,9 +25,12 @@ #include "postgres.h" #include "access/sysattr.h" +#include "catalog/namespace.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/defrem.h" +#include "commands/session_variable.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -52,6 +55,7 @@ #include "utils/backend_status.h" #include "utils/builtins.h" #include "utils/guc.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -85,6 +89,8 @@ static Query *transformCreateTableAsStmt(ParseState *pstate, CreateTableAsStmt *stmt); static Query *transformCallStmt(ParseState *pstate, CallStmt *stmt); +static Query *transformLetStmt(ParseState *pstate, + LetStmt *stmt); static void transformLockingClause(ParseState *pstate, Query *qry, LockingClause *lc, bool pushedDown); #ifdef RAW_EXPRESSION_COVERAGE_TEST @@ -328,6 +334,7 @@ transformStmt(ParseState *pstate, Node *parseTree) case T_UpdateStmt: case T_DeleteStmt: case T_MergeStmt: + case T_LetStmt: (void) test_raw_expression_coverage(parseTree, NULL); break; default: @@ -406,6 +413,11 @@ transformStmt(ParseState *pstate, Node *parseTree) (CallStmt *) parseTree); break; + case T_LetStmt: + result = transformLetStmt(pstate, + (LetStmt *) parseTree); + break; + default: /* @@ -457,6 +469,7 @@ stmt_requires_parse_analysis(RawStmt *parseTree) case T_SelectStmt: case T_ReturnStmt: case T_PLAssignStmt: + case T_LetStmt: result = true; break; @@ -565,6 +578,8 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) qry->hasTargetSRFs = pstate->p_hasTargetSRFs; qry->hasAggs = pstate->p_hasAggs; + qry->hasSessionVariables = pstate->p_hasSessionVariables; + assign_query_collations(pstate, qry); /* this must be done after collations, for reliable comparison of exprs */ @@ -989,6 +1004,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) qry->hasTargetSRFs = pstate->p_hasTargetSRFs; qry->hasSubLinks = pstate->p_hasSubLinks; + qry->hasSessionVariables = pstate->p_hasSessionVariables; assign_query_collations(pstate, qry); @@ -1443,6 +1459,7 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) qry->hasWindowFuncs = pstate->p_hasWindowFuncs; qry->hasTargetSRFs = pstate->p_hasTargetSRFs; qry->hasAggs = pstate->p_hasAggs; + qry->hasSessionVariables = pstate->p_hasSessionVariables; foreach(l, stmt->lockingClause) { @@ -1669,12 +1686,262 @@ transformValuesClause(ParseState *pstate, SelectStmt *stmt) qry->jointree = makeFromExpr(pstate->p_joinlist, NULL); qry->hasSubLinks = pstate->p_hasSubLinks; + qry->hasSessionVariables = pstate->p_hasSessionVariables; assign_query_collations(pstate, qry); return qry; } +/* + * transformLetStmt - + * transform an Let Statement + */ +static Query * +transformLetStmt(ParseState *pstate, LetStmt *stmt) +{ + Query *query; + Query *result; + List *exprList = NIL; + List *exprListCoer = NIL; + ListCell *lc; + ListCell *indirection_head = NULL; + Query *selectQuery; + Oid varid; + char *attrname = NULL; + bool not_unique; + bool is_rowtype; + Oid typid; + int32 typmod; + Oid collid; + AclResult aclresult; + List *names = NULL; + int indirection_start; + int i = 0; + + /* There can't be any outer WITH to worry about */ + Assert(pstate->p_ctenamespace == NIL); + + names = NamesFromList(stmt->target); + + /* + * The AccessShareLock is created on related session variable. The lock + * will be kept for the whole transaction. + */ + varid = IdentifyVariable(names, &attrname, ¬_unique, false); + if (not_unique) + ereport(ERROR, + (errcode(ERRCODE_AMBIGUOUS_PARAMETER), + errmsg("target \"%s\" of LET command is ambiguous", + NameListToString(names)), + parser_errposition(pstate, stmt->location))); + + if (!OidIsValid(varid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("session variable \"%s\" doesn't exist", + NameListToString(names)), + parser_errposition(pstate, stmt->location))); + + /* + * Calculate start of possible position of an indirection in list, and + * when it is inside the list, store pointer on first node of indirection. + */ + indirection_start = list_length(names) - (attrname ? 1 : 0); + if (list_length(stmt->target) > indirection_start) + indirection_head = list_nth_cell(stmt->target, indirection_start); + + get_session_variable_type_typmod_collid(varid, &typid, &typmod, &collid); + + is_rowtype = type_is_rowtype(typid); + + if (attrname && !is_rowtype) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("type \"%s\" of target session variable \"%s.%s\" is not a composite type", + format_type_be(typid), + get_namespace_name(get_session_variable_namespace(varid)), + get_session_variable_name(varid)), + parser_errposition(pstate, stmt->location))); + + aclresult = object_aclcheck(VariableRelationId, varid, GetUserId(), ACL_UPDATE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, NameListToString(names)); + + /* + * The behavior of EXPR_KIND_LET_TARGET is almost same like + * EXPR_KIND_UPDATE_TARGET. EXPR_KIND_LET_TARGET was introduced to be + * possible to raise more correct errors, where the command LET is + * mentioned (instead UPDATE command). + */ + pstate->p_expr_kind = EXPR_KIND_LET_TARGET; + + selectQuery = transformStmt(pstate, stmt->query); + + /* The grammar should have produced a SELECT */ + Assert(IsA(selectQuery, Query) && selectQuery->commandType == CMD_SELECT); + + /*---------- + * Generate an expression list for the LET that selects all the + * non-resjunk columns from the subquery. + *---------- + */ + exprList = NIL; + foreach(lc, selectQuery->targetList) + { + TargetEntry *tle = (TargetEntry *) lfirst(lc); + + if (tle->resjunk) + continue; + + exprList = lappend(exprList, tle->expr); + } + + /* don't allow multicolumn result */ + if (list_length(exprList) != 1) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg_plural("assignment expression returned %d column", + "assignment expression returned %d columns", + list_length(exprList), + list_length(exprList)), + parser_errposition(pstate, + exprLocation((Node *) exprList)))); + + exprListCoer = NIL; + + foreach(lc, exprList) + { + Expr *expr = (Expr *) lfirst(lc); + Expr *coerced_expr; + Param *param; + Oid exprtypid; + + if (IsA(expr, Const) && ((Const *) expr)->constisnull) + { + /* use known type for NULL value */ + expr = (Expr *) makeNullConst(typid, typmod, collid); + } + + /* now we can read type of expression */ + exprtypid = exprType((Node *) expr); + + param = makeNode(Param); + param->paramkind = PARAM_VARIABLE; + param->paramvarid = varid; + param->paramtype = typid; + param->paramtypmod = typmod; + + if (indirection_head) + { + bool targetIsArray; + char *targetName; + + targetName = get_session_variable_name(varid); + targetIsArray = OidIsValid(get_element_type(typid)); + + pstate->p_hasSessionVariables = true; + + coerced_expr = (Expr *) + transformAssignmentIndirection(pstate, + (Node *) param, + targetName, + targetIsArray, + typid, + typmod, + InvalidOid, + stmt->target, + indirection_head, + (Node *) expr, + COERCION_PLPGSQL, + stmt->location); + } + else + coerced_expr = (Expr *) + coerce_to_target_type(pstate, + (Node *) expr, + exprtypid, + typid, typmod, + COERCION_ASSIGNMENT, + COERCE_IMPLICIT_CAST, + stmt->location); + + if (coerced_expr == NULL) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("variable \"%s.%s\" is of type %s," + " but expression is of type %s", + get_namespace_name(get_session_variable_namespace(varid)), + get_session_variable_name(varid), + format_type_be(typid), + format_type_be(exprtypid)), + errhint("You will need to rewrite or cast the expression."), + parser_errposition(pstate, exprLocation((Node *) expr)))); + + exprListCoer = lappend(exprListCoer, coerced_expr); + } + + /* + * Generate query's target list using the computed list of expressions. + */ + query = makeNode(Query); + query->commandType = CMD_SELECT; + + foreach(lc, exprListCoer) + { + Expr *expr = (Expr *) lfirst(lc); + TargetEntry *tle; + + tle = makeTargetEntry(expr, + i + 1, + FigureColname((Node *) expr), + false); + query->targetList = lappend(query->targetList, tle); + } + + /* done building the range table and jointree */ + query->rtable = pstate->p_rtable; + query->jointree = makeFromExpr(pstate->p_joinlist, NULL); + + query->hasTargetSRFs = pstate->p_hasTargetSRFs; + query->hasSubLinks = pstate->p_hasSubLinks; + query->hasSessionVariables = pstate->p_hasSessionVariables; + + /* This is top query */ + query->canSetTag = true; + + /* + * Save target session variable id. This value is used multiple times: by + * query rewriter (for getting related defexpr), by planner (for acquiring + * AccessShareLock on target variable), and by executor (we need to know + * target variable id). + */ + query->resultVariable = varid; + + assign_query_collations(pstate, query); + + stmt->query = (Node *) query; + + /* + * Inside PL/pgSQL we don't want to execute LET statement as utility + * command, because it disallow to execute expression as simple + * expression. So for PL/pgSQL we have extra path, and we return SELECT. + * Then it can be executed by exec_eval_expr. Result is dirrectly assigned + * to target session variable inside PL/pgSQL LET statement handler. This + * is extra code, extra path, but possibility to get faster execution is + * too attractive. + */ + if (stmt->plpgsql_mode) + return query; + + /* represent the command as a utility Query */ + result = makeNode(Query); + result->commandType = CMD_UTILITY; + result->utilityStmt = (Node *) stmt; + + return result; +} + /* * transformSetOperationStmt - * transforms a set-operations tree @@ -1919,6 +2186,7 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) qry->hasWindowFuncs = pstate->p_hasWindowFuncs; qry->hasTargetSRFs = pstate->p_hasTargetSRFs; qry->hasAggs = pstate->p_hasAggs; + qry->hasSessionVariables = pstate->p_hasSessionVariables; foreach(l, lockingClause) { @@ -2459,6 +2727,7 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt) qry->hasTargetSRFs = pstate->p_hasTargetSRFs; qry->hasSubLinks = pstate->p_hasSubLinks; + qry->hasSessionVariables = pstate->p_hasSessionVariables; assign_query_collations(pstate, qry); @@ -2642,9 +2911,15 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt) /* * Transform the target reference. Typically we will get back a Param * node, but there's no reason to be too picky about its type. + * + * The session variables should not be used as target of PL/pgSQL assign + * statement. So we should to use special parser expr kind, that disallow + * usage of session variables. This block unwanted (in this context) + * possible warning so target PL/pgSQL's variable shadows some session + * variable. */ target = transformExpr(pstate, (Node *) cref, - EXPR_KIND_UPDATE_TARGET); + EXPR_KIND_ASSIGN_VARIABLE); targettype = exprType(target); targettypmod = exprTypmod(target); targetcollation = exprCollation(target); @@ -2686,6 +2961,10 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt *stmt) */ type_id = exprType((Node *) tle->expr); + /* + * For indirection processing and additional casts we can use expr_kind + * EXPR_KIND_UPDATE_TARGET + */ pstate->p_expr_kind = EXPR_KIND_UPDATE_TARGET; if (indirection) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index a794ca90b1..f12f0481ba 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -306,7 +306,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DropTransformStmt DropUserMappingStmt ExplainStmt FetchStmt GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt - ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt + LetStmt ListenStmt LoadStmt LockStmt MergeStmt NotifyStmt ExplainableStmt PreparableStmt CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRoleStmt RuleActionStmt RuleActionStmtOrEmpty RuleStmt @@ -454,6 +454,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); TriggerTransitions TriggerReferencing vacuum_relation_list opt_vacuum_relation_list drop_option_list pub_obj_list + let_target %type opt_routine_body %type group_clause @@ -729,7 +730,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); KEY KEYS LABEL LANGUAGE LARGE_P LAST_P LATERAL_P - LEADING LEAKPROOF LEAST LEFT LEVEL LIKE LIMIT LISTEN LOAD LOCAL + LEADING LEAKPROOF LEAST LEFT LET LEVEL LIKE LIMIT LISTEN LOAD LOCAL LOCALTIME LOCALTIMESTAMP LOCATION LOCK_P LOCKED LOGGED MAPPING MATCH MATCHED MATERIALIZED MAXVALUE MERGE METHOD @@ -808,6 +809,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %token MODE_PLPGSQL_ASSIGN1 %token MODE_PLPGSQL_ASSIGN2 %token MODE_PLPGSQL_ASSIGN3 +%token MODE_PLPGSQL_LET /* Precedence: lowest to highest */ @@ -923,6 +925,13 @@ parse_toplevel: pg_yyget_extra(yyscanner)->parsetree = list_make1(makeRawStmt((Node *) n, 0)); } + | MODE_PLPGSQL_LET LetStmt + { + LetStmt *n = (LetStmt *) $2; + n->plpgsql_mode = true; + pg_yyget_extra(yyscanner)->parsetree = + list_make1(makeRawStmt((Node *) n, 0)); + } ; /* @@ -1064,6 +1073,7 @@ stmt: | ImportForeignSchemaStmt | IndexStmt | InsertStmt + | LetStmt | ListenStmt | RefreshMatViewStmt | LoadStmt @@ -11946,7 +11956,8 @@ ExplainableStmt: | CreateAsStmt | CreateMatViewStmt | RefreshMatViewStmt - | ExecuteStmt /* by default all are $$=$1 */ + | ExecuteStmt + | LetStmt /* by default all are $$=$1 */ ; /***************************************************************************** @@ -11976,7 +11987,8 @@ PreparableStmt: | InsertStmt | UpdateStmt | DeleteStmt - | MergeStmt /* by default all are $$=$1 */ + | MergeStmt + | LetStmt /* by default all are $$=$1 */ ; /***************************************************************************** @@ -12572,6 +12584,47 @@ opt_hold: /* EMPTY */ { $$ = 0; } | WITHOUT HOLD { $$ = 0; } ; +/***************************************************************************** + * + * QUERY: + * LET STATEMENTS + * + *****************************************************************************/ +LetStmt: LET let_target '=' a_expr + { + LetStmt *n = makeNode(LetStmt); + SelectStmt *select; + ResTarget *res; + + n->target = $2; + + select = makeNode(SelectStmt); + res = makeNode(ResTarget); + + /* Create target list for implicit query */ + res->name = NULL; + res->indirection = NIL; + res->val = (Node *) $4; + res->location = @4; + + select->targetList = list_make1(res); + n->query = (Node *) select; + + n->location = @2; + $$ = (Node *) n; + } + ; + +let_target: + ColId opt_indirection + { + $$ = list_make1(makeString($1)); + if ($2) + $$ = list_concat($$, + check_indirection($2, yyscanner)); + } + ; + /***************************************************************************** * * QUERY: @@ -17255,6 +17308,7 @@ unreserved_keyword: | LARGE_P | LAST_P | LEAKPROOF + | LET | LEVEL | LISTEN | LOAD @@ -17844,6 +17898,7 @@ bare_label_keyword: | LEAKPROOF | LEAST | LEFT + | LET | LEVEL | LIKE | LISTEN diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 85cd47b7ae..30269708d6 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -564,6 +564,11 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) errkind = true; break; + case EXPR_KIND_ASSIGN_VARIABLE: + case EXPR_KIND_LET_TARGET: + errkind = true; + break; + /* * There is intentionally no default: case here, so that the * compiler will warn if we add a new ParseExprKind without @@ -953,6 +958,10 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_ASSIGN_VARIABLE: + case EXPR_KIND_LET_TARGET: + errkind = true; + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_cte.c b/src/backend/parser/parse_cte.c index c5b1a49725..35aa070571 100644 --- a/src/backend/parser/parse_cte.c +++ b/src/backend/parser/parse_cte.c @@ -133,6 +133,14 @@ transformWithClause(ParseState *pstate, WithClause *withClause) errmsg("MERGE not supported in WITH query"), parser_errposition(pstate, cte->location)); + /* LET is allowed by parser, but not supported. Reject for now */ + if (IsA(cte->ctequery, LetStmt)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("LET not supported in WITH query"), + parser_errposition(pstate, cte->location)); + + for_each_cell(rest, withClause->ctes, lnext(withClause->ctes, lc)) { CommonTableExpr *cte2 = (CommonTableExpr *) lfirst(rest); diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 0ab65dd6cc..b670ec7129 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -18,6 +18,7 @@ #include "catalog/pg_aggregate.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/dbcommands.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -34,16 +35,18 @@ #include "parser/parse_relation.h" #include "parser/parse_target.h" #include "parser/parse_type.h" +#include "storage/lmgr.h" #include "utils/builtins.h" #include "utils/date.h" #include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/timestamp.h" +#include "utils/typcache.h" #include "utils/xml.h" /* GUC parameters */ bool Transform_null_equals = false; - +bool session_variables_ambiguity_warning = false; static Node *transformExprRecurse(ParseState *pstate, Node *expr); static Node *transformParamRef(ParseState *pstate, ParamRef *pref); @@ -98,6 +101,9 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree, int location); static Node *make_nulltest_from_distinct(ParseState *pstate, A_Expr *distincta, Node *arg); +static Node *makeParamSessionVariable(ParseState *pstate, + Oid varid, Oid typid, int32 typmod, Oid collid, + char *attrname, int location); /* @@ -482,6 +488,89 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) return result; } +/* + * Returns true if the given expression kind is valid for session variables + * Session variables can be used everywhere where external parameters can be + * used. Session variables are not allowed in DDL commands or in constraints. + * + * An identifier can be parsed as a session variable only for expression kinds + * where session variables are allowed. This is the primary usage of this + * function. + * + * Second usage of this function is to decide whether "column does not exist" or + * "column or variable does not exist" error message should be printed. + * When we are in an expression where session variables cannot be used, we raise + * the first form of error message. + */ +static bool +expr_kind_allows_session_variables(ParseExprKind p_expr_kind) +{ + bool result = false; + + switch (p_expr_kind) + { + case EXPR_KIND_NONE: + Assert(false); /* can't happen */ + return false; + + /* allow */ + case EXPR_KIND_OTHER: + case EXPR_KIND_JOIN_ON: + case EXPR_KIND_FROM_SUBSELECT: + case EXPR_KIND_FROM_FUNCTION: + case EXPR_KIND_WHERE: + case EXPR_KIND_HAVING: + case EXPR_KIND_FILTER: + case EXPR_KIND_WINDOW_PARTITION: + case EXPR_KIND_WINDOW_ORDER: + case EXPR_KIND_WINDOW_FRAME_RANGE: + case EXPR_KIND_WINDOW_FRAME_ROWS: + case EXPR_KIND_WINDOW_FRAME_GROUPS: + case EXPR_KIND_SELECT_TARGET: + case EXPR_KIND_INSERT_TARGET: + case EXPR_KIND_UPDATE_SOURCE: + case EXPR_KIND_UPDATE_TARGET: + case EXPR_KIND_MERGE_WHEN: + case EXPR_KIND_GROUP_BY: + case EXPR_KIND_ORDER_BY: + case EXPR_KIND_DISTINCT_ON: + case EXPR_KIND_LIMIT: + case EXPR_KIND_OFFSET: + case EXPR_KIND_RETURNING: + case EXPR_KIND_VALUES: + case EXPR_KIND_VALUES_SINGLE: + case EXPR_KIND_ALTER_COL_TRANSFORM: + case EXPR_KIND_EXECUTE_PARAMETER: + case EXPR_KIND_POLICY: + case EXPR_KIND_CALL_ARGUMENT: + case EXPR_KIND_COPY_WHERE: + case EXPR_KIND_LET_TARGET: + result = true; + break; + + /* not allow */ + case EXPR_KIND_CHECK_CONSTRAINT: + case EXPR_KIND_DOMAIN_CHECK: + case EXPR_KIND_COLUMN_DEFAULT: + case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_INDEX_EXPRESSION: + case EXPR_KIND_INDEX_PREDICATE: + case EXPR_KIND_STATS_EXPRESSION: + case EXPR_KIND_TRIGGER_WHEN: + case EXPR_KIND_PARTITION_BOUND: + case EXPR_KIND_PARTITION_EXPRESSION: + case EXPR_KIND_GENERATED_COLUMN: + case EXPR_KIND_JOIN_USING: + case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_ASSIGN_VARIABLE: + + result = false; + break; + } + + return result; +} + /* * Transform a ColumnRef. * @@ -557,6 +646,8 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_COPY_WHERE: case EXPR_KIND_GENERATED_COLUMN: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_ASSIGN_VARIABLE: + case EXPR_KIND_LET_TARGET: /* okay */ break; @@ -829,6 +920,104 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) parser_errposition(pstate, cref->location))); } + /* + * There are contexts where session's variables are not allowed. The + * question is if we want to identify session's variables in these + * contexts? The code can be more simple, when we don't do it, but then we + * cannot to raise maybe useful message like "you cannot to use session + * variables here". On second hand, in this case the warnings about + * session's variable shadowing can be messy. + */ + if (expr_kind_allows_session_variables(pstate->p_expr_kind)) + { + Oid varid = InvalidOid; + char *attrname = NULL; + bool not_unique; + + /* + * Session variables are shadowed by columns, routine's variables or + * routine's arguments ever. We don't want to use session variable + * when it is not exactly shadowed, but RTE is valid like: + * + * CREATE TYPE T AS (c int); CREATE VARIABLE foo AS T; CREATE TABLE + * foo(a int, b int); + * + * SELECT foo.a, foo.b, foo.c FROM foo; + * + * This case can be messy and then we disallow it. When we know, so + * possible variable will be shadowed, we try to identify variable + * only when session_variables_ambiguity_warning is requested. + */ + if (node || + (!node && relname && crerr == CRERR_NO_COLUMN)) + { + /* + * In this path we just try (if it is wanted) detect if session + * variable is shadowed. + */ + if (session_variables_ambiguity_warning) + { + /* + * The AccessShareLock is created on related session variable. + * The lock will be kept for the whole transaction. + */ + varid = IdentifyVariable(cref->fields, &attrname, ¬_unique, true); + + if (OidIsValid(varid)) + { + /* This path will ending by WARNING. Unlock variable first */ + UnlockDatabaseObject(VariableRelationId, varid, 0, AccessShareLock); + + if (node) + ereport(WARNING, + (errcode(ERRCODE_AMBIGUOUS_COLUMN), + errmsg("session variable \"%s\" is shadowed", + NameListToString(cref->fields)), + errdetail("Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name."), + parser_errposition(pstate, cref->location))); + else + /* session variable is shadowed by RTE */ + ereport(WARNING, + (errcode(ERRCODE_AMBIGUOUS_COLUMN), + errmsg("session variable \"%s.%s\" is shadowed", + get_namespace_name(get_session_variable_namespace(varid)), + get_session_variable_name(varid)), + errdetail("Session variables can be shadowed by tables or table's aliases with the same name."), + parser_errposition(pstate, cref->location))); + } + } + } + else + { + /* + * The AccessShareLock is created on related session variable. The + * lock will be kept for the whole transaction. + */ + varid = IdentifyVariable(cref->fields, &attrname, ¬_unique, false); + + if (OidIsValid(varid)) + { + Oid typid; + int32 typmod; + Oid collid; + + if (not_unique) + ereport(ERROR, + (errcode(ERRCODE_AMBIGUOUS_PARAMETER), + errmsg("session variable reference \"%s\" is ambiguous", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location))); + + get_session_variable_type_typmod_collid(varid, &typid, &typmod, + &collid); + + node = makeParamSessionVariable(pstate, + varid, typid, typmod, collid, + attrname, cref->location); + } + } + } + /* * Throw error if no translation found. */ @@ -863,6 +1052,72 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) return node; } +/* + * Generate param variable for reference to session variable + */ +static Node * +makeParamSessionVariable(ParseState *pstate, + Oid varid, Oid typid, int32 typmod, Oid collid, + char *attrname, int location) +{ + Param *param; + + param = makeNode(Param); + + param->paramkind = PARAM_VARIABLE; + param->paramvarid = varid; + param->paramtype = typid; + param->paramtypmod = typmod; + param->paramcollid = collid; + + pstate->p_hasSessionVariables = true; + + if (attrname != NULL) + { + TupleDesc tupdesc; + int i; + + tupdesc = lookup_rowtype_tupdesc_noerror(typid, typmod, true); + if (!tupdesc) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("variable \"%s.%s\" is of type \"%s\", which is not a composite type", + get_namespace_name(get_session_variable_namespace(varid)), + get_session_variable_name(varid), + format_type_be(typid)), + parser_errposition(pstate, location))); + + for (i = 0; i < tupdesc->natts; i++) + { + Form_pg_attribute att = TupleDescAttr(tupdesc, i); + + if (strcmp(attrname, NameStr(att->attname)) == 0 && + !att->attisdropped) + { + /* Success, so generate a FieldSelect expression */ + FieldSelect *fselect = makeNode(FieldSelect); + + fselect->arg = (Expr *) param; + fselect->fieldnum = i + 1; + fselect->resulttype = att->atttypid; + fselect->resulttypmod = att->atttypmod; + /* save attribute's collation for parse_collate.c */ + fselect->resultcollid = att->attcollation; + + ReleaseTupleDesc(tupdesc); + return (Node *) fselect; + } + } + + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("could not identify column \"%s\" in variable", attrname), + parser_errposition(pstate, location))); + } + + return (Node *) param; +} + static Node * transformParamRef(ParseState *pstate, ParamRef *pref) { @@ -1771,6 +2026,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_LET_TARGET: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: @@ -1814,6 +2070,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_GENERATED_COLUMN: err = _("cannot use subquery in column generation expression"); break; + case EXPR_KIND_ASSIGN_VARIABLE: + err = _("cannot use subquery as target of assign statement"); + break; /* * There is intentionally no default: case here, so that the @@ -3150,6 +3409,10 @@ ParseExprKindName(ParseExprKind exprKind) return "GENERATED AS"; case EXPR_KIND_CYCLE_MARK: return "CYCLE"; + case EXPR_KIND_ASSIGN_VARIABLE: + return "ASSIGN"; + case EXPR_KIND_LET_TARGET: + return "LET"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index b3f0b6a137..b6e780de93 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2654,6 +2654,8 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) err = _("set-returning functions are not allowed in column generation expressions"); break; case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_ASSIGN_VARIABLE: + case EXPR_KIND_LET_TARGET: errkind = true; break; diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index e17c310cc1..d58f06342a 100644 --- a/src/backend/parser/parser.c +++ b/src/backend/parser/parser.c @@ -61,7 +61,8 @@ raw_parser(const char *str, RawParseMode mode) MODE_PLPGSQL_EXPR, /* RAW_PARSE_PLPGSQL_EXPR */ MODE_PLPGSQL_ASSIGN1, /* RAW_PARSE_PLPGSQL_ASSIGN1 */ MODE_PLPGSQL_ASSIGN2, /* RAW_PARSE_PLPGSQL_ASSIGN2 */ - MODE_PLPGSQL_ASSIGN3 /* RAW_PARSE_PLPGSQL_ASSIGN3 */ + MODE_PLPGSQL_ASSIGN3, /* RAW_PARSE_PLPGSQL_ASSIGN3 */ + MODE_PLPGSQL_LET /* RAW_PARSE_PLPGSQL_LET */ }; yyextra.have_lookahead = true; diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index b486ab559a..327ae82779 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -25,6 +25,7 @@ #include "access/table.h" #include "catalog/dependency.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/trigger.h" #include "executor/executor.h" #include "foreign/fdwapi.h" diff --git a/src/backend/tcop/dest.c b/src/backend/tcop/dest.c index bc8494ee7d..f63bbc0a17 100644 --- a/src/backend/tcop/dest.c +++ b/src/backend/tcop/dest.c @@ -37,6 +37,7 @@ #include "executor/functions.h" #include "executor/tqueue.h" #include "executor/tstoreReceiver.h" +#include "executor/svariableReceiver.h" #include "libpq/libpq.h" #include "libpq/pqformat.h" #include "utils/portal.h" @@ -152,6 +153,9 @@ CreateDestReceiver(CommandDest dest) case DestTupleQueue: return CreateTupleQueueDestReceiver(NULL); + + case DestVariable: + return CreateVariableDestReceiver(); } /* should never get here */ @@ -187,6 +191,7 @@ EndCommand(const QueryCompletion *qc, CommandDest dest, bool force_undecorated_o case DestSQLFunction: case DestTransientRel: case DestTupleQueue: + case DestVariable: break; } } @@ -232,6 +237,7 @@ NullCommand(CommandDest dest) case DestSQLFunction: case DestTransientRel: case DestTupleQueue: + case DestVariable: break; } } @@ -275,6 +281,7 @@ ReadyForQuery(CommandDest dest) case DestSQLFunction: case DestTransientRel: case DestTupleQueue: + case DestVariable: break; } } diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c index 5565f200c3..5382c450dc 100644 --- a/src/backend/tcop/pquery.c +++ b/src/backend/tcop/pquery.c @@ -86,6 +86,9 @@ CreateQueryDesc(PlannedStmt *plannedstmt, qd->queryEnv = queryEnv; qd->instrument_options = instrument_options; /* instrumentation wanted? */ + qd->num_session_variables = 0; + qd->session_variables = NULL; + /* null these fields until set by ExecutorStart */ qd->tupDesc = NULL; qd->estate = NULL; diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index c31114a011..e5d51f8c8b 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -52,6 +52,7 @@ #include "commands/schemacmds.h" #include "commands/seclabel.h" #include "commands/sequence.h" +#include "commands/session_variable.h" #include "commands/subscriptioncmds.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" @@ -242,6 +243,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) case T_CallStmt: case T_DoStmt: + case T_LetStmt: { /* * Commands inside the DO block or the called procedure might @@ -1075,6 +1077,11 @@ standard_ProcessUtility(PlannedStmt *pstmt, break; } + case T_LetStmt: + ExecuteLetStmt(pstate, (LetStmt *) parsetree, params, + queryEnv, qc); + break; + default: /* All other statement types have event trigger support */ ProcessUtilitySlow(pstate, pstmt, queryString, @@ -2208,6 +2215,10 @@ UtilityContainsQuery(Node *parsetree) return UtilityContainsQuery(qry->utilityStmt); return qry; + case T_LetStmt: + qry = castNode(Query, ((LetStmt *) parsetree)->query); + return qry; + default: return NULL; } @@ -2406,6 +2417,10 @@ CreateCommandTag(Node *parsetree) tag = CMDTAG_SELECT; break; + case T_LetStmt: + tag = CMDTAG_LET; + break; + /* utility statements --- same whether raw or cooked */ case T_TransactionStmt: { @@ -3291,6 +3306,7 @@ GetCommandLogLevel(Node *parsetree) break; case T_PLAssignStmt: + case T_LetStmt: lev = LOGSTMT_ALL; break; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 97b0ef22ac..44cf64b63d 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -38,6 +38,7 @@ #include "catalog/pg_statistic_ext.h" #include "catalog/pg_trigger.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/defrem.h" #include "commands/tablespace.h" #include "common/keywords.h" @@ -515,6 +516,7 @@ static char *generate_function_name(Oid funcid, int nargs, static char *generate_operator_name(Oid operid, Oid arg1, Oid arg2); static void add_cast_to(StringInfo buf, Oid typid); static char *generate_qualified_type_name(Oid typid); +static char *generate_session_variable_name(Oid varid); static text *string_to_text(char *str); static char *flatten_reloptions(Oid relid); static void get_reloptions(StringInfo buf, Datum reloptions); @@ -8178,6 +8180,14 @@ get_parameter(Param *param, deparse_context *context) return; } + /* translate paramvarid to session variable name */ + if (param->paramkind == PARAM_VARIABLE) + { + appendStringInfo(context->buf, "%s", + generate_session_variable_name(param->paramvarid)); + return; + } + /* * If it's an external parameter, see if the outermost namespace provides * function argument names. @@ -12493,6 +12503,42 @@ generate_collation_name(Oid collid) return result; } +/* + * generate_session_variable_name + * Compute the name to display for a session variable specified by OID + * + * The result includes all necessary quoting and schema-prefixing. + */ +static char * +generate_session_variable_name(Oid varid) +{ + HeapTuple tup; + Form_pg_variable varform; + char *varname; + char *nspname; + char *result; + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for variable %u", varid); + + varform = (Form_pg_variable) GETSTRUCT(tup); + + varname = NameStr(varform->varname); + + if (!VariableIsVisible(varid)) + nspname = get_namespace_name_or_temp(varform->varnamespace); + else + nspname = NULL; + + result = quote_qualified_identifier(nspname, varname); + + ReleaseSysCache(tup); + + return result; +} + /* * Given a C string, produce a TEXT datum. * diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c index 7d4168f82f..f12a9c733a 100644 --- a/src/backend/utils/cache/plancache.c +++ b/src/backend/utils/cache/plancache.c @@ -58,6 +58,7 @@ #include "access/transam.h" #include "catalog/namespace.h" +#include "catalog/pg_variable.h" #include "executor/executor.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" @@ -138,6 +139,7 @@ InitPlanCache(void) CacheRegisterSyscacheCallback(AMOPOPID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(FOREIGNSERVEROID, PlanCacheSysCallback, (Datum) 0); CacheRegisterSyscacheCallback(FOREIGNDATAWRAPPEROID, PlanCacheSysCallback, (Datum) 0); + CacheRegisterSyscacheCallback(VARIABLEOID, PlanCacheObjectCallback, (Datum) 0); } /* @@ -1880,18 +1882,33 @@ ScanQueryForLocks(Query *parsetree, bool acquire) /* * Recurse into sublink subqueries, too. But we already did the ones in - * the rtable and cteList. + * the rtable and cteList. We need to force recursive call for session + * variables too, to find and lock variables used in query (see + * ScanQueryWalker). */ - if (parsetree->hasSubLinks) + if (parsetree->hasSubLinks || + parsetree->hasSessionVariables) { query_tree_walker(parsetree, ScanQueryWalker, (void *) &acquire, QTW_IGNORE_RC_SUBQUERIES); } + + /* Process session variables */ + if (OidIsValid(parsetree->resultVariable)) + { + if (acquire) + LockDatabaseObject(VariableRelationId, parsetree->resultVariable, + 0, AccessShareLock); + else + UnlockDatabaseObject(VariableRelationId, parsetree->resultVariable, + 0, AccessShareLock); + } } /* - * Walker to find sublink subqueries for ScanQueryForLocks + * Walker to find sublink subqueries or referenced session variables + * for ScanQueryForLocks */ static bool ScanQueryWalker(Node *node, bool *acquire) @@ -1906,6 +1923,20 @@ ScanQueryWalker(Node *node, bool *acquire) ScanQueryForLocks(castNode(Query, sub->subselect), *acquire); /* Fall through to process lefthand args of SubLink */ } + else if (IsA(node, Param)) + { + Param *p = (Param *) node; + + if (p->paramkind == PARAM_VARIABLE) + { + if (acquire) + LockDatabaseObject(VariableRelationId, p->paramvarid, + 0, AccessShareLock); + else + UnlockDatabaseObject(VariableRelationId, p->paramvarid, + 0, AccessShareLock); + } + } /* * Do NOT recurse into Query nodes, because ScanQueryForLocks already @@ -2037,7 +2068,9 @@ PlanCacheRelCallback(Datum arg, Oid relid) /* * PlanCacheObjectCallback - * Syscache inval callback function for PROCOID and TYPEOID caches + * Syscache inval callback function for TYPEOID, PROCOID, NAMESPACEOID, + * OPEROID, AMOPOPID, FOREIGNSERVEROID, FOREIGNDATAWRAPPEROID and VARIABLEOID + * caches. * * Invalidate all plans mentioning the object with the specified hash value, * or all plans mentioning any member of this cache if hashvalue == 0. diff --git a/src/backend/utils/fmgr/fmgr.c b/src/backend/utils/fmgr/fmgr.c index 9dfdf890c5..24716a3a4f 100644 --- a/src/backend/utils/fmgr/fmgr.c +++ b/src/backend/utils/fmgr/fmgr.c @@ -2009,9 +2009,13 @@ get_call_expr_arg_stable(Node *expr, int argnum) */ if (IsA(arg, Const)) return true; - if (IsA(arg, Param) && - ((Param *) arg)->paramkind == PARAM_EXTERN) - return true; + if (IsA(arg, Param)) + { + Param *p = (Param *) arg; + + if (p->paramkind == PARAM_EXTERN || p->paramkind == PARAM_VARIABLE) + return true; + } return false; } diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index e565a3092f..797609d411 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -1545,6 +1545,16 @@ struct config_bool ConfigureNamesBool[] = false, NULL, NULL, NULL }, + { + {"session_variables_ambiguity_warning", PGC_USERSET, DEVELOPER_OPTIONS, + gettext_noop("Raise warning when reference to a session variable is ambiguous."), + NULL, + GUC_NOT_IN_SAMPLE + }, + &session_variables_ambiguity_warning, + false, + NULL, NULL, NULL + }, { {"default_transaction_read_only", PGC_USERSET, CLIENT_CONN_STATEMENT, gettext_noop("Sets the default read-only status of new transactions."), diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 433adeb3c1..591b9c99e0 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1701,8 +1701,8 @@ psql_completion(const char *text, int start, int end) "ABORT", "ALTER", "ANALYZE", "BEGIN", "CALL", "CHECKPOINT", "CLOSE", "CLUSTER", "COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", - "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LISTEN", "LOAD", "LOCK", - "MERGE INTO", "MOVE", "NOTIFY", "PREPARE", + "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LET", + "LISTEN", "LOAD", "LOCK", "MERGE INTO", "MOVE", "NOTIFY", "PREPARE", "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", @@ -4189,6 +4189,14 @@ psql_completion(const char *text, int start, int end) else if (TailMatches("VALUES") && !TailMatches("DEFAULT", "VALUES")) COMPLETE_WITH("("); +/* LET --- can be inside EXPLAIN, PREPARE etc */ + /* If prev. word is LET suggest a list of variables */ + else if (TailMatches("LET")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables); + /* Complete LET with "=" */ + else if (TailMatches("LET", MatchAny)) + COMPLETE_WITH("="); + /* LOCK */ /* Complete LOCK [TABLE] [ONLY] with a list of tables */ else if (Matches("LOCK")) diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h index f8ab97fce7..519c8e98f3 100644 --- a/src/include/catalog/namespace.h +++ b/src/include/catalog/namespace.h @@ -170,7 +170,9 @@ extern SearchPathMatcher *GetSearchPathMatcher(MemoryContext context); extern SearchPathMatcher *CopySearchPathMatcher(SearchPathMatcher *path); extern bool SearchPathMatchesCurrentEnvironment(SearchPathMatcher *path); +extern List *NamesFromList(List *names); extern Oid LookupVariable(const char *nspname, const char *varname, bool missing_ok); +extern Oid IdentifyVariable(List *names, char **attrname, bool *not_unique, bool noerror); extern Oid get_collation_oid(List *collname, bool missing_ok); extern Oid get_conversion_oid(List *conname, bool missing_ok); diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 58d0336206..cf9c38ebf1 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12065,4 +12065,11 @@ proname => 'any_value_transfn', prorettype => 'anyelement', proargtypes => 'anyelement anyelement', prosrc => 'any_value_transfn' }, +{ oid => '8488', descr => 'list of used session variables', + proname => 'pg_session_variables', prorows => '1000', proretset => 't', + provolatile => 's', prorettype => 'record', proargtypes => '', + proallargtypes => '{oid,text,text,oid,text,bool,bool,bool}', + proargmodes => '{o,o,o,o,o,o,o,o}', + proargnames => '{varid,schema,name,typid,typname,removed,can_select,can_update}', + prosrc => 'pg_session_variables' }, ] diff --git a/src/include/catalog/pg_variable.h b/src/include/catalog/pg_variable.h index ed135004ab..79776ff04f 100644 --- a/src/include/catalog/pg_variable.h +++ b/src/include/catalog/pg_variable.h @@ -26,6 +26,10 @@ /* ---------------- * pg_variable definition. cpp turns this into * typedef struct FormData_pg_variable + * + * The column varcreate_lsn of XlogRecPtr type (8-byte) should be on position + * divisible by 8 unconditionally and before varname column of NameData type. + * see sanity_check:check_columns * ---------------- */ CATALOG(pg_variable,9222,VariableRelationId) @@ -35,6 +39,9 @@ CATALOG(pg_variable,9222,VariableRelationId) /* OID of entry in pg_type for variable's type */ Oid vartype BKI_LOOKUP(pg_type); + /* used for identity check [oid, create_lsn] */ + XLogRecPtr varcreate_lsn; + /* variable name */ NameData varname; diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index 3d3e632a0c..e671d7a076 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -88,7 +88,8 @@ extern void ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, const char *queryString, ParamListInfo params, QueryEnvironment *queryEnv); -extern void ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, +extern void ExplainOnePlan(PlannedStmt *plannedstmt, + IntoClause *into, Oid targetvar, ExplainState *es, const char *queryString, ParamListInfo params, QueryEnvironment *queryEnv, const instr_time *planduration, diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h new file mode 100644 index 0000000000..53ff2a348a --- /dev/null +++ b/src/include/commands/session_variable.h @@ -0,0 +1,32 @@ +/*------------------------------------------------------------------------- + * + * sessionvariable.h + * prototypes for sessionvariable.c. + * + * + * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/commands/session_variable.h + * + *------------------------------------------------------------------------- + */ + +#ifndef SESSIONVARIABLE_H +#define SESSIONVARIABLE_H + +#include "nodes/params.h" +#include "nodes/parsenodes.h" +#include "parser/parse_node.h" +#include "tcop/cmdtag.h" +#include "utils/queryenvironment.h" + +extern void SetSessionVariable(Oid varid, Datum value, bool isNull); +extern void SetSessionVariableWithSecurityCheck(Oid varid, Datum value, bool isNull); +extern Datum GetSessionVariable(Oid varid, bool *isNull, Oid *typid); +extern Datum GetSessionVariableWithTypeCheck(Oid varid, bool *isNull, Oid expected_typid); + +extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo params, + QueryEnvironment *queryEnv, QueryCompletion *qc); + +#endif diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 048573c2bc..7aed55e7c1 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -159,6 +159,7 @@ typedef enum ExprEvalOp EEOP_PARAM_EXEC, EEOP_PARAM_EXTERN, EEOP_PARAM_CALLBACK, + EEOP_PARAM_VARIABLE, /* return CaseTestExpr value */ EEOP_CASE_TESTVAL, @@ -385,6 +386,13 @@ typedef struct ExprEvalStep Oid paramtype; /* OID of parameter's datatype */ } param; + /* for EEOP_PARAM_VARIABLE */ + struct + { + Oid varid; /* OID of assigned variable */ + Oid vartype; /* OID of parameter's datatype */ + } vparam; + /* for EEOP_PARAM_CALLBACK */ struct { @@ -776,6 +784,8 @@ extern void ExecEvalParamExec(ExprState *state, ExprEvalStep *op, ExprContext *econtext); extern void ExecEvalParamExtern(ExprState *state, ExprEvalStep *op, ExprContext *econtext); +extern void ExecEvalParamVariable(ExprState *state, ExprEvalStep *op, + ExprContext *econtext); extern void ExecEvalSQLValueFunction(ExprState *state, ExprEvalStep *op); extern void ExecEvalCurrentOfExpr(ExprState *state, ExprEvalStep *op); extern void ExecEvalNextValueExpr(ExprState *state, ExprEvalStep *op); diff --git a/src/include/executor/execdesc.h b/src/include/executor/execdesc.h index af2bf36dfb..c4c6331774 100644 --- a/src/include/executor/execdesc.h +++ b/src/include/executor/execdesc.h @@ -48,6 +48,10 @@ typedef struct QueryDesc EState *estate; /* executor's query-wide state */ PlanState *planstate; /* tree of per-plan-node state */ + /* reference to session variables buffer */ + int num_session_variables; + SessionVariableValue *session_variables; + /* This field is set by ExecutorRun */ bool already_executed; /* true if previously executed */ diff --git a/src/include/executor/svariableReceiver.h b/src/include/executor/svariableReceiver.h new file mode 100644 index 0000000000..63f6ee9b7f --- /dev/null +++ b/src/include/executor/svariableReceiver.h @@ -0,0 +1,25 @@ +/*------------------------------------------------------------------------- + * + * svariableReceiver.h + * prototypes for svariableReceiver.c + * + * + * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/executor/svariableReceiver.h + * + *------------------------------------------------------------------------- + */ + +#ifndef SVARIABLE_RECEIVER_H +#define SVARIABLE_RECEIVER_H + +#include "tcop/dest.h" + + +extern DestReceiver *CreateVariableDestReceiver(void); + +extern void SetVariableDestReceiverVarid(DestReceiver *self, Oid varid); + +#endif /* SVARIABLE_RECEIVER_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index cb714f4a19..48b41cc169 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -601,6 +601,18 @@ typedef struct AsyncRequest * tuples) */ } AsyncRequest; +/* ---------------- + * SessionVariableValue + * ---------------- + */ +typedef struct SessionVariableValue +{ + Oid varid; + Oid typid; + bool isnull; + Datum value; +} SessionVariableValue; + /* ---------------- * EState information * @@ -653,6 +665,13 @@ typedef struct EState ParamListInfo es_param_list_info; /* values of external params */ ParamExecData *es_param_exec_vals; /* values of internal params */ + /* Variables info: */ + /* number of used session variables */ + int es_num_session_variables; + + /* array of copied values of session variables */ + SessionVariableValue *es_session_variables; + QueryEnvironment *es_queryEnv; /* query environment */ /* Other working state: */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index a43d7edf2d..7d27707efb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -148,6 +148,9 @@ typedef struct Query */ int resultRelation pg_node_attr(query_jumble_ignore); + /* target variable of LET statement */ + Oid resultVariable; + /* has aggregates in tlist or havingQual */ bool hasAggs pg_node_attr(query_jumble_ignore); /* has window functions in tlist */ @@ -166,6 +169,8 @@ typedef struct Query bool hasForUpdate pg_node_attr(query_jumble_ignore); /* rewriter has applied some RLS policy */ bool hasRowSecurity pg_node_attr(query_jumble_ignore); + /* uses session variables */ + bool hasSessionVariables pg_node_attr(query_jumble_ignore); /* is a RETURN statement */ bool isReturn pg_node_attr(query_jumble_ignore); @@ -1954,6 +1959,20 @@ typedef struct MergeStmt WithClause *withClause; /* WITH clause */ } MergeStmt; +/* ---------------------- + * Let Statement + * ---------------------- + */ +typedef struct LetStmt +{ + NodeTag type; + List *target; /* target variable */ + Node *query; /* source expression */ + bool plpgsql_mode; /* true, when command will be executed + * (parsed) by plpgsql runtime */ + int location; +} LetStmt; + /* ---------------------- * Select Statement * diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h index 5702fbba60..6bac74def8 100644 --- a/src/include/nodes/pathnodes.h +++ b/src/include/nodes/pathnodes.h @@ -160,6 +160,9 @@ typedef struct PlannerGlobal /* partition descriptors */ PartitionDirectory partition_directory pg_node_attr(read_write_ignore); + + /* list of used session variables */ + List *sessionVariables; } PlannerGlobal; /* macro for fetching the Plan associated with a SubPlan node */ @@ -499,6 +502,8 @@ struct PlannerInfo bool placeholdersFrozen; /* true if planning a recursive WITH item */ bool hasRecursion; + /* true if session variables were used */ + bool hasSessionVariables; /* * Information about aggregates. Filled by preprocess_aggrefs(). diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index 1b787fe031..69084136e7 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -50,7 +50,7 @@ typedef struct PlannedStmt NodeTag type; - CmdType commandType; /* select|insert|update|delete|merge|utility */ + CmdType commandType; /* select|let|insert|update|delete|merge|utility */ uint64 queryId; /* query identifier (copied from Query) */ @@ -95,6 +95,8 @@ typedef struct PlannedStmt Node *utilityStmt; /* non-null if this is utility stmt */ + List *sessionVariables; /* OIDs for PARAM_VARIABLE Params */ + /* statement location in source string (copied from Query) */ int stmt_location; /* start location, or -1 if unknown */ int stmt_len; /* length in bytes; 0 means "rest of string" */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index 60d72a876b..25824a7efa 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -43,7 +43,9 @@ typedef struct Alias List *colnames; /* optional list of column aliases */ } Alias; -/* What to do at commit time for temporary relations */ +/* + * What to do at commit time for temporary relations or session variables. + */ typedef enum OnCommitAction { ONCOMMIT_NOOP, /* No ON COMMIT clause (do nothing) */ @@ -339,13 +341,17 @@ typedef struct Const * of the `paramid' field contain the SubLink's subLinkId, and * the low-order 16 bits contain the column number. (This type * of Param is also converted to PARAM_EXEC during planning.) + * + * PARAM_VARIABLE: The parameter is an access to session variable + * paramid holds varid. */ typedef enum ParamKind { PARAM_EXTERN, PARAM_EXEC, PARAM_SUBLINK, - PARAM_MULTIEXPR + PARAM_MULTIEXPR, + PARAM_VARIABLE } ParamKind; typedef struct Param @@ -358,6 +364,8 @@ typedef struct Param int32 paramtypmod pg_node_attr(query_jumble_ignore); /* OID of collation, or InvalidOid if none */ Oid paramcollid pg_node_attr(query_jumble_ignore); + /* OID of session variable if it is used */ + Oid paramvarid; /* token location, or -1 if unknown */ int location; } Param; diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h index 31c188176b..a3588041ba 100644 --- a/src/include/optimizer/planmain.h +++ b/src/include/optimizer/planmain.h @@ -116,4 +116,6 @@ extern void record_plan_function_dependency(PlannerInfo *root, Oid funcid); extern void record_plan_type_dependency(PlannerInfo *root, Oid typid); extern bool extract_query_dependencies_walker(Node *node, PlannerInfo *context); +extern void pull_up_has_session_variables(PlannerInfo *root); + #endif /* PLANMAIN_H */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 2c230d681e..a29275ab21 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -248,6 +248,7 @@ PG_KEYWORD("leading", LEADING, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("leakproof", LEAKPROOF, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("least", LEAST, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("left", LEFT, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) +PG_KEYWORD("let", LET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("level", LEVEL, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("like", LIKE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("limit", LIMIT, RESERVED_KEYWORD, AS_LABEL) diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h index 7d38ca75f7..f6a5ccb0c1 100644 --- a/src/include/parser/parse_expr.h +++ b/src/include/parser/parse_expr.h @@ -17,6 +17,7 @@ /* GUC parameters */ extern PGDLLIMPORT bool Transform_null_equals; +extern PGDLLIMPORT bool session_variables_ambiguity_warning; extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKind); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index f589112d5e..ad48d20663 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -81,6 +81,9 @@ typedef enum ParseExprKind EXPR_KIND_COPY_WHERE, /* WHERE condition in COPY FROM */ EXPR_KIND_GENERATED_COLUMN, /* generation expression for a column */ EXPR_KIND_CYCLE_MARK, /* cycle mark value */ + EXPR_KIND_ASSIGN_VARIABLE, /* PL/pgSQL assignment target - disallow + * session variables */ + EXPR_KIND_LET_TARGET, /* LET target */ } ParseExprKind; @@ -224,6 +227,7 @@ struct ParseState bool p_hasTargetSRFs; bool p_hasSubLinks; bool p_hasModifyingCTE; + bool p_hasSessionVariables; Node *p_last_srf; /* most recent set-returning func/op found */ diff --git a/src/include/parser/parser.h b/src/include/parser/parser.h index 8d90064d87..0548ad8370 100644 --- a/src/include/parser/parser.h +++ b/src/include/parser/parser.h @@ -33,6 +33,9 @@ * RAW_PARSE_PLPGSQL_ASSIGNn: parse a PL/pgSQL assignment statement, * and return a one-element List containing a RawStmt node. "n" * gives the number of dotted names comprising the target ColumnRef. + * + * RAW_PARSE_PLPGSQL_LET: parse a LET statement, and return a + * one-element List containing a RawStmt node. */ typedef enum { @@ -41,7 +44,8 @@ typedef enum RAW_PARSE_PLPGSQL_EXPR, RAW_PARSE_PLPGSQL_ASSIGN1, RAW_PARSE_PLPGSQL_ASSIGN2, - RAW_PARSE_PLPGSQL_ASSIGN3 + RAW_PARSE_PLPGSQL_ASSIGN3, + RAW_PARSE_PLPGSQL_LET } RawParseMode; /* Values for the backslash_quote GUC */ diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h index 259bdc994e..648a4af305 100644 --- a/src/include/tcop/cmdtaglist.h +++ b/src/include/tcop/cmdtaglist.h @@ -186,6 +186,7 @@ PG_CMDTAG(CMDTAG_GRANT, "GRANT", true, false, false) PG_CMDTAG(CMDTAG_GRANT_ROLE, "GRANT ROLE", false, false, false) PG_CMDTAG(CMDTAG_IMPORT_FOREIGN_SCHEMA, "IMPORT FOREIGN SCHEMA", true, false, false) PG_CMDTAG(CMDTAG_INSERT, "INSERT", false, false, true) +PG_CMDTAG(CMDTAG_LET, "LET", false, false, false) PG_CMDTAG(CMDTAG_LISTEN, "LISTEN", false, false, false) PG_CMDTAG(CMDTAG_LOAD, "LOAD", false, false, false) PG_CMDTAG(CMDTAG_LOCK_TABLE, "LOCK TABLE", false, false, false) diff --git a/src/include/tcop/dest.h b/src/include/tcop/dest.h index a7d86e7abd..e7dd749949 100644 --- a/src/include/tcop/dest.h +++ b/src/include/tcop/dest.h @@ -95,7 +95,8 @@ typedef enum DestCopyOut, /* results sent to COPY TO code */ DestSQLFunction, /* results sent to SQL-language func mgr */ DestTransientRel, /* results sent to transient relation */ - DestTupleQueue /* results sent to tuple queue */ + DestTupleQueue, /* results sent to tuple queue */ + DestVariable /* results sents to session variable */ } CommandDest; /* ---------------- diff --git a/src/pl/plpgsql/src/Makefile b/src/pl/plpgsql/src/Makefile index f7eb42d54f..64cce7f2ee 100644 --- a/src/pl/plpgsql/src/Makefile +++ b/src/pl/plpgsql/src/Makefile @@ -34,7 +34,7 @@ REGRESS_OPTS = --dbname=$(PL_TESTDB) REGRESS = plpgsql_array plpgsql_call plpgsql_control plpgsql_copy plpgsql_domain \ plpgsql_record plpgsql_cache plpgsql_simple plpgsql_transaction \ - plpgsql_trap plpgsql_trigger plpgsql_varprops + plpgsql_trap plpgsql_trigger plpgsql_varprops plpgsql_session_variable # where to find gen_keywordlist.pl and subsidiary files TOOLSDIR = $(top_srcdir)/src/tools diff --git a/src/pl/plpgsql/src/expected/plpgsql_session_variable.out b/src/pl/plpgsql/src/expected/plpgsql_session_variable.out new file mode 100644 index 0000000000..baf0ca3d05 --- /dev/null +++ b/src/pl/plpgsql/src/expected/plpgsql_session_variable.out @@ -0,0 +1,412 @@ +-- test of session variables +CREATE VARIABLE plpgsql_sv_var AS numeric; +LET plpgsql_sv_var = pi(); +-- passing parameters to DO block +DO $$ +BEGIN + RAISE NOTICE 'value of session variable is %', plpgsql_sv_var; +END; +$$; +NOTICE: value of session variable is 3.14159265358979 +-- passing output from DO block; +DO $$ +BEGIN + LET plpgsql_sv_var = 2 * pi(); +END +$$; +SELECT plpgsql_sv_var AS "pi_multiply_2"; + pi_multiply_2 +------------------ + 6.28318530717959 +(1 row) + +DROP VARIABLE plpgsql_sv_var; +-- test access from PL/pgSQL +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS numeric; +CREATE VARIABLE plpgsql_sv_var3 AS varchar; +CREATE OR REPLACE FUNCTION writer_func() +RETURNS void AS $$ +BEGIN + LET plpgsql_sv_var1 = 10; + LET plpgsql_sv_var2 = pi(); + -- very long value + LET plpgsql_sv_var3 = format('(%s)', repeat('*', 10000)); +END; +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION updater_func() +RETURNS void AS $$ +BEGIN + LET plpgsql_sv_var1 = plpgsql_sv_var1 + 100; + LET plpgsql_sv_var2 = plpgsql_sv_var2 + 100000000000; + -- very long value + LET plpgsql_sv_var3 = plpgsql_sv_var3 || format('(%s)', repeat('*', 10000)); +END; +$$ LANGUAGE plpgsql; +CREATE OR REPLACE FUNCTION reader_func() +RETURNS void AS $$ +BEGIN + RAISE NOTICE 'var1 = %', plpgsql_sv_var1; + RAISE NOTICE 'var2 = %', plpgsql_sv_var2; + RAISE NOTICE 'length of var3 = %', length(plpgsql_sv_var3); +END; +$$ LANGUAGE plpgsql; +-- execute under transaction +BEGIN; +SELECT writer_func(); + writer_func +------------- + +(1 row) + +SELECT reader_func(); +NOTICE: var1 = 10 +NOTICE: var2 = 3.14159265358979 +NOTICE: length of var3 = 10002 + reader_func +------------- + +(1 row) + +SELECT updater_func(); + updater_func +-------------- + +(1 row) + +SELECT reader_func(); +NOTICE: var1 = 110 +NOTICE: var2 = 100000000003.14159265358979 +NOTICE: length of var3 = 20004 + reader_func +------------- + +(1 row) + +END; +-- execute out of transaction +SELECT writer_func(); + writer_func +------------- + +(1 row) + +SELECT reader_func(); +NOTICE: var1 = 10 +NOTICE: var2 = 3.14159265358979 +NOTICE: length of var3 = 10002 + reader_func +------------- + +(1 row) + +SELECT updater_func(); + updater_func +-------------- + +(1 row) + +SELECT reader_func(); +NOTICE: var1 = 110 +NOTICE: var2 = 100000000003.14159265358979 +NOTICE: length of var3 = 20004 + reader_func +------------- + +(1 row) + +-- execute inside PL/pgSQL block +DO $$ +BEGIN + PERFORM writer_func(); + PERFORM reader_func(); + PERFORM updater_func(); + PERFORM reader_func(); +END; +$$; +NOTICE: var1 = 10 +NOTICE: var2 = 3.14159265358979 +NOTICE: length of var3 = 10002 +NOTICE: var1 = 110 +NOTICE: var2 = 100000000003.14159265358979 +NOTICE: length of var3 = 20004 +-- plan caches should be correctly invalidated +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3; +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS numeric; +CREATE VARIABLE plpgsql_sv_var3 AS varchar; +-- should to work again +DO $$ +BEGIN + PERFORM writer_func(); + PERFORM reader_func(); + PERFORM updater_func(); + PERFORM reader_func(); +END; +$$; +NOTICE: var1 = 10 +NOTICE: var2 = 3.14159265358979 +NOTICE: length of var3 = 10002 +NOTICE: var1 = 110 +NOTICE: var2 = 100000000003.14159265358979 +NOTICE: length of var3 = 20004 +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3; +DROP FUNCTION writer_func; +DROP FUNCTION reader_func; +DROP FUNCTION updater_func; +-- another check of correct plan cache invalidation +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS int[]; +CREATE OR REPLACE FUNCTION test_func() +RETURNS void AS $$ +DECLARE v int[] DEFAULT '{}'; +BEGIN + LET plpgsql_sv_var1 = 1; + v[plpgsql_sv_var1] = 100; + RAISE NOTICE '%', v; + LET plpgsql_sv_var2 = v; + LET plpgsql_sv_var2[plpgsql_sv_var1] = -1; + RAISE NOTICE '%', plpgsql_sv_var2; +END; +$$ LANGUAGE plpgsql; +SELECT test_func(); +NOTICE: {100} +NOTICE: {-1} + test_func +----------- + +(1 row) + +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2; +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS int[]; +SELECT test_func(); +NOTICE: {100} +NOTICE: {-1} + test_func +----------- + +(1 row) + +DROP FUNCTION test_func(); +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2; +-- check secure access +CREATE ROLE regress_var_owner_role; +CREATE ROLE regress_var_reader_role; +CREATE ROLE regress_var_exec_role; +GRANT ALL ON SCHEMA public TO regress_var_owner_role, regress_var_reader_role, regress_var_exec_role; +SET ROLE TO regress_var_owner_role; +CREATE VARIABLE plpgsql_sv_var1 AS int; +LET plpgsql_sv_var1 = 10; +SET ROLE TO DEFAULT; +SET ROLE TO regress_var_reader_role; +CREATE OR REPLACE FUNCTION var_read_func() +RETURNS void AS $$ +BEGIN + RAISE NOTICE '%', plpgsql_sv_var1; +END; +$$ LANGUAGE plpgsql SECURITY DEFINER; +SET ROLE TO DEFAULT; +SET ROLE TO regress_var_exec_role; +-- should to fail +SELECT var_read_func(); +ERROR: permission denied for session variable plpgsql_sv_var1 +CONTEXT: PL/pgSQL function var_read_func() line 3 at RAISE +SET ROLE TO DEFAULT; +SET ROLE TO regress_var_owner_role; +GRANT SELECT ON VARIABLE plpgsql_sv_var1 TO regress_var_reader_role; +SET ROLE TO DEFAULT; +SET ROLE TO regress_var_exec_role; +-- should be ok +SELECT var_read_func(); +NOTICE: 10 + var_read_func +--------------- + +(1 row) + +SET ROLE TO DEFAULT; +SET ROLE TO regress_var_owner_role; +DROP VARIABLE plpgsql_sv_var1; +SET ROLE TO DEFAULT; +SET ROLE TO regress_var_exec_role; +-- should to fail, but not crash +SELECT var_read_func(); +ERROR: column "plpgsql_sv_var1" does not exist +LINE 1: plpgsql_sv_var1 + ^ +QUERY: plpgsql_sv_var1 +CONTEXT: PL/pgSQL function var_read_func() line 3 at RAISE +SET ROLE TO DEFAULT; +DROP FUNCTION var_read_func; +REVOKE ALL ON SCHEMA public FROM regress_var_owner_role, regress_var_reader_role, regress_var_exec_role; +DROP ROLE regress_var_owner_role; +DROP ROLE regress_var_reader_role; +DROP ROLE regress_var_exec_role; +-- returns updated value +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE OR REPLACE FUNCTION inc_var_int(int) +RETURNS int AS $$ +BEGIN + LET plpgsql_sv_var1 = COALESCE(plpgsql_sv_var1 + $1, $1); + RETURN plpgsql_sv_var1; +END; +$$ LANGUAGE plpgsql; +SELECT inc_var_int(1); + inc_var_int +------------- + 1 +(1 row) + +SELECT inc_var_int(1); + inc_var_int +------------- + 2 +(1 row) + +SELECT inc_var_int(1); + inc_var_int +------------- + 3 +(1 row) + +SELECT inc_var_int(1) FROM generate_series(1,10); + inc_var_int +------------- + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 +(10 rows) + +CREATE VARIABLE plpgsql_sv_var2 AS numeric; +LET plpgsql_sv_var2 = 0.0; +CREATE OR REPLACE FUNCTION inc_var_num(numeric) +RETURNS int AS $$ +BEGIN + LET plpgsql_sv_var2 = COALESCE(plpgsql_sv_var2 + $1, $1); + RETURN plpgsql_sv_var2; +END; +$$ LANGUAGE plpgsql; +SELECT inc_var_num(1.0); + inc_var_num +------------- + 1 +(1 row) + +SELECT inc_var_num(1.0); + inc_var_num +------------- + 2 +(1 row) + +SELECT inc_var_num(1.0); + inc_var_num +------------- + 3 +(1 row) + +SELECT inc_var_num(1.0) FROM generate_series(1,10); + inc_var_num +------------- + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 +(10 rows) + +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2; +DROP FUNCTION inc_var_int; +DROP FUNCTION inc_var_num; +-- plpgsql variables are preferred against session variables +CREATE VARIABLE plpgsql_sv_var1 AS int; +DO $$ +<> +DECLARE plpgsql_sv_var1 int; +BEGIN + LET plpgsql_sv_var1 = 100; + + plpgsql_sv_var1 := 1000; + + -- print 100; + RAISE NOTICE 'session variable is %', public.plpgsql_sv_var1; + + -- print 1000 + RAISE NOTICE 'plpgsql variable is %', myblock.plpgsql_sv_var1; + + -- print 1000 + RAISE NOTICE 'variable is %', plpgsql_sv_var1; +END; +$$; +NOTICE: session variable is 100 +NOTICE: plpgsql variable is 1000 +NOTICE: variable is 1000 +-- test of warning when session variable is shadowed +SET session_variables_ambiguity_warning TO on; +DO $$ +<> +DECLARE plpgsql_sv_var1 int; +BEGIN + -- should be ok without warning + LET plpgsql_sv_var1 = 100; + + -- should be ok without warning + plpgsql_sv_var1 := 1000; + + -- should be ok without warning + RAISE NOTICE 'session variable is %', public.plpgsql_sv_var1; + + -- should be ok without warning + RAISE NOTICE 'plpgsql variable is %', myblock.plpgsql_sv_var1; + + -- should to print plpgsql variable with warning + RAISE NOTICE 'variable is %', plpgsql_sv_var1; +END; +$$; +NOTICE: session variable is 100 +NOTICE: plpgsql variable is 1000 +WARNING: session variable "plpgsql_sv_var1" is shadowed +LINE 1: plpgsql_sv_var1 + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. +QUERY: plpgsql_sv_var1 +NOTICE: variable is 1000 +SET session_variables_ambiguity_warning TO off; +DROP VARIABLE plpgsql_sv_var1; +-- the value should not be corrupted +CREATE VARIABLE plpgsql_sv_v text; +LET plpgsql_sv_v = 'abc'; +CREATE FUNCTION ffunc() +RETURNS text AS $$ +BEGIN + RETURN gfunc(plpgsql_sv_v); +END +$$ LANGUAGE plpgsql; +CREATE FUNCTION gfunc(t text) +RETURNS text AS $$ +BEGIN + LET plpgsql_sv_v = 'BOOM!'; + RETURN t; +END; +$$ LANGUAGE plpgsql; +select ffunc(); + ffunc +------- + abc +(1 row) + +DROP FUNCTION ffunc(); +DROP FUNCTION gfunc(text); +DROP VARIABLE plpgsql_sv_v; diff --git a/src/pl/plpgsql/src/meson.build b/src/pl/plpgsql/src/meson.build index 85e7293b37..ce24634269 100644 --- a/src/pl/plpgsql/src/meson.build +++ b/src/pl/plpgsql/src/meson.build @@ -87,6 +87,7 @@ tests += { 'plpgsql_trap', 'plpgsql_trigger', 'plpgsql_varprops', + 'plpgsql_session_variable', ], }, } diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index 4b76f7699a..583f84e8ea 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -24,6 +24,7 @@ #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "commands/defrem.h" +#include "commands/session_variable.h" #include "executor/execExpr.h" #include "executor/spi.h" #include "executor/tstoreReceiver.h" @@ -328,6 +329,8 @@ static int exec_stmt_commit(PLpgSQL_execstate *estate, PLpgSQL_stmt_commit *stmt); static int exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt); +static int exec_stmt_let(PLpgSQL_execstate *estate, + PLpgSQL_stmt_let *let); static void plpgsql_estate_setup(PLpgSQL_execstate *estate, PLpgSQL_function *func, @@ -2119,6 +2122,10 @@ exec_stmts(PLpgSQL_execstate *estate, List *stmts) rc = exec_stmt_rollback(estate, (PLpgSQL_stmt_rollback *) stmt); break; + case PLPGSQL_STMT_LET: + rc = exec_stmt_let(estate, (PLpgSQL_stmt_let *) stmt); + break; + default: /* point err_stmt to parent, since this one seems corrupt */ estate->err_stmt = save_estmt; @@ -4998,6 +5005,54 @@ exec_stmt_rollback(PLpgSQL_execstate *estate, PLpgSQL_stmt_rollback *stmt) return PLPGSQL_RC_OK; } +/* ---------- + * exec_stmt_let Evaluate an expression and + * put the result into a session variable. + * ---------- + */ +static int +exec_stmt_let(PLpgSQL_execstate *estate, PLpgSQL_stmt_let *stmt) +{ + bool isNull; + Oid valtype; + int32 valtypmod; + Datum value; + Oid varid; + + List *plansources; + CachedPlanSource *plansource; + + value = exec_eval_expr(estate, + stmt->expr, + &isNull, + &valtype, + &valtypmod); + + /* + * Oid of target session variable is stored in Query structure. It is + * safer to read this value directly from the plan than to hold this value + * in the plpgsql context, because it's not necessary to handle + * invalidation of the cached value. Next operations are read only without + * any allocations, so we can expect that retrieving varid from Query + * should be fast. + */ + plansources = SPI_plan_get_plan_sources(stmt->expr->plan); + if (list_length(plansources) != 1) + elog(ERROR, "unexpected length of plansources of query for LET statement"); + + plansource = (CachedPlanSource *) linitial(plansources); + if (list_length(plansource->query_list) != 1) + elog(ERROR, "unexpected length of plansource of query for LET statement"); + + varid = linitial_node(Query, plansource->query_list)->resultVariable; + if (!OidIsValid(varid)) + elog(ERROR, "oid of target session variable is not valid"); + + SetSessionVariableWithSecurityCheck(varid, value, isNull); + + return PLPGSQL_RC_OK; +} + /* ---------- * exec_assign_expr Put an expression's result into a variable. * ---------- diff --git a/src/pl/plpgsql/src/pl_funcs.c b/src/pl/plpgsql/src/pl_funcs.c index f010515fdf..73142a9147 100644 --- a/src/pl/plpgsql/src/pl_funcs.c +++ b/src/pl/plpgsql/src/pl_funcs.c @@ -288,6 +288,8 @@ plpgsql_stmt_typename(PLpgSQL_stmt *stmt) return "COMMIT"; case PLPGSQL_STMT_ROLLBACK: return "ROLLBACK"; + case PLPGSQL_STMT_LET: + return "LET"; } return "unknown"; @@ -370,6 +372,7 @@ static void free_perform(PLpgSQL_stmt_perform *stmt); static void free_call(PLpgSQL_stmt_call *stmt); static void free_commit(PLpgSQL_stmt_commit *stmt); static void free_rollback(PLpgSQL_stmt_rollback *stmt); +static void free_let(PLpgSQL_stmt_let *stmt); static void free_expr(PLpgSQL_expr *expr); @@ -459,6 +462,9 @@ free_stmt(PLpgSQL_stmt *stmt) case PLPGSQL_STMT_ROLLBACK: free_rollback((PLpgSQL_stmt_rollback *) stmt); break; + case PLPGSQL_STMT_LET: + free_let((PLpgSQL_stmt_let *) stmt); + break; default: elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type); break; @@ -713,6 +719,12 @@ free_getdiag(PLpgSQL_stmt_getdiag *stmt) { } +static void +free_let(PLpgSQL_stmt_let *stmt) +{ + free_expr(stmt->expr); +} + static void free_expr(PLpgSQL_expr *expr) { @@ -815,6 +827,7 @@ static void dump_perform(PLpgSQL_stmt_perform *stmt); static void dump_call(PLpgSQL_stmt_call *stmt); static void dump_commit(PLpgSQL_stmt_commit *stmt); static void dump_rollback(PLpgSQL_stmt_rollback *stmt); +static void dump_let(PLpgSQL_stmt_let *stmt); static void dump_expr(PLpgSQL_expr *expr); @@ -914,6 +927,9 @@ dump_stmt(PLpgSQL_stmt *stmt) case PLPGSQL_STMT_ROLLBACK: dump_rollback((PLpgSQL_stmt_rollback *) stmt); break; + case PLPGSQL_STMT_LET: + dump_let((PLpgSQL_stmt_let *) stmt); + break; default: elog(ERROR, "unrecognized cmd_type: %d", stmt->cmd_type); break; @@ -1590,6 +1606,14 @@ dump_getdiag(PLpgSQL_stmt_getdiag *stmt) printf("\n"); } +static void +dump_let(PLpgSQL_stmt_let *stmt) +{ + dump_ind(); + dump_expr(stmt->expr); + printf("\n"); +} + static void dump_expr(PLpgSQL_expr *expr) { diff --git a/src/pl/plpgsql/src/pl_gram.y b/src/pl/plpgsql/src/pl_gram.y index 6a09bfdd67..8aa6b5d7ab 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -195,7 +195,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %type stmt_return stmt_raise stmt_assert stmt_execsql %type stmt_dynexecute stmt_for stmt_perform stmt_call stmt_getdiag %type stmt_open stmt_fetch stmt_move stmt_close stmt_null -%type stmt_commit stmt_rollback +%type stmt_commit stmt_rollback stmt_let %type stmt_case stmt_foreach_a %type proc_exceptions @@ -302,6 +302,7 @@ static void check_raise_parameters(PLpgSQL_stmt_raise *stmt); %token K_INTO %token K_IS %token K_LAST +%token K_LET %token K_LOG %token K_LOOP %token K_MERGE @@ -870,6 +871,8 @@ proc_stmt : pl_block ';' { $$ = $1; } | stmt_rollback { $$ = $1; } + | stmt_let + { $$ = $1; } ; stmt_perform : K_PERFORM @@ -986,6 +989,29 @@ stmt_assign : T_DATUM } ; +stmt_let : K_LET + { + PLpgSQL_stmt_let *new; + RawParseMode pmode; + + pmode = RAW_PARSE_PLPGSQL_LET; + + new = palloc0(sizeof(PLpgSQL_stmt_let)); + new->cmd_type = PLPGSQL_STMT_LET; + new->lineno = plpgsql_location_to_lineno(@1); + new->stmtid = ++plpgsql_curr_compile->nstatements; + + /* Push back the head name to include it in the stmt */ + plpgsql_push_back_token(K_LET); + new->expr = read_sql_construct(';', 0, 0, ";", + pmode, + false, true, true, + NULL, NULL); + + $$ = (PLpgSQL_stmt *)new; + } + ; + stmt_getdiag : K_GET getdiag_area_opt K_DIAGNOSTICS getdiag_list ';' { PLpgSQL_stmt_getdiag *new; diff --git a/src/pl/plpgsql/src/pl_reserved_kwlist.h b/src/pl/plpgsql/src/pl_reserved_kwlist.h index c116abbb7a..90eb1ca8aa 100644 --- a/src/pl/plpgsql/src/pl_reserved_kwlist.h +++ b/src/pl/plpgsql/src/pl_reserved_kwlist.h @@ -40,6 +40,7 @@ PG_KEYWORD("from", K_FROM) PG_KEYWORD("if", K_IF) PG_KEYWORD("in", K_IN) PG_KEYWORD("into", K_INTO) +PG_KEYWORD("let", K_LET) PG_KEYWORD("loop", K_LOOP) PG_KEYWORD("not", K_NOT) PG_KEYWORD("null", K_NULL) diff --git a/src/pl/plpgsql/src/plpgsql.h b/src/pl/plpgsql/src/plpgsql.h index 2b4bcd1dbe..22c9d78f30 100644 --- a/src/pl/plpgsql/src/plpgsql.h +++ b/src/pl/plpgsql/src/plpgsql.h @@ -127,7 +127,8 @@ typedef enum PLpgSQL_stmt_type PLPGSQL_STMT_PERFORM, PLPGSQL_STMT_CALL, PLPGSQL_STMT_COMMIT, - PLPGSQL_STMT_ROLLBACK + PLPGSQL_STMT_ROLLBACK, + PLPGSQL_STMT_LET } PLpgSQL_stmt_type; /* @@ -520,6 +521,17 @@ typedef struct PLpgSQL_stmt_assign PLpgSQL_expr *expr; } PLpgSQL_stmt_assign; +/* + * Let statement + */ +typedef struct PLpgSQL_stmt_let +{ + PLpgSQL_stmt_type cmd_type; + int lineno; + unsigned int stmtid; + PLpgSQL_expr *expr; +} PLpgSQL_stmt_let; + /* * PERFORM statement */ diff --git a/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql b/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql new file mode 100644 index 0000000000..01c0d70019 --- /dev/null +++ b/src/pl/plpgsql/src/sql/plpgsql_session_variable.sql @@ -0,0 +1,315 @@ +-- test of session variables +CREATE VARIABLE plpgsql_sv_var AS numeric; + +LET plpgsql_sv_var = pi(); + +-- passing parameters to DO block +DO $$ +BEGIN + RAISE NOTICE 'value of session variable is %', plpgsql_sv_var; +END; +$$; + +-- passing output from DO block; +DO $$ +BEGIN + LET plpgsql_sv_var = 2 * pi(); +END +$$; + +SELECT plpgsql_sv_var AS "pi_multiply_2"; + +DROP VARIABLE plpgsql_sv_var; + +-- test access from PL/pgSQL +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS numeric; +CREATE VARIABLE plpgsql_sv_var3 AS varchar; + +CREATE OR REPLACE FUNCTION writer_func() +RETURNS void AS $$ +BEGIN + LET plpgsql_sv_var1 = 10; + LET plpgsql_sv_var2 = pi(); + -- very long value + LET plpgsql_sv_var3 = format('(%s)', repeat('*', 10000)); +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION updater_func() +RETURNS void AS $$ +BEGIN + LET plpgsql_sv_var1 = plpgsql_sv_var1 + 100; + LET plpgsql_sv_var2 = plpgsql_sv_var2 + 100000000000; + -- very long value + LET plpgsql_sv_var3 = plpgsql_sv_var3 || format('(%s)', repeat('*', 10000)); +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION reader_func() +RETURNS void AS $$ +BEGIN + RAISE NOTICE 'var1 = %', plpgsql_sv_var1; + RAISE NOTICE 'var2 = %', plpgsql_sv_var2; + RAISE NOTICE 'length of var3 = %', length(plpgsql_sv_var3); +END; +$$ LANGUAGE plpgsql; + +-- execute under transaction +BEGIN; +SELECT writer_func(); +SELECT reader_func(); +SELECT updater_func(); +SELECT reader_func(); +END; + +-- execute out of transaction +SELECT writer_func(); +SELECT reader_func(); +SELECT updater_func(); +SELECT reader_func(); + +-- execute inside PL/pgSQL block +DO $$ +BEGIN + PERFORM writer_func(); + PERFORM reader_func(); + PERFORM updater_func(); + PERFORM reader_func(); +END; +$$; + +-- plan caches should be correctly invalidated +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3; + +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS numeric; +CREATE VARIABLE plpgsql_sv_var3 AS varchar; + +-- should to work again +DO $$ +BEGIN + PERFORM writer_func(); + PERFORM reader_func(); + PERFORM updater_func(); + PERFORM reader_func(); +END; +$$; + +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2, plpgsql_sv_var3; + +DROP FUNCTION writer_func; +DROP FUNCTION reader_func; +DROP FUNCTION updater_func; + +-- another check of correct plan cache invalidation +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS int[]; + +CREATE OR REPLACE FUNCTION test_func() +RETURNS void AS $$ +DECLARE v int[] DEFAULT '{}'; +BEGIN + LET plpgsql_sv_var1 = 1; + v[plpgsql_sv_var1] = 100; + RAISE NOTICE '%', v; + LET plpgsql_sv_var2 = v; + LET plpgsql_sv_var2[plpgsql_sv_var1] = -1; + RAISE NOTICE '%', plpgsql_sv_var2; +END; +$$ LANGUAGE plpgsql; + +SELECT test_func(); + +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2; + +CREATE VARIABLE plpgsql_sv_var1 AS int; +CREATE VARIABLE plpgsql_sv_var2 AS int[]; + +SELECT test_func(); + +DROP FUNCTION test_func(); + +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2; + +-- check secure access +CREATE ROLE regress_var_owner_role; +CREATE ROLE regress_var_reader_role; +CREATE ROLE regress_var_exec_role; + +GRANT ALL ON SCHEMA public TO regress_var_owner_role, regress_var_reader_role, regress_var_exec_role; + +SET ROLE TO regress_var_owner_role; + +CREATE VARIABLE plpgsql_sv_var1 AS int; +LET plpgsql_sv_var1 = 10; + +SET ROLE TO DEFAULT; + +SET ROLE TO regress_var_reader_role; + +CREATE OR REPLACE FUNCTION var_read_func() +RETURNS void AS $$ +BEGIN + RAISE NOTICE '%', plpgsql_sv_var1; +END; +$$ LANGUAGE plpgsql SECURITY DEFINER; + +SET ROLE TO DEFAULT; + +SET ROLE TO regress_var_exec_role; + +-- should to fail +SELECT var_read_func(); + +SET ROLE TO DEFAULT; + +SET ROLE TO regress_var_owner_role; +GRANT SELECT ON VARIABLE plpgsql_sv_var1 TO regress_var_reader_role; + +SET ROLE TO DEFAULT; + +SET ROLE TO regress_var_exec_role; + +-- should be ok +SELECT var_read_func(); + +SET ROLE TO DEFAULT; + +SET ROLE TO regress_var_owner_role; + +DROP VARIABLE plpgsql_sv_var1; + +SET ROLE TO DEFAULT; + +SET ROLE TO regress_var_exec_role; + +-- should to fail, but not crash +SELECT var_read_func(); + +SET ROLE TO DEFAULT; + +DROP FUNCTION var_read_func; + +REVOKE ALL ON SCHEMA public FROM regress_var_owner_role, regress_var_reader_role, regress_var_exec_role; + +DROP ROLE regress_var_owner_role; +DROP ROLE regress_var_reader_role; +DROP ROLE regress_var_exec_role; + +-- returns updated value +CREATE VARIABLE plpgsql_sv_var1 AS int; + +CREATE OR REPLACE FUNCTION inc_var_int(int) +RETURNS int AS $$ +BEGIN + LET plpgsql_sv_var1 = COALESCE(plpgsql_sv_var1 + $1, $1); + RETURN plpgsql_sv_var1; +END; +$$ LANGUAGE plpgsql; + +SELECT inc_var_int(1); +SELECT inc_var_int(1); +SELECT inc_var_int(1); + +SELECT inc_var_int(1) FROM generate_series(1,10); + +CREATE VARIABLE plpgsql_sv_var2 AS numeric; + +LET plpgsql_sv_var2 = 0.0; + +CREATE OR REPLACE FUNCTION inc_var_num(numeric) +RETURNS int AS $$ +BEGIN + LET plpgsql_sv_var2 = COALESCE(plpgsql_sv_var2 + $1, $1); + RETURN plpgsql_sv_var2; +END; +$$ LANGUAGE plpgsql; + +SELECT inc_var_num(1.0); +SELECT inc_var_num(1.0); +SELECT inc_var_num(1.0); + +SELECT inc_var_num(1.0) FROM generate_series(1,10); + +DROP VARIABLE plpgsql_sv_var1, plpgsql_sv_var2; + +DROP FUNCTION inc_var_int; +DROP FUNCTION inc_var_num; + +-- plpgsql variables are preferred against session variables +CREATE VARIABLE plpgsql_sv_var1 AS int; + +DO $$ +<> +DECLARE plpgsql_sv_var1 int; +BEGIN + LET plpgsql_sv_var1 = 100; + + plpgsql_sv_var1 := 1000; + + -- print 100; + RAISE NOTICE 'session variable is %', public.plpgsql_sv_var1; + + -- print 1000 + RAISE NOTICE 'plpgsql variable is %', myblock.plpgsql_sv_var1; + + -- print 1000 + RAISE NOTICE 'variable is %', plpgsql_sv_var1; +END; +$$; + +-- test of warning when session variable is shadowed +SET session_variables_ambiguity_warning TO on; + +DO $$ +<> +DECLARE plpgsql_sv_var1 int; +BEGIN + -- should be ok without warning + LET plpgsql_sv_var1 = 100; + + -- should be ok without warning + plpgsql_sv_var1 := 1000; + + -- should be ok without warning + RAISE NOTICE 'session variable is %', public.plpgsql_sv_var1; + + -- should be ok without warning + RAISE NOTICE 'plpgsql variable is %', myblock.plpgsql_sv_var1; + + -- should to print plpgsql variable with warning + RAISE NOTICE 'variable is %', plpgsql_sv_var1; +END; +$$; + +SET session_variables_ambiguity_warning TO off; + +DROP VARIABLE plpgsql_sv_var1; + +-- the value should not be corrupted +CREATE VARIABLE plpgsql_sv_v text; +LET plpgsql_sv_v = 'abc'; + +CREATE FUNCTION ffunc() +RETURNS text AS $$ +BEGIN + RETURN gfunc(plpgsql_sv_v); +END +$$ LANGUAGE plpgsql; + +CREATE FUNCTION gfunc(t text) +RETURNS text AS $$ +BEGIN + LET plpgsql_sv_v = 'BOOM!'; + RETURN t; +END; +$$ LANGUAGE plpgsql; + +select ffunc(); + +DROP FUNCTION ffunc(); +DROP FUNCTION gfunc(text); + +DROP VARIABLE plpgsql_sv_v; diff --git a/src/test/regress/expected/session_variables.out b/src/test/regress/expected/session_variables.out index aca766042d..81263c50eb 100644 --- a/src/test/regress/expected/session_variables.out +++ b/src/test/regress/expected/session_variables.out @@ -55,3 +55,903 @@ SET ROLE TO DEFAULT; DROP VARIABLE svartest.var1; DROP SCHEMA svartest; DROP ROLE regress_variable_owner; +-- check access rights +CREATE ROLE regress_noowner; +CREATE VARIABLE var1 AS int; +LET var1 = 10; +-- should be ok +SELECT var1; + var1 +------ + 10 +(1 row) + +-- should to fail +SET ROLE TO regress_noowner; +SELECT var1; +ERROR: permission denied for session variable var1 +DO $$ +DECLARE t int; +BEGIN + t := var1; + RAISE NOTICE '%', t; +END; +$$; +ERROR: permission denied for session variable var1 +CONTEXT: PL/pgSQL function inline_code_block line 4 at assignment +SET ROLE TO DEFAULT; +GRANT SELECT ON VARIABLE var1 TO regress_noowner; +-- should be ok +SET ROLE TO regress_noowner; +SELECT var1; + var1 +------ + 10 +(1 row) + +DO $$ +DECLARE t int; +BEGIN + t := var1; + RAISE NOTICE '%', t; +END; +$$; +NOTICE: 10 +-- should to fail +LET var1 = 20; +ERROR: permission denied for session variable var1 +DO $$ +BEGIN + LET var1 = 30; + RAISE NOTICE '%', var1; +END; +$$; +ERROR: permission denied for session variable var1 +CONTEXT: LET statement "LET var1 = 30" +PL/pgSQL function inline_code_block line 3 at LET +SET ROLE TO DEFAULT; +GRANT UPDATE ON VARIABLE var1 TO regress_noowner; +-- should be ok +SET ROLE TO regress_noowner; +LET var1 = 20; +DO $$ +BEGIN + LET var1 = 30; + RAISE NOTICE '%', var1; +END; +$$; +NOTICE: 30 +SET ROLE TO DEFAULT; +DROP VARIABLE var1; +DROP ROLE regress_noowner; +-- use variables inside views +CREATE VARIABLE var1 AS numeric; +-- use variables in views +CREATE VIEW test_view AS SELECT COALESCE(var1 + v, 0) AS result FROM generate_series(1,2) g(v); +SELECT * FROM test_view; + result +-------- + 0 + 0 +(2 rows) + +LET var1 = 3.14; +SELECT * FROM test_view; + result +-------- + 4.14 + 5.14 +(2 rows) + +-- start a new session +\c +SELECT * FROM test_view; + result +-------- + 0 + 0 +(2 rows) + +LET var1 = 3.14; +SELECT * FROM test_view; + result +-------- + 4.14 + 5.14 +(2 rows) + +-- should fail, dependency +DROP VARIABLE var1; +ERROR: cannot drop session variable var1 because other objects depend on it +DETAIL: view test_view depends on session variable var1 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- should be ok +DROP VARIABLE var1 CASCADE; +NOTICE: drop cascades to view test_view +-- test search path +CREATE SCHEMA svartest; +CREATE VARIABLE svartest.var1 AS numeric; +-- should to fail +LET var1 = pi(); +ERROR: session variable "var1" doesn't exist +LINE 1: LET var1 = pi(); + ^ +SELECT var1; +ERROR: column "var1" does not exist +LINE 1: SELECT var1; + ^ +-- should be ok +LET svartest.var1 = pi(); +SELECT svartest.var1; + var1 +------------------ + 3.14159265358979 +(1 row) + +SET search_path TO svartest; +-- should be ok +LET var1 = pi() + 10; +SELECT var1; + var1 +------------------ + 13.1415926535898 +(1 row) + +RESET search_path; +DROP SCHEMA svartest CASCADE; +NOTICE: drop cascades to session variable svartest.var1 +CREATE VARIABLE var1 AS text; +-- variables can be updated under RO transaction +BEGIN; +SET TRANSACTION READ ONLY; +LET var1 = 'hello'; +COMMIT; +SELECT var1; + var1 +------- + hello +(1 row) + +DROP VARIABLE var1; +-- test of domains +CREATE DOMAIN int_domain AS int NOT NULL CHECK (VALUE > 100); +CREATE VARIABLE var1 AS int_domain; +-- should fail +SELECT var1; +ERROR: domain int_domain does not allow null values +-- should be ok +LET var1 = 1000; +SELECT var1; + var1 +------ + 1000 +(1 row) + +-- should fail +LET var1 = 10; +ERROR: value for domain int_domain violates check constraint "int_domain_check" +-- should fail +LET var1 = NULL; +-- note - domain defaults are not supported yet (like PLpgSQL) +DROP VARIABLE var1; +DROP DOMAIN int_domain; +-- test plan cache deps +CREATE VARIABLE var1 AS numeric; +SET plan_cache_mode TO force_generic_plan; +PREPARE p1(numeric) AS LET var1 = $1; +PREPARE p2 AS SELECT var1; +EXECUTE p1(pi() + 100); +EXECUTE p2; + var1 +----------------- + 103.14159265359 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p1(pi()); + QUERY PLAN +---------------------------- + SET SESSION VARIABLE + Result + Output: 3.14159265358979 +(3 rows) + +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p2; + QUERY PLAN +---------------- + Result + Output: var1 +(2 rows) + +-- EXPLAIN ANALYZE should to set variable +LET var1 = 0; +EXPLAIN (COSTS OFF, TIMING OFF, ANALYZE, SUMMARY OFF) LET var1 = 20; + QUERY PLAN +-------------------------------- + SET SESSION VARIABLE + Result (actual rows=1 loops=1) +(2 rows) + +SELECT var1; + var1 +------ + 20 +(1 row) + +EXPLAIN (COSTS OFF, TIMING OFF, ANALYZE, SUMMARY OFF) EXECUTE p1(30); + QUERY PLAN +-------------------------------- + SET SESSION VARIABLE + Result (actual rows=1 loops=1) +(2 rows) + +SELECT var1; + var1 +------ + 30 +(1 row) + +DROP VARIABLE var1; +CREATE VARIABLE var1 AS numeric; +-- should be NULL +EXECUTE p2; + var1 +------ + +(1 row) + +-- should be ok, no result +EXECUTE p1(pi()); +-- should be ok, result pi +EXECUTE p2; + var1 +------------------ + 3.14159265358979 +(1 row) + +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p1(pi()); + QUERY PLAN +---------------------------- + SET SESSION VARIABLE + Result + Output: 3.14159265358979 +(3 rows) + +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p2; + QUERY PLAN +---------------- + Result + Output: var1 +(2 rows) + +DEALLOCATE p1; +DEALLOCATE p2; +DROP VARIABLE var1; +CREATE SCHEMA svartest CREATE VARIABLE var1 AS int CREATE TABLE foo(a int); +LET svartest.var1 = 100; +SELECT svartest.var1; + var1 +------ + 100 +(1 row) + +SET search_path to public, svartest; +SELECT var1; + var1 +------ + 100 +(1 row) + +DROP SCHEMA svartest CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to table foo +drop cascades to session variable var1 +CREATE VARIABLE var1 AS int; +CREATE VARIABLE var2 AS int[]; +LET var1 = 2; +LET var2 = '{}'::int[]; +LET var2[var1] = 0; +SELECT var2; + var2 +----------- + [2:2]={0} +(1 row) + +PREPARE p1(int) AS LET var2[var1] = $1; +EXECUTE p1(100); +SELECT var2; + var2 +------------- + [2:2]={100} +(1 row) + +DROP VARIABLE var1, var2; +CREATE VARIABLE var1 AS int; +CREATE VARIABLE var2 AS int[]; +LET var1 = 2; +LET var2 = '{}'::int[]; +EXECUTE p1(200); +SELECT var2; + var2 +------------- + [2:2]={200} +(1 row) + +DEALLOCATE p1; +DROP VARIABLE var1, var2; +-- the LET statement should be disallowed in CTE +CREATE VARIABLE var1 AS int; +WITH x AS (LET var1 = 100) SELECT * FROM x; +ERROR: LET not supported in WITH query +LINE 1: WITH x AS (LET var1 = 100) SELECT * FROM x; + ^ +-- should be ok +LET var1 = generate_series(1, 1); +-- should fail +LET var1 = generate_series(1, 2); +ERROR: expression returned more than one row +LET var1 = generate_series(1, 0); +ERROR: expression returned no rows +DROP VARIABLE var1; +-- composite variables +CREATE TYPE sv_xyz AS (x int, y int, z numeric(10,2)); +CREATE VARIABLE v1 AS sv_xyz; +CREATE VARIABLE v2 AS sv_xyz; +LET v1 = (1, 2, 3.14); +LET v2 = (10, 20, 3.14 * 10); +-- should work too - there are prepared casts +LET v1 = (1, 2, 3); +SELECT v1; + v1 +------------ + (1,2,3.00) +(1 row) + +SELECT v2; + v2 +--------------- + (10,20,31.40) +(1 row) + +SELECT (v1).*; + x | y | z +---+---+------ + 1 | 2 | 3.00 +(1 row) + +SELECT (v2).*; + x | y | z +----+----+------- + 10 | 20 | 31.40 +(1 row) + +SELECT v1.x + v1.z; + ?column? +---------- + 4.00 +(1 row) + +SELECT v2.x + v2.z; + ?column? +---------- + 41.40 +(1 row) + +-- access to composite fields should be safe too +-- should fail +CREATE ROLE regress_var_test_role; +SET ROLE TO regress_var_test_role; +SELECT v2.x; +ERROR: permission denied for session variable v2 +SET ROLE TO DEFAULT; +DROP VARIABLE v1; +DROP VARIABLE v2; +DROP ROLE regress_var_test_role; +CREATE TYPE t1 AS (a int, b numeric, c text); +CREATE VARIABLE v1 AS t1; +LET v1 = (1, pi(), 'hello'); +SELECT v1; + v1 +---------------------------- + (1,3.14159265358979,hello) +(1 row) + +LET v1.b = 10.2222; +SELECT v1; + v1 +------------------- + (1,10.2222,hello) +(1 row) + +-- should fail, attribute doesn't exist +LET v1.x = 10; +ERROR: cannot assign to field "x" of column "v1" because there is no such column in data type t1 +LINE 1: LET v1.x = 10; + ^ +-- should fail, don't allow multi column query +LET v1 = (NULL::t1).*; +ERROR: assignment expression returned 3 columns +LINE 1: LET v1 = (NULL::t1).*; + ^ +-- allow DROP or ADD ATTRIBUTE on composite types +-- should be ok +ALTER TYPE t1 DROP ATTRIBUTE c; +SELECT v1; + v1 +------------- + (1,10.2222) +(1 row) + +-- should be ok +ALTER TYPE t1 ADD ATTRIBUTE c int; +SELECT v1; + v1 +-------------- + (1,10.2222,) +(1 row) + +LET v1 = (10, 10.3, 20); +SELECT v1; + v1 +-------------- + (10,10.3,20) +(1 row) + +-- should be ok +ALTER TYPE t1 DROP ATTRIBUTE b; +SELECT v1; + v1 +--------- + (10,20) +(1 row) + +-- should fail, disallow data type change +ALTER TYPE t1 ALTER ATTRIBUTE c TYPE int; +ERROR: cannot alter type "t1" because session variable "public.v1" uses it +DROP VARIABLE v1; +DROP TYPE t1; +-- the table type can be used as composite type too +CREATE TABLE svar_test(a int, b numeric, c date); +CREATE VARIABLE var1 AS svar_test; +LET var1 = (10, pi(), '2023-05-26'); +SELECT var1; + var1 +---------------------------------- + (10,3.14159265358979,05-26-2023) +(1 row) + +-- should fail due dependency +ALTER TABLE svar_test ALTER COLUMN a TYPE text; +ERROR: cannot alter table "svar_test" because session variable "public.var1" uses it +-- should fail +DROP TABLE svar_test; +ERROR: cannot drop table svar_test because other objects depend on it +DETAIL: session variable var1 depends on type svar_test +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP VARIABLE var1; +DROP TABLE svar_test; +-- arrays are supported +CREATE VARIABLE var1 AS numeric[]; +LET var1 = ARRAY[1.1,2.1]; +LET var1[1] = 10.1; +SELECT var1; + var1 +------------ + {10.1,2.1} +(1 row) + +-- LET target doesn't allow srf, should fail +LET var1[generate_series(1,3)] = 100; +ERROR: set-returning functions are not allowed in LET +LINE 1: LET var1[generate_series(1,3)] = 100; + ^ +DROP VARIABLE var1; +-- arrays inside composite +CREATE TYPE t1 AS (a numeric, b numeric[]); +CREATE VARIABLE var1 AS t1; +LET var1 = (10.1, ARRAY[0.0, 0.0]); +LET var1.a = 10.2; +SELECT var1; + var1 +-------------------- + (10.2,"{0.0,0.0}") +(1 row) + +LET var1.b[1] = 10.3; +SELECT var1; + var1 +--------------------- + (10.2,"{10.3,0.0}") +(1 row) + +DROP VARIABLE var1; +DROP TYPE t1; +-- Encourage use of parallel plans +SET parallel_setup_cost = 0; +SET parallel_tuple_cost = 0; +SET min_parallel_table_scan_size = 0; +SET max_parallel_workers_per_gather = 2; +-- test on query with workers +CREATE TABLE svar_test(a int); +INSERT INTO svar_test SELECT * FROM generate_series(1,1000); +ANALYZE svar_test; +CREATE VARIABLE zero int; +LET zero = 0; +-- parallel workers should be used +EXPLAIN (costs off) SELECT count(*) FROM svar_test WHERE a%10 = zero; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Gather + Workers Planned: 2 + -> Parallel Seq Scan on svar_test + Filter: ((a % 10) = zero) +(5 rows) + +-- result should be 100 +SELECT count(*) FROM svar_test WHERE a%10 = zero; + count +------- + 100 +(1 row) + +LET zero = (SELECT count(*) FROM svar_test); +-- result should be 1000 +SELECT zero; + zero +------ + 1000 +(1 row) + +-- parallel workers should be used +EXPLAIN (costs off) LET zero = (SELECT count(*) FROM svar_test); + QUERY PLAN +---------------------------------------------------------- + SET SESSION VARIABLE + Result + InitPlan 1 (returns $1) + -> Finalize Aggregate + -> Gather + Workers Planned: 2 + -> Partial Aggregate + -> Parallel Seq Scan on svar_test +(8 rows) + +DROP VARIABLE zero; +DROP TABLE svar_test; +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; +-- the result of view should be same in parallel mode too +CREATE VARIABLE var1 AS int; +LET var1 = 10; +CREATE VIEW var1view AS SELECT COALESCE(var1, 0) AS result; +SELECT * FROM var1view; + result +-------- + 10 +(1 row) + +SET debug_parallel_query TO on; +SELECT * FROM var1view; + result +-------- + 10 +(1 row) + +SET debug_parallel_query TO off; +DROP VIEW var1view; +DROP VARIABLE var1; +-- visibility check +-- variables should be shadowed always +CREATE VARIABLE var1 AS text; +SELECT var1.relname FROM pg_class var1 WHERE var1.relname = 'pg_class'; + relname +---------- + pg_class +(1 row) + +DROP VARIABLE var1; +CREATE TABLE xxtab(avar int); +INSERT INTO xxtab VALUES(333); +CREATE TYPE xxtype AS (avar int); +CREATE VARIABLE xxtab AS xxtype; +INSERT INTO xxtab VALUES(10); +-- it is ambiguous, but columns are preferred +SELECT xxtab.avar FROM xxtab; + avar +------ + 333 + 10 +(2 rows) + +SET session_variables_ambiguity_warning TO on; +-- should to raise warning +SELECT xxtab.avar FROM xxtab; +WARNING: session variable "xxtab.avar" is shadowed +LINE 1: SELECT xxtab.avar FROM xxtab; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + avar +------ + 333 + 10 +(2 rows) + +-- should be ok +SELECT avar FROM xxtab; + avar +------ + 333 + 10 +(2 rows) + +CREATE VARIABLE public.avar AS int; +-- should be ok, see the table +SELECT avar FROM xxtab; +WARNING: session variable "avar" is shadowed +LINE 1: SELECT avar FROM xxtab; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + avar +------ + 333 + 10 +(2 rows) + +-- should be ok +SELECT public.avar FROM xxtab; + avar +------ + + +(2 rows) + +DROP VARIABLE xxtab; +SELECT xxtab.avar FROM xxtab; + avar +------ + 333 + 10 +(2 rows) + +DROP VARIABLE public.avar; +DROP TYPE xxtype; +DROP TABLE xxtab; +SET session_variables_ambiguity_warning TO default; +-- The variable can be shadowed by table or by alias +CREATE TYPE public.svar_type AS (a int, b int, c int); +CREATE VARIABLE public.svar AS public.svar_type; +CREATE TABLE public.svar(a int, b int); +INSERT INTO public.svar VALUES(10, 20); +LET public.svar = (100, 200, 300); +-- should be ok +-- show table +SELECT * FROM public.svar; + a | b +----+---- + 10 | 20 +(1 row) + +SELECT svar.a FROM public.svar; + a +---- + 10 +(1 row) + +SELECT svar.* FROM public.svar; + a | b +----+---- + 10 | 20 +(1 row) + +-- show variable +SELECT public.svar; + svar +--------------- + (100,200,300) +(1 row) + +SELECT public.svar.c; + c +----- + 300 +(1 row) + +SELECT (public.svar).*; + a | b | c +-----+-----+----- + 100 | 200 | 300 +(1 row) + +-- the variable is shadowed, raise error +SELECT public.svar.c FROM public.svar; +ERROR: column svar.c does not exist +LINE 1: SELECT public.svar.c FROM public.svar; + ^ +-- can be fixed by alias +SELECT public.svar.c FROM public.svar x; + c +----- + 300 +(1 row) + +-- again with warnings +SET session_variables_ambiguity_warning TO ON; +SELECT * FROM public.svar; + a | b +----+---- + 10 | 20 +(1 row) + +SELECT svar.a FROM public.svar; +WARNING: session variable "svar.a" is shadowed +LINE 1: SELECT svar.a FROM public.svar; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + a +---- + 10 +(1 row) + +SELECT svar.* FROM public.svar; + a | b +----+---- + 10 | 20 +(1 row) + +-- show variable +SELECT public.svar; + svar +--------------- + (100,200,300) +(1 row) + +SELECT public.svar.c; + c +----- + 300 +(1 row) + +SELECT (public.svar).*; + a | b | c +-----+-----+----- + 100 | 200 | 300 +(1 row) + +-- the variable is shadowed, raise error +SELECT public.svar.c FROM public.svar; +WARNING: session variable "public.svar" is shadowed +LINE 1: SELECT public.svar.c FROM public.svar; + ^ +DETAIL: Session variables can be shadowed by tables or table's aliases with the same name. +ERROR: column svar.c does not exist +LINE 1: SELECT public.svar.c FROM public.svar; + ^ +-- can be fixed by alias +SELECT public.svar.c FROM public.svar x; + c +----- + 300 +(1 row) + +SET session_variables_ambiguity_warning TO DEFAULT; +DROP VARIABLE public.svar; +DROP TABLE public.svar; +DROP TYPE public.svar_type; +CREATE TYPE ab AS (a integer, b integer); +CREATE VARIABLE v_ab AS ab; +CREATE TABLE v_ab (a integer, b integer); +SET session_variables_ambiguity_warning TO ON; +-- warning should be raised +SELECT v_ab.a FROM v_ab; +WARNING: session variable "v_ab.a" is shadowed +LINE 1: SELECT v_ab.a FROM v_ab; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + a +--- +(0 rows) + +CREATE SCHEMA v_ab; +CREATE VARIABLE v_ab.a AS integer; +-- warning should be raised +SELECT v_ab.a FROM v_ab; +WARNING: session variable "v_ab.a" is shadowed +LINE 1: SELECT v_ab.a FROM v_ab; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + a +--- +(0 rows) + +DROP VARIABLE v_ab; +DROP TABLE v_ab; +DROP TYPE ab; +CREATE TYPE t_am_type AS (b int); +CREATE SCHEMA xxx_am; +SET search_path TO public; +CREATE VARIABLE xxx_am AS t_am_type; +LET xxx_am = ROW(10); +-- should be ok +SELECT xxx_am; + xxx_am +-------- + (10) +(1 row) + +CREATE VARIABLE xxx_am.b AS int; +LET :"DBNAME".xxx_am.b = 20; +-- should be still ok +SELECT xxx_am; + xxx_am +-------- + (10) +(1 row) + +-- should fail, the reference should be ambiguous +SELECT xxx_am.b; +ERROR: session variable reference "xxx_am.b" is ambiguous +LINE 1: SELECT xxx_am.b; + ^ +-- enhanced references should be ok +SELECT public.xxx_am.b; + b +---- + 10 +(1 row) + +SELECT :"DBNAME".xxx_am.b; + b +---- + 20 +(1 row) + +CREATE TABLE xxx_am(b int); +-- should be warning, not error (variables are shadowed) +SELECT xxx_am.b FROM xxx_am; +WARNING: session variable "xxx_am.b" is shadowed +LINE 1: SELECT xxx_am.b FROM xxx_am; + ^ +DETAIL: Session variables can be shadowed by columns, routine's variables and routine's arguments with the same name. + b +--- +(0 rows) + +-- no warning +SELECT x.b FROM xxx_am x; + b +--- +(0 rows) + +DROP TABLE xxx_am; +DROP VARIABLE public.xxx_am; +DROP VARIABLE xxx_am.b; +DROP SCHEMA xxx_am; +SET session_variables_ambiguity_warning TO DEFAULT; +CREATE SCHEMA :"DBNAME"; +CREATE VARIABLE :"DBNAME".:"DBNAME".:"DBNAME" AS t_am_type; +CREATE VARIABLE :"DBNAME".:"DBNAME".b AS int; +SET search_path TO :"DBNAME"; +-- should be ambiguous +SELECT :"DBNAME".b; +ERROR: session variable reference "regression.b" is ambiguous +LINE 1: SELECT "regression".b; + ^ +-- should be ambiguous too +SELECT :"DBNAME".:"DBNAME".b; +ERROR: session variable reference "regression.regression.b" is ambiguous +LINE 1: SELECT "regression"."regression".b; + ^ +CREATE TABLE :"DBNAME"(b int); +-- should be ok +SELECT :"DBNAME".b FROM :"DBNAME"; + b +--- +(0 rows) + +DROP TABLE :"DBNAME"; +DROP VARIABLE :"DBNAME".:"DBNAME".b; +DROP VARIABLE :"DBNAME".:"DBNAME".:"DBNAME"; +DROP SCHEMA :"DBNAME"; +RESET search_path; diff --git a/src/test/regress/sql/session_variables.sql b/src/test/regress/sql/session_variables.sql index 42dfb95a02..48fb428ec9 100644 --- a/src/test/regress/sql/session_variables.sql +++ b/src/test/regress/sql/session_variables.sql @@ -59,3 +59,590 @@ DROP VARIABLE svartest.var1; DROP SCHEMA svartest; DROP ROLE regress_variable_owner; + +-- check access rights +CREATE ROLE regress_noowner; + +CREATE VARIABLE var1 AS int; + +LET var1 = 10; +-- should be ok +SELECT var1; +-- should to fail +SET ROLE TO regress_noowner; + +SELECT var1; +DO $$ +DECLARE t int; +BEGIN + t := var1; + RAISE NOTICE '%', t; +END; +$$; + +SET ROLE TO DEFAULT; +GRANT SELECT ON VARIABLE var1 TO regress_noowner; + +-- should be ok +SET ROLE TO regress_noowner; + +SELECT var1; +DO $$ +DECLARE t int; +BEGIN + t := var1; + RAISE NOTICE '%', t; +END; +$$; + +-- should to fail +LET var1 = 20; + +DO $$ +BEGIN + LET var1 = 30; + RAISE NOTICE '%', var1; +END; +$$; + +SET ROLE TO DEFAULT; +GRANT UPDATE ON VARIABLE var1 TO regress_noowner; + +-- should be ok +SET ROLE TO regress_noowner; +LET var1 = 20; + +DO $$ +BEGIN + LET var1 = 30; + RAISE NOTICE '%', var1; +END; +$$; + +SET ROLE TO DEFAULT; +DROP VARIABLE var1; +DROP ROLE regress_noowner; + +-- use variables inside views +CREATE VARIABLE var1 AS numeric; + +-- use variables in views +CREATE VIEW test_view AS SELECT COALESCE(var1 + v, 0) AS result FROM generate_series(1,2) g(v); +SELECT * FROM test_view; +LET var1 = 3.14; +SELECT * FROM test_view; + +-- start a new session +\c + +SELECT * FROM test_view; +LET var1 = 3.14; +SELECT * FROM test_view; + +-- should fail, dependency +DROP VARIABLE var1; + +-- should be ok +DROP VARIABLE var1 CASCADE; + +-- test search path +CREATE SCHEMA svartest; +CREATE VARIABLE svartest.var1 AS numeric; + +-- should to fail +LET var1 = pi(); +SELECT var1; + +-- should be ok +LET svartest.var1 = pi(); +SELECT svartest.var1; + +SET search_path TO svartest; + +-- should be ok +LET var1 = pi() + 10; +SELECT var1; + +RESET search_path; +DROP SCHEMA svartest CASCADE; + +CREATE VARIABLE var1 AS text; + +-- variables can be updated under RO transaction +BEGIN; +SET TRANSACTION READ ONLY; +LET var1 = 'hello'; +COMMIT; + +SELECT var1; + +DROP VARIABLE var1; + +-- test of domains +CREATE DOMAIN int_domain AS int NOT NULL CHECK (VALUE > 100); +CREATE VARIABLE var1 AS int_domain; + +-- should fail +SELECT var1; + +-- should be ok +LET var1 = 1000; +SELECT var1; + +-- should fail +LET var1 = 10; + +-- should fail +LET var1 = NULL; + +-- note - domain defaults are not supported yet (like PLpgSQL) + +DROP VARIABLE var1; +DROP DOMAIN int_domain; + +-- test plan cache deps +CREATE VARIABLE var1 AS numeric; + +SET plan_cache_mode TO force_generic_plan; + +PREPARE p1(numeric) AS LET var1 = $1; +PREPARE p2 AS SELECT var1; + +EXECUTE p1(pi() + 100); +EXECUTE p2; + +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p1(pi()); +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p2; + +-- EXPLAIN ANALYZE should to set variable +LET var1 = 0; + +EXPLAIN (COSTS OFF, TIMING OFF, ANALYZE, SUMMARY OFF) LET var1 = 20; +SELECT var1; + +EXPLAIN (COSTS OFF, TIMING OFF, ANALYZE, SUMMARY OFF) EXECUTE p1(30); +SELECT var1; + +DROP VARIABLE var1; + +CREATE VARIABLE var1 AS numeric; + +-- should be NULL +EXECUTE p2; + +-- should be ok, no result +EXECUTE p1(pi()); + +-- should be ok, result pi +EXECUTE p2; + +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p1(pi()); +EXPLAIN (VERBOSE, COSTS OFF) EXECUTE p2; + +DEALLOCATE p1; +DEALLOCATE p2; + +DROP VARIABLE var1; + +CREATE SCHEMA svartest CREATE VARIABLE var1 AS int CREATE TABLE foo(a int); +LET svartest.var1 = 100; +SELECT svartest.var1; + +SET search_path to public, svartest; + +SELECT var1; + +DROP SCHEMA svartest CASCADE; + +CREATE VARIABLE var1 AS int; +CREATE VARIABLE var2 AS int[]; + +LET var1 = 2; +LET var2 = '{}'::int[]; + +LET var2[var1] = 0; + +SELECT var2; + +PREPARE p1(int) AS LET var2[var1] = $1; + +EXECUTE p1(100); + +SELECT var2; + +DROP VARIABLE var1, var2; + +CREATE VARIABLE var1 AS int; +CREATE VARIABLE var2 AS int[]; + +LET var1 = 2; +LET var2 = '{}'::int[]; + +EXECUTE p1(200); + +SELECT var2; + +DEALLOCATE p1; + +DROP VARIABLE var1, var2; + +-- the LET statement should be disallowed in CTE +CREATE VARIABLE var1 AS int; +WITH x AS (LET var1 = 100) SELECT * FROM x; + +-- should be ok +LET var1 = generate_series(1, 1); + +-- should fail +LET var1 = generate_series(1, 2); +LET var1 = generate_series(1, 0); + +DROP VARIABLE var1; + +-- composite variables +CREATE TYPE sv_xyz AS (x int, y int, z numeric(10,2)); + +CREATE VARIABLE v1 AS sv_xyz; +CREATE VARIABLE v2 AS sv_xyz; + +LET v1 = (1, 2, 3.14); +LET v2 = (10, 20, 3.14 * 10); + +-- should work too - there are prepared casts +LET v1 = (1, 2, 3); + +SELECT v1; +SELECT v2; +SELECT (v1).*; +SELECT (v2).*; + +SELECT v1.x + v1.z; +SELECT v2.x + v2.z; + +-- access to composite fields should be safe too +-- should fail +CREATE ROLE regress_var_test_role; + +SET ROLE TO regress_var_test_role; + +SELECT v2.x; + +SET ROLE TO DEFAULT; + +DROP VARIABLE v1; +DROP VARIABLE v2; + +DROP ROLE regress_var_test_role; + +CREATE TYPE t1 AS (a int, b numeric, c text); + +CREATE VARIABLE v1 AS t1; +LET v1 = (1, pi(), 'hello'); +SELECT v1; +LET v1.b = 10.2222; +SELECT v1; + +-- should fail, attribute doesn't exist +LET v1.x = 10; + +-- should fail, don't allow multi column query +LET v1 = (NULL::t1).*; + +-- allow DROP or ADD ATTRIBUTE on composite types +-- should be ok +ALTER TYPE t1 DROP ATTRIBUTE c; +SELECT v1; + +-- should be ok +ALTER TYPE t1 ADD ATTRIBUTE c int; +SELECT v1; + +LET v1 = (10, 10.3, 20); +SELECT v1; + +-- should be ok +ALTER TYPE t1 DROP ATTRIBUTE b; +SELECT v1; + +-- should fail, disallow data type change +ALTER TYPE t1 ALTER ATTRIBUTE c TYPE int; + +DROP VARIABLE v1; +DROP TYPE t1; + +-- the table type can be used as composite type too +CREATE TABLE svar_test(a int, b numeric, c date); +CREATE VARIABLE var1 AS svar_test; + +LET var1 = (10, pi(), '2023-05-26'); +SELECT var1; + +-- should fail due dependency +ALTER TABLE svar_test ALTER COLUMN a TYPE text; + +-- should fail +DROP TABLE svar_test; + +DROP VARIABLE var1; +DROP TABLE svar_test; + +-- arrays are supported +CREATE VARIABLE var1 AS numeric[]; +LET var1 = ARRAY[1.1,2.1]; +LET var1[1] = 10.1; +SELECT var1; + +-- LET target doesn't allow srf, should fail +LET var1[generate_series(1,3)] = 100; + +DROP VARIABLE var1; + +-- arrays inside composite +CREATE TYPE t1 AS (a numeric, b numeric[]); +CREATE VARIABLE var1 AS t1; +LET var1 = (10.1, ARRAY[0.0, 0.0]); +LET var1.a = 10.2; +SELECT var1; +LET var1.b[1] = 10.3; +SELECT var1; + +DROP VARIABLE var1; +DROP TYPE t1; + +-- Encourage use of parallel plans +SET parallel_setup_cost = 0; +SET parallel_tuple_cost = 0; +SET min_parallel_table_scan_size = 0; +SET max_parallel_workers_per_gather = 2; + +-- test on query with workers +CREATE TABLE svar_test(a int); +INSERT INTO svar_test SELECT * FROM generate_series(1,1000); +ANALYZE svar_test; +CREATE VARIABLE zero int; +LET zero = 0; + +-- parallel workers should be used +EXPLAIN (costs off) SELECT count(*) FROM svar_test WHERE a%10 = zero; + +-- result should be 100 +SELECT count(*) FROM svar_test WHERE a%10 = zero; + +LET zero = (SELECT count(*) FROM svar_test); + +-- result should be 1000 +SELECT zero; + +-- parallel workers should be used +EXPLAIN (costs off) LET zero = (SELECT count(*) FROM svar_test); + +DROP VARIABLE zero; +DROP TABLE svar_test; + +RESET parallel_setup_cost; +RESET parallel_tuple_cost; +RESET min_parallel_table_scan_size; +RESET max_parallel_workers_per_gather; + +-- the result of view should be same in parallel mode too +CREATE VARIABLE var1 AS int; +LET var1 = 10; + +CREATE VIEW var1view AS SELECT COALESCE(var1, 0) AS result; + +SELECT * FROM var1view; + +SET debug_parallel_query TO on; + +SELECT * FROM var1view; + +SET debug_parallel_query TO off; + +DROP VIEW var1view; +DROP VARIABLE var1; + +-- visibility check +-- variables should be shadowed always +CREATE VARIABLE var1 AS text; +SELECT var1.relname FROM pg_class var1 WHERE var1.relname = 'pg_class'; + +DROP VARIABLE var1; + +CREATE TABLE xxtab(avar int); + +INSERT INTO xxtab VALUES(333); + +CREATE TYPE xxtype AS (avar int); + +CREATE VARIABLE xxtab AS xxtype; + +INSERT INTO xxtab VALUES(10); + +-- it is ambiguous, but columns are preferred +SELECT xxtab.avar FROM xxtab; + +SET session_variables_ambiguity_warning TO on; + +-- should to raise warning +SELECT xxtab.avar FROM xxtab; + +-- should be ok +SELECT avar FROM xxtab; + +CREATE VARIABLE public.avar AS int; + +-- should be ok, see the table +SELECT avar FROM xxtab; + +-- should be ok +SELECT public.avar FROM xxtab; + +DROP VARIABLE xxtab; + +SELECT xxtab.avar FROM xxtab; + +DROP VARIABLE public.avar; + +DROP TYPE xxtype; + +DROP TABLE xxtab; + +SET session_variables_ambiguity_warning TO default; + +-- The variable can be shadowed by table or by alias +CREATE TYPE public.svar_type AS (a int, b int, c int); +CREATE VARIABLE public.svar AS public.svar_type; + +CREATE TABLE public.svar(a int, b int); + +INSERT INTO public.svar VALUES(10, 20); + +LET public.svar = (100, 200, 300); + +-- should be ok +-- show table +SELECT * FROM public.svar; +SELECT svar.a FROM public.svar; +SELECT svar.* FROM public.svar; + +-- show variable +SELECT public.svar; +SELECT public.svar.c; +SELECT (public.svar).*; + +-- the variable is shadowed, raise error +SELECT public.svar.c FROM public.svar; + +-- can be fixed by alias +SELECT public.svar.c FROM public.svar x; + +-- again with warnings +SET session_variables_ambiguity_warning TO ON; + +SELECT * FROM public.svar; +SELECT svar.a FROM public.svar; +SELECT svar.* FROM public.svar; + +-- show variable +SELECT public.svar; +SELECT public.svar.c; +SELECT (public.svar).*; + +-- the variable is shadowed, raise error +SELECT public.svar.c FROM public.svar; + +-- can be fixed by alias +SELECT public.svar.c FROM public.svar x; + +SET session_variables_ambiguity_warning TO DEFAULT; + +DROP VARIABLE public.svar; +DROP TABLE public.svar; +DROP TYPE public.svar_type; + +CREATE TYPE ab AS (a integer, b integer); + +CREATE VARIABLE v_ab AS ab; + +CREATE TABLE v_ab (a integer, b integer); + +SET session_variables_ambiguity_warning TO ON; + +-- warning should be raised +SELECT v_ab.a FROM v_ab; + +CREATE SCHEMA v_ab; + +CREATE VARIABLE v_ab.a AS integer; + +-- warning should be raised +SELECT v_ab.a FROM v_ab; + +DROP VARIABLE v_ab; +DROP TABLE v_ab; +DROP TYPE ab; + +CREATE TYPE t_am_type AS (b int); +CREATE SCHEMA xxx_am; + +SET search_path TO public; + +CREATE VARIABLE xxx_am AS t_am_type; +LET xxx_am = ROW(10); + +-- should be ok +SELECT xxx_am; + +CREATE VARIABLE xxx_am.b AS int; +LET :"DBNAME".xxx_am.b = 20; + +-- should be still ok +SELECT xxx_am; + +-- should fail, the reference should be ambiguous +SELECT xxx_am.b; + +-- enhanced references should be ok +SELECT public.xxx_am.b; +SELECT :"DBNAME".xxx_am.b; + +CREATE TABLE xxx_am(b int); + +-- should be warning, not error (variables are shadowed) +SELECT xxx_am.b FROM xxx_am; + +-- no warning +SELECT x.b FROM xxx_am x; + +DROP TABLE xxx_am; +DROP VARIABLE public.xxx_am; +DROP VARIABLE xxx_am.b; +DROP SCHEMA xxx_am; + +SET session_variables_ambiguity_warning TO DEFAULT; + +CREATE SCHEMA :"DBNAME"; + +CREATE VARIABLE :"DBNAME".:"DBNAME".:"DBNAME" AS t_am_type; +CREATE VARIABLE :"DBNAME".:"DBNAME".b AS int; + +SET search_path TO :"DBNAME"; + +-- should be ambiguous +SELECT :"DBNAME".b; + +-- should be ambiguous too +SELECT :"DBNAME".:"DBNAME".b; + +CREATE TABLE :"DBNAME"(b int); + +-- should be ok +SELECT :"DBNAME".b FROM :"DBNAME"; + +DROP TABLE :"DBNAME"; + +DROP VARIABLE :"DBNAME".:"DBNAME".b; +DROP VARIABLE :"DBNAME".:"DBNAME".:"DBNAME"; +DROP SCHEMA :"DBNAME"; + +RESET search_path; diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index b7e5d46175..7a5d553fd1 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1416,6 +1416,7 @@ LargeObjectDesc Latch LauncherLastStartTimesEntry LerpFunc +LetStmt LexDescr LexemeEntry LexemeHashKey @@ -1804,6 +1805,7 @@ PLpgSQL_stmt_forq PLpgSQL_stmt_fors PLpgSQL_stmt_getdiag PLpgSQL_stmt_if +PLpgSQL_stmt_let PLpgSQL_stmt_loop PLpgSQL_stmt_open PLpgSQL_stmt_perform @@ -2494,6 +2496,7 @@ SerializedTransactionState Session SessionBackupState SessionEndType +SessionVariableValue SetConstraintState SetConstraintStateData SetConstraintTriggerData @@ -2680,6 +2683,9 @@ SupportRequestRows SupportRequestSelectivity SupportRequestSimplify SupportRequestWFuncMonotonic +SVariable +SVariableData +SVariableState Syn SyncOps SyncRepConfigData -- 2.41.0