diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 71ae423f63..67f7aa673c 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -700,6 +700,57 @@ SELECT name, elevation + + Session Variables + + + Session variables + + + + session variable + introduction + + + + Session variables are database objects that can hold a value. + Session variables, like relations, exist within a schema and their access is + controlled via GRANT and REVOKE 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 is 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. + + + + + Conclusion diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 03e2537b07..cabb3071df 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -364,6 +364,11 @@ pg_user_mapping mappings of users to foreign servers + + + pg_variable + schema variables + @@ -13975,4 +13980,143 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx + + <structname>pg_variable</structname> + + + pg_variable + + + + The table pg_variable provides information + about schema variables. + + + + <structname>pg_variable</structname> Columns + + + + + Name + Type + References + Description + + + + + oid + oid + + Row identifier + + + + varname + name + + Name of the schema variable + + + + varnamespace + oid + pg_namespace.oid + + The OID of the namespace that contains this variable + + + + + vartype + oid + pg_type.oid + + The OID of the variable's data type. + + + + + vartypmod + int4 + + + vartypmod records type-specific data + supplied at variable creation time (for example, the maximum + length of a varchar column). It is passed to + type-specific input functions and length coercion functions. + The value will generally be -1 for types that do not need vartypmod. + + + + + varowner + oid + pg_authid.oid + Owner of the variable + + + + varcollation + oid + pg_collation.oid + + The defined collation of the variable, or zero if the variable is + not of a collatable data type. + + + + + varisnotnull + boolean + + + True if the schema variable doesn't allow null value. The default value is false. + + + + + varisimmutable + boolean + + + True if the variable is immutable (cannot be modified). The default value is false. + + + + + vareoxaction + char + + + Action performed at end of transaction: + n = no action, d = drop the variable, + r = reset the variable to its default value. + + + + + vardefexpr + pg_node_tree + + The internal representation of the variable default value. + + + + varacl + aclitem[] + + + Access privileges; see + and + + for details + + + + +
+ + diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index e5c1356d8c..423677b9af 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -5952,6 +5952,19 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE; + + + <command>Session variables and constants</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 described in . + + diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index d67270ccc3..8458cad752 100644 --- a/doc/src/sgml/ref/allfiles.sgml +++ b/doc/src/sgml/ref/allfiles.sgml @@ -47,6 +47,7 @@ Complete list of usable sgml source files in this directory. + @@ -99,6 +100,7 @@ Complete list of usable sgml source files in this directory. + @@ -148,6 +150,7 @@ Complete list of usable sgml source files in this directory. + @@ -155,6 +158,7 @@ Complete list of usable sgml source files in this directory. + diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index f1d54f5aa3..95e8352167 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -50,6 +50,10 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] +GRANT { READ | WRITE | ALL [ PRIVILEGES ] } + ON VARIABLES + TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] + REVOKE [ GRANT OPTION FOR ] { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } @@ -81,6 +85,12 @@ REVOKE [ GRANT OPTION FOR ] ON SCHEMAS FROM { [ GROUP ] role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ] + +REVOKE [ GRANT OPTION FOR ] + { { READ | WRITE } [, ...] | ALL [ PRIVILEGES ] } + ON VARIABLES + FROM { [ GROUP ] role_name | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] @@ -92,8 +102,8 @@ REVOKE [ GRANT OPTION FOR ] that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the privileges for schemas, tables (including views and foreign - tables), sequences, functions, and types (including domains) can be - altered. For this command, functions include aggregates and procedures. + tables), sequences, functions, types (including domains) and schema variables + can be altered. For this command, functions include aggregates and procedures. The words FUNCTIONS and ROUTINES are equivalent in this command. (ROUTINES is preferred going forward as the standard term for functions and procedures taken diff --git a/doc/src/sgml/ref/alter_variable.sgml b/doc/src/sgml/ref/alter_variable.sgml new file mode 100644 index 0000000000..85a38fb9d5 --- /dev/null +++ b/doc/src/sgml/ref/alter_variable.sgml @@ -0,0 +1,179 @@ + + + + + ALTER VARIABLE + + + + schema variable + altering + + + + ALTER VARIABLE + 7 + SQL - Language Statements + + + + ALTER VARIABLE + + change the definition of a variable + + + + + +ALTER VARIABLE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER } +ALTER VARIABLE name RENAME TO new_name +ALTER VARIABLE name SET SCHEMA new_schema + + + + + Description + + + The ALTER VARIABLE command changes the definition of an existing + variable. There are several subforms: + + + + OWNER + + + This form changes the owner of the variable. + + + + + + RENAME + + + This form changes the name of the variable. + + + + + + SET SCHEMA + + + This form moves the variable into another schema. + + + + + + + + + Only the owner or a superuser is allowed to alter a schema variable. + In order to move a schema variable from one schema to another, the user must + also have the CREATE privilege on the new schema (or be + a superuser). + + In order to move the schema variable ownership from one role to another, + the user must also be a direct or indirect member of the new + owning role, and that role must have the CREATE privilege + on the variable's schema (or be a superuser). These restrictions enforce that + altering the owner doesn't do anything you couldn't do by dropping and + recreating the variable. + + + + + Parameters + + + + + name + + + The name (possibly schema-qualified) of the existing variable to + alter. + + + + + + new_name + + + The new name for the variable. + + + + + + new_owner + + + The user name of the new owner of the variable. + + + + + + new_schema + + + The new schema for the variable. + + + + + + + + + Examples + + + To rename a variable: + +ALTER VARIABLE foo RENAME TO boo; + + + + + To change the owner of the variable boo + to joe: + +ALTER VARIABLE boo OWNER TO joe; + + + + + To change the schema of the variable boo + to private: + +ALTER VARIABLE boo SET SCHEMA private; + + + + + + Compatibility + + + Schema variables and this command in particular are a PostgreSQL extension. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index e07fc47fd3..46b093fda1 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -65,6 +65,7 @@ COMMENT ON TRANSFORM FOR type_name LANGUAGE lang_name | TRIGGER trigger_name ON table_name | TYPE object_name | + VARIABLE object_name | VIEW object_name } IS 'text' diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml new file mode 100644 index 0000000000..08e105c39b --- /dev/null +++ b/doc/src/sgml/ref/create_variable.sgml @@ -0,0 +1,193 @@ + + + + + CREATE VARIABLE + + + + session variable + defining + + + + CREATE VARIABLE + 7 + SQL - Language Statements + + + + CREATE VARIABLE + define a session variable + + + + +CREATE [ { TEMPORARY | TEMP } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] name [ AS ] data_type ] [ COLLATE collation ] + [ NOT NULL ] [ DEFAULT default_expr ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ] + + + + Description + + + The CREATE VARIABLE command creates a session variable. + Schema variables, like relations, exist within a schema and their access is + controlled via GRANT and REVOKE commands. + Changing a session variable is non-transactional. + + + + 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 + is set to something else in the current session with a LET command. The content + of a variable is not transactional. This is the same as regular variables in PL languages. + + + + Schema variables are retrieved by the SELECT SQL command. + Their value is set with the LET SQL command. + While session variables share properties with tables, their value cannot be updated + with an UPDATE command. + + + + + Parameters + + + + IMMUTABLE + + + The assigned value of the variable cannot be changed. The value can be assigned + by command LET only first time (when session variable has not + defined default expression), or never (when session variable has defined not null + default expression). + + + + + + IF NOT EXISTS + + + Do not throw an error if the name already exists. A notice is issued in this case. + + + + + + name + + + The name, optionally schema-qualified, of the variable. + + + + + + data_type + + + The name, optionally schema-qualified, of the data type of the variable. + + + + + + COLLATE collation + + + The COLLATE clause assigns a collation to + the variable (which must be of a collatable data type). + If not specified, the data type's default collation is used. + + + + + + NOT NULL + + + The NOT NULL clause forbids setting the variable to + a null value. A variable created as NOT NULL and without an explicitly + declared default value cannot be read until it is initialized by a LET + command. This requires the user to explicitly initialize the variable + content before reading it. + + + + + + DEFAULT default_expr + + + The DEFAULT clause can be used to assign a default value to + a session variable. + + + + + + ON COMMIT DROP, ON TRANSACTION END RESET + + + The ON COMMIT DROP clause specifies the behaviour + of a temporary session variable at transaction commit. With this clause, the +      variable is dropped at commit time. The clause is only allowed +      for temporary variables. The ON TRANSACTION END RESET + clause causes the variable to be reset to its default value when + the transaction is committed or rolled back. + + + + + + + + + Notes + + + Use the DROP VARIABLE command to remove a variable. + + + + + Examples + + + Create an integer variable var1: + +CREATE VARIABLE var1 AS date; +LET var1 = current_date; +SELECT var1; + + + + + + + Compatibility + + + The CREATE VARIABLE command is a PostgreSQL extension. + + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml index bf44c523ca..48d96af187 100644 --- a/doc/src/sgml/ref/discard.sgml +++ b/doc/src/sgml/ref/discard.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation -DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP } +DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES } @@ -75,6 +75,17 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP } + + VARIABLES + + + Resets the value of all session variables. If a variable +      is later reused, it is re-initialized to either +      NULL or its default value. + + + + ALL @@ -93,6 +104,7 @@ SELECT pg_advisory_unlock_all(); DISCARD PLANS; DISCARD TEMP; DISCARD SEQUENCES; +DISCARD VARIABLES; diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml new file mode 100644 index 0000000000..5cced4ea90 --- /dev/null +++ b/doc/src/sgml/ref/drop_variable.sgml @@ -0,0 +1,120 @@ + + + + + DROP VARIABLE + + + + session variable + removing + + + + DROP VARIABLE + 7 + SQL - Language Statements + + + + DROP VARIABLE + remove a session variable + + + + +DROP VARIABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ] + + + + + Description + + + DROP VARIABLE removes a session variable. + A variable can only be dropped by its owner or a superuser. + + + + + Parameters + + + + IF EXISTS + + + Do not throw an error if the variable does not exist. A notice is issued + in this case. + + + + + + name + + + The name, optionally schema-qualified, of a session variable. + + + + + + CASCADE + + + Automatically drop objects that depend on the variable (such as + views), + and in turn all objects that depend on those objects + (see ). + + + + + + RESTRICT + + + Refuse to drop the variable if any objects depend on it. This is + the default. + + + + + + + + Examples + + + To remove the session variable var1: + + +DROP VARIABLE var1; + + + + + Compatibility + + + The DROP VARIABLE command is a PostgreSQL extension. + + + + + + See Also + + + + + + + + + diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index a897712de2..2bf8eb9d54 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -103,6 +103,11 @@ GRANT role_name [, ...] TO variable_name [, ...] + TO role_specification [, ...] [ WITH GRANT OPTION ] + @@ -201,6 +206,24 @@ GRANT role_name [, ...] TO + + READ + + + Allows reading of a schema variable. + + + + + + WRITE + + + Allows setting the value of a schema variable. + + + + ALL PRIVILEGES diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml new file mode 100644 index 0000000000..fcbfefb4f6 --- /dev/null +++ b/doc/src/sgml/ref/let.sgml @@ -0,0 +1,107 @@ + + + + + LET + + + + session variable + changing + + + + LET + 7 + SQL - Language Statements + + + + LET + change a session variable's value + + + + +LET session_variable = sql_expression +LET session_variable = DEFAULT + + + + + 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. The result is cast to the data type of the session variable. + + + + + + DEFAULT + + + Reset the session variable to its default value, if that is defined. + If no explicit default value has been assigned, the session variable + is set to NULL. + + + + + + + Example: + +CREATE VARIABLE myvar AS integer; +LET myvar = 10; +LET myvar = (SELECT sum(val) FROM tab); +LET myvar = DEFAULT; + + + + + + Compatibility + + + LET extends the syntax defined in the SQL + standard. The SET command from the SQL standard + is used for different purposes in PostgreSQL. + + + + + See Also + + + + + + + + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 93ea937ac8..0d86239eb4 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -106,6 +106,17 @@ PostgreSQL documentation + + + + + + Restore a named schema variable only. Multiple schema variables may be specified with + multiple switches. + + + + diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 3014c864ea..3e9f9804be 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -130,6 +130,12 @@ REVOKE [ ADMIN OPTION FOR ] | CURRENT_ROLE | CURRENT_USER | SESSION_USER + +REVOKE [ GRANT OPTION FOR ] + { { READ | WRITE } [, ...] | ALL [ PRIVILEGES ] } + ON VARIABLE variable_name [, ...] + FROM { [ GROUP ] role_name | PUBLIC } [, ...] + [ CASCADE | RESTRICT ] diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml index da421ff24e..f9e42443b6 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -75,6 +75,7 @@ &alterType; &alterUser; &alterUserMapping; + &alterVariable; &alterView; &analyze; &begin; @@ -127,6 +128,7 @@ &createType; &createUser; &createUserMapping; + &createVariable; &createView; &deallocate; &declare; @@ -175,6 +177,7 @@ &dropType; &dropUser; &dropUserMapping; + &dropVariable; &dropView; &end; &execute; @@ -183,6 +186,7 @@ &grant; &importForeignSchema; &insert; + &let; &listen; &load; &lock; diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index e7b0bc804d..4cde2dd609 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -35,6 +35,7 @@ #include "catalog/pg_enum.h" #include "catalog/storage.h" #include "commands/async.h" +#include "commands/session_variable.h" #include "commands/tablecmds.h" #include "commands/trigger.h" #include "common/pg_prng.h" @@ -2197,6 +2198,9 @@ CommitTransaction(void) */ smgrDoPendingSyncs(true, is_parallel_worker); + /* Let ON COMMIT DROP or ON TRANSACTION END */ + AtPreEOXact_SessionVariable_on_xact_actions(true); + /* close large objects before lower-level cleanup */ AtEOXact_LargeObject(true); @@ -2773,6 +2777,9 @@ AbortTransaction(void) AtAbort_Portals(); smgrDoPendingSyncs(false, is_parallel_worker); AtEOXact_LargeObject(false); + + /* 'false' means it's abort */ + AtPreEOXact_SessionVariable_on_xact_actions(false); AtAbort_Notify(); AtEOXact_RelationMap(false, is_parallel_worker); AtAbort_Twophase(); @@ -4962,6 +4969,8 @@ CommitSubTransaction(void) AtEOSubXact_SPI(true, s->subTransactionId); AtEOSubXact_on_commit_actions(true, s->subTransactionId, s->parent->subTransactionId); + AtEOSubXact_SessionVariable_on_xact_actions(true, s->subTransactionId, + s->parent->subTransactionId); AtEOSubXact_Namespace(true, s->subTransactionId, s->parent->subTransactionId); AtEOSubXact_Files(true, s->subTransactionId, @@ -5126,6 +5135,8 @@ AbortSubTransaction(void) AtEOSubXact_SPI(false, s->subTransactionId); AtEOSubXact_on_commit_actions(false, s->subTransactionId, s->parent->subTransactionId); + AtEOSubXact_SessionVariable_on_xact_actions(false, s->subTransactionId, + s->parent->subTransactionId); AtEOSubXact_Namespace(false, s->subTransactionId, s->parent->subTransactionId); AtEOSubXact_Files(false, s->subTransactionId, diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile index 4e6efda97f..5c402a5ed8 100644 --- a/src/backend/catalog/Makefile +++ b/src/backend/catalog/Makefile @@ -43,6 +43,7 @@ OBJS = \ pg_shdepend.o \ pg_subscription.o \ pg_type.o \ + pg_variable.o \ storage.o \ toasting.o @@ -69,7 +70,8 @@ CATALOG_HEADERS := \ pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \ pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \ pg_sequence.h pg_publication.h pg_publication_namespace.h \ - pg_publication_rel.h pg_subscription.h pg_subscription_rel.h + pg_publication_rel.h pg_subscription.h pg_subscription_rel.h \ + pg_variable.h GENERATED_HEADERS := $(CATALOG_HEADERS:%.h=%_d.h) schemapg.h system_fk_info.h diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index ce0a4ff14e..d3eb006023 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -58,6 +58,7 @@ #include "catalog/pg_ts_parser.h" #include "catalog/pg_ts_template.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/dbcommands.h" #include "commands/defrem.h" #include "commands/event_trigger.h" @@ -112,6 +113,7 @@ static void ExecGrant_Largeobject(InternalGrant *grantStmt); static void ExecGrant_Namespace(InternalGrant *grantStmt); static void ExecGrant_Tablespace(InternalGrant *grantStmt); static void ExecGrant_Type(InternalGrant *grantStmt); +static void ExecGrant_Variable(InternalGrant *grantStmt); static void SetDefaultACLsInSchemas(InternalDefaultACL *iacls, List *nspnames); static void SetDefaultACL(InternalDefaultACL *iacls); @@ -259,6 +261,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs, case OBJECT_TYPE: whole_mask = ACL_ALL_RIGHTS_TYPE; break; + case OBJECT_VARIABLE: + whole_mask = ACL_ALL_RIGHTS_VARIABLE; + break; default: elog(ERROR, "unrecognized object type: %d", objtype); /* not reached, but keep compiler quiet */ @@ -498,6 +503,10 @@ ExecuteGrantStmt(GrantStmt *stmt) all_privileges = ACL_ALL_RIGHTS_FOREIGN_SERVER; errormsg = gettext_noop("invalid privilege type %s for foreign server"); break; + case OBJECT_VARIABLE: + all_privileges = ACL_ALL_RIGHTS_VARIABLE; + errormsg = gettext_noop("invalid privilege type %s for session variable"); + break; default: elog(ERROR, "unrecognized GrantStmt.objtype: %d", (int) stmt->objtype); @@ -600,6 +609,9 @@ ExecGrantStmt_oids(InternalGrant *istmt) case OBJECT_TABLESPACE: ExecGrant_Tablespace(istmt); break; + case OBJECT_VARIABLE: + ExecGrant_Variable(istmt); + break; default: elog(ERROR, "unrecognized GrantStmt.objtype: %d", (int) istmt->objtype); @@ -759,6 +771,16 @@ objectNamesToOids(ObjectType objtype, List *objnames) objects = lappend_oid(objects, srvid); } break; + case OBJECT_VARIABLE: + foreach(cell, objnames) + { + RangeVar *varvar = (RangeVar *) lfirst(cell); + Oid relOid; + + relOid = LookupVariable(varvar->schemaname, varvar->relname, false); + objects = lappend_oid(objects, relOid); + } + break; default: elog(ERROR, "unrecognized GrantStmt.objtype: %d", (int) objtype); @@ -848,6 +870,33 @@ objectsInSchemaToOids(ObjectType objtype, List *nspnames) table_close(rel, AccessShareLock); } break; + case OBJECT_VARIABLE: + { + ScanKeyData key; + Relation rel; + TableScanDesc scan; + HeapTuple tuple; + + ScanKeyInit(&key, + Anum_pg_variable_varnamespace, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(namespaceId)); + + rel = table_open(VariableRelationId, AccessShareLock); + scan = table_beginscan_catalog(rel, 1, &key); + + while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) + { + Oid oid = ((Form_pg_proc) GETSTRUCT(tuple))->oid; + + objects = lappend_oid(objects, oid); + } + + table_endscan(scan); + table_close(rel, AccessShareLock); + } + break; + default: /* should not happen */ elog(ERROR, "unrecognized GrantStmt.objtype: %d", @@ -1007,6 +1056,10 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s all_privileges = ACL_ALL_RIGHTS_SCHEMA; errormsg = gettext_noop("invalid privilege type %s for schema"); break; + case OBJECT_VARIABLE: + all_privileges = ACL_ALL_RIGHTS_VARIABLE; + errormsg = gettext_noop("invalid privilege type %s for session variable"); + break; default: elog(ERROR, "unrecognized GrantStmt.objtype: %d", (int) action->objtype); @@ -1204,6 +1257,12 @@ SetDefaultACL(InternalDefaultACL *iacls) this_privileges = ACL_ALL_RIGHTS_SCHEMA; break; + case OBJECT_VARIABLE: + objtype = DEFACLOBJ_VARIABLE; + if (iacls->all_privs && this_privileges == ACL_NO_RIGHTS) + this_privileges = ACL_ALL_RIGHTS_VARIABLE; + break; + default: elog(ERROR, "unrecognized objtype: %d", (int) iacls->objtype); @@ -1437,6 +1496,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid) case DEFACLOBJ_NAMESPACE: iacls.objtype = OBJECT_SCHEMA; break; + case DEFACLOBJ_VARIABLE: + iacls.objtype = OBJECT_VARIABLE; + break; default: /* Shouldn't get here */ elog(ERROR, "unexpected default ACL type: %d", @@ -1494,6 +1556,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid) case ForeignDataWrapperRelationId: istmt.objtype = OBJECT_FDW; break; + case VariableRelationId: + istmt.objtype = OBJECT_VARIABLE; + break; default: elog(ERROR, "unexpected object class %u", classid); break; @@ -3225,6 +3290,129 @@ ExecGrant_Type(InternalGrant *istmt) table_close(relation, RowExclusiveLock); } +static void +ExecGrant_Variable(InternalGrant *istmt) +{ + Relation relation; + ListCell *cell; + + if (istmt->all_privs && istmt->privileges == ACL_NO_RIGHTS) + istmt->privileges = ACL_ALL_RIGHTS_VARIABLE; + + relation = table_open(VariableRelationId, RowExclusiveLock); + + foreach(cell, istmt->objects) + { + Oid varId = lfirst_oid(cell); + Form_pg_variable pg_variable_tuple; + Datum aclDatum; + bool isNull; + AclMode avail_goptions; + AclMode this_privileges; + Acl *old_acl; + Acl *new_acl; + Oid grantorId; + Oid ownerId; + HeapTuple tuple; + HeapTuple newtuple; + Datum values[Natts_pg_variable]; + bool nulls[Natts_pg_variable]; + bool replaces[Natts_pg_variable]; + int noldmembers; + int nnewmembers; + Oid *oldmembers; + Oid *newmembers; + + tuple = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varId)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for session variable %u", varId); + + pg_variable_tuple = (Form_pg_variable) GETSTRUCT(tuple); + + /* + * Get owner ID and working copy of existing ACL. If there's no ACL, + * substitute the proper default. + */ + ownerId = pg_variable_tuple->varowner; + aclDatum = SysCacheGetAttr(VARIABLEOID, tuple, Anum_pg_variable_varacl, + &isNull); + if (isNull) + { + old_acl = acldefault(OBJECT_VARIABLE, ownerId); + /* There are no old member roles according to the catalogs */ + noldmembers = 0; + oldmembers = NULL; + } + else + { + old_acl = DatumGetAclPCopy(aclDatum); + /* Get the roles mentioned in the existing ACL */ + noldmembers = aclmembers(old_acl, &oldmembers); + } + + /* Determine ID to do the grant as, and available grant options */ + select_best_grantor(GetUserId(), istmt->privileges, + old_acl, ownerId, + &grantorId, &avail_goptions); + + /* + * Restrict the privileges to what we can actually grant, and emit the + * standards-mandated warning and error messages. + */ + this_privileges = + restrict_and_check_grant(istmt->is_grant, avail_goptions, + istmt->all_privs, istmt->privileges, + varId, grantorId, OBJECT_VARIABLE, + NameStr(pg_variable_tuple->varname), + 0, NULL); + + /* + * Generate new ACL. + */ + new_acl = merge_acl_with_grant(old_acl, istmt->is_grant, + istmt->grant_option, istmt->behavior, + istmt->grantees, this_privileges, + grantorId, ownerId); + + /* + * We need the members of both old and new ACLs so we can correct the + * shared dependency information. + */ + nnewmembers = aclmembers(new_acl, &newmembers); + + /* finished building new ACL value, now insert it */ + MemSet(values, 0, sizeof(values)); + MemSet(nulls, false, sizeof(nulls)); + MemSet(replaces, false, sizeof(replaces)); + + replaces[Anum_pg_variable_varacl - 1] = true; + values[Anum_pg_variable_varacl - 1] = PointerGetDatum(new_acl); + + newtuple = heap_modify_tuple(tuple, RelationGetDescr(relation), values, + nulls, replaces); + + CatalogTupleUpdate(relation, &newtuple->t_self, newtuple); + + /* Update initial privileges for extensions */ + recordExtensionInitPriv(varId, VariableRelationId, 0, new_acl); + + /* Update the shared dependency ACL info */ + updateAclDependencies(VariableRelationId, varId, 0, + ownerId, + noldmembers, oldmembers, + nnewmembers, newmembers); + + ReleaseSysCache(tuple); + + pfree(new_acl); + + /* prevent error when processing duplicate objects */ + CommandCounterIncrement(); + } + + table_close(relation, RowExclusiveLock); +} + static AclMode string_to_privilege(const char *privname) @@ -3257,6 +3445,10 @@ string_to_privilege(const char *privname) return ACL_CONNECT; if (strcmp(privname, "rule") == 0) return 0; /* ignore old RULE privileges */ + if (strcmp(privname, "read") == 0) + return ACL_READ; + if (strcmp(privname, "write") == 0) + return ACL_WRITE; ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("unrecognized privilege type \"%s\"", privname))); @@ -3292,6 +3484,10 @@ privilege_to_string(AclMode privilege) return "TEMP"; case ACL_CONNECT: return "CONNECT"; + case ACL_READ: + return "READ"; + case ACL_WRITE: + return "WRITE"; default: elog(ERROR, "unrecognized privilege: %d", (int) privilege); } @@ -3415,6 +3611,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_TYPE: msg = gettext_noop("permission denied for type %s"); break; + case OBJECT_VARIABLE: + msg = gettext_noop("permission denied for session variable %s"); + break; case OBJECT_VIEW: msg = gettext_noop("permission denied for view %s"); break; @@ -3526,6 +3725,9 @@ aclcheck_error(AclResult aclerr, ObjectType objtype, case OBJECT_TYPE: msg = gettext_noop("must be owner of type %s"); break; + case OBJECT_VARIABLE: + msg = gettext_noop("must be owner of session variable %s"); + break; case OBJECT_VIEW: msg = gettext_noop("must be owner of view %s"); break; @@ -3671,6 +3873,8 @@ pg_aclmask(ObjectType objtype, Oid table_oid, AttrNumber attnum, Oid roleid, return ACL_NO_RIGHTS; case OBJECT_TYPE: return pg_type_aclmask(table_oid, roleid, mask, how); + case OBJECT_VARIABLE: + return pg_variable_aclmask(table_oid, roleid, mask, how); default: elog(ERROR, "unrecognized objtype: %d", (int) objtype); @@ -4539,6 +4743,66 @@ pg_type_aclmask(Oid type_oid, Oid roleid, AclMode mask, AclMaskHow how) return result; } +/* + * Exported routine for examining a user's privileges for a variable. + */ +AclMode +pg_variable_aclmask(Oid var_oid, Oid roleid, AclMode mask, AclMaskHow how) +{ + AclMode result; + HeapTuple tuple; + Datum aclDatum; + bool isNull; + Acl *acl; + Oid ownerId; + + Form_pg_variable varForm; + + /* Bypass permission checks for superusers */ + if (superuser_arg(roleid)) + return mask; + + /* + * Must get the variables's tuple from pg_variable + */ + tuple = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(var_oid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("variable with OID %u does not exist", + var_oid))); + varForm = (Form_pg_variable) GETSTRUCT(tuple); + + /* + * Now get the variable's owner and ACL from the tuple + */ + ownerId = varForm->varowner; + + aclDatum = SysCacheGetAttr(VARIABLEOID, tuple, + Anum_pg_variable_varacl, &isNull); + if (isNull) + { + /* No ACL, so build default ACL */ + acl = acldefault(OBJECT_VARIABLE, ownerId); + aclDatum = (Datum) 0; + } + else + { + /* detoast rel's ACL if necessary */ + acl = DatumGetAclP(aclDatum); + } + + result = aclmask(acl, roleid, ownerId, mask, how); + + /* if we have a detoasted copy, free it */ + if (acl && (Pointer) acl != DatumGetPointer(aclDatum)) + pfree(acl); + + ReleaseSysCache(tuple); + + return result; +} + /* * Exported routine for checking a user's access privileges to a column * @@ -4813,6 +5077,18 @@ pg_type_aclcheck(Oid type_oid, Oid roleid, AclMode mode) return ACLCHECK_NO_PRIV; } +/* + * Exported routine for checking a user's access privileges to a variable + */ +AclResult +pg_variable_aclcheck(Oid type_oid, Oid roleid, AclMode mode) +{ + if (pg_variable_aclmask(type_oid, roleid, mode, ACLMASK_ANY) != 0) + return ACLCHECK_OK; + else + return ACLCHECK_NO_PRIV; +} + /* * Ownership check for a relation (specified by OID). */ @@ -5430,6 +5706,33 @@ pg_statistics_object_ownercheck(Oid stat_oid, Oid roleid) return has_privs_of_role(roleid, ownerId); } +/* + * Ownership check for a session variable (specified by OID). + */ +bool +pg_variable_ownercheck(Oid db_oid, Oid roleid) +{ + HeapTuple tuple; + Oid ownerId; + + /* Superusers bypass all permission checking. */ + if (superuser_arg(roleid)) + return true; + + tuple = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(db_oid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_DATABASE), + errmsg("session variable with OID %u does not exist", db_oid))); + + ownerId = ((Form_pg_variable) GETSTRUCT(tuple))->varowner; + + ReleaseSysCache(tuple); + + return has_privs_of_role(roleid, ownerId); +} + + /* * Check whether specified role has CREATEROLE privilege (or is a superuser) * @@ -5558,6 +5861,10 @@ get_user_default_acl(ObjectType objtype, Oid ownerId, Oid nsp_oid) defaclobjtype = DEFACLOBJ_NAMESPACE; break; + case OBJECT_VARIABLE: + defaclobjtype = DEFACLOBJ_VARIABLE; + break; + default: return NULL; } diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index fe9c714257..baf79d66f4 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -63,12 +63,15 @@ #include "catalog/pg_ts_template.h" #include "catalog/pg_type.h" #include "catalog/pg_user_mapping.h" +#include "catalog/pg_variable.h" #include "commands/comment.h" #include "commands/defrem.h" #include "commands/event_trigger.h" #include "commands/extension.h" #include "commands/policy.h" #include "commands/publicationcmds.h" +#include "commands/schemacmds.h" +#include "commands/session_variable.h" #include "commands/seclabel.h" #include "commands/sequence.h" #include "commands/trigger.h" @@ -183,7 +186,8 @@ static const Oid object_classes[] = { PublicationRelationId, /* OCLASS_PUBLICATION */ PublicationRelRelationId, /* OCLASS_PUBLICATION_REL */ SubscriptionRelationId, /* OCLASS_SUBSCRIPTION */ - TransformRelationId /* OCLASS_TRANSFORM */ + TransformRelationId, /* OCLASS_TRANSFORM */ + VariableRelationId /* OCLASS_VARIABLE */ }; @@ -1501,6 +1505,10 @@ doDeletion(const ObjectAddress *object, int flags) DropObjectById(object); break; + case OCLASS_VARIABLE: + RemoveSessionVariable(object->objectId); + break; + /* * These global object types are not supported here. */ @@ -1879,6 +1887,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); @@ -2879,6 +2892,9 @@ getObjectClass(const ObjectAddress *object) case TransformRelationId: return OCLASS_TRANSFORM; + + case VariableRelationId: + return OCLASS_VARIABLE; } /* shouldn't get here */ diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c index 4de8400fd0..5df9459f00 100644 --- a/src/backend/catalog/namespace.c +++ b/src/backend/catalog/namespace.c @@ -39,6 +39,7 @@ #include "catalog/pg_ts_parser.h" #include "catalog/pg_ts_template.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/dbcommands.h" #include "funcapi.h" #include "mb/pg_wchar.h" @@ -763,6 +764,69 @@ RelationIsVisible(Oid relid) return visible; } +/* + * VariableIsVisible + * Determine whether a variable (identified by OID) is visible in the + * current search path. Visible means "would be found by searching + * for the unqualified variable name". + */ +bool +VariableIsVisible(Oid varid) +{ + HeapTuple vartup; + Form_pg_variable varform; + Oid varnamespace; + bool visible; + + vartup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + if (!HeapTupleIsValid(vartup)) + elog(ERROR, "cache lookup failed for session variable %u", varid); + varform = (Form_pg_variable) GETSTRUCT(vartup); + + recomputeNamespacePath(); + + /* + * Quick check: if it ain't in the path at all, it ain't visible. Items in + * the system namespace are surely in the path and so we needn't even do + * list_member_oid() for them. + */ + varnamespace = varform->varnamespace; + if (varnamespace != PG_CATALOG_NAMESPACE && + !list_member_oid(activeSearchPath, varnamespace)) + visible = false; + else + { + /* + * If it is in the path, it might still not be visible; it could be + * hidden by another relation of the same name earlier in the path. So + * we must do a slow check for conflicting relations. + */ + char *varname = NameStr(varform->varname); + ListCell *l; + + visible = false; + foreach(l, activeSearchPath) + { + Oid namespaceId = lfirst_oid(l); + + if (namespaceId == varnamespace) + { + /* Found it first in path */ + visible = true; + break; + } + if (OidIsValid(get_varname_varid(varname, namespaceId))) + { + /* Found something else first in path */ + break; + } + } + } + + ReleaseSysCache(vartup); + + return visible; +} /* * TypenameGetTypid @@ -2842,6 +2906,291 @@ TSConfigIsVisible(Oid cfgid) return visible; } +/* + * Returns oid of session variable specified by possibly qualified + * identifier. + * + * If not found, returns InvalidOid if missing_ok, else throws error. + */ +Oid +LookupVariable(const char *nspname, const char *varname, bool missing_ok) +{ + Oid namespaceId; + Oid varoid = InvalidOid; + ListCell *l; + + if (nspname) + { + namespaceId = LookupExplicitNamespace(nspname, missing_ok); + + /* + * When nspname is not known namespace, then nspname.varname + * cannot to identify any session variable. + */ + if (!OidIsValid(namespaceId)) + return InvalidOid; + + varoid = GetSysCacheOid2(VARIABLENAMENSP, Anum_pg_variable_oid, + PointerGetDatum(varname), + ObjectIdGetDatum(namespaceId)); + } + else + { + /* Iterate over schemas in search_path */ + recomputeNamespacePath(); + + foreach(l, activeSearchPath) + { + namespaceId = lfirst_oid(l); + + varoid = GetSysCacheOid2(VARIABLENAMENSP, Anum_pg_variable_oid, + PointerGetDatum(varname), + ObjectIdGetDatum(namespaceId)); + + if (OidIsValid(varoid)) + break; + } + } + + if (!OidIsValid(varoid) && !missing_ok) + { + if (nspname) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("session variable \"%s.%s\" does not exist", + nspname, varname))); + else + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("session variable \"%s\" does not exist", + varname))); + } + + 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. + * + * 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 ambigonuous. In this case this routine returns InvalidOid, and + * sets the output parameter "not_unique" to true. This parameter is + * used for more meaningfull error message. + */ +Oid +IdentifyVariable(List *names, char **attrname, bool *not_unique) +{ + 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 varoid_without_attr = InvalidOid; + Oid varoid_with_attr = InvalidOid; + + *not_unique = false; + + switch (list_length(names)) + { + case 1: + field1 = linitial(names); + + Assert(IsA(field1, String)); + + return LookupVariable(NULL, strVal(field1), true); + + case 2: + field1 = linitial(names); + field2 = lsecond(names); + + Assert(IsA(field1, String)); + a = strVal(field1); + + if (IsA(field2, String)) + { + 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. Second node can be star. In + * this case, the only allowed possibility is "variable"."*". + */ + varoid_without_attr = LookupVariable(a, b, true); + varoid_with_attr = LookupVariable(NULL, a, true); + } + else + { + Assert(IsA(field2, A_Star)); + + /* + * Session variables doesn't support unboxing by star syntax. But + * this syntax have to be calculated here, because can come from + * non session variables related expressions. + */ + return InvalidOid; + } + + if (OidIsValid(varoid_without_attr) && OidIsValid(varoid_with_attr)) + { + *not_unique = true; + return InvalidOid; + } + else if (OidIsValid(varoid_without_attr)) + { + *attrname = NULL; + return varoid_without_attr; + } + else + { + *attrname = b; + return varoid_with_attr; + } + break; + + case 3: + 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 returns + * InvalidOid with not_unique flag, when both interpretations are + * possible. When third node is star, the only possible + * interpretation is "schema"."variable"."*". + */ + varoid_without_attr = LookupVariable(b, c, true); + varoid_with_attr = LookupVariable(a, b, true); + } + else + { + Assert(IsA(field3, A_Star)); + return InvalidOid; + } + + if (OidIsValid(varoid_without_attr) && OidIsValid(varoid_with_attr)) + { + *not_unique = true; + return InvalidOid; + } + else if (OidIsValid(varoid_without_attr)) + { + *attrname = NULL; + + /* + * In this case, "a" is used as catalog name - check it. + */ + if (strcmp(a, get_database_name(MyDatabaseId)) != 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cross-database references are not implemented: %s", + NameListToString(names)))); + + return varoid_without_attr; + } + else + { + *attrname = c; + return varoid_with_attr; + } + 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) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cross-database references are not implemented: %s", + NameListToString(names)))); + + if (IsA(field4, String)) + { + d = strVal(field4); + } + else + { + Assert(IsA(field4, A_Star)); + return InvalidOid; + } + + *attrname = d; + return LookupVariable(b, c, true); + + default: + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("improper qualified name (too many dotted names): %s", + NameListToString(names)))); + break; + } +} /* * DeconstructQualifiedName @@ -4657,3 +5006,14 @@ pg_is_other_temp_schema(PG_FUNCTION_ARGS) PG_RETURN_BOOL(isOtherTempNamespace(oid)); } + +Datum +pg_variable_is_visible(PG_FUNCTION_ARGS) +{ + Oid oid = PG_GETARG_OID(0); + + if (!SearchSysCacheExists1(VARIABLEOID, ObjectIdGetDatum(oid))) + PG_RETURN_NULL(); + + PG_RETURN_BOOL(VariableIsVisible(oid)); +} diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 2bae3fbb17..a8cbadd049 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -62,6 +62,7 @@ #include "catalog/pg_ts_template.h" #include "catalog/pg_type.h" #include "catalog/pg_user_mapping.h" +#include "catalog/pg_variable.h" #include "commands/dbcommands.h" #include "commands/defrem.h" #include "commands/event_trigger.h" @@ -617,6 +618,20 @@ static const ObjectPropertyType ObjectProperty[] = OBJECT_USER_MAPPING, false }, + { + "session variable", + VariableRelationId, + VariableObjectIndexId, + VARIABLEOID, + VARIABLENAMENSP, + Anum_pg_variable_oid, + Anum_pg_variable_varname, + Anum_pg_variable_varnamespace, + Anum_pg_variable_varowner, + Anum_pg_variable_varacl, + OBJECT_VARIABLE, + true + } }; /* @@ -845,6 +860,10 @@ static const struct object_type_map /* OCLASS_STATISTIC_EXT */ { "statistics object", OBJECT_STATISTIC_EXT + }, + /* OCLASS_VARIABLE */ + { + "session variable", OBJECT_VARIABLE } }; @@ -870,6 +889,7 @@ static ObjectAddress get_object_address_attrdef(ObjectType objtype, bool missing_ok); static ObjectAddress get_object_address_type(ObjectType objtype, TypeName *typename, bool missing_ok); +static ObjectAddress get_object_address_variable(List *object, bool missing_ok); static ObjectAddress get_object_address_opcf(ObjectType objtype, List *object, bool missing_ok); static ObjectAddress get_object_address_opf_member(ObjectType objtype, @@ -1139,6 +1159,9 @@ get_object_address(ObjectType objtype, Node *object, missing_ok); address.objectSubId = 0; break; + case OBJECT_VARIABLE: + address = get_object_address_variable(castNode(List, object), missing_ok); + break; default: elog(ERROR, "unrecognized objtype: %d", (int) objtype); /* placate compiler, in case it thinks elog might return */ @@ -2038,16 +2061,20 @@ get_object_address_defacl(List *object, bool missing_ok) case DEFACLOBJ_NAMESPACE: objtype_str = "schemas"; break; + case DEFACLOBJ_VARIABLE: + objtype_str = "variables"; + break; default: ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), errmsg("unrecognized default ACL object type \"%c\"", objtype), - errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".", + errhint("Valid object types are \"%c\", \"%c\", \"%c\", \"%c\", \"%c\", \"%c\".", DEFACLOBJ_RELATION, DEFACLOBJ_SEQUENCE, DEFACLOBJ_FUNCTION, DEFACLOBJ_TYPE, - DEFACLOBJ_NAMESPACE))); + DEFACLOBJ_NAMESPACE, + DEFACLOBJ_VARIABLE))); } /* @@ -2132,6 +2159,24 @@ textarray_to_strvaluelist(ArrayType *arr) return list; } +/* + * Find the ObjectAddress for a session variable + */ +static ObjectAddress +get_object_address_variable(List *object, bool missing_ok) +{ + ObjectAddress address; + char *nspname = NULL; + char *varname = NULL; + + ObjectAddressSet(address, VariableRelationId, InvalidOid); + + DeconstructQualifiedName(object, &nspname, &varname); + address.objectId = LookupVariable(nspname, varname, missing_ok); + + return address; +} + /* * SQL-callable version of get_object_address */ @@ -2322,6 +2367,7 @@ pg_get_object_address(PG_FUNCTION_ARGS) case OBJECT_TABCONSTRAINT: case OBJECT_OPCLASS: case OBJECT_OPFAMILY: + case OBJECT_VARIABLE: objnode = (Node *) name; break; case OBJECT_ACCESS_METHOD: @@ -2630,6 +2676,11 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, aclcheck_error(ACLCHECK_NOT_OWNER, objtype, NameListToString(castNode(List, object))); break; + case OBJECT_VARIABLE: + if (!pg_variable_ownercheck(address.objectId, roleid)) + aclcheck_error(ACLCHECK_NOT_OWNER, objtype, + NameListToString(castNode(List, object))); + break; default: elog(ERROR, "unrecognized object type: %d", (int) objtype); @@ -3558,6 +3609,32 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok) break; } + case OCLASS_VARIABLE: + { + char *nspname; + HeapTuple tup; + Form_pg_variable varform; + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(object->objectId)); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for session variable %u", + object->objectId); + + varform = (Form_pg_variable) GETSTRUCT(tup); + + if (VariableIsVisible(object->objectId)) + nspname = NULL; + else + nspname = get_namespace_name(varform->varnamespace); + + appendStringInfo(&buffer, _("session variable %s"), + quote_qualified_identifier(nspname, + NameStr(varform->varname))); + + ReleaseSysCache(tup); + break; + } + case OCLASS_TSPARSER: { HeapTuple tup; @@ -3868,6 +3945,16 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok) _("default privileges on new schemas belonging to role %s"), rolename); break; + case DEFACLOBJ_VARIABLE: + if (nspname) + appendStringInfo(&buffer, + _("default privileges on new session variables belonging to role %s in schema %s"), + rolename, nspname); + else + appendStringInfo(&buffer, + _("default privileges on new session variables belonging to role %s"), + rolename); + break; default: /* shouldn't get here */ if (nspname) @@ -4610,6 +4697,10 @@ getObjectTypeDescription(const ObjectAddress *object, bool missing_ok) appendStringInfoString(&buffer, "transform"); break; + case OCLASS_VARIABLE: + appendStringInfoString(&buffer, "session variable"); + break; + /* * There's intentionally no default: case here; we want the * compiler to warn if a new OCLASS hasn't been handled above. @@ -5703,6 +5794,10 @@ getObjectIdentityParts(const ObjectAddress *object, appendStringInfoString(&buffer, " on schemas"); break; + case DEFACLOBJ_VARIABLE: + appendStringInfoString(&buffer, + " on session variables"); + break; } if (objname) @@ -5918,6 +6013,33 @@ getObjectIdentityParts(const ObjectAddress *object, } break; + case OCLASS_VARIABLE: + { + char *schema; + char *varname; + HeapTuple tup; + Form_pg_variable varform; + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(object->objectId)); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for session variable %u", + object->objectId); + + varform = (Form_pg_variable) GETSTRUCT(tup); + + schema = get_namespace_name_or_temp(varform->varnamespace); + varname = NameStr(varform->varname); + + appendStringInfo(&buffer, "%s", + quote_qualified_identifier(schema, varname)); + + if (objname) + *objname = list_make2(schema, varname); + + ReleaseSysCache(tup); + break; + } + /* * There's intentionally no default: case here; we want the * compiler to warn if a new OCLASS hasn't been handled above. diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c index c20b1fbb96..41b8aec090 100644 --- a/src/backend/catalog/pg_shdepend.c +++ b/src/backend/catalog/pg_shdepend.c @@ -46,6 +46,7 @@ #include "catalog/pg_ts_dict.h" #include "catalog/pg_type.h" #include "catalog/pg_user_mapping.h" +#include "catalog/pg_variable.h" #include "commands/alter.h" #include "commands/collationcmds.h" #include "commands/conversioncmds.h" @@ -1593,6 +1594,7 @@ shdepReassignOwned(List *roleids, Oid newrole) case DatabaseRelationId: case TSConfigRelationId: case TSDictionaryRelationId: + case VariableRelationId: { Oid classId = sdepForm->classid; Relation catalog; diff --git a/src/backend/catalog/pg_variable.c b/src/backend/catalog/pg_variable.c new file mode 100644 index 0000000000..f1216c1a3c --- /dev/null +++ b/src/backend/catalog/pg_variable.c @@ -0,0 +1,387 @@ +/*------------------------------------------------------------------------- + * + * pg_variable.c + * session variables + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/catalog/pg_variable.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/heapam.h" +#include "access/htup_details.h" + +#include "catalog/catalog.h" +#include "catalog/dependency.h" +#include "catalog/indexing.h" +#include "catalog/namespace.h" +#include "catalog/objectaccess.h" +#include "catalog/pg_namespace.h" +#include "catalog/pg_type.h" +#include "catalog/pg_variable.h" + +#include "commands/session_variable.h" + +#include "storage/lmgr.h" + +#include "utils/builtins.h" +#include "utils/lsyscache.h" +#include "utils/syscache.h" + +static VariableEOXActionCodes +to_eoxaction_code(VariableEOXAction action) +{ + switch (action) + { + case VARIABLE_EOX_NOOP: + return VARIABLE_EOX_CODE_NOOP; + + case VARIABLE_EOX_DROP: + return VARIABLE_EOX_CODE_DROP; + + case VARIABLE_EOX_RESET: + return VARIABLE_EOX_CODE_RESET; + + default: + elog(ERROR, "unexpected action"); + } +} + +static VariableEOXAction +to_eoxaction(VariableEOXActionCodes code) +{ + switch (code) + { + case VARIABLE_EOX_CODE_NOOP: + return VARIABLE_EOX_NOOP; + + case VARIABLE_EOX_CODE_DROP: + return VARIABLE_EOX_DROP; + + case VARIABLE_EOX_CODE_RESET: + return VARIABLE_EOX_RESET; + + default: + elog(ERROR, "unexpected code"); + } +} + +/* + * Returns name of session variable. When the variable is not in the path, + * the name is qualified. + */ +char * +session_variable_get_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 = NULL; + else + nspname = get_namespace_name(varform->varnamespace); + + result = quote_qualified_identifier(nspname, varname); + + ReleaseSysCache(tup); + + return result; +} + +/* + * Returns varname field of pg_variable + */ +char * +get_session_variable_name(Oid varid) +{ + HeapTuple tup; + Form_pg_variable varform; + char *varname; + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for session variable %u", varid); + + varform = (Form_pg_variable) GETSTRUCT(tup); + + varname = NameStr(varform->varname); + + ReleaseSysCache(tup); + + return varname; +} + +/* + * Returns type, typmod of session variable + */ +void +get_session_variable_type_typmod_collid(Oid varid, Oid *typid, int32 *typmod, Oid *collid) +{ + HeapTuple tup; + Form_pg_variable varform; + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for session variable %u", varid); + + varform = (Form_pg_variable) GETSTRUCT(tup); + + *typid = varform->vartype; + *typmod = varform->vartypmod; + *collid = varform->varcollation; + + ReleaseSysCache(tup); + + return; +} + +/* + * Fetch all fields of session variable from the syscache. + */ +void +initVariable(Variable *var, Oid varid, bool missing_ok, bool fast_only) +{ + HeapTuple tup; + Form_pg_variable varform; + Datum defexpr_datum; + bool defexpr_isnull; + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + + if (!HeapTupleIsValid(tup)) + { + if (missing_ok) + { + var->oid = InvalidOid; + return; + } + + elog(ERROR, "cache lookup failed for session variable %u", varid); + } + + varform = (Form_pg_variable) GETSTRUCT(tup); + + var->oid = varid; + var->name = pstrdup(NameStr(varform->varname)); + var->namespace = varform->varnamespace; + var->typid = varform->vartype; + var->typmod = varform->vartypmod; + var->owner = varform->varowner; + var->collation = varform->varcollation; + var->eoxaction = to_eoxaction(varform->vareoxaction); + var->is_not_null = varform->varisnotnull; + var->is_immutable = varform->varisimmutable; + + /* Get defexpr */ + defexpr_datum = SysCacheGetAttr(VARIABLEOID, + tup, + Anum_pg_variable_vardefexpr, + &defexpr_isnull); + + var->has_defexpr = !defexpr_isnull; + + /* + * Sometimes we don't need to deserialize defexpr, but we need info about + * the existence of defexpr. + */ + if (!fast_only) + { + Datum aclDatum; + bool isnull; + + /* name */ + var->name = pstrdup(NameStr(varform->varname)); + + if (!defexpr_isnull) + var->defexpr = stringToNode(TextDatumGetCString(defexpr_datum)); + else + var->defexpr = NULL; + + /* Get varacl */ + aclDatum = SysCacheGetAttr(VARIABLEOID, + tup, + Anum_pg_variable_varacl, + &isnull); + if (!isnull) + var->acl = DatumGetAclPCopy(aclDatum); + else + var->acl = NULL; + } + else + { + var->name = NULL; + var->defexpr = NULL; + var->acl = NULL; + } + + ReleaseSysCache(tup); + + return; +} + +/* + * Create entry in pg_variable table + */ +ObjectAddress +VariableCreate(const char *varName, + Oid varNamespace, + Oid varType, + int32 varTypmod, + Oid varOwner, + Oid varCollation, + Node *varDefexpr, + VariableEOXAction eoxaction, + bool is_not_null, + bool if_not_exists, + bool is_immutable) +{ + Acl *varacl; + NameData varname; + bool nulls[Natts_pg_variable]; + Datum values[Natts_pg_variable]; + Relation rel; + HeapTuple tup, + oldtup; + TupleDesc tupdesc; + ObjectAddress myself, + referenced; + Oid varid; + int i; + + for (i = 0; i < Natts_pg_variable; i++) + { + nulls[i] = false; + values[i] = (Datum) 0; + } + + namestrcpy(&varname, varName); + + rel = table_open(VariableRelationId, RowExclusiveLock); + + varid = GetNewOidWithIndex(rel, VariableObjectIndexId, Anum_pg_variable_oid); + values[Anum_pg_variable_oid - 1] = ObjectIdGetDatum(varid); + values[Anum_pg_variable_varname - 1] = NameGetDatum(&varname); + values[Anum_pg_variable_varnamespace - 1] = ObjectIdGetDatum(varNamespace); + values[Anum_pg_variable_vartype - 1] = ObjectIdGetDatum(varType); + values[Anum_pg_variable_vartypmod - 1] = Int32GetDatum(varTypmod); + values[Anum_pg_variable_varowner - 1] = ObjectIdGetDatum(varOwner); + values[Anum_pg_variable_varcollation - 1] = ObjectIdGetDatum(varCollation); + values[Anum_pg_variable_vareoxaction - 1] = CharGetDatum((char) to_eoxaction_code(eoxaction)); + values[Anum_pg_variable_varisnotnull - 1] = BoolGetDatum(is_not_null); + values[Anum_pg_variable_varisimmutable - 1] = BoolGetDatum(is_immutable); + /* varacl will be determined later */ + + if (varDefexpr) + values[Anum_pg_variable_vardefexpr - 1] = CStringGetTextDatum(nodeToString(varDefexpr)); + else + nulls[Anum_pg_variable_vardefexpr - 1] = true; + + tupdesc = RelationGetDescr(rel); + + oldtup = SearchSysCache2(VARIABLENAMENSP, + PointerGetDatum(varName), + ObjectIdGetDatum(varNamespace)); + + if (HeapTupleIsValid(oldtup)) + { + if (if_not_exists) + ereport(NOTICE, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("session variable \"%s\" already exists, skipping", + varName))); + else + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_OBJECT), + errmsg("session variable \"%s\" already exists", + varName))); + + ReleaseSysCache(oldtup); + table_close(rel, RowExclusiveLock); + + return InvalidObjectAddress; + } + + varacl = get_user_default_acl(OBJECT_VARIABLE, varOwner, + varNamespace); + + if (varacl != NULL) + values[Anum_pg_variable_varacl - 1] = PointerGetDatum(varacl); + else + nulls[Anum_pg_variable_varacl - 1] = true; + + tup = heap_form_tuple(tupdesc, values, nulls); + CatalogTupleInsert(rel, tup); + + myself.classId = VariableRelationId; + myself.objectId = varid; + myself.objectSubId = 0; + + /* dependency on namespace */ + referenced.classId = NamespaceRelationId; + referenced.objectId = varNamespace; + referenced.objectSubId = 0; + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + + /* dependency on used type */ + referenced.classId = TypeRelationId; + referenced.objectId = varType; + referenced.objectSubId = 0; + recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL); + + /* dependency on default expr */ + if (varDefexpr) + recordDependencyOnExpr(&myself, (Node *) varDefexpr, + NIL, DEPENDENCY_NORMAL); + + /* dependency on any roles mentioned in ACL */ + if (varacl != NULL) + { + int nnewmembers; + Oid *newmembers; + + nnewmembers = aclmembers(varacl, &newmembers); + updateAclDependencies(VariableRelationId, varid, 0, + varOwner, + 0, NULL, + nnewmembers, newmembers); + } + + /* dependency on extension */ + recordDependencyOnCurrentExtension(&myself, false); + + /* + * When this new field in catalog is temporal, we need to create + * new xact action to ensure delete from catalog. + */ + if (eoxaction == VARIABLE_EOX_DROP) + RegisterOnCommitDropSessionVariable(myself.objectId); + + heap_freetuple(tup); + + /* Post creation hook for new function */ + InvokeObjectPostCreateHook(VariableRelationId, varid, 0); + + table_close(rel, RowExclusiveLock); + + return myself; +} diff --git a/src/backend/commands/Makefile b/src/backend/commands/Makefile index e8504f0ae4..c723e229f9 100644 --- a/src/backend/commands/Makefile +++ b/src/backend/commands/Makefile @@ -48,6 +48,7 @@ OBJS = \ proclang.o \ publicationcmds.o \ schemacmds.o \ + sessionvariable.o \ seclabel.o \ sequence.o \ statscmds.o \ diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 40044070cf..a5aba05314 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -40,6 +40,7 @@ #include "catalog/pg_ts_dict.h" #include "catalog/pg_ts_parser.h" #include "catalog/pg_ts_template.h" +#include "catalog/pg_variable.h" #include "commands/alter.h" #include "commands/collationcmds.h" #include "commands/conversioncmds.h" @@ -141,6 +142,10 @@ report_namespace_conflict(Oid classId, const char *name, Oid nspOid) Assert(OidIsValid(nspOid)); msgfmt = gettext_noop("text search configuration \"%s\" already exists in schema \"%s\""); break; + case VariableRelationId: + Assert(OidIsValid(nspOid)); + msgfmt = gettext_noop("session variable \"%s\" already exists in schema \"%s\""); + break; default: elog(ERROR, "unsupported object class %u", classId); break; @@ -393,6 +398,7 @@ ExecRenameStmt(RenameStmt *stmt) case OBJECT_TSTEMPLATE: case OBJECT_PUBLICATION: case OBJECT_SUBSCRIPTION: + case OBJECT_VARIABLE: { ObjectAddress address; Relation catalog; @@ -536,6 +542,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt, case OBJECT_TSDICTIONARY: case OBJECT_TSPARSER: case OBJECT_TSTEMPLATE: + case OBJECT_VARIABLE: { Relation catalog; Relation relation; @@ -626,6 +633,7 @@ AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid, case OCLASS_TSDICT: case OCLASS_TSTEMPLATE: case OCLASS_TSCONFIG: + case OCLASS_VARIABLE: { Relation catalog; @@ -884,6 +892,7 @@ ExecAlterOwnerStmt(AlterOwnerStmt *stmt) case OBJECT_TABLESPACE: case OBJECT_TSDICTIONARY: case OBJECT_TSCONFIGURATION: + case OBJECT_VARIABLE: { Relation catalog; Relation relation; diff --git a/src/backend/commands/discard.c b/src/backend/commands/discard.c index 57d3d7dd9b..fb14002393 100644 --- a/src/backend/commands/discard.c +++ b/src/backend/commands/discard.c @@ -19,6 +19,7 @@ #include "commands/discard.h" #include "commands/prepare.h" #include "commands/sequence.h" +#include "commands/session_variable.h" #include "utils/guc.h" #include "utils/portal.h" @@ -48,6 +49,10 @@ DiscardCommand(DiscardStmt *stmt, bool isTopLevel) ResetTempTableNamespace(); break; + case DISCARD_VARIABLES: + ResetSessionVariables(); + break; + default: elog(ERROR, "unrecognized DISCARD target: %d", stmt->target); } @@ -75,4 +80,5 @@ DiscardAll(bool isTopLevel) ResetPlanCache(); ResetTempTableNamespace(); ResetSequenceCaches(); + ResetSessionVariables(); } diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c index 4e545adf95..4a0d7c7814 100644 --- a/src/backend/commands/dropcmds.c +++ b/src/backend/commands/dropcmds.c @@ -481,6 +481,10 @@ does_not_exist_skipping(ObjectType objtype, Node *object) msg = gettext_noop("publication \"%s\" does not exist, skipping"); name = strVal(object); break; + case OBJECT_VARIABLE: + msg = gettext_noop("session variable \"%s\" does not exist, skipping"); + name = NameListToString(castNode(List, object)); + break; default: elog(ERROR, "unrecognized object type: %d", (int) objtype); break; diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index df264329d8..98d0e2ec25 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -991,6 +991,7 @@ EventTriggerSupportsObjectType(ObjectType obtype) case OBJECT_TSTEMPLATE: case OBJECT_TYPE: case OBJECT_USER_MAPPING: + case OBJECT_VARIABLE: case OBJECT_VIEW: return true; @@ -1055,6 +1056,7 @@ EventTriggerSupportsObjectClass(ObjectClass objclass) case OCLASS_PUBLICATION_REL: case OCLASS_SUBSCRIPTION: case OCLASS_TRANSFORM: + case OCLASS_VARIABLE: return true; /* @@ -2106,6 +2108,8 @@ stringify_grant_objtype(ObjectType objtype) return "TABLESPACE"; case OBJECT_TYPE: return "TYPE"; + case OBJECT_VARIABLE: + return "VARIABLE"; /* these currently aren't used */ case OBJECT_ACCESS_METHOD: case OBJECT_AGGREGATE: @@ -2189,6 +2193,8 @@ stringify_adefprivs_objtype(ObjectType objtype) return "TABLESPACES"; case OBJECT_TYPE: return "TYPES"; + case OBJECT_VARIABLE: + return "VARIABLES"; /* these currently aren't used */ case OBJECT_ACCESS_METHOD: case OBJECT_AGGREGATE: diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 09f5253abb..3a92159c28 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -492,6 +492,22 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es, else ExplainDummyGroup("Notify", NULL, es); } + else if (IsA(utilityStmt, LetStmt)) + { + LetStmt *letstmt = (LetStmt *) utilityStmt; + List *rewritten; + + 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); + ExplainOneQuery(linitial_node(Query, rewritten), + CURSOR_OPT_PARALLEL_OK, NULL, es, + queryString, params, queryEnv); + } else { if (es->format == EXPLAIN_FORMAT_TEXT) diff --git a/src/backend/commands/seclabel.c b/src/backend/commands/seclabel.c index 53c18628a7..c094a3871c 100644 --- a/src/backend/commands/seclabel.c +++ b/src/backend/commands/seclabel.c @@ -91,6 +91,7 @@ SecLabelSupportsObjectType(ObjectType objtype) case OBJECT_TSPARSER: case OBJECT_TSTEMPLATE: case OBJECT_USER_MAPPING: + case OBJECT_VARIABLE: return false; /* diff --git a/src/backend/commands/sessionvariable.c b/src/backend/commands/sessionvariable.c new file mode 100644 index 0000000000..8d0eb9e91c --- /dev/null +++ b/src/backend/commands/sessionvariable.c @@ -0,0 +1,1085 @@ +/*------------------------------------------------------------------------- + * + * sessionvariable.c + * session variable creation/manipulation commands + * + * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * IDENTIFICATION + * src/backend/commands/sessionvariable.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" +#include "miscadmin.h" +#include "access/heapam.h" +#include "access/htup_details.h" +#include "access/xact.h" +#include "catalog/dependency.h" +#include "catalog/indexing.h" +#include "catalog/namespace.h" +#include "catalog/pg_variable.h" +#include "commands/session_variable.h" +#include "executor/executor.h" +#include "executor/svariableReceiver.h" +#include "optimizer/optimizer.h" +#include "parser/parse_coerce.h" +#include "parser/parse_collate.h" +#include "parser/parse_expr.h" +#include "parser/parse_type.h" +#include "rewrite/rewriteHandler.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" + +/* + * An values of session variables are stored in local memory + * in sessionvars hash table. This local memory have to be + * cleaned, when a) session variable is dropped by current or + * by other session, b) when user enforce it by using clause + * ON TRANSACTION END RESET. The life cycle of temporal session + * variable can be limmited by using clause ON COMMIT DROP. + * + * Although session variables are not transactional, we don't + * want (and we cannot) to run cleaning immediately (when we + * got sinval message). The value of session variables can + * be still used or the operation that emits cleaning can be + * reverted. Unfortunatelly, this check can be done only in + * when transaction is committed (the check against system + * catalog requires transaction state). + * + * This is reason why cleaning memory (session variable reset) + * is postponed to end of transaction, and we need to hold + * some actions lists. We have to hold two separate action + * lists - one for dropping from system catalog, and second + * for resetting. It's necessary, because dropping session + * variable enforces session variable reset. The drop operation + * can be executed when we iterate over action list, and in + * this moment we would not to modify same action list. + * + * We want to support possibility to reset session variable at + * the end of transaction. This ensure initial state of session + * variable at the begin of each transaction. The reset is + * implemented like removing variable from sessionvars hash table. + * This enforce full initialization in next usage. + * Attention - this is not same as drop session variable. + * + * Another functionality is dropping temporary session variable + * with the option ON COMMIT DROP. + */ +typedef enum SVariableXActAction +{ + ON_COMMIT_DROP, /* used for ON COMMIT DROP */ + ON_COMMIT_RESET, /* used for DROP VARIABLE */ + RESET, /* used for ON TRANSACTION END RESET */ + RECHECK /* recheck if session variable is living */ +} SVariableXActAction; + +typedef struct SVariableXActActionItem +{ + Oid varid; /* varid of session variable */ + SVariableXActAction action; /* reset or drop */ + + /* + * If this entry was created during the current transaction, + * creating_subid is the ID of the creating subxact; if created in a prior + * transaction, creating_subid is zero. If deleted during the current + * transaction, deleting_subid is the ID of the deleting subxact; if no + * deletion request is pending, deleting_subid is zero. + */ + SubTransactionId creating_subid; + SubTransactionId deleting_subid; +} SVariableXActActionItem; + +static List *xact_drop_actions = NIL; +static List *xact_reset_actions = NIL; + +typedef struct SVariableData +{ + Oid varid; /* pg_variable OID of this sequence (hash key) */ + Oid typid; /* OID of the data type */ + int16 typlen; + bool typbyval; + bool isnull; + bool freeval; + Datum value; + + bool is_rowtype; /* true when variable is composite */ + bool is_not_null; /* don't allow null values */ + bool is_immutable; /* true when variable is immutable */ + bool has_defexpr; /* true when there are default value */ + + bool is_valid; /* true when variable was successfuly + * initialized */ + + uint32 hashvalue; +} SVariableData; + +typedef SVariableData * SVariable; + +static HTAB *sessionvars = NULL; /* hash table for session variables */ +static MemoryContext SVariableMemoryContext = NULL; + +static bool first_time = true; + +static bool sync_sessionvars_all = false; + +static void register_session_variable_xact_action(Oid varid, SVariableXActAction action); +static void delete_session_variable_xact_action(Oid varid, SVariableXActAction action); + +/* + * Releases stored data from session variable. The hash entry + * stay without change. + */ +static void +free_session_variable_value(SVariable svar) +{ + if (svar->freeval) + pfree(DatumGetPointer(svar->value)); + + /* Clean current value */ + svar->value = (Datum) 0; + svar->isnull = true; + svar->freeval = false; + + /* + * We can mark this session variable as valid when + * it has not default expression, and when null is + * allowed. When it has defexpr, then the content + * will be valid after an assignment or defexp evaluation. + */ + svar->is_valid = !svar->has_defexpr && !svar->is_not_null; +} + +/* + * Release the variable defined by varid from sessionvars + * hashtab. + */ +static void +free_session_variable(SVariable svar) +{ + free_session_variable_value(svar); + + if (hash_search(sessionvars, + (void *) &svar->varid, + HASH_REMOVE, + NULL) == NULL) + elog(DEBUG1, "hash table corrupted"); +} + +/* + * Release the variable defined by varid from sessionvars + * hashtab. + */ +static void +free_session_variable_varid(Oid varid) +{ + SVariable svar; + bool found; + + if (sessionvars == NULL) + return; + + svar = (SVariable) hash_search(sessionvars, &varid, + HASH_FIND, &found); + if (found) + free_session_variable(svar); +} + +/* + * Assign sinval mark to session variable. This mark probably + * signalized, so the session variable was dropped. But this + * should be rechecked later against system catalog. + */ +static void +pg_variable_cache_callback(Datum arg, int cacheid, uint32 hashvalue) +{ + /* + * We can have not sessionvars in initialized state, and + * we can get this message. This is possible after execution of + * DISCARD VARIABLES command. + */ + if (!sessionvars) + return; + + /* + * When we know so all session variables should be synchronized, + * then is useless to continue; + */ + if (sync_sessionvars_all) + return; + + /* + * Because we cannot to decode varid from hashValue, we should + * to iterate over all currently used session variables to find + * session variable with same hashValue. On second hand, this can + * save us some CPU later, because we don't need to check any used + * session variable (by current session) against system catalog. + */ + if (hashvalue != 0) + { + HASH_SEQ_STATUS status; + SVariable svar; + + hash_seq_init(&status, sessionvars); + + while ((svar = (SVariable) hash_seq_search(&status)) != NULL) + { + if (svar->hashvalue == hashvalue) + register_session_variable_xact_action(svar->varid, RECHECK); + + /* + * although it there is low probability, we have to iterate + * over all actively used session variables, because hashvalue + * is not unique identifier. + */ + } + } + else + sync_sessionvars_all = true; +} + +/* + * When we need to recheck all session variables, then + * the most effective method is seq scan over hash tab. + */ +static void +sync_sessionvars_xact_callback(XactEvent event, void *arg) +{ + HASH_SEQ_STATUS status; + SVariable svar; + + if (!sync_sessionvars_all) + return; + + /* + * This callback can be called when database is not in + * transaction state - XACT_EVENT_ABORT, XACT_EVENT_COMMIT, + * but the checks against system catalog can be done only + * in transaction state. + */ + if (!IsTransactionState()) + return; + + Assert(sessionvars); + + hash_seq_init(&status, sessionvars); + + while ((svar = (SVariable) hash_seq_search(&status)) != NULL) + { + HeapTuple tp; + + tp = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(svar->varid)); + + if (HeapTupleIsValid(tp)) + ReleaseSysCache(tp); + else + free_session_variable(svar); + } + + sync_sessionvars_all = false; +} + +/* + * Create the hash table for storing session variables + */ +static void +create_sessionvars_hashtable(void) +{ + HASHCTL ctl; + + /* set callbacks */ + if (first_time) + { + /* Read sinval messages */ + CacheRegisterSyscacheCallback(VARIABLEOID, + pg_variable_cache_callback, + (Datum) 0); + + /* at transaction end recheck sinvalidated variables */ + RegisterXactCallback(sync_sessionvars_xact_callback, NULL); + + first_time = false; + } + + /* needs its own long lived memory context */ + if (SVariableMemoryContext == NULL) + { + SVariableMemoryContext = + AllocSetContextCreate(TopMemoryContext, + "session variables", + ALLOCSET_START_SMALL_SIZES); + } + + memset(&ctl, 0, sizeof(ctl)); + ctl.keysize = sizeof(Oid); + ctl.entrysize = sizeof(SVariableData); + ctl.hcxt = SVariableMemoryContext; + + sessionvars = hash_create("Session variables", 64, &ctl, + HASH_ELEM | HASH_BLOBS | HASH_CONTEXT); +} + +/* + * Assign some content to the session variable. It's copied to + * SVariableMemoryContext if it is necessary. + * + * init_mode is true, when the value of session variable is initialized + * by default expression or by null. Only in this moment we can allow to + * modify immutable variables with default expression. + */ +static void +set_session_variable(SVariable svar, Datum value, + bool isnull, Oid typid, + bool init_mode) +{ + MemoryContext oldcxt; + Datum newval = value; + + /* Don't allow assignment of null to NOT NULL variable */ + if (isnull && svar->is_not_null) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value is not allowed for NOT NULL session variable \"%s\"", + session_variable_get_name(svar->varid)))); + + if (!isnull && svar->typid != typid) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("type \"%s\" of assigned value is different than type \"%s\" of session variable \"%s\"", + format_type_be(typid), + format_type_be(svar->typid), + session_variable_get_name(svar->varid)))); + + /* + * Don't allow updating of immutable session variable that has assigned + * not null value or has default expression (and then the value should be + * result of default expression always). Don't do this check, when variable + * is initialized. + */ + if (!init_mode && + (svar->is_immutable && (!svar->isnull || svar->has_defexpr))) + ereport(ERROR, + (errcode(ERRCODE_ERROR_IN_ASSIGNMENT), + errmsg("session variable \"%s\" is declared IMMUTABLE", + session_variable_get_name(svar->varid)))); + + /* copy value to session persistent context */ + oldcxt = MemoryContextSwitchTo(SVariableMemoryContext); + if (!isnull) + newval = datumCopy(value, svar->typbyval, svar->typlen); + MemoryContextSwitchTo(oldcxt); + + free_session_variable_value(svar); + + svar->value = newval; + svar->isnull = isnull; + svar->freeval = newval != value; + svar->is_valid = true; +} + +/* + * Initialize svar from var + * svar - SVariable - holds data + * var - Variable - holds metadata + */ +static void +init_session_variable(SVariable svar, Variable *var) +{ + Assert(OidIsValid(var->oid)); + + svar->varid = var->oid; + svar->typid = var->typid; + + get_typlenbyval(var->typid, + &svar->typlen, + &svar->typbyval); + + svar->isnull = true; + svar->freeval = false; + svar->value = (Datum) 0; + + svar->is_rowtype = type_is_rowtype(var->typid); + svar->is_not_null = var->is_not_null; + svar->is_immutable = var->is_immutable; + svar->has_defexpr = var->has_defexpr; + + svar->hashvalue = GetSysCacheHashValue1(VARIABLEOID, + ObjectIdGetDatum(var->oid)); + + /* the value of variable is not known yet */ + svar->is_valid = false; + + if (var->eoxaction == VARIABLE_EOX_RESET || + var->eoxaction == VARIABLE_EOX_DROP) + register_session_variable_xact_action(var->oid, RESET); +} + +/* + * Try to search value in hash table. If it doesn't + * exist, then insert it (and calculate defexpr if it exists). + */ +static SVariable +prepare_variable_for_reading(Oid varid) +{ + SVariable svar; + Variable var; + bool found; + + var.oid = InvalidOid; + + if (!sessionvars) + create_sessionvars_hashtable(); + + svar = (SVariable) hash_search(sessionvars, &varid, + HASH_ENTER, &found); + + /* Return content if it is available and valid */ + if (found && svar->is_valid) + return svar; + + /* We need to load defexpr. */ + initVariable(&var, varid, false, false); + + if (!found) + init_session_variable(svar, &var); + + /* Raise an error when we cannot initialize variable correctly */ + if (var.is_not_null && !var.defexpr) + ereport(ERROR, + (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("null value is not allowed for NOT NULL session variable \"%s\"", + session_variable_get_name(varid)), + errdetail("The session variable was not initialized yet."))); + + if (svar->has_defexpr) + { + Datum value = (Datum) 0; + bool isnull; + EState *estate = NULL; + Expr *defexpr; + ExprState *defexprs; + MemoryContext oldcxt; + + /* Prepare default expr */ + estate = CreateExecutorState(); + + oldcxt = MemoryContextSwitchTo(estate->es_query_cxt); + + defexpr = expression_planner((Expr *) var.defexpr); + defexprs = ExecInitExpr(defexpr, NULL); + value = ExecEvalExprSwitchContext(defexprs, + GetPerTupleExprContext(estate), + &isnull); + + + /* Store result before releasing Executor memory */ + set_session_variable(svar, value, isnull, svar->typid, true); + + MemoryContextSwitchTo(oldcxt); + + FreeExecutorState(estate); + } + else + set_session_variable(svar, (Datum) 0, true, svar->typid, true); + + return svar; +} + +/* + * Write value to variable. We expect secured access in this moment. + * We try not to break the previous value, if something is wrong. + */ +void +SetSessionVariable(Oid varid, Datum value, bool isNull, Oid typid) +{ + SVariable svar; + bool found; + + if (!sessionvars) + create_sessionvars_hashtable(); + + svar = (SVariable) hash_search(sessionvars, &varid, + HASH_ENTER, &found); + + /* Initialize svar when not initialized or when stored value is null */ + if (!found) + { + Variable var; + + /* don't need defexpr and acl here */ + initVariable(&var, varid, false, true); + init_session_variable(svar, &var); + } + + set_session_variable(svar, value, isNull, typid, false); +} + +/* + * Write value to variable with security check. + * We try not to break the previous value, if something is wrong. + */ +void +SetSessionVariableWithSecurityCheck(Oid varid, Datum value, bool isNull, Oid typid) +{ + AclResult aclresult; + + /* + * Is possible to write to session variable? + */ + aclresult = pg_variable_aclcheck(varid, GetUserId(), ACL_WRITE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, session_variable_get_name(varid)); + + SetSessionVariable(varid, value, isNull, typid); +} + +/* + * Returns a copy of value of the session variable specified by varid + */ +Datum +CopySessionVariable(Oid varid, bool *isNull, Oid *typid) +{ + SVariable svar; + + svar = prepare_variable_for_reading(varid); + Assert(svar != NULL && svar->is_valid); + + *isNull = svar->isnull; + *typid = svar->typid; + + if (!svar->isnull) + return datumCopy(svar->value, svar->typbyval, svar->typlen); + + return (Datum) 0; +} + +/* + * Returns the value of the session variable specified by varid. Check correct + * result type. Optionally the result can be copied. + */ +Datum +GetSessionVariable(Oid varid, bool *isNull, Oid expected_typid, bool copy) +{ + SVariable svar; + Datum value; + bool isnull; + + svar = prepare_variable_for_reading(varid); + Assert(svar != NULL); + + if (expected_typid != svar->typid) + elog(ERROR, "type of variable \"%s\" is different than expected", + session_variable_get_name(varid)); + + value = svar->value; + isnull = svar->isnull; + + *isNull = isnull; + + if (!isnull && copy) + return datumCopy(value, svar->typbyval, svar->typlen); + + return value; +} + + +/* + * Routines used for manipulation with session variables from + * SQL level + */ + +/* + * Creates new variable - entry in pg_catalog.pg_variable table + * + * Used by CREATE VARIABLE command + */ +ObjectAddress +DefineSessionVariable(ParseState *pstate, CreateSessionVarStmt *stmt) +{ + Oid namespaceid; + AclResult aclresult; + Oid typid; + int32 typmod; + Oid varowner = GetUserId(); + Oid collation; + Oid typcollation; + ObjectAddress variable; + + Node *cooked_default = NULL; + + /* + * Check consistency of arguments + */ + if (stmt->eoxaction == VARIABLE_EOX_DROP + && stmt->variable->relpersistence != RELPERSISTENCE_TEMP) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("ON COMMIT DROP can only be used on temporary variables"))); + + if (stmt->is_not_null && stmt->is_immutable && !stmt->defexpr) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("IMMUTABLE NOT NULL variable requires default expression"))); + + namespaceid = + RangeVarGetAndCheckCreationNamespace(stmt->variable, NoLock, NULL); + + typenameTypeIdAndMod(pstate, stmt->typeName, &typid, &typmod); + typcollation = get_typcollation(typid); + + aclresult = pg_type_aclcheck(typid, GetUserId(), ACL_USAGE); + if (aclresult != ACLCHECK_OK) + aclcheck_error_type(aclresult, typid); + + if (stmt->collClause) + collation = LookupCollation(pstate, + stmt->collClause->collname, + stmt->collClause->location); + else + collation = typcollation;; + + /* Complain if COLLATE is applied to an uncollatable type */ + if (OidIsValid(collation) && !OidIsValid(typcollation)) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("collations are not supported by type %s", + format_type_be(typid)), + parser_errposition(pstate, stmt->collClause->location))); + + if (stmt->defexpr) + { + cooked_default = transformExpr(pstate, stmt->defexpr, + EXPR_KIND_VARIABLE_DEFAULT); + + cooked_default = coerce_to_specific_type(pstate, + cooked_default, typid, "DEFAULT"); + assign_expr_collations(pstate, cooked_default); + } + + variable = VariableCreate(stmt->variable->relname, + namespaceid, + typid, + typmod, + varowner, + collation, + cooked_default, + stmt->eoxaction, + stmt->is_not_null, + stmt->if_not_exists, + stmt->is_immutable); + + /* + * We must bump the command counter to make the newly-created variable + * tuple visible for any other operations. + */ + CommandCounterIncrement(); + + return variable; +} + +/* + * Create new ON_COMMIT_DROP xact action. We have to drop + * ON COMMIT DROP variable, although this variable should not + * be used. So we need to register this action in CREATE VARIABLE + * time. + */ +void +RegisterOnCommitDropSessionVariable(Oid varid) +{ + register_session_variable_xact_action(varid, ON_COMMIT_DROP); +} + +/* + * Drop variable by OID. This routine doesn't try to remove + * the value of session variable immediately. It will be + * removed on transaction end in sync_sessionvars_xact_callback + * routine. This routine manipulate just with system catalog. + */ +void +RemoveSessionVariable(Oid varid) +{ + Relation rel; + HeapTuple tup; + + rel = table_open(VariableRelationId, RowExclusiveLock); + + tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid)); + + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for variable %u", varid); + + CatalogTupleDelete(rel, &tup->t_self); + + ReleaseSysCache(tup); + + table_close(rel, RowExclusiveLock); + + /* + * we removed entry from sys catalog already, we should not to do + * again at xact time, + */ + delete_session_variable_xact_action(varid, ON_COMMIT_DROP); + + /* + * and if this transaction or subtransaction will be committed, + * we want to enforce variable cleaning. (we don't need to wait for + * sinval message). The cleaning action for one session variable + * can be repeated in the action list, and it doesn't do any problem + * (so we don't need to ensure uniqueness). + * We need separate action than RESET, because RESET + * is executed on any transaction end, but we want to execute cleaning + * only when current transaction will be commited. + */ + register_session_variable_xact_action(varid, ON_COMMIT_RESET); +} + +/* + * Fast drop complete content of all session variables hash table. + * This is code for DISCARD VARIABLES command. This command + * cannot to run inside transaction, so we don't need to handle + * end of transaction actions. + */ +void +ResetSessionVariables(void) +{ + /* Destroy hash table and reset related memory context */ + if (sessionvars) + { + hash_destroy(sessionvars); + sessionvars = NULL; + } + + /* Release memory allocated by session variables */ + if (SVariableMemoryContext != NULL) + MemoryContextReset(SVariableMemoryContext); + + /* + * There are not any session variables, so trim + * both xact action lists. + */ + list_free_deep(xact_drop_actions); + xact_drop_actions = NIL; + + list_free_deep(xact_reset_actions); + xact_drop_actions = NIL; +} + +/* + * 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 = pg_variable_aclcheck(varid, GetUserId(), ACL_WRITE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, session_variable_get_name(varid)); + + /* Create dest receiver for LET */ + dest = CreateDestReceiver(DestVariable); + SetVariableDestReceiverParams(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 */ + 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(); +} + + +/* + * Implementation of drop or reset actions executed on session variables + * at transaction end time. We want to drop temporary session variables + * with clause ON COMMIT DROP, or we want to reset values of session variables + * with clause ON TRANSACTION END RESET or we want to clean (reset) memory + * allocated by values of dropped session variables. + */ + +/* + * Register a session variable xact action. + */ +void +register_session_variable_xact_action(Oid varid, + SVariableXActAction action) +{ + SVariableXActActionItem *xact_ai; + MemoryContext oldcxt; + + oldcxt = MemoryContextSwitchTo(CacheMemoryContext); + + xact_ai = (SVariableXActActionItem *) + palloc(sizeof(SVariableXActActionItem)); + + xact_ai->varid = varid; + xact_ai->action = action; + + xact_ai->creating_subid = GetCurrentSubTransactionId(); + xact_ai->deleting_subid = InvalidSubTransactionId; + + if (action == ON_COMMIT_DROP) + xact_drop_actions = lcons(xact_ai, xact_drop_actions); + else + xact_reset_actions = lcons(xact_ai, xact_reset_actions); + + MemoryContextSwitchTo(oldcxt); +} + +/* + * Remove variable from action list. In this moment, + * the action is just marked as deleted by setting + * deleting_subid. + */ +static void +delete_session_variable_xact_action(Oid varid, + SVariableXActAction action) +{ + ListCell *l; + + Assert(action == ON_COMMIT_DROP); + + foreach(l, xact_drop_actions) + { + SVariableXActActionItem *xact_ai = + (SVariableXActActionItem *) lfirst(l); + + if (xact_ai->varid == varid && xact_ai->action == action) + xact_ai->deleting_subid = GetCurrentSubTransactionId(); + } +} + +/* + * Perform ON TRANSACTION END RESET or ON COMMIT DROP + * and COMMIT/ROLLBACK of transaction session variables. + */ +void +AtPreEOXact_SessionVariable_on_xact_actions(bool isCommit) +{ + ListCell *l; + + foreach(l, xact_drop_actions) + { + SVariableXActActionItem *xact_ai = + (SVariableXActActionItem *) lfirst(l); + + /* Iterate only over non dropped entries */ + if (xact_ai->deleting_subid == InvalidSubTransactionId) + { + Assert(xact_ai->action == ON_COMMIT_DROP); + + /* + * ON COMMIT DROP is allowed only for temp session + * variables. So we should explicitly delete only when + * current transaction was committed. When it's rollback, + * then session variable is removed automatically. + */ + if (isCommit) + { + ObjectAddress object; + + object.classId = VariableRelationId; + object.objectId = xact_ai->varid; + object.objectSubId = 0; + + /* + * Since this is an automatic drop, rather than one + * directly initiated by the user, we pass the + * PERFORM_DELETION_INTERNAL flag. + */ + performDeletion(&object, DROP_CASCADE, + PERFORM_DELETION_INTERNAL | + PERFORM_DELETION_QUIETLY); + } + } + } + + list_free_deep(xact_drop_actions); + xact_drop_actions = NIL; + + foreach(l, xact_reset_actions) + { + SVariableXActActionItem *xact_ai = + (SVariableXActActionItem *) lfirst(l); + + if (xact_ai->action == RECHECK) + { + /* + * we can do recheck only in transactionn state + * when we can touch system catalog. When transaction + * is ending by ROLLBACK we are not in transaction state. + */ + if (IsTransactionState()) + { + SVariable svar; + bool found; + + svar = (SVariable) hash_search(sessionvars, &xact_ai->varid, + HASH_FIND, &found); + + if (found) + { + HeapTuple tp; + + tp = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(svar->varid)); + + if (HeapTupleIsValid(tp)) + ReleaseSysCache(tp); + else + free_session_variable(svar); + } + + xact_reset_actions = foreach_delete_current(xact_reset_actions, l); + } + } + else + { + /* + * We want to reset session variable (release it from + * local memory) when RESET is required or when session + * variable was removed explicitly (DROP VARIABLE) or + * implicitly (ON COMMIT DROP). Explicit releasing should + * be done only if the transaction is commited. + */ + if ((xact_ai->action == RESET) || + (xact_ai->action == ON_COMMIT_RESET && + xact_ai->deleting_subid == InvalidSubTransactionId && + isCommit)) + free_session_variable_varid(xact_ai->varid); + + xact_reset_actions = foreach_delete_current(xact_reset_actions, l); + } + } + + list_free_deep(xact_drop_actions); + list_free_deep(xact_reset_actions); + + xact_drop_actions = NIL; + xact_reset_actions = NIL; +} + +/* + * Post-subcommit or post-subabort cleanup of xact action list. + * + * During subabort, we can immediately remove entries created during this + * subtransaction. During subcommit, just relabel entries marked during + * this subtransaction as being the parent's responsibility. + */ +void +AtEOSubXact_SessionVariable_on_xact_actions(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid) +{ + ListCell *cur_item; + + foreach(cur_item, xact_drop_actions) + { + SVariableXActActionItem *xact_ai = + (SVariableXActActionItem *) lfirst(cur_item); + + if (!isCommit && xact_ai->creating_subid == mySubid) + { + /* cur_item must be removed */ + xact_drop_actions = foreach_delete_current(xact_drop_actions, cur_item); + pfree(xact_ai); + } + else + { + /* cur_item must be preserved */ + if (xact_ai->creating_subid == mySubid) + xact_ai->creating_subid = parentSubid; + if (xact_ai->deleting_subid == mySubid) + xact_ai->deleting_subid = isCommit ? parentSubid : InvalidSubTransactionId; + } + } + + /* + * Reset and recheck actions - cleaning memory should be used every time + * (when the variable with short life cycle was used) and then + * cannot be removed from xact action list. + */ + foreach(cur_item, xact_reset_actions) + { + SVariableXActActionItem *xact_ai = + (SVariableXActActionItem *) lfirst(cur_item); + + if (!isCommit && + xact_ai->creating_subid == mySubid && + xact_ai->action != RESET && + xact_ai->action != RECHECK) + { + /* cur_item must be removed */ + xact_reset_actions = foreach_delete_current(xact_reset_actions, cur_item); + pfree(xact_ai); + } + else + { + /* cur_item must be preserved */ + if (xact_ai->creating_subid == mySubid) + xact_ai->creating_subid = parentSubid; + if (xact_ai->deleting_subid == mySubid) + xact_ai->deleting_subid = isCommit ? parentSubid : InvalidSubTransactionId; + } + } +} diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index bf42587e38..852959e69d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -12387,6 +12387,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, case OCLASS_PUBLICATION_REL: case OCLASS_SUBSCRIPTION: case OCLASS_TRANSFORM: + case OCLASS_VARIABLE: /* * We don't expect any of these sorts of objects to depend on 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 7d343f0678..059603d3e7 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -33,6 +33,7 @@ #include "access/nbtree.h" #include "catalog/objectaccess.h" #include "catalog/pg_type.h" +#include "commands/session_variable.h" #include "executor/execExpr.h" #include "executor/nodeSubplan.h" #include "funcapi.h" @@ -994,6 +995,60 @@ 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; + } + + /* + * We should use session variable buffer, when + * it is available. + */ + if (es_session_variables) + { + SessionVariableValue *var; + + /* check params, unexpected */ + if (param->paramid >= es_num_session_variables) + elog(ERROR, "paramid of PARAM_VARIABLE param is out of range"); + + var = &es_session_variables[param->paramid]; + + /* unexpected */ + if (var->typid != param->paramtype) + elog(ERROR, "type of buffered value is different than PARAM_VARIABLE type"); + + /* + * In this case, the parameter is like a + * constant + */ + scratch.opcode = EEOP_CONST; + scratch.d.constval.value = var->value; + scratch.d.constval.isnull = var->isnull; + ExprEvalPushStep(state, &scratch); + } + else + { + /* + * When we have no full PlanState (plpgsql + * simple expr evaluation), then we should + * use direct access. + */ + scratch.opcode = EEOP_PARAM_VARIABLE; + scratch.d.vparam.varid = param->paramvarid; + scratch.d.vparam.vartype = param->paramtype; + ExprEvalPushStep(state, &scratch); + } + } + break; + case PARAM_EXTERN: /* diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index eb49817cee..548db9ab2c 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" @@ -444,6 +445,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, @@ -1078,6 +1080,16 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_PARAM_VARIABLE) + { + /* direct access to session variable (without buffering) */ + *op->resvalue = GetSessionVariable(op->d.vparam.varid, + op->resnull, + op->d.vparam.vartype, + true); + EEO_NEXT(); + } + EEO_CASE(EEOP_CASE_TESTVAL) { /* diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index b3ce4bae53..a5db32d9d4 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -45,10 +45,13 @@ #include "access/xact.h" #include "catalog/namespace.h" #include "catalog/pg_publication.h" +#include "catalog/pg_variable.h" #include "commands/matview.h" #include "commands/trigger.h" +#include "commands/session_variable.h" #include "executor/execdebug.h" #include "executor/nodeSubplan.h" +#include "executor/svariableReceiver.h" #include "foreign/fdwapi.h" #include "jit/jit.h" #include "mb/pg_wchar.h" @@ -199,6 +202,52 @@ standard_ExecutorStart(QueryDesc *queryDesc, int eflags) Assert(queryDesc->sourceText != NULL); estate->es_sourceText = queryDesc->sourceText; + /* + * Prepare session variables, if not prepared in queryDesc + */ + if (queryDesc->num_session_variables > 0) + { + /* + * link shared memory with working copy of session variable's values + * used in this query. This access is used by parallel query executor's + * workers. + */ + 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; + + nSessionVariables = list_length(queryDesc->plannedstmt->sessionVariables); + + /* Create buffer used for session variables */ + estate->es_session_variables = (SessionVariableValue *) + palloc(nSessionVariables * sizeof(SessionVariableValue)); + + foreach(lc, queryDesc->plannedstmt->sessionVariables) + { + AclResult aclresult; + Oid varid = lfirst_oid(lc); + + aclresult = pg_variable_aclcheck(varid, GetUserId(), ACL_READ); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, + session_variable_get_name(varid)); + + estate->es_session_variables[i].varid = varid; + estate->es_session_variables[i].value = CopySessionVariable(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 f8a4a40e7b..b87faec411 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. */ @@ -597,6 +605,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; @@ -606,6 +615,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; @@ -661,6 +671,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. * @@ -755,6 +770,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)); @@ -1402,6 +1422,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; @@ -1427,6 +1448,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,118 @@ 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/spi.c b/src/backend/executor/spi.c index 0568ae123f..1b37ef33a4 100644 --- a/src/backend/executor/spi.c +++ b/src/backend/executor/spi.c @@ -2867,6 +2867,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..66b8b6ec8c --- /dev/null +++ b/src/backend/executor/svariableReceiver.c @@ -0,0 +1,145 @@ +/*------------------------------------------------------------------------- + * + * svariableReceiver.c + * An implementation of DestReceiver that stores the result value in + * a session variable. + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/executor/svariableReceiver.c + * + *------------------------------------------------------------------------- + */ + +#include "postgres.h" + +#include "access/detoast.h" +#include "executor/svariableReceiver.h" +#include "commands/session_variable.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; + + for (i = 0; i < natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(typeinfo, i); + + if (attr->attisdropped) + continue; + + if (++outcols > 1) + elog(ERROR, "svariable DestReceiver can take only one attribute"); + + myState->typid = attr->atttypid; + myState->typmod = attr->atttypmod; + myState->typlen = attr->attlen; + myState->slot_offset = i; + } + + 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; + } + + SetSessionVariable(myState->varid, value, isnull, myState->typid); + + if (freeval) + pfree(DatumGetPointer(value)); + + return true; +} + +/* + * Clean up at end of an executor run + */ +static void +svariableShutdownReceiver(DestReceiver *self) +{ + /* Do nothing */ +} + +/* + * 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 SetVariableDestReceiverParams */ + + return (DestReceiver *) self; +} + +/* + * Set parameters for a VariableDestReceiver + */ +void +SetVariableDestReceiverParams(DestReceiver *self, Oid varid) +{ + svariableState *myState = (svariableState *) self; + + Assert(myState->pub.mydest == DestVariable); + Assert(OidIsValid(varid)); + + myState->varid = varid; +} diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c index 6d1181225e..4299809eb5 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/copyfuncs.c b/src/backend/nodes/copyfuncs.c index df0b747883..d802ab64fe 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -106,6 +106,7 @@ _copyPlannedStmt(const PlannedStmt *from) COPY_NODE_FIELD(invalItems); COPY_NODE_FIELD(paramExecTypes); COPY_NODE_FIELD(utilityStmt); + COPY_NODE_FIELD(sessionVariables); COPY_LOCATION_FIELD(stmt_location); COPY_SCALAR_FIELD(stmt_len); @@ -1508,6 +1509,7 @@ _copyParam(const Param *from) COPY_SCALAR_FIELD(paramtype); COPY_SCALAR_FIELD(paramtypmod); COPY_SCALAR_FIELD(paramcollid); + COPY_SCALAR_FIELD(paramvarid); COPY_LOCATION_FIELD(location); return newnode; @@ -3166,6 +3168,7 @@ _copyQuery(const Query *from) COPY_SCALAR_FIELD(canSetTag); COPY_NODE_FIELD(utilityStmt); COPY_SCALAR_FIELD(resultRelation); + COPY_SCALAR_FIELD(resultVariable); COPY_SCALAR_FIELD(hasAggs); COPY_SCALAR_FIELD(hasWindowFuncs); COPY_SCALAR_FIELD(hasTargetSRFs); @@ -3176,6 +3179,7 @@ _copyQuery(const Query *from) COPY_SCALAR_FIELD(hasForUpdate); COPY_SCALAR_FIELD(hasRowSecurity); COPY_SCALAR_FIELD(isReturn); + COPY_SCALAR_FIELD(hasSessionVariables); COPY_NODE_FIELD(cteList); COPY_NODE_FIELD(rtable); COPY_NODE_FIELD(jointree); @@ -3289,6 +3293,20 @@ _copySelectStmt(const SelectStmt *from) return newnode; } +static LetStmt * +_copyLetStmt(const LetStmt * from) +{ + LetStmt *newnode = makeNode(LetStmt); + + COPY_NODE_FIELD(target); + COPY_NODE_FIELD(query); + COPY_SCALAR_FIELD(set_default); + COPY_SCALAR_FIELD(plpgsql_mode); + COPY_LOCATION_FIELD(location); + + return newnode; +} + static SetOperationStmt * _copySetOperationStmt(const SetOperationStmt *from) { @@ -4903,6 +4921,23 @@ _copyDropSubscriptionStmt(const DropSubscriptionStmt *from) return newnode; } +static CreateSessionVarStmt * +_copyCreateSessionVarStmt(const CreateSessionVarStmt *from) +{ + CreateSessionVarStmt *newnode = makeNode(CreateSessionVarStmt); + + COPY_NODE_FIELD(variable); + COPY_NODE_FIELD(typeName); + COPY_NODE_FIELD(collClause); + COPY_NODE_FIELD(defexpr); + COPY_SCALAR_FIELD(eoxaction); + COPY_SCALAR_FIELD(if_not_exists); + COPY_SCALAR_FIELD(is_not_null); + COPY_SCALAR_FIELD(is_immutable); + + return newnode; +} + /* **************************************************************** * extensible.h copy functions * **************************************************************** @@ -5406,6 +5441,9 @@ copyObjectImpl(const void *from) case T_SelectStmt: retval = _copySelectStmt(from); break; + case T_LetStmt: + retval = _copyLetStmt(from); + break; case T_SetOperationStmt: retval = _copySetOperationStmt(from); break; @@ -5751,6 +5789,9 @@ copyObjectImpl(const void *from) case T_DropSubscriptionStmt: retval = _copyDropSubscriptionStmt(from); break; + case T_CreateSessionVarStmt: + retval = _copyCreateSessionVarStmt(from); + break; case T_A_Expr: retval = _copyA_Expr(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index cb7ddd463c..4220dd47ac 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -215,6 +215,7 @@ _equalParam(const Param *a, const Param *b) COMPARE_SCALAR_FIELD(paramtype); COMPARE_SCALAR_FIELD(paramtypmod); COMPARE_SCALAR_FIELD(paramcollid); + COMPARE_SCALAR_FIELD(paramvarid); COMPARE_LOCATION_FIELD(location); return true; @@ -980,6 +981,7 @@ _equalQuery(const Query *a, const Query *b) COMPARE_SCALAR_FIELD(canSetTag); COMPARE_NODE_FIELD(utilityStmt); COMPARE_SCALAR_FIELD(resultRelation); + COMPARE_SCALAR_FIELD(resultVariable); COMPARE_SCALAR_FIELD(hasAggs); COMPARE_SCALAR_FIELD(hasWindowFuncs); COMPARE_SCALAR_FIELD(hasTargetSRFs); @@ -990,6 +992,7 @@ _equalQuery(const Query *a, const Query *b) COMPARE_SCALAR_FIELD(hasForUpdate); COMPARE_SCALAR_FIELD(hasRowSecurity); COMPARE_SCALAR_FIELD(isReturn); + COMPARE_SCALAR_FIELD(hasSessionVariables); COMPARE_NODE_FIELD(cteList); COMPARE_NODE_FIELD(rtable); COMPARE_NODE_FIELD(jointree); @@ -1093,6 +1096,17 @@ _equalSelectStmt(const SelectStmt *a, const SelectStmt *b) return true; } +static bool +_equalLetStmt(const LetStmt * a, const LetStmt * b) +{ + COMPARE_NODE_FIELD(target); + COMPARE_NODE_FIELD(query); + COMPARE_SCALAR_FIELD(set_default); + COMPARE_SCALAR_FIELD(plpgsql_mode); + + return true; +} + static bool _equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b) { @@ -2377,6 +2391,22 @@ _equalDropSubscriptionStmt(const DropSubscriptionStmt *a, return true; } +static bool +_equalCreateSessionVarStmt(const CreateSessionVarStmt *a, + const CreateSessionVarStmt *b) +{ + COMPARE_NODE_FIELD(variable); + COMPARE_NODE_FIELD(typeName); + COMPARE_NODE_FIELD(collClause); + COMPARE_NODE_FIELD(defexpr); + COMPARE_SCALAR_FIELD(eoxaction); + COMPARE_SCALAR_FIELD(if_not_exists); + COMPARE_SCALAR_FIELD(is_not_null); + COMPARE_SCALAR_FIELD(is_immutable); + + return true; +} + static bool _equalCreatePolicyStmt(const CreatePolicyStmt *a, const CreatePolicyStmt *b) { @@ -3409,6 +3439,9 @@ equal(const void *a, const void *b) case T_SelectStmt: retval = _equalSelectStmt(a, b); break; + case T_LetStmt: + retval = _equalLetStmt(a, b); + break; case T_SetOperationStmt: retval = _equalSetOperationStmt(a, b); break; @@ -3754,6 +3787,9 @@ equal(const void *a, const void *b) case T_DropSubscriptionStmt: retval = _equalDropSubscriptionStmt(a, b); break; + case T_CreateSessionVarStmt: + retval = _equalCreateSessionVarStmt(a, b); + break; case T_A_Expr: retval = _equalA_Expr(a, b); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 91a89b6d51..329d10cd5f 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -324,6 +324,7 @@ _outPlannedStmt(StringInfo str, const PlannedStmt *node) WRITE_NODE_FIELD(invalItems); WRITE_NODE_FIELD(paramExecTypes); WRITE_NODE_FIELD(utilityStmt); + WRITE_NODE_FIELD(sessionVariables); WRITE_LOCATION_FIELD(stmt_location); WRITE_INT_FIELD(stmt_len); } @@ -1166,6 +1167,7 @@ _outParam(StringInfo str, const Param *node) WRITE_OID_FIELD(paramtype); WRITE_INT_FIELD(paramtypmod); WRITE_OID_FIELD(paramcollid); + WRITE_OID_FIELD(paramvarid); WRITE_LOCATION_FIELD(location); } @@ -2279,6 +2281,7 @@ _outPlannerGlobal(StringInfo str, const PlannerGlobal *node) WRITE_NODE_FIELD(relationOids); WRITE_NODE_FIELD(invalItems); WRITE_NODE_FIELD(paramExecTypes); + WRITE_NODE_FIELD(sessionVariables); WRITE_UINT_FIELD(lastPHId); WRITE_UINT_FIELD(lastRowMarkId); WRITE_INT_FIELD(lastPlanNodeId); @@ -2339,6 +2342,7 @@ _outPlannerInfo(StringInfo str, const PlannerInfo *node) WRITE_BOOL_FIELD(hasPseudoConstantQuals); WRITE_BOOL_FIELD(hasAlternativeSubPlans); WRITE_BOOL_FIELD(hasRecursion); + WRITE_BOOL_FIELD(hasSessionVariables); WRITE_INT_FIELD(wt_param_id); WRITE_BITMAPSET_FIELD(curOuterRels); WRITE_NODE_FIELD(curOuterParams); @@ -2874,6 +2878,18 @@ _outPLAssignStmt(StringInfo str, const PLAssignStmt *node) WRITE_LOCATION_FIELD(location); } +static void +_outLetStmt(StringInfo str, const LetStmt * node) +{ + WRITE_NODE_TYPE("LET"); + + WRITE_NODE_FIELD(target); + WRITE_NODE_FIELD(query); + WRITE_BOOL_FIELD(set_default); + WRITE_BOOL_FIELD(plpgsql_mode); + WRITE_LOCATION_FIELD(location); +} + static void _outFuncCall(StringInfo str, const FuncCall *node) { @@ -3054,6 +3070,7 @@ _outQuery(StringInfo str, const Query *node) case T_IndexStmt: case T_NotifyStmt: case T_DeclareCursorStmt: + case T_LetStmt: WRITE_NODE_FIELD(utilityStmt); break; default: @@ -3065,6 +3082,7 @@ _outQuery(StringInfo str, const Query *node) appendStringInfoString(str, " :utilityStmt <>"); WRITE_INT_FIELD(resultRelation); + WRITE_OID_FIELD(resultVariable); WRITE_BOOL_FIELD(hasAggs); WRITE_BOOL_FIELD(hasWindowFuncs); WRITE_BOOL_FIELD(hasTargetSRFs); @@ -3075,6 +3093,7 @@ _outQuery(StringInfo str, const Query *node) WRITE_BOOL_FIELD(hasForUpdate); WRITE_BOOL_FIELD(hasRowSecurity); WRITE_BOOL_FIELD(isReturn); + WRITE_BOOL_FIELD(hasSessionVariables); WRITE_NODE_FIELD(cteList); WRITE_NODE_FIELD(rtable); WRITE_NODE_FIELD(jointree); @@ -4376,6 +4395,9 @@ outNode(StringInfo str, const void *obj) case T_PLAssignStmt: _outPLAssignStmt(str, obj); break; + case T_LetStmt: + _outLetStmt(str, obj); + break; case T_ColumnDef: _outColumnDef(str, obj); break; diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 76cff8a2b1..2a9849a59c 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -254,6 +254,7 @@ _readQuery(void) READ_BOOL_FIELD(canSetTag); READ_NODE_FIELD(utilityStmt); READ_INT_FIELD(resultRelation); + READ_OID_FIELD(resultVariable); READ_BOOL_FIELD(hasAggs); READ_BOOL_FIELD(hasWindowFuncs); READ_BOOL_FIELD(hasTargetSRFs); @@ -264,6 +265,7 @@ _readQuery(void) READ_BOOL_FIELD(hasForUpdate); READ_BOOL_FIELD(hasRowSecurity); READ_BOOL_FIELD(isReturn); + READ_BOOL_FIELD(hasSessionVariables); READ_NODE_FIELD(cteList); READ_NODE_FIELD(rtable); READ_NODE_FIELD(jointree); @@ -628,6 +630,7 @@ _readParam(void) READ_OID_FIELD(paramtype); READ_INT_FIELD(paramtypmod); READ_OID_FIELD(paramcollid); + READ_OID_FIELD(paramvarid); READ_LOCATION_FIELD(location); READ_DONE(); @@ -1599,6 +1602,7 @@ _readPlannedStmt(void) READ_NODE_FIELD(invalItems); READ_NODE_FIELD(paramExecTypes); READ_NODE_FIELD(utilityStmt); + READ_NODE_FIELD(sessionVariables); READ_LOCATION_FIELD(stmt_location); READ_INT_FIELD(stmt_len); diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c index bd01ec0526..a14f6ac823 100644 --- a/src/backend/optimizer/plan/planner.c +++ b/src/backend/optimizer/plan/planner.c @@ -316,6 +316,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 @@ -529,6 +530,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; @@ -678,6 +680,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 6ccec759bd..78d7395eb3 100644 --- a/src/backend/optimizer/plan/setrefs.c +++ b/src/backend/optimizer/plan/setrefs.c @@ -172,6 +172,8 @@ static List *set_returning_clause_references(PlannerInfo *root, Plan *topplan, Index resultRelation, int rtoffset); +static bool pull_up_has_session_variables_walker(Node *node, + PlannerInfo *root); /***************************************************************************** @@ -1127,6 +1129,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 @@ -1758,10 +1804,14 @@ fix_expr_common(PlannerInfo *root, Node *node) /* * 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 should to calculate paramid. */ static Node * fix_param_node(PlannerInfo *root, Param *p) @@ -1780,6 +1830,52 @@ 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) + { + PlanInvalItem *inval_item = makeNode(PlanInvalItem); + + /* paramid is still session variable id */ + inval_item->cacheId = VARIABLEOID; + inval_item->hashValue = GetSysCacheHashValue1(VARIABLEOID, + ObjectIdGetDatum(p->paramvarid)); + + /* Append this variable to global, register dependency */ + root->glob->invalItems = lappend(root->glob->invalItems, + inval_item); + root->glob->sessionVariables = lappend_oid(root->glob->sessionVariables, + p->paramvarid); + + p->paramid = n; + } + + return (Node *) p; + } + return (Node *) copyObject(p); } @@ -1841,7 +1937,9 @@ 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. + * and adding OIDs from regclass Const nodes into root->glob->relationOids, + * and replacing PARAM_VARIABLE paramid, that is the oid of the session variable + * to offset the array by query used session variables. ??? * * 'node': the expression to be modified * 'rtoffset': how much to increment varnos by @@ -1863,7 +1961,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); } diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 873e43bfe6..ab65692a64 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" @@ -819,7 +820,8 @@ max_parallel_hazard_walker(Node *node, max_parallel_hazard_context *context) { 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 || @@ -2229,6 +2231,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 * @@ -2351,6 +2354,30 @@ 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 = GetSessionVariable(param->paramvarid, + &isnull, + param->paramtype, + true); + + return (Node *) makeConst(param->paramtype, + param->paramtypmod, + param->paramcollid, + (int) typLen, + pval, + isnull, + typByVal); + } /* * Not replaceable, so just copy the Param (no need to @@ -4735,22 +4762,46 @@ substitute_actual_parameters_mutator(Node *node, { if (node == NULL) return NULL; + + /* + * The expression of SQL function can contain params of two separated + * by different paramkind field. The nodes with paramkind PARAM_EXTERN + * are related to function's arguments (and should be replaced in this + * step), because this is mechanism how we apply the function's arguments + * for an expression. + * + * The nodes with paramkind PARAM_VARIABLE are related to an 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 can substitute params + * related to immutable session variables with default expression by + * this default expression, it is safer don't do it. We don't need 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); - /* 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); + /* 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); + } } + 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 146ee8dd1e..42c5e977a3 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -25,8 +25,11 @@ #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/session_variable.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -50,6 +53,7 @@ #include "utils/builtins.h" #include "utils/guc.h" #include "utils/queryjumble.h" +#include "utils/lsyscache.h" #include "utils/rel.h" #include "utils/syscache.h" @@ -88,6 +92,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 @@ -289,6 +295,7 @@ transformStmt(ParseState *pstate, Node *parseTree) case T_InsertStmt: case T_UpdateStmt: case T_DeleteStmt: + case T_LetStmt: (void) test_raw_expression_coverage(parseTree, NULL); break; default: @@ -358,6 +365,11 @@ transformStmt(ParseState *pstate, Node *parseTree) (CallStmt *) parseTree); break; + case T_LetStmt: + result = transformLetStmt(pstate, + (LetStmt *) parseTree); + break; + default: /* @@ -399,6 +411,7 @@ analyze_requires_snapshot(RawStmt *parseTree) case T_UpdateStmt: case T_SelectStmt: case T_PLAssignStmt: + case T_LetStmt: result = true; break; @@ -482,6 +495,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 */ @@ -899,6 +914,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); @@ -1269,8 +1285,9 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) /* process the FROM clause */ transformFromClause(pstate, stmt->fromClause); - /* transform targetlist */ - qry->targetList = transformTargetList(pstate, stmt->targetList, + /* Transform targetlist. */ + qry->targetList = transformTargetList(pstate, + stmt->targetList, EXPR_KIND_SELECT_TARGET); /* mark column origins */ @@ -1361,6 +1378,8 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt) (LockingClause *) lfirst(l), false); } + qry->hasSessionVariables = pstate->p_hasSessionVariables; + assign_query_collations(pstate, qry); /* this must be done after collations, for reliable comparison of exprs */ @@ -1585,12 +1604,268 @@ 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; + List *indirection = NIL; + ListCell *lc; + Query *selectQuery; + int i = 0; + 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; + + /* There can't be any outer WITH to worry about */ + Assert(pstate->p_ctenamespace == NIL); + + names = NamesFromList(stmt->target); + + varid = IdentifyVariable(names, &attrname, ¬_unique); + 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))); + + indirection_start = list_length(names) - (attrname ? 1 : 0); + indirection = list_copy_tail(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("target variable \"%s\" is not row type", + session_variable_get_name(varid)), + parser_errposition(pstate, stmt->location))); + + if (stmt->set_default && attrname != NULL) + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("only complete variable can be set to default"), + parser_errposition(pstate, stmt->location))); + + aclresult = pg_variable_aclcheck(varid, GetUserId(), ACL_WRITE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_VARIABLE, NameListToString(names)); + + pstate->p_expr_kind = EXPR_KIND_LET_TARGET; + + /* + * stmt->query is SelectStmt node. An tranformation of + * this node doesn't support SetToDefault node. Instead injecting + * of transformSelectStmt or parse state, we can directly + * transform target list here if holds SetToDefault node. + */ + if (stmt->set_default) + { + selectQuery = makeNode(Query); + selectQuery->commandType = CMD_SELECT; + + /* + * ResTarget(SetToDefault) -> TargetEntry(expr(SetToDefault)) */ + selectQuery->targetList = transformTargetList(pstate, + ((SelectStmt *) stmt->query)->targetList, + EXPR_KIND_LET_TARGET); + } + else + selectQuery = transformStmt(pstate, stmt->query); + + /* The grammar should have produced a SELECT */ + if (!IsA(selectQuery, Query) || + selectQuery->commandType != CMD_SELECT) + elog(ERROR, "unexpected non-SELECT command in LET command"); + + /*---------- + * 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("expression is not scalar value"), + 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, SetToDefault)) + { + SetToDefault *def = (SetToDefault *) expr; + + def->typeId = typid; + def->typeMod = typmod; + def->collation = collid; + } + else 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 != NULL) + { + bool targetIsArray; + char *targetName; + + targetName = attrname != NULL ? attrname : 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, + indirection, + list_head(indirection), + (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\" is of type %s," + " but expression is of type %s", + session_variable_get_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; + + /* + * rewrite SetToDefaults needs varid in Query structure + */ + query->resultVariable = varid; + + assign_query_collations(pstate, query); + + stmt->query = (Node *) query; + + /* + * When statement is executed as an expression as PLpgSQL LET + * statement, then we should return query. We should not + * use a utility wrapper node. + */ + 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 @@ -1841,6 +2116,8 @@ transformSetOperationStmt(ParseState *pstate, SelectStmt *stmt) (LockingClause *) lfirst(l), false); } + qry->hasSessionVariables = pstate->p_hasSessionVariables; + assign_query_collations(pstate, qry); /* this must be done after collations, for reliable comparison of exprs */ @@ -2369,6 +2646,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); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3d4dd43e47..ac65dc86c6 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -244,6 +244,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); JoinType jtype; DropBehavior dbehavior; OnCommitAction oncommit; + VariableEOXAction oneoxaction; List *list; Node *node; ObjectType objtype; @@ -296,8 +297,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt - CreateSchemaStmt CreateSeqStmt CreateStmt CreateStatsStmt CreateTableSpaceStmt - CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt + CreateSchemaStmt CreateSessionVarStmt CreateSeqStmt CreateStmt CreateStatsStmt + CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt CreateAssertionStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt @@ -307,7 +308,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); DropTransformStmt DropUserMappingStmt ExplainStmt FetchStmt GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt - ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt + LetStmt ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRoleStmt RuleActionStmt RuleActionStmtOrEmpty RuleStmt @@ -447,6 +448,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 @@ -469,6 +471,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type OptTemp %type OptNoLog %type OnCommitOption +%type OnEOXActionOption %type for_locking_strength %type for_locking_item @@ -632,6 +635,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type PartitionBoundSpec %type hash_partbound %type hash_partbound_elem +%type OptSessionVarDefExpr +%type OptNotNull OptImmutable /* @@ -701,7 +706,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); KEY 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 MATERIALIZED MAXVALUE METHOD MINUTE_P MINVALUE MODE MONTH_P MOVE @@ -740,8 +745,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); UESCAPE UNBOUNDED UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN UNLISTEN UNLOGGED UNTIL UPDATE USER USING - VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING - VERBOSE VERSION_P VIEW VIEWS VOLATILE + VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIABLE VARIABLES + VARIADIC VARYING VERBOSE VERSION_P VIEW VIEWS VOLATILE WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE @@ -776,6 +781,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 */ @@ -881,6 +887,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)); + } ; /* @@ -984,6 +997,7 @@ stmt: | CreatePolicyStmt | CreatePLangStmt | CreateSchemaStmt + | CreateSessionVarStmt | CreateSeqStmt | CreateStmt | CreateSubscriptionStmt @@ -1021,6 +1035,7 @@ stmt: | ImportForeignSchemaStmt | IndexStmt | InsertStmt + | LetStmt | ListenStmt | RefreshMatViewStmt | LoadStmt @@ -1913,7 +1928,12 @@ DiscardStmt: n->target = DISCARD_SEQUENCES; $$ = (Node *) n; } - + | DISCARD VARIABLES + { + DiscardStmt *n = makeNode(DiscardStmt); + n->target = DISCARD_VARIABLES; + $$ = (Node *) n; + } ; @@ -4743,6 +4763,69 @@ create_extension_opt_item: } ; +/***************************************************************************** + * + * QUERY : + * CREATE VARIABLE varname [AS] type + * + *****************************************************************************/ + +CreateSessionVarStmt: + CREATE OptTemp OptImmutable VARIABLE qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr OnEOXActionOption + { + CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt); + $5->relpersistence = $2; + n->is_immutable = $3; + n->variable = $5; + n->typeName = $7; + n->collClause = (CollateClause *) $8; + n->is_not_null = $9; + n->defexpr = $10; + n->eoxaction = $11; + n->if_not_exists = false; + $$ = (Node *) n; + } + | CREATE OptTemp OptImmutable VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename opt_collate_clause OptNotNull OptSessionVarDefExpr OnEOXActionOption + { + CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt); + $8->relpersistence = $2; + n->is_immutable = $3; + n->variable = $8; + n->typeName = $10; + n->collClause = (CollateClause *) $11; + n->is_not_null = $12; + n->defexpr = $13; + n->eoxaction = $14; + n->if_not_exists = true; + $$ = (Node *) n; + } + ; + +OptSessionVarDefExpr: DEFAULT b_expr { $$ = $2; } + | /* EMPTY */ { $$ = NULL; } + ; + +/* + * Temporary session variables can be dropped on successful + * transaction end like tables. We can force only reset on + * transaction end. Because the session variables are not + * transactional, we have calculate with ROLLBACK too. + * The clause ON TRANSACTION END is more illustrative + * synonymum to ON COMMIT ROLLBACK RESET. + */ +OnEOXActionOption: ON COMMIT DROP { $$ = VARIABLE_EOX_DROP; } + | ON TRANSACTION END_P RESET { $$ = VARIABLE_EOX_RESET; } + | /*EMPTY*/ { $$ = VARIABLE_EOX_NOOP; } + ; + +OptNotNull: NOT NULL_P { $$ = true; } + | /* EMPTY */ { $$ = false; } + ; + +OptImmutable: IMMUTABLE { $$ = true; } + | /* EMPTY */ { $$ = false; } + ; + /***************************************************************************** * * ALTER EXTENSION name UPDATE [ TO version ] @@ -6430,6 +6513,7 @@ object_type_any_name: | TEXT_P SEARCH DICTIONARY { $$ = OBJECT_TSDICTIONARY; } | TEXT_P SEARCH TEMPLATE { $$ = OBJECT_TSTEMPLATE; } | TEXT_P SEARCH CONFIGURATION { $$ = OBJECT_TSCONFIGURATION; } + | VARIABLE { $$ = OBJECT_VARIABLE; } ; /* @@ -7198,6 +7282,14 @@ privilege_target: n->objs = $2; $$ = n; } + | VARIABLE qualified_name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; + n->objtype = OBJECT_VARIABLE; + n->objs = $2; + $$ = n; + } | ALL TABLES IN_P SCHEMA name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); @@ -7238,6 +7330,14 @@ privilege_target: n->objs = $5; $$ = n; } + | ALL VARIABLES IN_P SCHEMA name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_ALL_IN_SCHEMA; + n->objtype = OBJECT_VARIABLE; + n->objs = $5; + $$ = n; + } ; @@ -7400,6 +7500,7 @@ defacl_privilege_target: | SEQUENCES { $$ = OBJECT_SEQUENCE; } | TYPES_P { $$ = OBJECT_TYPE; } | SCHEMAS { $$ = OBJECT_SCHEMA; } + | VARIABLES { $$ = OBJECT_VARIABLE; } ; @@ -9102,6 +9203,25 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name n->missing_ok = false; $$ = (Node *)n; } + | ALTER VARIABLE any_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_VARIABLE; + n->object = (Node *) $3; + n->newname = $6; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER VARIABLE IF_P EXISTS any_name RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_VARIABLE; + n->object = (Node *) $5; + n->newname = $8; + n->missing_ok = true; + $$ = (Node *)n; + } + ; opt_column: COLUMN @@ -9430,6 +9550,25 @@ AlterObjectSchemaStmt: n->missing_ok = false; $$ = (Node *)n; } + | ALTER VARIABLE any_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_VARIABLE; + n->object = (Node *) $3; + n->newschema = $6; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER VARIABLE IF_P EXISTS any_name SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_VARIABLE; + n->object = (Node *) $5; + n->newschema = $8; + n->missing_ok = true; + $$ = (Node *)n; + } + ; /***************************************************************************** @@ -9683,6 +9822,14 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec n->newowner = $6; $$ = (Node *)n; } + | ALTER VARIABLE any_name OWNER TO RoleSpec + { + AlterOwnerStmt *n = makeNode(AlterOwnerStmt); + n->objectType = OBJECT_VARIABLE; + n->object = (Node *) $3; + n->newowner = $6; + $$ = (Node *)n; + } ; @@ -11018,6 +11165,7 @@ ExplainableStmt: | CreateMatViewStmt | RefreshMatViewStmt | ExecuteStmt /* by default all are $$=$1 */ + | LetStmt ; /***************************************************************************** @@ -11046,6 +11194,7 @@ PreparableStmt: | InsertStmt | UpdateStmt | DeleteStmt /* by default all are $$=$1 */ + | LetStmt ; /***************************************************************************** @@ -11463,6 +11612,49 @@ 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->set_default = IsA($4, SetToDefault); + + n->location = @2; + $$ = (Node *) n; + } + ; + +let_target: + ColId opt_indirection + { + $$ = list_make1(makeString($1)); + if ($2) + $$ = list_concat($$, + check_indirection($2, yyscanner)); + } + ; + /***************************************************************************** * * QUERY: @@ -15771,6 +15963,7 @@ unreserved_keyword: | LARGE_P | LAST_P | LEAKPROOF + | LET | LEVEL | LISTEN | LOAD @@ -15928,6 +16121,8 @@ unreserved_keyword: | VALIDATE | VALIDATOR | VALUE_P + | VARIABLE + | VARIABLES | VARYING | VERSION_P | VIEW @@ -16335,6 +16530,7 @@ bare_label_keyword: | LEAKPROOF | LEAST | LEFT + | LET | LEVEL | LIKE | LISTEN @@ -16534,6 +16730,8 @@ bare_label_keyword: | VALUE_P | VALUES | VARCHAR + | VARIABLE + | VARIABLES | VARIADIC | VERBOSE | VERSION_P diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 7d829a05a9..f3b384efbe 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -348,6 +348,7 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) Assert(false); /* can't happen */ break; case EXPR_KIND_OTHER: + case EXPR_KIND_LET_TARGET: /* * Accept aggregate/grouping here; caller must throw error if @@ -464,6 +465,7 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) break; case EXPR_KIND_COLUMN_DEFAULT: case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_VARIABLE_DEFAULT: if (isAgg) err = _("aggregate functions are not allowed in DEFAULT expressions"); @@ -905,6 +907,7 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, break; case EXPR_KIND_COLUMN_DEFAULT: case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_VARIABLE_DEFAULT: err = _("window functions are not allowed in DEFAULT expressions"); break; case EXPR_KIND_INDEX_EXPRESSION: @@ -943,6 +946,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_LET_TARGET: + err = _("window functions are not allowed in LET statement"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 2d1a477154..b780018f2c 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -16,6 +16,7 @@ #include "postgres.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "commands/dbcommands.h" #include "miscadmin.h" #include "nodes/makefuncs.h" @@ -36,6 +37,7 @@ #include "utils/date.h" #include "utils/lsyscache.h" #include "utils/timestamp.h" +#include "utils/typcache.h" #include "utils/xml.h" /* GUC parameters */ @@ -82,6 +84,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); /* @@ -443,6 +448,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) char *relname = NULL; char *colname = NULL; ParseNamespaceItem *nsitem; + Oid varid = InvalidOid; + char *attrname = NULL; + bool not_unique; int levels_up; enum { @@ -504,6 +512,9 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_COPY_WHERE: case EXPR_KIND_GENERATED_COLUMN: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_VARIABLE_DEFAULT: + case EXPR_KIND_LET_TARGET: + /* okay */ break; @@ -785,6 +796,87 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) parser_errposition(pstate, cref->location))); } + /* The col's reference can be reference to session variable too */ + varid = IdentifyVariable(cref->fields, &attrname, ¬_unique); + + if (not_unique) + ereport(ERROR, + (errcode(ERRCODE_AMBIGUOUS_PARAMETER), + errmsg("session variable reference \"%s\" is ambiguous", + NameListToString(cref->fields)), + parser_errposition(pstate, cref->location))); + + if (OidIsValid(varid)) + { + Oid typid; + int32 typmod; + Oid collid; + + get_session_variable_type_typmod_collid(varid, &typid, &typmod, &collid); + + if (node != NULL) + { + /* + * Some cases with ambiguous references can be solved without + * raising an error. When there is an collision between column + * name (or label) and some session variable name, and when we know + * attribute name, then we can ignore the collision when: + * + * a) variable is of scalar type (then indirection cannot be + * applied on this session variable. + * + * b) when related variable has no field of attrname, then + * indirection cannot be applied on this session variable. + */ + if (attrname) + { + if (type_is_rowtype(typid)) + { + TupleDesc tupdesc; + bool found = false; + int i; + + /* slow part, I hope it will not be to often */ + tupdesc = lookup_rowtype_tupdesc(typid, typmod); + for (i = 0; i < tupdesc->natts; i++) + { + if (namestrcmp(&(TupleDescAttr(tupdesc, i)->attname), attrname) == 0 && + !TupleDescAttr(tupdesc, i)->attisdropped) + { + found = true; + break; + } + } + + FreeTupleDesc(tupdesc); + + /* there is no composite variable with this field */ + if (!found) + varid = InvalidOid; + } + else + /* there is no composite variable with this name */ + varid = InvalidOid; + } + + /* + * Raise error if varid is still valid, since it is ambiguous. + */ + if (OidIsValid(varid)) + ereport(ERROR, + (errcode(ERRCODE_AMBIGUOUS_COLUMN), + errmsg("column reference \"%s\" is ambiguous", + NameListToString(cref->fields)), + errdetail("The qualified identifier can be column reference or session variable reference"), + parser_errposition(pstate, cref->location))); + } + + if (OidIsValid(varid)) + node = makeParamSessionVariable(pstate, + varid, typid, typmod, collid, + attrname, cref->location); + } + /* * Throw error if no translation found. */ @@ -819,6 +911,74 @@ 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; + + /* + * There are two ways to access session variables - direct, used by simple + * plpgsql expressions, where it is not necessary to emulate stability. + * And Buffered access, which is used everywhere else. We should ensure + * stable values, and because session variables are global, then we should + * work with copied values instead of directly accessing variables. For + * direct access, the varid is best. For buffered access, we need + * to assign an index to the buffer - later, when we know what variables are + * used. Now, we just remember, so we use session variables. + */ + pstate->p_hasSessionVariables = true; + + if (attrname != NULL) + { + TupleDesc tupdesc; + int i; + + tupdesc = lookup_rowtype_tupdesc(typid, typmod); + + 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) { @@ -1721,6 +1881,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: @@ -1729,6 +1890,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) break; case EXPR_KIND_COLUMN_DEFAULT: case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_VARIABLE_DEFAULT: err = _("cannot use subquery in DEFAULT expression"); break; case EXPR_KIND_INDEX_EXPRESSION: @@ -3059,6 +3221,7 @@ ParseExprKindName(ParseExprKind exprKind) return "CHECK"; case EXPR_KIND_COLUMN_DEFAULT: case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_VARIABLE_DEFAULT: return "DEFAULT"; case EXPR_KIND_INDEX_EXPRESSION: return "index expression"; @@ -3084,6 +3247,8 @@ ParseExprKindName(ParseExprKind exprKind) return "GENERATED AS"; case EXPR_KIND_CYCLE_MARK: return "CYCLE"; + 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 542f9167aa..a1198d64ce 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2617,6 +2617,7 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) break; case EXPR_KIND_COLUMN_DEFAULT: case EXPR_KIND_FUNCTION_DEFAULT: + case EXPR_KIND_VARIABLE_DEFAULT: err = _("set-returning functions are not allowed in DEFAULT expressions"); break; case EXPR_KIND_INDEX_EXPRESSION: @@ -2655,6 +2656,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_LET_TARGET: + err = _("set-returning functions are not allowed in CALL arguments"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_target.c b/src/backend/parser/parse_target.c index 9ce3a0de96..a79e612129 100644 --- a/src/backend/parser/parse_target.c +++ b/src/backend/parser/parse_target.c @@ -89,7 +89,9 @@ transformTargetEntry(ParseState *pstate, * through unmodified. (transformExpr will throw the appropriate * error if we're disallowing it.) */ - if (exprKind == EXPR_KIND_UPDATE_SOURCE && IsA(node, SetToDefault)) + if ((exprKind == EXPR_KIND_UPDATE_SOURCE || + exprKind == EXPR_KIND_LET_TARGET) + && IsA(node, SetToDefault)) expr = node; else expr = transformExpr(pstate, node, exprKind); diff --git a/src/backend/parser/parser.c b/src/backend/parser/parser.c index 875de7ba28..0f499712d8 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 9521e81100..94ebe8b0a3 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 "foreign/fdwapi.h" #include "miscadmin.h" @@ -3664,6 +3665,39 @@ RewriteQuery(Query *parsetree, List *rewrite_events) } } + /* + * Rewrite SetToDefault by default expression of Let statement. + */ + if (event == CMD_SELECT && OidIsValid(parsetree->resultVariable)) + { + Oid resultVariable = parsetree->resultVariable; + + if (list_length(parsetree->targetList) == 1 && + IsA(((TargetEntry *) linitial(parsetree->targetList))->expr, SetToDefault)) + { + Variable var; + TargetEntry *tle; + TargetEntry *newtle; + Expr *defexpr; + + /* Read session variable metadata with defexpr */ + initVariable(&var, resultVariable, false, false); + + if (var.has_defexpr) + defexpr = (Expr *) var.defexpr; + else + defexpr = (Expr *) makeNullConst(var.typid, var.typmod, var.collation); + + tle = (TargetEntry *) linitial(parsetree->targetList); + newtle = makeTargetEntry(defexpr, + tle->resno, + pstrdup(tle->resname), + false); + + parsetree->targetList = list_make1(newtle); + } + } + /* * If the statement is an insert, update, or delete, adjust its targetlist * as needed, and then fire INSERT/UPDATE/DELETE rules on it. diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c index e10f94904e..447c6f0abf 100644 --- a/src/backend/rewrite/rowsecurity.c +++ b/src/backend/rewrite/rowsecurity.c @@ -213,10 +213,10 @@ get_row_security_policies(Query *root, RangeTblEntry *rte, int rt_index, } /* - * For SELECT, UPDATE and DELETE, add security quals to enforce the USING - * policies. These security quals control access to existing table rows. - * Restrictive policies are combined together using AND, and permissive - * policies are combined together using OR. + * For SELECT, LET, UPDATE and DELETE, add security quals to enforce the + * USING policies. These security quals control access to existing table + * rows. Restrictive policies are combined together using AND, and + * permissive policies are combined together using OR. */ get_policies_for_relation(rel, commandType, user_id, &permissive_policies, diff --git a/src/backend/tcop/dest.c b/src/backend/tcop/dest.c index 1dfadfa8e1..c676cb19fc 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 */ @@ -207,6 +211,7 @@ EndCommand(const QueryCompletion *qc, CommandDest dest, bool force_undecorated_o case DestSQLFunction: case DestTransientRel: case DestTupleQueue: + case DestVariable: break; } } @@ -252,6 +257,7 @@ NullCommand(CommandDest dest) case DestSQLFunction: case DestTransientRel: case DestTupleQueue: + case DestVariable: break; } } @@ -295,6 +301,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 960f3fadce..7910c0d3f6 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 1fbc387d47..c3cd6521ff 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -49,6 +49,7 @@ #include "commands/proclang.h" #include "commands/publicationcmds.h" #include "commands/schemacmds.h" +#include "commands/session_variable.h" #include "commands/seclabel.h" #include "commands/sequence.h" #include "commands/subscriptioncmds.h" @@ -187,6 +188,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) case T_CreateRangeStmt: case T_CreateRoleStmt: case T_CreateSchemaStmt: + case T_CreateSessionVarStmt: case T_CreateSeqStmt: case T_CreateStatsStmt: case T_CreateStmt: @@ -238,6 +240,7 @@ ClassifyUtilityCommandAsReadOnly(Node *parsetree) case T_CallStmt: case T_DoStmt: + case T_LetStmt: { /* * Commands inside the DO block or the called procedure might @@ -1062,6 +1065,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, @@ -1386,6 +1394,10 @@ ProcessUtilitySlow(ParseState *pstate, } break; + case T_CreateSessionVarStmt: + address = DefineSessionVariable(pstate, (CreateSessionVarStmt *) parsetree); + break; + /* * ************* object creation / destruction ************** */ @@ -2176,6 +2188,10 @@ UtilityContainsQuery(Node *parsetree) return UtilityContainsQuery(qry->utilityStmt); return qry; + case T_LetStmt: + qry = castNode(Query, ((LetStmt *) parsetree)->query); + return qry; + default: return NULL; } @@ -2317,6 +2333,9 @@ AlterObjectTypeCommandTag(ObjectType objtype) case OBJECT_STATISTIC_EXT: tag = CMDTAG_ALTER_STATISTICS; break; + case OBJECT_VARIABLE: + tag = CMDTAG_ALTER_VARIABLE; + break; default: tag = CMDTAG_UNKNOWN; break; @@ -2367,6 +2386,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: { @@ -2621,6 +2644,9 @@ CreateCommandTag(Node *parsetree) case OBJECT_STATISTIC_EXT: tag = CMDTAG_DROP_STATISTICS; break; + case OBJECT_VARIABLE: + tag = CMDTAG_DROP_VARIABLE; + break; default: tag = CMDTAG_UNKNOWN; } @@ -2909,6 +2935,9 @@ CreateCommandTag(Node *parsetree) case DISCARD_SEQUENCES: tag = CMDTAG_DISCARD_SEQUENCES; break; + case DISCARD_VARIABLES: + tag = CMDTAG_DISCARD_VARIABLES; + break; default: tag = CMDTAG_UNKNOWN; } @@ -3193,6 +3222,10 @@ CreateCommandTag(Node *parsetree) } break; + case T_CreateSessionVarStmt: + tag = CMDTAG_CREATE_VARIABLE; + break; + default: elog(WARNING, "unrecognized node type: %d", (int) nodeTag(parsetree)); @@ -3240,6 +3273,7 @@ GetCommandLogLevel(Node *parsetree) break; case T_PLAssignStmt: + case T_LetStmt: lev = LOGSTMT_ALL; break; diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index 67f8b29434..1b3c4bc41d 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -306,6 +306,12 @@ aclparse(const char *s, AclItem *aip) case ACL_CONNECT_CHR: read = ACL_CONNECT; break; + case ACL_READ_CHR: + read = ACL_READ; + break; + case ACL_WRITE_CHR: + read = ACL_WRITE; + break; case 'R': /* ignore old RULE privileges */ read = 0; break; @@ -794,6 +800,10 @@ acldefault(ObjectType objtype, Oid ownerId) world_default = ACL_USAGE; owner_default = ACL_ALL_RIGHTS_TYPE; break; + case OBJECT_VARIABLE: + world_default = ACL_NO_RIGHTS; + owner_default = ACL_ALL_RIGHTS_VARIABLE; + break; default: elog(ERROR, "unrecognized objtype: %d", (int) objtype); world_default = ACL_NO_RIGHTS; /* keep compiler quiet */ @@ -888,6 +898,9 @@ acldefault_sql(PG_FUNCTION_ARGS) case 'T': objtype = OBJECT_TYPE; break; + case 'V': + objtype = OBJECT_VARIABLE; + break; default: elog(ERROR, "unrecognized objtype abbreviation: %c", objtypec); } @@ -1604,6 +1617,10 @@ convert_priv_string(text *priv_type_text) return ACL_CONNECT; if (pg_strcasecmp(priv_type, "RULE") == 0) return 0; /* ignore old RULE privileges */ + if (pg_strcasecmp(priv_type, "READ") == 0) + return ACL_READ; + if (pg_strcasecmp(priv_type, "WRITE") == 0) + return ACL_WRITE; ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), @@ -1698,6 +1715,10 @@ convert_aclright_to_string(int aclright) return "TEMPORARY"; case ACL_CONNECT: return "CONNECT"; + case ACL_READ: + return "READ"; + case ACL_WRITE: + return "WRITE"; default: elog(ERROR, "unrecognized aclright: %d", aclright); return NULL; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 8da525c715..b5d5d5f006 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" @@ -7925,6 +7926,14 @@ get_parameter(Param *param, deparse_context *context) return; } + /* translate paramvarid to session variable name */ + if (param->paramkind == PARAM_VARIABLE) + { + appendStringInfo(context->buf, "%s", + session_variable_get_name(param->paramvarid)); + return; + } + /* * If it's an external parameter, see if the outermost namespace provides * function argument names. diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 9176514a96..af5ed1bca0 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -35,6 +35,7 @@ #include "catalog/pg_statistic.h" #include "catalog/pg_transform.h" #include "catalog/pg_type.h" +#include "catalog/pg_variable.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "utils/array.h" @@ -1860,6 +1861,18 @@ get_relname_relid(const char *relname, Oid relnamespace) ObjectIdGetDatum(relnamespace)); } +/* + * get_varname_varid + * Given name and namespace of variable, look up the OID. + */ +Oid +get_varname_varid(const char *varname, Oid varnamespace) +{ + return GetSysCacheOid2(VARIABLENAMENSP, Anum_pg_variable_oid, + PointerGetDatum(varname), + ObjectIdGetDatum(varnamespace)); +} + #ifdef NOT_USED /* * get_relnatts diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c index 56870b46e4..b385e2e677 100644 --- a/src/backend/utils/cache/syscache.c +++ b/src/backend/utils/cache/syscache.c @@ -74,6 +74,7 @@ #include "catalog/pg_ts_template.h" #include "catalog/pg_type.h" #include "catalog/pg_user_mapping.h" +#include "catalog/pg_variable.h" #include "lib/qunique.h" #include "utils/catcache.h" #include "utils/rel.h" @@ -1014,6 +1015,28 @@ static const struct cachedesc cacheinfo[] = { 0 }, 2 + }, + {VariableRelationId, /* VARIABLENAMENSP */ + VariableNameNspIndexId, + 2, + { + Anum_pg_variable_varname, + Anum_pg_variable_varnamespace, + 0, + 0 + }, + 8 + }, + {VariableRelationId, /* VARIABLEOID */ + VariableObjectIndexId, + 1, + { + Anum_pg_variable_oid, + 0, + 0, + 0 + }, + 8 } }; diff --git a/src/backend/utils/fmgr/fmgr.c b/src/backend/utils/fmgr/fmgr.c index 3dfe6e5825..3f60c7b365 100644 --- a/src/backend/utils/fmgr/fmgr.c +++ b/src/backend/utils/fmgr/fmgr.c @@ -1910,15 +1910,19 @@ get_call_expr_arg_stable(Node *expr, int argnum) arg = (Node *) list_nth(args, argnum); /* - * Either a true Const or an external Param will have a value that doesn't - * change during the execution of the query. In future we might want to - * consider other cases too, e.g. now(). + * Either a true Const or an external Param or variable will have a value + * that doesn't change during the execution of the query. In future we + * might want to consider other cases too, e.g. now(). */ 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/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index 36f8d5682e..85bbb41da3 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -263,7 +263,8 @@ getSchemaData(Archive *fout, int *numTablesPtr) pg_log_info("reading subscriptions"); getSubscriptions(fout); - free(inhinfo); /* not needed any longer */ + pg_log_info("reading variables"); + getVariables(fout); *numTablesPtr = numTables; return tblinfo; diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index b5900e4ed8..e458472c96 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -540,6 +540,11 @@ do { \ CONVERT_PRIV('r', "SELECT"); CONVERT_PRIV('w', "UPDATE"); } + else if (strcmp(type, "VARIABLE") == 0) + { + CONVERT_PRIV('S', "READ"); + CONVERT_PRIV('W', "WRITE"); + } else abort(); diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index 753252e05e..4f29d09ad8 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -130,12 +130,14 @@ typedef struct _restoreOptions int selFunction; int selTrigger; int selTable; + int selVariable; SimpleStringList indexNames; SimpleStringList functionNames; SimpleStringList schemaNames; SimpleStringList schemaExcludeNames; SimpleStringList triggerNames; SimpleStringList tableNames; + SimpleStringList variableNames; int useDB; ConnParams cparams; /* parameters to use if useDB */ diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 8903a694ae..0199a96411 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -2921,6 +2921,14 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) !simple_string_list_member(&ropt->triggerNames, te->tag)) return 0; } + else if (strcmp(te->desc, "VARIABLE") == 0) + { + if (!ropt->selVariable) + return 0; + if (ropt->variableNames.head != NULL && + !simple_string_list_member(&ropt->variableNames, te->tag)) + return 0; + } else return 0; } @@ -3404,6 +3412,7 @@ _getObjectDescription(PQExpBuffer buf, TocEntry *te) strcmp(type, "TEXT SEARCH DICTIONARY") == 0 || strcmp(type, "TEXT SEARCH CONFIGURATION") == 0 || strcmp(type, "STATISTICS") == 0 || + strcmp(type, "VARIABLE") == 0 || /* non-schema-specified objects */ strcmp(type, "DATABASE") == 0 || strcmp(type, "PROCEDURAL LANGUAGE") == 0 || @@ -3596,7 +3605,8 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData) strcmp(te->desc, "SERVER") == 0 || strcmp(te->desc, "STATISTICS") == 0 || strcmp(te->desc, "PUBLICATION") == 0 || - strcmp(te->desc, "SUBSCRIPTION") == 0) + strcmp(te->desc, "SUBSCRIPTION") == 0 || + strcmp(te->desc, "VARIABLE") == 0) { PQExpBuffer temp = createPQExpBuffer(); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 784771c2d7..ef9b21be68 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -276,6 +276,7 @@ static void dumpPolicy(Archive *fout, const PolicyInfo *polinfo); static void dumpPublication(Archive *fout, const PublicationInfo *pubinfo); static void dumpPublicationTable(Archive *fout, const PublicationRelInfo *pubrinfo); static void dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo); +static void dumpVariable(Archive *fout, const VariableInfo * varinfo); static void dumpDatabase(Archive *AH); static void dumpDatabaseConfig(Archive *AH, PQExpBuffer outbuf, const char *dbname, Oid dboid); @@ -4502,6 +4503,211 @@ get_next_possible_free_pg_type_oid(Archive *fout, PQExpBuffer upgrade_query) return next_possible_free_oid; } +/* + * getVariables + * get information about variables + */ +void +getVariables(Archive *fout) +{ + PQExpBuffer query; + PGresult *res; + VariableInfo *varinfo; + int i_tableoid; + int i_oid; + int i_varname; + int i_varnamespace; + int i_vartype; + int i_vartypname; + int i_vardefexpr; + int i_vareoxaction; + int i_varisnotnull; + int i_varisimmutable; + int i_rolname; + int i_varacl; + int i_acldefault; + int i, + ntups; + + if (fout->remoteVersion < 150000) + return; + + query = createPQExpBuffer(); + + resetPQExpBuffer(query); + + /* Get the variables in current database. */ + appendPQExpBuffer(query, + "SELECT v.tableoid, v.oid, v.varname, " + "v.vareoxaction, " + "v.varnamespace, " + "v.vartype, " + "pg_catalog.format_type(v.vartype, v.vartypmod) as vartypname, " + "v.varisnotnull, " + "v.varisimmutable, " + "pg_catalog.pg_get_expr(v.vardefexpr,0) as vardefexpr, " + "(%s varowner) AS rolname, " + "v.varacl, " + "acldefault('V', v.varowner) AS acldefault " + "FROM pg_variable v ", + username_subquery); + + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + + ntups = PQntuples(res); + + i_tableoid = PQfnumber(res, "tableoid"); + i_oid = PQfnumber(res, "oid"); + i_varname = PQfnumber(res, "varname"); + i_varnamespace = PQfnumber(res, "varnamespace"); + i_vartype = PQfnumber(res, "vartype"); + i_vartypname = PQfnumber(res, "vartypname"); + i_vareoxaction = PQfnumber(res, "vareoxaction"); + i_vardefexpr = PQfnumber(res, "vardefexpr"); + i_varisnotnull = PQfnumber(res, "varisnotnull"); + i_varisimmutable = PQfnumber(res, "varisimmutable"); + + i_rolname = PQfnumber(res, "rolname"); + i_varacl = PQfnumber(res, "varacl"); + i_acldefault = PQfnumber(res, "acldefault"); + + varinfo = pg_malloc(ntups * sizeof(VariableInfo)); + + for (i = 0; i < ntups; i++) + { + TypeInfo *vtype; + + varinfo[i].dobj.objType = DO_VARIABLE; + varinfo[i].dobj.catId.tableoid = + atooid(PQgetvalue(res, i, i_tableoid)); + varinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid)); + AssignDumpId(&varinfo[i].dobj); + varinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_varname)); + varinfo[i].dobj.namespace = + findNamespace(atooid(PQgetvalue(res, i, i_varnamespace))); + + varinfo[i].vartype = atooid(PQgetvalue(res, i, i_vartype)); + varinfo[i].vartypname = pg_strdup(PQgetvalue(res, i, i_vartypname)); + varinfo[i].vareoxaction = pg_strdup(PQgetvalue(res, i, i_vareoxaction)); + varinfo[i].varisnotnull = *(PQgetvalue(res, i, i_varisnotnull)) == 't'; + varinfo[i].varisimmutable = *(PQgetvalue(res, i, i_varisimmutable)) == 't'; + + varinfo[i].dacl.acl = pg_strdup(PQgetvalue(res, i, i_varacl)); + varinfo[i].dacl.acldefault = pg_strdup(PQgetvalue(res, i, i_acldefault)); + varinfo[i].dacl.privtype = 0; + varinfo[i].dacl.initprivs = NULL; + varinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname)); + + /* Decide whether we want to dump it */ + selectDumpableObject(&(varinfo[i].dobj), fout); + + /* Do not try to dump ACL if no ACL exists. */ + if (!PQgetisnull(res, i, i_varacl)) + varinfo[i].dobj.components |= DUMP_COMPONENT_ACL; + + if (PQgetisnull(res, i, i_vardefexpr)) + varinfo[i].vardefexpr = NULL; + else + varinfo[i].vardefexpr = pg_strdup(PQgetvalue(res, i, i_vardefexpr)); + + if (strlen(varinfo[i].rolname) == 0) + pg_log_warning("owner of variable \"%s\" appears to be invalid", + varinfo[i].dobj.name); + + /* Decide whether we want to dump it */ + selectDumpableObject(&(varinfo[i].dobj), fout); + + vtype = findTypeByOid(varinfo[i].vartype); + addObjectDependency(&varinfo[i].dobj, vtype->dobj.dumpId); + } + PQclear(res); + + destroyPQExpBuffer(query); +} + +/* + * dumpVariable + * dump the definition of the given variables + */ +static void +dumpVariable(Archive *fout, const VariableInfo * varinfo) +{ + DumpOptions *dopt = fout->dopt; + + PQExpBuffer delq; + PQExpBuffer query; + char *qualvarname; + const char *vartypname; + const char *vardefexpr; + const char *vareoxaction; + const char *varisnotnull; + const char *varisimmutable; + + /* Skip if not to be dumped */ + if (!varinfo->dobj.dump || dopt->dataOnly) + return; + + delq = createPQExpBuffer(); + query = createPQExpBuffer(); + + qualvarname = pg_strdup(fmtQualifiedDumpable(varinfo)); + vartypname = varinfo->vartypname; + vardefexpr = varinfo->vardefexpr; + vareoxaction = varinfo->vareoxaction; + varisnotnull = varinfo->varisnotnull ? " NOT NULL" : ""; + varisimmutable = varinfo->varisimmutable ? "IMMUTABLE " : ""; + + appendPQExpBuffer(delq, "DROP VARIABLE %s;\n", + qualvarname); + + appendPQExpBuffer(query, "CREATE %sVARIABLE %s AS %s%s", + varisimmutable, qualvarname, vartypname, varisnotnull); + + if (vardefexpr) + appendPQExpBuffer(query, " DEFAULT %s", + vardefexpr); + + if (strcmp(vareoxaction, "d") == 0) + appendPQExpBuffer(query, " ON COMMIT DROP"); + else if (strcmp(vareoxaction, "r") == 0) + appendPQExpBuffer(query, " ON TRANSACTION END RESET"); + + appendPQExpBuffer(query, ";\n"); + + if (varinfo->dobj.dump & DUMP_COMPONENT_DEFINITION) + ArchiveEntry(fout, varinfo->dobj.catId, varinfo->dobj.dumpId, + ARCHIVE_OPTS(.tag = varinfo->dobj.name, + .namespace = varinfo->dobj.namespace->dobj.name, + .owner = varinfo->rolname, + .description = "VARIABLE", + .section = SECTION_PRE_DATA, + .createStmt = query->data, + .dropStmt = delq->data)); + + /* Dump comment if any */ + if (varinfo->dobj.dump & DUMP_COMPONENT_COMMENT) + dumpComment(fout, "VARIABLE", qualvarname, + NULL, varinfo->rolname, + varinfo->dobj.catId, 0, varinfo->dobj.dumpId); + + /* Dump ACL if any */ + if (varinfo->dobj.dump & DUMP_COMPONENT_ACL) + { + char *qvarname = pg_strdup(fmtId(varinfo->dobj.name)); + + dumpACL(fout, varinfo->dobj.dumpId, InvalidDumpId, "VARIABLE", + qvarname, NULL, + varinfo->dobj.namespace->dobj.name, varinfo->rolname, &varinfo->dacl); + + free(qvarname); + } + + destroyPQExpBuffer(delq); + destroyPQExpBuffer(query); + + free(qualvarname); +} + static void binary_upgrade_set_type_oids_by_type_oid(Archive *fout, PQExpBuffer upgrade_buffer, @@ -9115,7 +9321,8 @@ getAdditionalACLs(Archive *fout) dobj->objType == DO_TABLE || dobj->objType == DO_PROCLANG || dobj->objType == DO_FDW || - dobj->objType == DO_FOREIGN_SERVER) + dobj->objType == DO_FOREIGN_SERVER || + dobj->objType == DO_VARIABLE) { DumpableObjectWithAcl *daobj = (DumpableObjectWithAcl *) dobj; @@ -9707,6 +9914,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) case DO_SUBSCRIPTION: dumpSubscription(fout, (const SubscriptionInfo *) dobj); break; + case DO_VARIABLE: + dumpVariable(fout, (VariableInfo *) dobj); + break; case DO_PRE_DATA_BOUNDARY: case DO_POST_DATA_BOUNDARY: /* never dumped, nothing to do */ @@ -14135,6 +14345,9 @@ dumpDefaultACL(Archive *fout, const DefaultACLInfo *daclinfo) case DEFACLOBJ_NAMESPACE: type = "SCHEMAS"; break; + case DEFACLOBJ_VARIABLE: + type = "VARIABLE"; + break; default: /* shouldn't get here */ fatal("unrecognized object type in default privileges: %d", @@ -17679,6 +17892,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs, case DO_CONVERSION: case DO_TABLE: case DO_TABLE_ATTACH: + case DO_VARIABLE: case DO_ATTRDEF: case DO_PROCLANG: case DO_CAST: diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index f011ace8a8..28fc950eef 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -52,6 +52,7 @@ typedef enum DO_TABLE, DO_TABLE_ATTACH, DO_ATTRDEF, + DO_VARIABLE, DO_INDEX, DO_INDEX_ATTACH, DO_STATSEXT, @@ -82,7 +83,7 @@ typedef enum DO_PUBLICATION, DO_PUBLICATION_REL, DO_PUBLICATION_TABLE_IN_SCHEMA, - DO_SUBSCRIPTION + DO_SUBSCRIPTION, } DumpableObjectType; /* @@ -659,6 +660,26 @@ typedef struct _SubscriptionInfo char *subpublications; } SubscriptionInfo; +/* + * The VariableInfo struct is used to represent session variables + */ +typedef struct _VariableInfo +{ + DumpableObject dobj; + DumpableAcl dacl; + Oid vartype; + char *vartypname; + char *vareoxaction; + char *vardefexpr; + char *varacl; + char *rvaracl; + char *initvaracl; + char *initrvaracl; + bool varisnotnull; + bool varisimmutable; + char *rolname; /* name of owner, or empty string */ +} VariableInfo; + /* * common utility functions */ @@ -741,5 +762,6 @@ extern void getPublicationNamespaces(Archive *fout); extern void getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables); extern void getSubscriptions(Archive *fout); +extern void getVariables(Archive *fout); #endif /* PG_DUMP_H */ diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index 5399ec1cd9..45108079c7 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -75,6 +75,7 @@ enum dbObjectTypePriorities PRIO_TABLE_ATTACH, PRIO_DUMMY_TYPE, PRIO_ATTRDEF, + PRIO_VARIABLE, PRIO_BLOB, PRIO_PRE_DATA_BOUNDARY, /* boundary! */ PRIO_TABLE_DATA, @@ -116,6 +117,7 @@ static const int dbObjectTypePriority[] = PRIO_TABLE, /* DO_TABLE */ PRIO_TABLE_ATTACH, /* DO_TABLE_ATTACH */ PRIO_ATTRDEF, /* DO_ATTRDEF */ + PRIO_VARIABLE, /* DO_VARIABLE */ PRIO_INDEX, /* DO_INDEX */ PRIO_INDEX_ATTACH, /* DO_INDEX_ATTACH */ PRIO_STATSEXT, /* DO_STATSEXT */ @@ -1508,6 +1510,10 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize) "POST-DATA BOUNDARY (ID %d)", obj->dumpId); return; + case DO_VARIABLE: + snprintf(buf, bufsize, + "VARIABLE %s (ID %d OID %u)", + obj->name, obj->dumpId, obj->catId.oid); } /* shouldn't get here */ snprintf(buf, bufsize, diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index 64aaa80eee..cc00e485db 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -102,6 +102,7 @@ main(int argc, char **argv) {"trigger", 1, NULL, 'T'}, {"use-list", 1, NULL, 'L'}, {"username", 1, NULL, 'U'}, + {"variable", 1, NULL, 'A'}, {"verbose", 0, NULL, 'v'}, {"single-transaction", 0, NULL, '1'}, @@ -149,7 +150,7 @@ main(int argc, char **argv) } } - while ((c = getopt_long(argc, argv, "acCd:ef:F:h:I:j:lL:n:N:Op:P:RsS:t:T:U:vwWx1", + while ((c = getopt_long(argc, argv, "A:acCd:ef:F:h:I:j:lL:n:N:Op:P:RsS:t:T:U:vwWx1", cmdopts, NULL)) != -1) { switch (c) @@ -157,6 +158,11 @@ main(int argc, char **argv) case 'a': /* Dump data only */ opts->dataOnly = 1; break; + case 'A': /* vAriable */ + opts->selTypes = 1; + opts->selVariable = 1; + simple_string_list_append(&opts->variableNames, optarg); + break; case 'c': /* clean (i.e., drop) schema prior to create */ opts->dropSchema = 1; break; @@ -461,6 +467,7 @@ usage(const char *progname) printf(_("\nOptions controlling the restore:\n")); printf(_(" -a, --data-only restore only the data, no schema\n")); + printf(_(" -A, --variable=NAME restore named session variable\n")); printf(_(" -c, --clean clean (drop) database objects before recreating\n")); printf(_(" -C, --create create the target database\n")); printf(_(" -e, --exit-on-error exit on error, default is to continue\n")); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index fd01651d9c..0c0adc2ae5 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -501,6 +501,16 @@ my %tests = ( unlike => { no_privs => 1, }, }, + 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT READ ON VARIABLES TO PUBLIC' + => { + create_order => 56, + create_sql => 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT READ ON VARIABLES TO PUBLIC;', + regexp => qr/^ + \QALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT READ ON VARIABLE TO PUBLIC;\E/xm, + like => { %full_runs, section_post_data => 1, }, + unlike => { no_privs => 1, }, + }, + 'ALTER ROLE regress_dump_test_role' => { regexp => qr/^ \QALTER ROLE regress_dump_test_role WITH \E @@ -1256,6 +1266,22 @@ my %tests = ( unlike => { exclude_dump_test_schema => 1, }, }, + 'COMMENT ON VARIABLE dump_test.variable1' => { + create_order => 71, + create_sql => 'COMMENT ON VARIABLE dump_test.variable1 + IS \'comment on variable\';', + regexp => + qr/^\QCOMMENT ON VARIABLE dump_test.variable1 IS 'comment on variable';\E/m, + like => { + %full_runs, + %dump_test_schema_runs, + section_pre_data => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + }, + }, + 'COPY test_table' => { create_order => 4, create_sql => 'INSERT INTO dump_test.test_table (col1) ' @@ -3061,6 +3087,30 @@ my %tests = ( }, }, + 'CREATE VARIABLE test_variable' => { + all_runs => 1, + catch_all => 'CREATE ... commands', + create_order => 61, + create_sql => 'CREATE VARIABLE dump_test.variable1 AS integer DEFAULT 0;', + regexp => qr/^ + \QCREATE VARIABLE dump_test.variable1 AS integer DEFAULT 0;\E/xm, + like => + { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, + unlike => { exclude_dump_test_schema => 1, }, + }, + + 'CREATE IMMUTABLE VARIABLE test_variable' => { + all_runs => 1, + catch_all => 'CREATE ... commands', + create_order => 61, + create_sql => 'CREATE IMMUTABLE VARIABLE dump_test.variable2 AS integer DEFAULT 0;', + regexp => qr/^ + \QCREATE IMMUTABLE VARIABLE dump_test.variable2 AS integer DEFAULT 0;\E/xm, + like => + { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, + unlike => { exclude_dump_test_schema => 1, }, + }, + 'CREATE VIEW test_view' => { create_order => 61, create_sql => 'CREATE VIEW dump_test.test_view @@ -3500,6 +3550,21 @@ my %tests = ( like => {}, }, + 'GRANT READ ON VARIABLE dump_test.variable1' => { + create_order => 73, + create_sql => + 'GRANT READ ON VARIABLE dump_test.variable1 TO regress_dump_test_role;', + regexp => qr/^ + \QGRANT READ ON VARIABLE dump_test.variable1 TO regress_dump_test_role;\E + /xm, + like => + { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, + unlike => { + exclude_dump_test_schema => 1, + no_privs => 1, + }, + }, + 'REFRESH MATERIALIZED VIEW matview' => { regexp => qr/^\QREFRESH MATERIALIZED VIEW dump_test.matview;\E/m, like => diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index ccd7b48108..23e86ee515 100644 --- a/src/bin/psql/command.c +++ b/src/bin/psql/command.c @@ -929,6 +929,9 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd) break; } break; + case 'V': /* Variables */ + success = listVariables(pattern, show_verbose); + break; case 'x': /* Extensions */ if (show_verbose) success = listExtensionContents(pattern); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index c28788e84f..442c714dd6 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -4773,6 +4773,97 @@ listSchemas(const char *pattern, bool verbose, bool showSystem) return true; } +/* + * \dV + * + * listVariables() + */ +bool +listVariables(const char *pattern, bool verbose) +{ + PQExpBufferData buf; + PGresult *res; + printQueryOpt myopt = pset.popt; + static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false}; + + initPQExpBuffer(&buf); + + printfPQExpBuffer(&buf, + "SELECT n.nspname as \"%s\",\n" + " v.varname as \"%s\",\n" + " pg_catalog.format_type(v.vartype, v.vartypmod) as \"%s\",\n" + " NOT v.varisnotnull as \"%s\",\n" + " NOT v.varisimmutable as \"%s\",\n" + " pg_catalog.pg_get_expr(v.vardefexpr, 0) as \"%s\",\n" + " pg_catalog.pg_get_userbyid(v.varowner) as \"%s\",\n" + " CASE v.vareoxaction\n" + " WHEN 'd' THEN 'ON COMMIT DROP'\n" + " WHEN 'r' THEN 'ON TRANSACTION END RESET' END as \"%s\"\n", + gettext_noop("Schema"), + gettext_noop("Name"), + gettext_noop("Type"), + gettext_noop("Is nullable"), + gettext_noop("Is mutable"), + gettext_noop("Default"), + gettext_noop("Owner"), + gettext_noop("Transactional end action")); + + if (verbose) + { + appendPQExpBufferStr(&buf, ",\n "); + printACLColumn(&buf, "v.varacl"); + appendPQExpBuffer(&buf, + ",\n pg_catalog.obj_description(v.oid, 'pg_variable') AS \"%s\"", + gettext_noop("Description")); + } + + appendPQExpBufferStr(&buf, + "\nFROM pg_catalog.pg_variable v" + "\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = v.varnamespace"); + + appendPQExpBufferStr(&buf, "\nWHERE true\n"); + if (!pattern) + appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n" + " AND n.nspname <> 'information_schema'\n"); + + processSQLNamePattern(pset.db, &buf, pattern, true, false, + "n.nspname", "v.varname", NULL, + "pg_catalog.pg_variable_is_visible(v.oid)"); + + appendPQExpBufferStr(&buf, "ORDER BY 1,2;"); + + res = PSQLexec(buf.data); + termPQExpBuffer(&buf); + if (!res) + return false; + + /* + * Most functions in this file are content to print an empty table when + * there are no matching objects. We intentionally deviate from that + * here, but only in !quiet mode, for historical reasons. + */ + if (PQntuples(res) == 0 && !pset.quiet) + { + if (pattern) + pg_log_error("Did not find any session variable named \"%s\".", + pattern); + else + pg_log_error("Did not find any session variables."); + } + else + { + myopt.nullPrint = NULL; + myopt.title = _("List of variables"); + myopt.translate_header = true; + myopt.translate_columns = translate_columns; + myopt.n_translate_columns = lengthof(translate_columns); + + printQuery(res, &myopt, pset.queryFout, false, pset.logfile); + } + + PQclear(res); + return true; +} /* * \dFp diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h index 71b320f1fc..a86fccc0d7 100644 --- a/src/bin/psql/describe.h +++ b/src/bin/psql/describe.h @@ -139,5 +139,7 @@ extern bool listOpFamilyOperators(const char *accessMethod_pattern, extern bool listOpFamilyFunctions(const char *access_method_pattern, const char *family_pattern, bool verbose); +/* \dV */ +extern bool listVariables(const char *pattern, bool varbose); #endif /* DESCRIBE_H */ diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index db12a8b2f3..a6a3b2eba2 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -265,6 +265,7 @@ slashUsage(unsigned short int pager) fprintf(output, _(" \\dT[S+] [PATTERN] list data types\n")); fprintf(output, _(" \\du[S+] [PATTERN] list roles\n")); fprintf(output, _(" \\dv[S+] [PATTERN] list views\n")); + fprintf(output, _(" \\dV [PATTERN] list variables\n")); fprintf(output, _(" \\dx[+] [PATTERN] list extensions\n")); fprintf(output, _(" \\dX [PATTERN] list extended statistics\n")); fprintf(output, _(" \\dy[+] [PATTERN] list event triggers\n")); diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index b524dc87fc..ff5a701360 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -628,6 +628,13 @@ static const SchemaQuery Query_for_list_of_collations = { .result = "pg_catalog.quote_ident(c.collname)", }; +static const SchemaQuery Query_for_list_of_variables = { + .min_server_version = 150000, + .catname = "pg_catalog.pg_variable v", + .viscondition = "pg_catalog.pg_variable_is_visible(v.oid)", + .namespace = "v.varnamespace", + .result = "pg_catalog.quote_ident(v.varname)", +}; /* * Queries to get lists of names of various kinds of things, possibly @@ -1097,6 +1104,7 @@ static const pgsql_thing_t words_after_create[] = { * TABLE ... */ {"USER", Query_for_list_of_roles " UNION SELECT 'MAPPING FOR'"}, {"USER MAPPING FOR", NULL, NULL, NULL}, + {"VARIABLE", NULL, NULL, &Query_for_list_of_variables}, {"VIEW", NULL, NULL, &Query_for_list_of_views}, {NULL} /* end of list */ }; @@ -1495,8 +1503,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", - "MOVE", "NOTIFY", "PREPARE", + "FETCH", "GRANT", "IMPORT FOREIGN SCHEMA", "INSERT INTO", "LET", "LISTEN", "LOAD", + "LOCK", "MOVE", "NOTIFY", "PREPARE", "REASSIGN", "REFRESH MATERIALIZED VIEW", "REINDEX", "RELEASE", "RESET", "REVOKE", "ROLLBACK", "SAVEPOINT", "SECURITY LABEL", "SELECT", "SET", "SHOW", "START", @@ -1515,7 +1523,7 @@ psql_completion(const char *text, int start, int end) "\\dF", "\\dFd", "\\dFp", "\\dFt", "\\dg", "\\di", "\\dl", "\\dL", "\\dm", "\\dn", "\\do", "\\dO", "\\dp", "\\dP", "\\dPi", "\\dPt", "\\drds", "\\dRs", "\\dRp", "\\ds", - "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy", + "\\dt", "\\dT", "\\dv", "\\du", "\\dx", "\\dX", "\\dy", "\\dV", "\\echo", "\\edit", "\\ef", "\\elif", "\\else", "\\encoding", "\\endif", "\\errverbose", "\\ev", "\\f", @@ -1932,6 +1940,9 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_alter_system_set_vars); else if (Matches("ALTER", "SYSTEM", "SET", MatchAny)) COMPLETE_WITH("TO"); + /* ALTER VARIABLE */ + else if (Matches("ALTER", "VARIABLE", MatchAny)) + COMPLETE_WITH("OWNER TO", "RENAME TO", "SET SCHEMA"); /* ALTER VIEW */ else if (Matches("ALTER", "VIEW", MatchAny)) COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME", @@ -2431,7 +2442,7 @@ psql_completion(const char *text, int start, int end) "ROUTINE", "RULE", "SCHEMA", "SEQUENCE", "SERVER", "STATISTICS", "SUBSCRIPTION", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRANSFORM FOR", - "TRIGGER", "TYPE", "VIEW"); + "TRIGGER", "TYPE", "VARIABLE", "VIEW"); else if (Matches("COMMENT", "ON", "ACCESS", "METHOD")) COMPLETE_WITH_QUERY(Query_for_list_of_access_methods); else if (Matches("COMMENT", "ON", "CONSTRAINT")) @@ -2853,7 +2864,7 @@ psql_completion(const char *text, int start, int end) /* CREATE TABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */ /* Complete "CREATE TEMP/TEMPORARY" with the possible temp objects */ else if (TailMatches("CREATE", "TEMP|TEMPORARY")) - COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW"); + COMPLETE_WITH("SEQUENCE", "TABLE", "VIEW", "VARIABLE"); /* Complete "CREATE UNLOGGED" with TABLE or MATVIEW */ else if (TailMatches("CREATE", "UNLOGGED")) COMPLETE_WITH("TABLE", "MATERIALIZED VIEW"); @@ -3160,6 +3171,14 @@ psql_completion(const char *text, int start, int end) else if (TailMatches("=", MatchAnyExcept("*)"))) COMPLETE_WITH(",", ")"); } +/* CREATE VARIABLE --- is allowed inside CREATE SCHEMA, so use TailMatches */ + /* Complete CREATE VARIABLE with AS */ + else if (TailMatches("CREATE", "VARIABLE", MatchAny) || + TailMatches("TEMP|TEMPORARY", "VARIABLE", MatchAny)) + COMPLETE_WITH("AS"); + else if (TailMatches("VARIABLE", MatchAny, "AS")) + /* Complete CREATE VARIABLE with AS types */ + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL); /* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */ /* Complete CREATE [ OR REPLACE ] VIEW with AS */ @@ -3274,7 +3293,7 @@ psql_completion(const char *text, int start, int end) /* DISCARD */ else if (Matches("DISCARD")) - COMPLETE_WITH("ALL", "PLANS", "SEQUENCES", "TEMP"); + COMPLETE_WITH("ALL", "PLANS", "SEQUENCES", "TEMP", "VARIABLES"); /* DO */ else if (Matches("DO")) @@ -3401,6 +3420,12 @@ psql_completion(const char *text, int start, int end) else if (Matches("DROP", "TRANSFORM", "FOR", MatchAny, "LANGUAGE", MatchAny)) COMPLETE_WITH("CASCADE", "RESTRICT"); + /* DROP VARIABLE */ + else if (Matches("DROP", "VARIABLE")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL); + else if (Matches("DROP", "VARIABLE", MatchAny)) + COMPLETE_WITH("CASCADE", "RESTRICT"); + /* EXECUTE */ else if (Matches("EXECUTE")) COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements); @@ -3516,7 +3541,8 @@ psql_completion(const char *text, int start, int end) if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES")) COMPLETE_WITH("SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", - "EXECUTE", "USAGE", "ALL"); + "EXECUTE", "USAGE", "ALL", + "READ", "WRITE"); else COMPLETE_WITH_QUERY(Query_for_list_of_roles " UNION SELECT 'SELECT'" @@ -3531,6 +3557,8 @@ psql_completion(const char *text, int start, int end) " UNION SELECT 'TEMPORARY'" " UNION SELECT 'EXECUTE'" " UNION SELECT 'USAGE'" + " UNION SELECT 'READ'" + " UNION SELECT 'WRITE'" " UNION SELECT 'ALL'"); } @@ -3540,7 +3568,7 @@ psql_completion(const char *text, int start, int end) */ else if (TailMatches("GRANT|REVOKE", MatchAny)) { - if (TailMatches("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|ALL")) + if (TailMatches("SELECT|INSERT|UPDATE|DELETE|TRUNCATE|REFERENCES|TRIGGER|CREATE|CONNECT|TEMPORARY|TEMP|EXECUTE|USAGE|READ|WRITE|ALL")) COMPLETE_WITH("ON"); else if (TailMatches("GRANT", MatchAny)) COMPLETE_WITH("TO"); @@ -3565,7 +3593,7 @@ psql_completion(const char *text, int start, int end) * objects supported. */ if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES")) - COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS"); + COMPLETE_WITH("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS", "VARIABLES"); else COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_grantables, " UNION SELECT 'ALL FUNCTIONS IN SCHEMA'" @@ -3573,6 +3601,7 @@ psql_completion(const char *text, int start, int end) " UNION SELECT 'ALL ROUTINES IN SCHEMA'" " UNION SELECT 'ALL SEQUENCES IN SCHEMA'" " UNION SELECT 'ALL TABLES IN SCHEMA'" + " UNION SELECT 'ALL VARIABLES IN SCHEMA'" " UNION SELECT 'DATABASE'" " UNION SELECT 'DOMAIN'" " UNION SELECT 'FOREIGN DATA WRAPPER'" @@ -3586,14 +3615,16 @@ psql_completion(const char *text, int start, int end) " UNION SELECT 'SEQUENCE'" " UNION SELECT 'TABLE'" " UNION SELECT 'TABLESPACE'" - " UNION SELECT 'TYPE'"); + " UNION SELECT 'TYPE'" + " UNION SELECT 'VARIABLE'"); } else if (TailMatches("GRANT|REVOKE", MatchAny, "ON", "ALL")) COMPLETE_WITH("FUNCTIONS IN SCHEMA", "PROCEDURES IN SCHEMA", "ROUTINES IN SCHEMA", "SEQUENCES IN SCHEMA", - "TABLES IN SCHEMA"); + "TABLES IN SCHEMA", + "VARIABLES IN SCHEMA"); else if (TailMatches("GRANT|REVOKE", MatchAny, "ON", "FOREIGN")) COMPLETE_WITH("DATA WRAPPER", "SERVER"); @@ -3627,6 +3658,8 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces); else if (TailMatches("TYPE")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes, NULL); + else if (TailMatches("VARIABLE")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL); else if (TailMatches("GRANT", MatchAny, MatchAny, MatchAny)) COMPLETE_WITH("TO"); else @@ -3799,7 +3832,7 @@ psql_completion(const char *text, int start, int end) /* PREPARE xx AS */ else if (Matches("PREPARE", MatchAny, "AS")) - COMPLETE_WITH("SELECT", "UPDATE", "INSERT INTO", "DELETE FROM"); + COMPLETE_WITH("SELECT", "UPDATE", "INSERT INTO", "DELETE FROM", "LET"); /* * PREPARE TRANSACTION is missing on purpose. It's intended for transaction @@ -4073,6 +4106,14 @@ psql_completion(const char *text, int start, int end) else if (TailMatches("UPDATE", MatchAny, "SET", MatchAnyExcept("*="))) 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, NULL); + /* Complete LET with "=" */ + else if (TailMatches("LET", MatchAny)) + COMPLETE_WITH("="); + /* USER MAPPING */ else if (Matches("ALTER|CREATE|DROP", "USER", "MAPPING")) COMPLETE_WITH("FOR"); @@ -4237,6 +4278,8 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_QUERY(Query_for_list_of_roles); else if (TailMatchesCS("\\dv*")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_views, NULL); + else if (TailMatchesCS("\\dV*")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables, NULL); else if (TailMatchesCS("\\dx*")) COMPLETE_WITH_QUERY(Query_for_list_of_extensions); else if (TailMatchesCS("\\dX*")) diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index 3eca295ff4..347008a606 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -125,10 +125,11 @@ typedef enum ObjectClass OCLASS_PUBLICATION_NAMESPACE, /* pg_publication_namespace */ OCLASS_PUBLICATION_REL, /* pg_publication_rel */ OCLASS_SUBSCRIPTION, /* pg_subscription */ - OCLASS_TRANSFORM /* pg_transform */ + OCLASS_TRANSFORM, /* pg_transform */ + OCLASS_VARIABLE /* pg_variable */ } ObjectClass; -#define LAST_OCLASS OCLASS_TRANSFORM +#define LAST_OCLASS OCLASS_VARIABLE /* flag bits for performDeletion/performMultipleDeletions: */ #define PERFORM_DELETION_INTERNAL 0x0001 /* internal action */ diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h index b98f284356..419de41ca2 100644 --- a/src/include/catalog/namespace.h +++ b/src/include/catalog/namespace.h @@ -96,6 +96,8 @@ extern Oid TypenameGetTypid(const char *typname); extern Oid TypenameGetTypidExtended(const char *typname, bool temp_ok); extern bool TypeIsVisible(Oid typid); +extern bool VariableIsVisible(Oid varid); + extern FuncCandidateList FuncnameGetCandidates(List *names, int nargs, List *argnames, bool expand_variadic, @@ -164,6 +166,10 @@ extern void SetTempNamespaceState(Oid tempNamespaceId, Oid tempToastNamespaceId); extern void ResetTempTableNamespace(void); +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); + extern OverrideSearchPath *GetOverrideSearchPath(MemoryContext context); extern OverrideSearchPath *CopyOverrideSearchPath(OverrideSearchPath *path); extern bool OverrideSearchPathMatchesCurrent(OverrideSearchPath *path); diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h index eb72dd6293..2ca72a0dcd 100644 --- a/src/include/catalog/pg_default_acl.h +++ b/src/include/catalog/pg_default_acl.h @@ -66,6 +66,7 @@ DECLARE_UNIQUE_INDEX_PKEY(pg_default_acl_oid_index, 828, DefaultAclOidIndexId, o #define DEFACLOBJ_FUNCTION 'f' /* function */ #define DEFACLOBJ_TYPE 'T' /* type */ #define DEFACLOBJ_NAMESPACE 'n' /* namespace */ +#define DEFACLOBJ_VARIABLE 'V' /* variable */ #endif /* EXPOSE_TO_CLIENT_CODE */ diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 4d992dc224..c9a2e1431a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -6246,6 +6246,9 @@ proname => 'pg_collation_is_visible', procost => '10', provolatile => 's', prorettype => 'bool', proargtypes => 'oid', prosrc => 'pg_collation_is_visible' }, +{ oid => '9221', descr => 'is session variable visible in search path?', + proname => 'pg_variable_is_visible', procost => '10', provolatile => 's', + prorettype => 'bool', proargtypes => 'oid', prosrc => 'pg_variable_is_visible' }, { oid => '2854', descr => 'get OID of current session\'s temp schema, if any', proname => 'pg_my_temp_schema', provolatile => 's', proparallel => 'r', diff --git a/src/include/catalog/pg_variable.h b/src/include/catalog/pg_variable.h new file mode 100644 index 0000000000..b6716ca8cc --- /dev/null +++ b/src/include/catalog/pg_variable.h @@ -0,0 +1,117 @@ +/*------------------------------------------------------------------------- + * + * pg_variable.h + * definition of session variables system catalog (pg_variables) + * + * + * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * src/include/catalog/pg_variable.h + * + * NOTES + * The Catalog.pm module reads this file and derives schema + * information. + * + *------------------------------------------------------------------------- + */ +#ifndef PG_VARIABLE_H +#define PG_VARIABLE_H + +#include "catalog/genbki.h" +#include "catalog/objectaddress.h" +#include "catalog/pg_variable_d.h" +#include "utils/acl.h" + +/* ---------------- + * pg_variable definition. cpp turns this into + * typedef struct FormData_pg_variable + * ---------------- + */ +CATALOG(pg_variable,9222,VariableRelationId) +{ + Oid oid; /* oid */ + NameData varname; /* variable name */ + Oid varnamespace; /* OID of namespace containing variable class */ + Oid vartype; /* OID of entry in pg_type for variable's type */ + int32 vartypmod; /* typmode for variable's type */ + Oid varowner; /* class owner */ + Oid varcollation; /* variable collation */ + bool varisnotnull; /* Don't allow NULL */ + bool varisimmutable; /* Don't allow changes */ + char vareoxaction; /* action on transaction end */ + +#ifdef CATALOG_VARLEN /* variable-length fields start here */ + + /* list of expression trees for variable default (NULL if none) */ + pg_node_tree vardefexpr BKI_DEFAULT(_null_); + + aclitem varacl[1] BKI_DEFAULT(_null_); /* access permissions */ + +#endif +} FormData_pg_variable; + +typedef enum VariableEOXActionCodes +{ + VARIABLE_EOX_CODE_NOOP = 'n', /* NOOP */ + VARIABLE_EOX_CODE_DROP = 'd', /* ON COMMIT DROP */ + VARIABLE_EOX_CODE_RESET = 'r', /* ON COMMIT RESET */ +} VariableEOXActionCodes; + +/* ---------------- + * Form_pg_variable corresponds to a pointer to a tuple with + * the format of pg_variable relation. + * ---------------- + */ +typedef FormData_pg_variable *Form_pg_variable; + +DECLARE_UNIQUE_INDEX_PKEY(pg_variable_oid_index, 9223, VariableOidIndexId, on pg_variable using btree(oid oid_ops)); +#define VariableObjectIndexId 9223 + +DECLARE_UNIQUE_INDEX(pg_variable_varname_nsp_index, 9224, VariableNameNspIndexId, on pg_variable using btree(varname name_ops, varnamespace oid_ops)); +#define VariableNameNspIndexId 9224 + +typedef struct Variable +{ + Oid oid; + char *name; + Oid namespace; + Oid typid; + int32 typmod; + Oid owner; + Oid collation; + bool is_not_null; + bool is_immutable; + VariableEOXAction eoxaction; + bool has_defexpr; + Node *defexpr; + Acl *acl; +} Variable; + +/* returns fields from pg_variable table */ +extern char *get_session_variable_name(Oid varid); +extern void get_session_variable_type_typmod_collid(Oid varid, + Oid *typid, + int32 *typmod, + Oid *collid); + +/* returns name of variable based on current search path */ +extern char *session_variable_get_name(Oid varid); + +extern void initVariable(Variable *var, + Oid varid, + bool missing_ok, + bool fast_only); +extern ObjectAddress VariableCreate(const char *varName, + Oid varNamespace, + Oid varType, + int32 varTypmod, + Oid varOwner, + Oid varCollation, + Node *varDefexpr, + VariableEOXAction eoxaction, + bool is_not_null, + bool if_not_exists, + bool is_immutable); + +#endif /* PG_VARIABLE_H */ diff --git a/src/include/commands/session_variable.h b/src/include/commands/session_variable.h new file mode 100644 index 0000000000..c8c3ab2570 --- /dev/null +++ b/src/include/commands/session_variable.h @@ -0,0 +1,44 @@ +/*------------------------------------------------------------------------- + * + * sessionvariable.h + * prototypes for sessionvariable.c. + * + * + * Portions Copyright (c) 1996-2020, 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 "catalog/objectaddress.h" +#include "catalog/pg_variable.h" +#include "nodes/params.h" +#include "nodes/parsenodes.h" +#include "nodes/plannodes.h" +#include "tcop/cmdtag.h" +#include "utils/queryenvironment.h" + +extern void ResetSessionVariables(void); +extern void RemoveSessionVariable(Oid varid); +extern ObjectAddress DefineSessionVariable(ParseState *pstate, CreateSessionVarStmt * stmt); + +extern Datum GetSessionVariable(Oid varid, bool *isNull, Oid expected_typid, bool copy); +extern Datum CopySessionVariable(Oid varid, bool *isNull, Oid *typid); +extern void SetSessionVariable(Oid varid, Datum value, bool isNull, Oid typid); +extern void SetSessionVariableWithSecurityCheck(Oid varid, Datum value, bool isNull, Oid typid); + +extern void ExecuteLetStmt(ParseState *pstate, LetStmt *stmt, ParamListInfo params, + QueryEnvironment *queryEnv, QueryCompletion *qc); + +extern void RegisterOnCommitDropSessionVariable(Oid varid); + +extern void AtPreEOXact_SessionVariable_on_xact_actions(bool isCommit); +extern void AtEOSubXact_SessionVariable_on_xact_actions(bool isCommit, SubTransactionId mySubid, + SubTransactionId parentSubid); + +#endif diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index 6a24341faa..123a18f298 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -158,6 +158,7 @@ typedef enum ExprEvalOp EEOP_PARAM_EXEC, EEOP_PARAM_EXTERN, EEOP_PARAM_CALLBACK, + EEOP_PARAM_VARIABLE, /* return CaseTestExpr value */ EEOP_CASE_TESTVAL, @@ -380,6 +381,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 { @@ -736,6 +744,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 017ad87117..00d2536e30 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..5305936547 --- /dev/null +++ b/src/include/executor/svariableReceiver.h @@ -0,0 +1,25 @@ +/*------------------------------------------------------------------------- + * + * svariableReceiver.h + * prototypes for svariableReceiver.c + * + * + * Portions Copyright (c) 1996-2020, 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 SetVariableDestReceiverParams(DestReceiver *self, Oid varid); + +#endif /* SVARIABLE_RECEIVER_H */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index ddc3529332..cc01381d7c 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -544,6 +544,18 @@ typedef struct AsyncRequest * tuples) */ } AsyncRequest; +/* ---------------- + * SessionVariableValue + * ---------------- + */ +typedef struct SessionVariableValue +{ + Oid varid; + Oid typid; + bool isnull; + Datum value; +} SessionVariableValue; + /* ---------------- * EState information * @@ -595,6 +607,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/nodes.h b/src/include/nodes/nodes.h index 7c657c1241..96aa0a2270 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -355,6 +355,7 @@ typedef enum NodeTag T_CreateTableAsStmt, T_CreateSeqStmt, T_AlterSeqStmt, + T_CreateSessionVarStmt, T_VariableSetStmt, T_VariableShowStmt, T_DiscardStmt, @@ -428,6 +429,7 @@ typedef enum NodeTag T_AlterCollationStmt, T_CallStmt, T_AlterStatsStmt, + T_LetStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 4c5a8a39bf..b4b7c19ba6 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -92,7 +92,9 @@ typedef uint32 AclMode; /* a bitmask of privilege bits */ #define ACL_CREATE (1<<9) /* for namespaces and databases */ #define ACL_CREATE_TEMP (1<<10) /* for databases */ #define ACL_CONNECT (1<<11) /* for databases */ -#define N_ACL_RIGHTS 12 /* 1 plus the last 1<err_stmt = save_estmt; @@ -4950,6 +4957,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, valtype); + + 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 e0863acb3d..265f93fb7b 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"; @@ -368,6 +370,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); @@ -457,6 +460,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; @@ -711,6 +717,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) { @@ -813,6 +825,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); @@ -912,6 +925,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; @@ -1588,6 +1604,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 0f6a5b30b1..14845e64a2 100644 --- a/src/pl/plpgsql/src/pl_gram.y +++ b/src/pl/plpgsql/src/pl_gram.y @@ -197,7 +197,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 @@ -304,6 +304,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_MESSAGE @@ -897,6 +898,8 @@ proc_stmt : pl_block ';' { $$ = $1; } | stmt_rollback { $$ = $1; } + | stmt_let + { $$ = $1; } ; stmt_perform : K_PERFORM @@ -1013,6 +1016,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 daf835e683..5e64984ff4 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 3936866bb7..e4cb07d6b1 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; /* @@ -519,6 +520,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/test/regress/expected/misc_sanity.out b/src/test/regress/expected/misc_sanity.out index a57fd142a9..ce9bad7211 100644 --- a/src/test/regress/expected/misc_sanity.out +++ b/src/test/regress/expected/misc_sanity.out @@ -60,7 +60,9 @@ ORDER BY 1, 2; pg_index | indpred | pg_node_tree pg_largeobject | data | bytea pg_largeobject_metadata | lomacl | aclitem[] -(11 rows) + pg_variable | varacl | aclitem[] + pg_variable | vardefexpr | pg_node_tree +(13 rows) -- system catalogs without primary keys -- diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 63706a28cc..a71d246128 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -165,6 +165,7 @@ pg_ts_parser|t pg_ts_template|t pg_type|t pg_user_mapping|t +pg_variable|t point_tbl|t polygon_tbl|t quad_box_tbl|t diff --git a/src/test/regress/expected/schema_variables.out b/src/test/regress/expected/schema_variables.out new file mode 100644 index 0000000000..58e85a44ea --- /dev/null +++ b/src/test/regress/expected/schema_variables.out @@ -0,0 +1,655 @@ +CREATE SCHEMA svartest; +SET search_path = svartest; +CREATE VARIABLE var1 AS integer; +CREATE TEMP VARIABLE var2 AS text; +DROP VARIABLE var1, var2; +-- functional interface +CREATE VARIABLE var1 AS numeric; +CREATE ROLE var_test_role; +GRANT USAGE ON SCHEMA svartest TO var_test_role; +SET ROLE TO var_test_role; +-- should fail +SELECT var1; +ERROR: permission denied for schema variable var1 +SET ROLE TO DEFAULT; +GRANT READ ON VARIABLE var1 TO var_test_role; +SET ROLE TO var_test_role; +-- should fail +LET var1 = 10; +ERROR: permission denied for schema variable var1 +-- should work +SELECT var1; + var1 +------ + +(1 row) + +SET ROLE TO DEFAULT; +GRANT WRITE ON VARIABLE var1 TO var_test_role; +SET ROLE TO var_test_role; +-- should work +LET var1 = 333; +SET ROLE TO DEFAULT; +REVOKE ALL ON VARIABLE var1 FROM var_test_role; +CREATE OR REPLACE FUNCTION secure_var() +RETURNS int AS $$ + SELECT svartest.var1::int; +$$ LANGUAGE sql SECURITY DEFINER; +SELECT secure_var(); + secure_var +------------ + 333 +(1 row) + +SET ROLE TO var_test_role; +-- should fail +SELECT svartest.var1; +ERROR: permission denied for schema variable var1 +-- should work; +SELECT secure_var(); + secure_var +------------ + 333 +(1 row) + +SET ROLE TO DEFAULT; +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var1; + QUERY PLAN +----------------------------------------------- + Function Scan on pg_catalog.generate_series g + Output: v + Function Call: generate_series(1, 100) + Filter: ((g.v)::numeric = var1) +(4 rows) + +CREATE VIEW schema_var_view AS SELECT var1; +SELECT * FROM schema_var_view; + var1 +------ + 333 +(1 row) + +\c - +SET search_path = svartest; +-- should work still, but var will be empty +SELECT * FROM schema_var_view; + var1 +------ + +(1 row) + +LET var1 = pi(); +SELECT var1; + var1 +------------------ + 3.14159265358979 +(1 row) + +-- we can see execution plan of LET statement +EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi(); + QUERY PLAN +---------------------------- + SET SCHEMA VARIABLE + Result + Output: 3.14159265358979 +(3 rows) + +SELECT var1; + var1 +------------------ + 3.14159265358979 +(1 row) + +CREATE VARIABLE var3 AS int; +CREATE OR REPLACE FUNCTION inc(int) +RETURNS int AS $$ +BEGIN + LET svartest.var3 = COALESCE(svartest.var3 + $1, $1); + RETURN var3; +END; +$$ LANGUAGE plpgsql; +SELECT inc(1); + inc +----- + 1 +(1 row) + +SELECT inc(1); + inc +----- + 2 +(1 row) + +SELECT inc(1); + inc +----- + 3 +(1 row) + +SELECT inc(1) FROM generate_series(1,10); + inc +----- + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 +(10 rows) + +SET ROLE TO var_test_role; +-- should fail +LET var3 = 0; +ERROR: permission denied for schema variable var3 +SET ROLE TO DEFAULT; +DROP VIEW schema_var_view; +DROP VARIABLE var1 CASCADE; +DROP VARIABLE var3 CASCADE; +-- 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; +\d v1 +\d v2 +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.14); +SELECT v1; + v1 +------------ + (1,2,3.14) +(1 row) + +SELECT v2; + v2 +--------------- + (10,20,31.40) +(1 row) + +SELECT (v1).*; + x | y | z +---+---+------ + 1 | 2 | 3.14 +(1 row) + +SELECT (v2).*; + x | y | z +----+----+------- + 10 | 20 | 31.40 +(1 row) + +SELECT v1.x + v1.z; + ?column? +---------- + 4.14 +(1 row) + +SELECT v2.x + v2.z; + ?column? +---------- + 41.40 +(1 row) + +-- access to composite fields should be safe too +-- should fail +SET ROLE TO var_test_role; +SELECT v2.x; +ERROR: permission denied for schema variable v2 +SET ROLE TO DEFAULT; +DROP VARIABLE v1; +DROP VARIABLE v2; +REVOKE USAGE ON SCHEMA svartest FROM var_test_role; +DROP ROLE var_test_role; +-- scalar variables should not be in conflict with qualified column +CREATE VARIABLE varx AS text; +SELECT varx.relname FROM pg_class varx WHERE varx.relname = 'pg_class'; + relname +---------- + pg_class +(1 row) + +-- should fail +SELECT varx.xxx; +ERROR: type text is not composite +-- variables can be updated under RO transaction +BEGIN; +SET TRANSACTION READ ONLY; +LET varx = 'hello'; +COMMIT; +SELECT varx; + varx +------- + hello +(1 row) + +DROP VARIABLE varx; +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 +LET v1.x = 10; +ERROR: cannot assign to field "x" of column "x" because there is no such column in data type t1 +LINE 1: LET v1.x = 10; + ^ +DROP VARIABLE v1; +DROP TYPE t1; +-- arrays are supported +CREATE VARIABLE va1 AS numeric[]; +LET va1 = ARRAY[1.1,2.1]; +LET va1[1] = 10.1; +SELECT va1; + va1 +------------ + {10.1,2.1} +(1 row) + +CREATE TYPE ta2 AS (a numeric, b numeric[]); +CREATE VARIABLE va2 AS ta2; +LET va2 = (10.1, ARRAY[0.0, 0.0]); +LET va2.a = 10.2; +SELECT va2; + va2 +-------------------- + (10.2,"{0.0,0.0}") +(1 row) + +LET va2.b[1] = 10.3; +SELECT va2; + va2 +--------------------- + (10.2,"{10.3,0.0}") +(1 row) + +DROP VARIABLE va1; +DROP VARIABLE va2; +DROP TYPE ta2; +-- default values +CREATE VARIABLE v1 AS numeric DEFAULT pi(); +LET v1 = v1 * 2; +SELECT v1; + v1 +------------------ + 6.28318530717958 +(1 row) + +CREATE TYPE t2 AS (a numeric, b text); +CREATE VARIABLE v2 AS t2 DEFAULT (NULL, 'Hello'); +LET svartest.v2.a = pi(); +SELECT v2; + v2 +-------------------------- + (3.14159265358979,Hello) +(1 row) + +-- should fail due dependency +DROP TYPE t2; +ERROR: cannot drop type t2 because other objects depend on it +DETAIL: schema variable v2 depends on type t2 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- should be ok +DROP VARIABLE v1; +DROP VARIABLE v2; +-- tests of alters +CREATE SCHEMA var_schema1; +CREATE SCHEMA var_schema2; +CREATE VARIABLE var_schema1.var1 AS integer; +LET var_schema1.var1 = 1000; +SELECT var_schema1.var1; + var1 +------ + 1000 +(1 row) + +ALTER VARIABLE var_schema1.var1 SET SCHEMA var_schema2; +SELECT var_schema2.var1; + var1 +------ + 1000 +(1 row) + +CREATE ROLE var_test_role; +ALTER VARIABLE var_schema2.var1 OWNER TO var_test_role; +SET ROLE TO var_test_role; +-- should fail, no access to schema var_schema2.var +SELECT var_schema2.var1; +ERROR: permission denied for schema var_schema2 +DROP VARIABLE var_schema2.var1; +ERROR: permission denied for schema var_schema2 +SET ROLE TO DEFAULT; +ALTER VARIABLE var_schema2.var1 SET SCHEMA public; +SET ROLE TO var_test_role; +SELECT public.var1; + var1 +------ + 1000 +(1 row) + +ALTER VARIABLE public.var1 RENAME TO var1_renamed; +SELECT public.var1_renamed; + var1_renamed +-------------- + 1000 +(1 row) + +DROP VARIABLE public.var1_renamed; +SET ROLE TO DEFAULt; +DROP ROLE var_test_role; +CREATE VARIABLE xx AS text DEFAULT 'hello'; +SELECT xx, upper(xx); + xx | upper +-------+------- + hello | HELLO +(1 row) + +LET xx = 'Hi'; +SELECT xx; + xx +---- + Hi +(1 row) + +DROP VARIABLE xx; +-- ON TRANSACTION END RESET tests +CREATE VARIABLE t1 AS int DEFAULT -1 ON TRANSACTION END RESET; +BEGIN; + SELECT t1; + t1 +---- + -1 +(1 row) + + LET t1 = 100; + SELECT t1; + t1 +----- + 100 +(1 row) + +COMMIT; +SELECT t1; + t1 +---- + -1 +(1 row) + +BEGIN; + SELECT t1; + t1 +---- + -1 +(1 row) + + LET t1 = 100; + SELECT t1; + t1 +----- + 100 +(1 row) + +ROLLBACK; +SELECT t1; + t1 +---- + -1 +(1 row) + +DROP VARIABLE t1; +CREATE VARIABLE v1 AS int DEFAULT 0; +CREATE VARIABLE v2 AS text DEFAULT 'none'; +LET v1 = 100; +LET v2 = 'Hello'; +SELECT v1, v2; + v1 | v2 +-----+------- + 100 | Hello +(1 row) + +LET v1 = DEFAULT; +LET v2 = DEFAULT; +SELECT v1, v2; + v1 | v2 +----+------ + 0 | none +(1 row) + +DROP VARIABLE v1; +DROP VARIABLE v2; +-- ON COMMIT DROP tests +-- should be 0 always +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +BEGIN; + CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +COMMIT; +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +BEGIN; + CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +ROLLBACK; +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +-- test on query with workers +CREATE TABLE svar_test(a int); +INSERT INTO svar_test SELECT * FROM generate_series(1,1000000); +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 +-------------------------------------------------- + Finalize Aggregate + -> Gather + Workers Planned: 2 + -> Partial Aggregate + -> Parallel Seq Scan on svar_test + Filter: ((a % 10) = zero) +(6 rows) + +-- result should be 100000 +SELECT count(*) FROM svar_test WHERE a%10 = zero; + count +-------- + 100000 +(1 row) + +LET zero = (SELECT count(*) FROM svar_test); +-- result should be 1000000 +SELECT zero; + zero +--------- + 1000000 +(1 row) + +-- parallel workers should be used +EXPLAIN (costs off) LET zero = (SELECT count(*) FROM svar_test); + QUERY PLAN +---------------------------------------------------------- + SET SCHEMA VARIABLE + Result + InitPlan 1 (returns $1) + -> Finalize Aggregate + -> Gather + Workers Planned: 2 + -> Partial Aggregate + -> Parallel Seq Scan on svar_test +(8 rows) + +DROP TABLE svar_test; +DROP VARIABLE zero; +-- use variables in prepared statements +CREATE VARIABLE v AS numeric; +LET v = 3.14; +-- use variables in views +CREATE VIEW vv AS SELECT COALESCE(v, 0) + 1000 AS result; +SELECT * FROM vv; + result +--------- + 1003.14 +(1 row) + +-- start a new session +\c +SET search_path to svartest; +SELECT * FROM vv; + result +-------- + 1000 +(1 row) + +LET v = 3.14; +SELECT * FROM vv; + result +--------- + 1003.14 +(1 row) + +-- should fail, dependency +DROP VARIABLE v; +ERROR: cannot drop schema variable v because other objects depend on it +DETAIL: view vv depends on schema variable v +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- should be ok +DROP VARIABLE v CASCADE; +NOTICE: drop cascades to view vv +-- other features +CREATE VARIABLE dt AS integer DEFAULT 0; +LET dt = 100; +SELECT dt; + dt +----- + 100 +(1 row) + +DISCARD VARIABLES; +SELECT dt; + dt +---- + 0 +(1 row) + +DROP VARIABLE dt; +-- NOT NULL +CREATE VARIABLE v1 AS int NOT NULL; +CREATE VARIABLE v2 AS int NOT NULL DEFAULT NULL; +-- should fail +SELECT v1; +ERROR: null value is not allowed for NOT NULL schema variable "v1" +DETAIL: The schema variable was not initialized yet. +SELECT v2; +ERROR: null value is not allowed for NOT NULL schema variable "v2" +LET v1 = NULL; +ERROR: null value is not allowed for NOT NULL schema variable "v1" +LET v2 = NULL; +ERROR: null value is not allowed for NOT NULL schema variable "v2" +LET v1 = DEFAULT; +ERROR: null value is not allowed for NOT NULL schema variable "v1" +LET v2 = DEFAULT; +ERROR: null value is not allowed for NOT NULL schema variable "v2" +-- should be ok +LET v1 = 100; +LET v2 = 1000; +SELECT v1, v2; + v1 | v2 +-----+------ + 100 | 1000 +(1 row) + +DROP VARIABLE v1; +DROP VARIABLE v2; +CREATE VARIABLE tv AS int; +CREATE VARIABLE IF NOT EXISTS tv AS int; +NOTICE: schema variable "tv" already exists, skipping +DROP VARIABLE tv; +CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100; +SELECT iv; + iv +----- + 100 +(1 row) + +-- should fail; +LET iv = 10000; +ERROR: schema variable "iv" is declared IMMUTABLE +DROP VARIABLE iv; +-- create variable inside plpgsql block +DO $$ +BEGIN + CREATE VARIABLE do_test_svar AS date DEFAULT '2000-01-01'; +END; +$$; +SELECT do_test_svar; + do_test_svar +-------------- + 01-01-2000 +(1 row) + +DROP VARIABLE do_test_svar; +-- should fail +CREATE IMMUTABLE VARIABLE xx AS int NOT NULL; +ERROR: IMMUTABLE NOT NULL variable requires default expression +-- REASSIGN OWNED test +CREATE ROLE var_test_role1; +CREATE ROLE var_test_role2; +CREATE VARIABLE xxx_var AS int; +ALTER VARIABLE xxx_var OWNER TO var_test_role1; +REASSIGN OWNED BY var_test_role1 to var_test_role2; +SELECT varowner::regrole FROM pg_variable WHERE varname = 'xxx_var'; + varowner +---------------- + var_test_role2 +(1 row) + +DROP OWNED BY var_test_role1; +DROP ROLE var_test_role1; +SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var'; + count +------- + 1 +(1 row) + +DROP OWNED BY var_test_role2; +DROP ROLE var_test_role2; +SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var'; + count +------- + 0 +(1 row) + diff --git a/src/test/regress/expected/session_variables.out b/src/test/regress/expected/session_variables.out new file mode 100644 index 0000000000..669727b88b --- /dev/null +++ b/src/test/regress/expected/session_variables.out @@ -0,0 +1,744 @@ +CREATE SCHEMA svartest; +SET search_path = svartest; +CREATE VARIABLE var1 AS integer; +CREATE TEMP VARIABLE var2 AS text; +DROP VARIABLE var1, var2; +-- functional interface +CREATE VARIABLE var1 AS numeric; +CREATE ROLE var_test_role; +GRANT USAGE ON SCHEMA svartest TO var_test_role; +SET ROLE TO var_test_role; +-- should fail +SELECT var1; +ERROR: permission denied for session variable var1 +SET ROLE TO DEFAULT; +GRANT READ ON VARIABLE var1 TO var_test_role; +SET ROLE TO var_test_role; +-- should fail +LET var1 = 10; +ERROR: permission denied for session variable var1 +-- should work +SELECT var1; + var1 +------ + +(1 row) + +SET ROLE TO DEFAULT; +GRANT WRITE ON VARIABLE var1 TO var_test_role; +SET ROLE TO var_test_role; +-- should work +LET var1 = 333; +SET ROLE TO DEFAULT; +REVOKE ALL ON VARIABLE var1 FROM var_test_role; +CREATE OR REPLACE FUNCTION secure_var() +RETURNS int AS $$ + SELECT svartest.var1::int; +$$ LANGUAGE sql SECURITY DEFINER; +SELECT secure_var(); + secure_var +------------ + 333 +(1 row) + +SET ROLE TO var_test_role; +-- should fail +SELECT svartest.var1; +ERROR: permission denied for session variable var1 +-- should work; +SELECT secure_var(); + secure_var +------------ + 333 +(1 row) + +SET ROLE TO DEFAULT; +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var1; + QUERY PLAN +----------------------------------------------- + Function Scan on pg_catalog.generate_series g + Output: v + Function Call: generate_series(1, 100) + Filter: ((g.v)::numeric = var1) +(4 rows) + +CREATE VIEW schema_var_view AS SELECT var1; +SELECT * FROM schema_var_view; + var1 +------ + 333 +(1 row) + +\c - +SET search_path = svartest; +-- should work still, but var will be empty +SELECT * FROM schema_var_view; + var1 +------ + +(1 row) + +LET var1 = pi(); +SELECT var1; + var1 +------------------ + 3.14159265358979 +(1 row) + +-- we can see execution plan of LET statement +EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi(); + QUERY PLAN +---------------------------- + SET SESSION VARIABLE + Result + Output: 3.14159265358979 +(3 rows) + +SELECT var1; + var1 +------------------ + 3.14159265358979 +(1 row) + +CREATE VARIABLE var3 AS int; +CREATE OR REPLACE FUNCTION inc(int) +RETURNS int AS $$ +BEGIN + LET svartest.var3 = COALESCE(svartest.var3 + $1, $1); + RETURN var3; +END; +$$ LANGUAGE plpgsql; +SELECT inc(1); + inc +----- + 1 +(1 row) + +SELECT inc(1); + inc +----- + 2 +(1 row) + +SELECT inc(1); + inc +----- + 3 +(1 row) + +SELECT inc(1) FROM generate_series(1,10); + inc +----- + 4 + 5 + 6 + 7 + 8 + 9 + 10 + 11 + 12 + 13 +(10 rows) + +SET ROLE TO var_test_role; +-- should fail +LET var3 = 0; +ERROR: permission denied for session variable var3 +SET ROLE TO DEFAULT; +DROP VIEW schema_var_view; +DROP VARIABLE var1 CASCADE; +DROP VARIABLE var3 CASCADE; +-- 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; +\d v1 +\d v2 +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.14); +SELECT v1; + v1 +------------ + (1,2,3.14) +(1 row) + +SELECT v2; + v2 +--------------- + (10,20,31.40) +(1 row) + +SELECT (v1).*; + x | y | z +---+---+------ + 1 | 2 | 3.14 +(1 row) + +SELECT (v2).*; + x | y | z +----+----+------- + 10 | 20 | 31.40 +(1 row) + +SELECT v1.x + v1.z; + ?column? +---------- + 4.14 +(1 row) + +SELECT v2.x + v2.z; + ?column? +---------- + 41.40 +(1 row) + +-- access to composite fields should be safe too +-- should fail +SET ROLE TO var_test_role; +SELECT v2.x; +ERROR: permission denied for session variable v2 +SET ROLE TO DEFAULT; +DROP VARIABLE v1; +DROP VARIABLE v2; +REVOKE USAGE ON SCHEMA svartest FROM var_test_role; +DROP ROLE var_test_role; +-- scalar variables should not be in conflict with qualified column +CREATE VARIABLE varx AS text; +SELECT varx.relname FROM pg_class varx WHERE varx.relname = 'pg_class'; + relname +---------- + pg_class +(1 row) + +-- should fail +SELECT varx.xxx; +ERROR: type text is not composite +-- variables can be updated under RO transaction +BEGIN; +SET TRANSACTION READ ONLY; +LET varx = 'hello'; +COMMIT; +SELECT varx; + varx +------- + hello +(1 row) + +DROP VARIABLE varx; +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 +LET v1.x = 10; +ERROR: cannot assign to field "x" of column "x" because there is no such column in data type t1 +LINE 1: LET v1.x = 10; + ^ +DROP VARIABLE v1; +DROP TYPE t1; +-- arrays are supported +CREATE VARIABLE va1 AS numeric[]; +LET va1 = ARRAY[1.1,2.1]; +LET va1[1] = 10.1; +SELECT va1; + va1 +------------ + {10.1,2.1} +(1 row) + +CREATE TYPE ta2 AS (a numeric, b numeric[]); +CREATE VARIABLE va2 AS ta2; +LET va2 = (10.1, ARRAY[0.0, 0.0]); +LET va2.a = 10.2; +SELECT va2; + va2 +-------------------- + (10.2,"{0.0,0.0}") +(1 row) + +LET va2.b[1] = 10.3; +SELECT va2; + va2 +--------------------- + (10.2,"{10.3,0.0}") +(1 row) + +DROP VARIABLE va1; +DROP VARIABLE va2; +DROP TYPE ta2; +-- default values +CREATE VARIABLE v1 AS numeric DEFAULT pi(); +LET v1 = v1 * 2; +SELECT v1; + v1 +------------------ + 6.28318530717958 +(1 row) + +CREATE TYPE t2 AS (a numeric, b text); +CREATE VARIABLE v2 AS t2 DEFAULT (NULL, 'Hello'); +LET svartest.v2.a = pi(); +SELECT v2; + v2 +-------------------------- + (3.14159265358979,Hello) +(1 row) + +-- should fail due dependency +DROP TYPE t2; +ERROR: cannot drop type t2 because other objects depend on it +DETAIL: session variable v2 depends on type t2 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- should be ok +DROP VARIABLE v1; +DROP VARIABLE v2; +-- tests of alters +CREATE SCHEMA var_schema1; +CREATE SCHEMA var_schema2; +CREATE VARIABLE var_schema1.var1 AS integer; +LET var_schema1.var1 = 1000; +SELECT var_schema1.var1; + var1 +------ + 1000 +(1 row) + +ALTER VARIABLE var_schema1.var1 SET SCHEMA var_schema2; +SELECT var_schema2.var1; + var1 +------ + 1000 +(1 row) + +CREATE ROLE var_test_role; +ALTER VARIABLE var_schema2.var1 OWNER TO var_test_role; +SET ROLE TO var_test_role; +-- should fail, no access to schema var_schema2.var +SELECT var_schema2.var1; +ERROR: permission denied for schema var_schema2 +DROP VARIABLE var_schema2.var1; +ERROR: permission denied for schema var_schema2 +SET ROLE TO DEFAULT; +ALTER VARIABLE var_schema2.var1 SET SCHEMA public; +SET ROLE TO var_test_role; +SELECT public.var1; + var1 +------ + 1000 +(1 row) + +ALTER VARIABLE public.var1 RENAME TO var1_renamed; +SELECT public.var1_renamed; + var1_renamed +-------------- + 1000 +(1 row) + +DROP VARIABLE public.var1_renamed; +SET ROLE TO DEFAULt; +-- default rights test +ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON VARIABLES TO var_test_role; +CREATE VARIABLE public.var2 AS int; +SET ROLE TO var_test_role; +-- should be ok +LET public.var2 = 100; +SELECT public.var2; + var2 +------ + 100 +(1 row) + +SET ROLE TO DEFAULt; +DROP VARIABLE public.var2; +DROP OWNED BY var_test_role; +DROP ROLE var_test_role; +CREATE VARIABLE xx AS text DEFAULT 'hello'; +SELECT xx, upper(xx); + xx | upper +-------+------- + hello | HELLO +(1 row) + +LET xx = 'Hi'; +SELECT xx; + xx +---- + Hi +(1 row) + +DROP VARIABLE xx; +-- ON TRANSACTION END RESET tests +CREATE VARIABLE t1 AS int DEFAULT -1 ON TRANSACTION END RESET; +BEGIN; + SELECT t1; + t1 +---- + -1 +(1 row) + + LET t1 = 100; + SELECT t1; + t1 +----- + 100 +(1 row) + +COMMIT; +SELECT t1; + t1 +---- + -1 +(1 row) + +BEGIN; + SELECT t1; + t1 +---- + -1 +(1 row) + + LET t1 = 100; + SELECT t1; + t1 +----- + 100 +(1 row) + +ROLLBACK; +SELECT t1; + t1 +---- + -1 +(1 row) + +DROP VARIABLE t1; +CREATE VARIABLE v1 AS int DEFAULT 0; +CREATE VARIABLE v2 AS text DEFAULT 'none'; +LET v1 = 100; +LET v2 = 'Hello'; +SELECT v1, v2; + v1 | v2 +-----+------- + 100 | Hello +(1 row) + +LET v1 = DEFAULT; +LET v2 = DEFAULT; +SELECT v1, v2; + v1 | v2 +----+------ + 0 | none +(1 row) + +DROP VARIABLE v1; +DROP VARIABLE v2; +-- ON COMMIT DROP tests +-- should be 0 always +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +BEGIN; + CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +COMMIT; +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +BEGIN; + CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +ROLLBACK; +SELECT count(*) FROM pg_variable; + count +------- + 0 +(1 row) + +-- test on query with workers +CREATE TABLE svar_test(a int); +INSERT INTO svar_test SELECT * FROM generate_series(1,1000000); +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 +-------------------------------------------------- + Finalize Aggregate + -> Gather + Workers Planned: 2 + -> Partial Aggregate + -> Parallel Seq Scan on svar_test + Filter: ((a % 10) = zero) +(6 rows) + +-- result should be 100000 +SELECT count(*) FROM svar_test WHERE a%10 = zero; + count +-------- + 100000 +(1 row) + +LET zero = (SELECT count(*) FROM svar_test); +-- result should be 1000000 +SELECT zero; + zero +--------- + 1000000 +(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 TABLE svar_test; +DROP VARIABLE zero; +-- use variables in prepared statements +CREATE VARIABLE v AS numeric; +LET v = 3.14; +-- use variables in views +CREATE VIEW vv AS SELECT COALESCE(v, 0) + 1000 AS result; +SELECT * FROM vv; + result +--------- + 1003.14 +(1 row) + +-- start a new session +\c +SET search_path to svartest; +SELECT * FROM vv; + result +-------- + 1000 +(1 row) + +LET v = 3.14; +SELECT * FROM vv; + result +--------- + 1003.14 +(1 row) + +-- should fail, dependency +DROP VARIABLE v; +ERROR: cannot drop session variable v because other objects depend on it +DETAIL: view vv depends on session variable v +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- should be ok +DROP VARIABLE v CASCADE; +NOTICE: drop cascades to view vv +-- other features +CREATE VARIABLE dt AS integer DEFAULT 0; +LET dt = 100; +SELECT dt; + dt +----- + 100 +(1 row) + +DISCARD VARIABLES; +SELECT dt; + dt +---- + 0 +(1 row) + +DROP VARIABLE dt; +-- NOT NULL +CREATE VARIABLE v1 AS int NOT NULL; +CREATE VARIABLE v2 AS int NOT NULL DEFAULT NULL; +-- should fail +SELECT v1; +ERROR: null value is not allowed for NOT NULL session variable "v1" +DETAIL: The session variable was not initialized yet. +SELECT v2; +ERROR: null value is not allowed for NOT NULL session variable "v2" +LET v1 = NULL; +ERROR: null value is not allowed for NOT NULL session variable "v1" +LET v2 = NULL; +ERROR: null value is not allowed for NOT NULL session variable "v2" +LET v1 = DEFAULT; +ERROR: null value is not allowed for NOT NULL session variable "v1" +LET v2 = DEFAULT; +ERROR: null value is not allowed for NOT NULL session variable "v2" +-- should be ok +LET v1 = 100; +LET v2 = 1000; +SELECT v1, v2; + v1 | v2 +-----+------ + 100 | 1000 +(1 row) + +DROP VARIABLE v1; +DROP VARIABLE v2; +CREATE VARIABLE tv AS int; +CREATE VARIABLE IF NOT EXISTS tv AS int; +NOTICE: session variable "tv" already exists, skipping +DROP VARIABLE tv; +CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100; +SELECT iv; + iv +----- + 100 +(1 row) + +-- should fail; +LET iv = 10000; +ERROR: session variable "iv" is declared IMMUTABLE +DROP VARIABLE iv; +-- different order +CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100; +-- should to fail +LET iv = 10000; +ERROR: session variable "iv" is declared IMMUTABLE +-- should be ok +SELECT iv; + iv +----- + 100 +(1 row) + +DROP VARIABLE iv; +CREATE IMMUTABLE VARIABLE iv AS int; +-- should be ok +LET iv = NULL; +LET iv = NULL; +-- should be ok +LET iv = 10; +-- should fail +LET iv = 10; +ERROR: session variable "iv" is declared IMMUTABLE +DROP VARIABLE iv; +-- create variable inside plpgsql block +DO $$ +BEGIN + CREATE VARIABLE do_test_svar AS date DEFAULT '2000-01-01'; +END; +$$; +SELECT do_test_svar; + do_test_svar +-------------- + 01-01-2000 +(1 row) + +DROP VARIABLE do_test_svar; +-- should fail +CREATE IMMUTABLE VARIABLE xx AS int NOT NULL; +ERROR: IMMUTABLE NOT NULL variable requires default expression +-- REASSIGN OWNED test +CREATE ROLE var_test_role1; +CREATE ROLE var_test_role2; +CREATE VARIABLE xxx_var AS int; +ALTER VARIABLE xxx_var OWNER TO var_test_role1; +REASSIGN OWNED BY var_test_role1 to var_test_role2; +SELECT varowner::regrole FROM pg_variable WHERE varname = 'xxx_var'; + varowner +---------------- + var_test_role2 +(1 row) + +DROP OWNED BY var_test_role1; +DROP ROLE var_test_role1; +SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var'; + count +------- + 1 +(1 row) + +DROP OWNED BY var_test_role2; +DROP ROLE var_test_role2; +SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var'; + count +------- + 0 +(1 row) + +-- creating, dropping temporal variable +BEGIN; +CREATE TEMP VARIABLE tempvar AS INT ON COMMIT DROP; +LET tempvar = 100; +SAVEPOINT s1; +DROP VARIABLE tempvar; +ROLLBACK TO s1; +SELECT tempvar; + tempvar +--------- + 100 +(1 row) + +COMMIT; +-- should to fail +LET tempvar = 100; +ERROR: session variable "tempvar" doesn't exist +LINE 1: LET tempvar = 100; + ^ +BEGIN; +SAVEPOINT s1; +CREATE TEMP VARIABLE tempvar AS INT ON COMMIT DROP; +LET tempvar = 100; +ROLLBACK TO s1; +COMMIT; +-- should to fail +LET tempvar = 100; +ERROR: session variable "tempvar" doesn't exist +LINE 1: LET tempvar = 100; + ^ +CREATE VARIABLE var1 AS int; +LET var1 = 100; +BEGIN; +DROP VARIABLE var1; +ROLLBACK; +SELECT var1; + var1 +------ + 100 +(1 row) + +DROP VARIABLE var1; +CREATE VARIABLE var1 AS int DEFAULT 100; +COMMENT ON VARIABLE var1 IS 'some variable comment'; +SELECT pg_catalog.obj_description(oid, 'pg_variable') FROM pg_variable WHERE varname = 'var1'; + obj_description +----------------------- + some variable comment +(1 row) + +DROP VARIABLE var1; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 5b0c73d7e3..d9ad9c801b 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -119,7 +119,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath # NB: temp.sql does a reconnect which transiently uses 2 connections, # so keep this parallel group to at most 19 tests # ---------- -test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml session_variables # ---------- # Another group of parallel tests diff --git a/src/test/regress/sql/session_variables.sql b/src/test/regress/sql/session_variables.sql new file mode 100644 index 0000000000..658beecd77 --- /dev/null +++ b/src/test/regress/sql/session_variables.sql @@ -0,0 +1,530 @@ +CREATE SCHEMA svartest; + +SET search_path = svartest; + +CREATE VARIABLE var1 AS integer; +CREATE TEMP VARIABLE var2 AS text; + +DROP VARIABLE var1, var2; + +-- functional interface +CREATE VARIABLE var1 AS numeric; + +CREATE ROLE var_test_role; +GRANT USAGE ON SCHEMA svartest TO var_test_role; + +SET ROLE TO var_test_role; + +-- should fail +SELECT var1; + +SET ROLE TO DEFAULT; + +GRANT READ ON VARIABLE var1 TO var_test_role; + +SET ROLE TO var_test_role; +-- should fail +LET var1 = 10; +-- should work +SELECT var1; + +SET ROLE TO DEFAULT; + +GRANT WRITE ON VARIABLE var1 TO var_test_role; + +SET ROLE TO var_test_role; + +-- should work +LET var1 = 333; + +SET ROLE TO DEFAULT; + +REVOKE ALL ON VARIABLE var1 FROM var_test_role; + +CREATE OR REPLACE FUNCTION secure_var() +RETURNS int AS $$ + SELECT svartest.var1::int; +$$ LANGUAGE sql SECURITY DEFINER; + +SELECT secure_var(); + +SET ROLE TO var_test_role; + +-- should fail +SELECT svartest.var1; + +-- should work; +SELECT secure_var(); + +SET ROLE TO DEFAULT; + +EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM generate_series(1,100) g(v) WHERE v = var1; + +CREATE VIEW schema_var_view AS SELECT var1; + +SELECT * FROM schema_var_view; + +\c - + +SET search_path = svartest; + +-- should work still, but var will be empty +SELECT * FROM schema_var_view; + +LET var1 = pi(); + +SELECT var1; + +-- we can see execution plan of LET statement +EXPLAIN (VERBOSE, COSTS OFF) LET var1 = pi(); + +SELECT var1; + +CREATE VARIABLE var3 AS int; + +CREATE OR REPLACE FUNCTION inc(int) +RETURNS int AS $$ +BEGIN + LET svartest.var3 = COALESCE(svartest.var3 + $1, $1); + RETURN var3; +END; +$$ LANGUAGE plpgsql; + +SELECT inc(1); +SELECT inc(1); +SELECT inc(1); + +SELECT inc(1) FROM generate_series(1,10); + +SET ROLE TO var_test_role; + +-- should fail +LET var3 = 0; + +SET ROLE TO DEFAULT; + +DROP VIEW schema_var_view; + +DROP VARIABLE var1 CASCADE; +DROP VARIABLE var3 CASCADE; + +-- 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; + +\d v1 +\d v2 + +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.14); + +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 +SET ROLE TO var_test_role; + +SELECT v2.x; + +SET ROLE TO DEFAULT; + +DROP VARIABLE v1; +DROP VARIABLE v2; + +REVOKE USAGE ON SCHEMA svartest FROM var_test_role; +DROP ROLE var_test_role; + +-- scalar variables should not be in conflict with qualified column +CREATE VARIABLE varx AS text; +SELECT varx.relname FROM pg_class varx WHERE varx.relname = 'pg_class'; + +-- should fail +SELECT varx.xxx; + +-- variables can be updated under RO transaction + +BEGIN; +SET TRANSACTION READ ONLY; +LET varx = 'hello'; +COMMIT; + +SELECT varx; + +DROP VARIABLE varx; + +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 +LET v1.x = 10; + +DROP VARIABLE v1; +DROP TYPE t1; + +-- arrays are supported +CREATE VARIABLE va1 AS numeric[]; +LET va1 = ARRAY[1.1,2.1]; +LET va1[1] = 10.1; +SELECT va1; + +CREATE TYPE ta2 AS (a numeric, b numeric[]); +CREATE VARIABLE va2 AS ta2; +LET va2 = (10.1, ARRAY[0.0, 0.0]); +LET va2.a = 10.2; +SELECT va2; +LET va2.b[1] = 10.3; +SELECT va2; + +DROP VARIABLE va1; +DROP VARIABLE va2; +DROP TYPE ta2; + +-- default values +CREATE VARIABLE v1 AS numeric DEFAULT pi(); +LET v1 = v1 * 2; +SELECT v1; + +CREATE TYPE t2 AS (a numeric, b text); +CREATE VARIABLE v2 AS t2 DEFAULT (NULL, 'Hello'); +LET svartest.v2.a = pi(); +SELECT v2; + +-- should fail due dependency +DROP TYPE t2; + +-- should be ok +DROP VARIABLE v1; +DROP VARIABLE v2; + +-- tests of alters +CREATE SCHEMA var_schema1; +CREATE SCHEMA var_schema2; + +CREATE VARIABLE var_schema1.var1 AS integer; +LET var_schema1.var1 = 1000; +SELECT var_schema1.var1; +ALTER VARIABLE var_schema1.var1 SET SCHEMA var_schema2; +SELECT var_schema2.var1; + +CREATE ROLE var_test_role; + +ALTER VARIABLE var_schema2.var1 OWNER TO var_test_role; +SET ROLE TO var_test_role; + +-- should fail, no access to schema var_schema2.var +SELECT var_schema2.var1; +DROP VARIABLE var_schema2.var1; + +SET ROLE TO DEFAULT; + +ALTER VARIABLE var_schema2.var1 SET SCHEMA public; + +SET ROLE TO var_test_role; +SELECT public.var1; + +ALTER VARIABLE public.var1 RENAME TO var1_renamed; + +SELECT public.var1_renamed; + +DROP VARIABLE public.var1_renamed; + +SET ROLE TO DEFAULt; + +-- default rights test +ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON VARIABLES TO var_test_role; + +CREATE VARIABLE public.var2 AS int; + +SET ROLE TO var_test_role; + +-- should be ok +LET public.var2 = 100; +SELECT public.var2; + +SET ROLE TO DEFAULt; + +DROP VARIABLE public.var2; +DROP OWNED BY var_test_role; + +DROP ROLE var_test_role; + +CREATE VARIABLE xx AS text DEFAULT 'hello'; + +SELECT xx, upper(xx); + +LET xx = 'Hi'; + +SELECT xx; + +DROP VARIABLE xx; + +-- ON TRANSACTION END RESET tests +CREATE VARIABLE t1 AS int DEFAULT -1 ON TRANSACTION END RESET; + +BEGIN; + SELECT t1; + LET t1 = 100; + SELECT t1; +COMMIT; + +SELECT t1; + +BEGIN; + SELECT t1; + LET t1 = 100; + SELECT t1; +ROLLBACK; + +SELECT t1; + +DROP VARIABLE t1; + +CREATE VARIABLE v1 AS int DEFAULT 0; +CREATE VARIABLE v2 AS text DEFAULT 'none'; + +LET v1 = 100; +LET v2 = 'Hello'; +SELECT v1, v2; +LET v1 = DEFAULT; +LET v2 = DEFAULT; +SELECT v1, v2; + +DROP VARIABLE v1; +DROP VARIABLE v2; + +-- ON COMMIT DROP tests +-- should be 0 always +SELECT count(*) FROM pg_variable; + +CREATE TEMP VARIABLE g AS int ON COMMIT DROP; + +SELECT count(*) FROM pg_variable; + +BEGIN; + CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +COMMIT; + +SELECT count(*) FROM pg_variable; + +BEGIN; + CREATE TEMP VARIABLE g AS int ON COMMIT DROP; +ROLLBACK; + +SELECT count(*) FROM pg_variable; + +-- test on query with workers +CREATE TABLE svar_test(a int); +INSERT INTO svar_test SELECT * FROM generate_series(1,1000000); +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 100000 +SELECT count(*) FROM svar_test WHERE a%10 = zero; + +LET zero = (SELECT count(*) FROM svar_test); + +-- result should be 1000000 +SELECT zero; + +-- parallel workers should be used +EXPLAIN (costs off) LET zero = (SELECT count(*) FROM svar_test); + +DROP TABLE svar_test; +DROP VARIABLE zero; + +-- use variables in prepared statements +CREATE VARIABLE v AS numeric; +LET v = 3.14; + +-- use variables in views +CREATE VIEW vv AS SELECT COALESCE(v, 0) + 1000 AS result; +SELECT * FROM vv; + +-- start a new session +\c + +SET search_path to svartest; + +SELECT * FROM vv; +LET v = 3.14; +SELECT * FROM vv; + +-- should fail, dependency +DROP VARIABLE v; + +-- should be ok +DROP VARIABLE v CASCADE; + +-- other features +CREATE VARIABLE dt AS integer DEFAULT 0; + +LET dt = 100; +SELECT dt; + +DISCARD VARIABLES; + +SELECT dt; + +DROP VARIABLE dt; + +-- NOT NULL +CREATE VARIABLE v1 AS int NOT NULL; +CREATE VARIABLE v2 AS int NOT NULL DEFAULT NULL; + +-- should fail +SELECT v1; +SELECT v2; +LET v1 = NULL; +LET v2 = NULL; +LET v1 = DEFAULT; +LET v2 = DEFAULT; + +-- should be ok +LET v1 = 100; +LET v2 = 1000; +SELECT v1, v2; + +DROP VARIABLE v1; +DROP VARIABLE v2; + +CREATE VARIABLE tv AS int; +CREATE VARIABLE IF NOT EXISTS tv AS int; +DROP VARIABLE tv; + +CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100; +SELECT iv; + +-- should fail; +LET iv = 10000; + +DROP VARIABLE iv; + +-- different order +CREATE IMMUTABLE VARIABLE iv AS int DEFAULT 100; +-- should to fail +LET iv = 10000; +-- should be ok +SELECT iv; + +DROP VARIABLE iv; + +CREATE IMMUTABLE VARIABLE iv AS int; + +-- should be ok +LET iv = NULL; +LET iv = NULL; + +-- should be ok +LET iv = 10; + +-- should fail +LET iv = 10; + +DROP VARIABLE iv; + +-- create variable inside plpgsql block +DO $$ +BEGIN + CREATE VARIABLE do_test_svar AS date DEFAULT '2000-01-01'; +END; +$$; + +SELECT do_test_svar; + +DROP VARIABLE do_test_svar; + +-- should fail +CREATE IMMUTABLE VARIABLE xx AS int NOT NULL; + + + +-- REASSIGN OWNED test +CREATE ROLE var_test_role1; +CREATE ROLE var_test_role2; + +CREATE VARIABLE xxx_var AS int; + +ALTER VARIABLE xxx_var OWNER TO var_test_role1; +REASSIGN OWNED BY var_test_role1 to var_test_role2; + +SELECT varowner::regrole FROM pg_variable WHERE varname = 'xxx_var'; + +DROP OWNED BY var_test_role1; +DROP ROLE var_test_role1; +SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var'; + +DROP OWNED BY var_test_role2; +DROP ROLE var_test_role2; +SELECT count(*) FROM pg_variable WHERE varname = 'xxx_var'; + +-- creating, dropping temporal variable +BEGIN; + +CREATE TEMP VARIABLE tempvar AS INT ON COMMIT DROP; + +LET tempvar = 100; + +SAVEPOINT s1; + +DROP VARIABLE tempvar; + +ROLLBACK TO s1; + +SELECT tempvar; + +COMMIT; + +-- should to fail +LET tempvar = 100; + +BEGIN; + +SAVEPOINT s1; + +CREATE TEMP VARIABLE tempvar AS INT ON COMMIT DROP; + +LET tempvar = 100; + +ROLLBACK TO s1; + +COMMIT; + +-- should to fail +LET tempvar = 100; + +CREATE VARIABLE var1 AS int; +LET var1 = 100; +BEGIN; +DROP VARIABLE var1; +ROLLBACK; +SELECT var1; + +DROP VARIABLE var1; + +CREATE VARIABLE var1 AS int DEFAULT 100; +COMMENT ON VARIABLE var1 IS 'some variable comment'; + +SELECT pg_catalog.obj_description(oid, 'pg_variable') FROM pg_variable WHERE varname = 'var1'; + +DROP VARIABLE var1;