public inbox for [email protected]
help / color / mirror / Atom feedFrom: Laurenz Albe <[email protected]>
To: Pavel Stehule <[email protected]>
Cc: Erik Rijkers <[email protected]>
Cc: Michael Paquier <[email protected]>
Cc: Amit Kapila <[email protected]>
Cc: DUVAL REMI <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: proposal: schema variables
Date: Mon, 29 Jul 2024 11:56:46 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CAFj8pRBoGBKopkiTa4ki3dMgy-cSTRZ-BQPKOq7=Tk0SSNsowA@mail.gmail.com>
<CAFj8pRBCiWn12H9FHymYs17fk68nRd9Xpn+SYf18TLdb2YSUrQ@mail.gmail.com>
<CAA4eK1JV-Ox0oRFdXnPqXSzM84wTR5QFkRzCpNVF_+0FNjS5Mg@mail.gmail.com>
<CAFj8pRByCCcgDkeXyafAnH6LgxtAyCVwg6yfJAhyTY6GLscfZg@mail.gmail.com>
<CAFj8pRBvUonC_ug3F=w1Q55Dp=DggojvAeL7Vmh14Q-WhFHxzw@mail.gmail.com>
<CAFj8pRDj72P-f=SUygtOXnTOBQ0RzmL_fN=wLfaCzcbPVpGgzw@mail.gmail.com>
<CAFj8pRDD2GQaJ_iDT4vSVe658+oHRXU2S2af7Y1-it9jaP8VFg@mail.gmail.com>
<[email protected]>
<CAFj8pRAFktynx5wkanv5SRuzXkZgXu77XpVACiSE=v7i1xHFbw@mail.gmail.com>
<CAFj8pRA=bn_g5T2AZduy5gNOQoOnUJ+pMHmnRMHi6mR0n=TAsA@mail.gmail.com>
<[email protected]>
<CAFj8pRC9de05HSb4tEHDUwJ98+4Wh30W-rJrNOPnTz6ARcv0Fw@mail.gmail.com>
<CAFj8pRBC5Wz1xHKKBmKsM0xYN0+PdSZ5oXPsk5SZt+VprdUW3A@mail.gmail.com>
<CAFj8pRAh4pzMoZrKCLt9h+Lr2L=vhgs2PjAF45uLbp_7sijM5w@mail.gmail.com>
<CAFj8pRA-kxQ1oErcuDeUKYsrgwB5XGLhquatwxOe3dCVy1gcyQ@mail.gmail.com>
<[email protected]>
<CAFj8pRBbt2xhY9PyabOY0ZN+Aig6ee3oCon-DM9qi0Uw_3qfbw@mail.gmail.com>
<CAFj8pRDSa52J7kPmCYXgq1BBbu3YBXwpdSOVpjgU=hnE2k04Cw@mail.gmail.com>
<CAFj8pRD+QiWOoPrFk2NnPs3t5Eaf4X=aGRV-9ww11cnPP+fV4g@mail.gmail.com>
<[email protected]>
<CAFj8pRCGTjqHvH9oeiSf4T6Bydhk9pm033DxxibgF+B7SHC6MQ@mail.gmail.com>
<CAFj8pRAzNDhFgbZnT0T0mJ7ygA1Qje1Hc0TiKwXM8++kGooPYg@mail.gmail.com>
<[email protected]>
<CAFj8pRBarjJYfkN-0-i=JRZJ4PTOYC+K7XgAhfdDqWGqRiPkyw@mail.gmail.com>
<CAFj8pRDqdWdCULxd5asbKs5C4e9kT2TuKBkR5L-e1=hP5wF2uw@mail.gmail.com>
<[email protected]>
<CAFj8pRCPW56pFr0F0BcasdXjFeo3SFixNSpWKaBk0ibvznum-A@mail.gmail.com>
<CAFj8pRD1Feit93CgwmYm1Q=X+M+AZqffCEZPFQ7qEMNHZRN4fA@mail.gmail.com>
<CAFj8pRCc=B9-FRQg5eWDSkGwS2vpkq88hR6042cmPPizHuEGSA@mail.gmail.com>
<CAFj8pRBk8x7afUXKLBOU-Ctg6A7QJvTAGGVEi0b6Jc8YTe8nUg@mail.gmail.com>
<CAFj8pRCSwHQ4BJUbjF2YEausK1Z6+ejMyedpqAnWJbG+FEJDLw@mail.gmail.com>
<CAFj8pRAbY+N+UqjqgESL5x-bsGmV+aVyyUkxUSgaGDZToZjDqQ@mail.gmail.com>
<CAFj8pRBzKcqzj=23BHfv1QaXHt=2_SN=uhdR3rb_dAVQoit7ug@mail.gmail.com>
<CAFj8pRCi-n6SzkAB+OHG=TZvL13xxta_qgffBLDOY0HEBqDhvg@mail.gmail.com>
<[email protected]>
<CAFj8pRBaD0_bMrCREWnVLfcTMdc0v7ns7Rt=sEvd1EoFmLfarQ@mail.gmail.com>
<[email protected]>
<CAFj8pRDXo-RRcy2VFDm_vzv3Eaaz6Ex=X19up=x8W4COyBNmaQ@mail.gmail.com>
<[email protected]>
<CAFj8pRDgraz64HHcCoFGCKDM1Cxv1ukELsqfsxux4JP3PM6RyQ@mail.gmail.com>
<[email protected]>
<CAFj8pRAsjr0pzQpQeRrQ-wR-ew1oTc6qkW=4355KveN1jKQ+1Q@mail.gmail.com>
<[email protected]>
On Sat, 2024-07-27 at 16:19 +0200, Laurenz Albe wrote:
> I went through the v20240724-2-0001 patch and mostly changed some documentation
> and the comments. Attached is my updated version.
Sorry; I forgot the new files. Here is an updated replacement for your first patch.
I'll start working on the second patch now.
Yours,
Laurenz Albe
Attachments:
[text/x-patch] v20240729-0001-Enhancing-catalog-for-support-session-vari.patch (132.7K, 2-v20240729-0001-Enhancing-catalog-for-support-session-vari.patch)
download | inline diff:
From f2b7654d3b9dc758316fd7cb2de05e78ceeee737 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
Date: Mon, 29 Jul 2024 11:54:25 +0200
Subject: [PATCH v20240729] Enhancing catalog for support session variables and
related support
This patch introduces new system catalog table pg_variable. This table holds metadata about
session variables created by command CREATE VARIABLE, and dropped by command DROP VARIABLE.
Both commands are implemented by this patch. Possibility to change owner, schema or rename.
Access to session variables can be controlled by SELECT or UPDATE rights. Both rights are
introduced by this patch too.
This patch enhancing pg_dump and psql to support session variables. The changes are related
to system catalog.
This patch is not short, but the code is simple.
---
doc/src/sgml/catalogs.sgml | 120 +++++++++
doc/src/sgml/ddl.sgml | 31 +++
doc/src/sgml/event-trigger.sgml | 24 ++
doc/src/sgml/func.sgml | 13 +
doc/src/sgml/glossary.sgml | 15 ++
doc/src/sgml/ref/allfiles.sgml | 3 +
.../sgml/ref/alter_default_privileges.sgml | 26 +-
doc/src/sgml/ref/alter_variable.sgml | 178 ++++++++++++
doc/src/sgml/ref/comment.sgml | 1 +
doc/src/sgml/ref/create_schema.sgml | 7 +-
doc/src/sgml/ref/create_variable.sgml | 151 +++++++++++
doc/src/sgml/ref/drop_variable.sgml | 117 ++++++++
doc/src/sgml/ref/grant.sgml | 6 +
doc/src/sgml/ref/revoke.sgml | 7 +
doc/src/sgml/reference.sgml | 3 +
src/backend/catalog/Makefile | 1 +
src/backend/catalog/aclchk.c | 77 ++++++
src/backend/catalog/dependency.c | 6 +
src/backend/catalog/meson.build | 1 +
src/backend/catalog/namespace.c | 135 ++++++++++
src/backend/catalog/objectaddress.c | 121 ++++++++-
src/backend/catalog/pg_shdepend.c | 2 +
src/backend/catalog/pg_variable.c | 255 ++++++++++++++++++
src/backend/commands/alter.c | 9 +
src/backend/commands/dropcmds.c | 4 +
src/backend/commands/event_trigger.c | 4 +
src/backend/commands/seclabel.c | 1 +
src/backend/commands/tablecmds.c | 41 +++
src/backend/parser/gram.y | 105 +++++++-
src/backend/parser/parse_utilcmd.c | 12 +
src/backend/tcop/utility.c | 16 ++
src/backend/utils/adt/acl.c | 7 +
src/backend/utils/cache/lsyscache.c | 113 ++++++++
src/bin/pg_dump/common.c | 3 +
src/bin/pg_dump/dumputils.c | 6 +
src/bin/pg_dump/pg_backup.h | 2 +
src/bin/pg_dump/pg_backup_archiver.c | 9 +
src/bin/pg_dump/pg_dump.c | 195 +++++++++++++-
src/bin/pg_dump/pg_dump.h | 19 ++
src/bin/pg_dump/pg_dump_sort.c | 6 +
src/bin/pg_dump/t/002_pg_dump.pl | 64 +++++
src/bin/psql/command.c | 3 +
src/bin/psql/describe.c | 96 +++++++
src/bin/psql/describe.h | 3 +
src/bin/psql/help.c | 1 +
src/bin/psql/tab-complete.c | 44 ++-
src/include/catalog/Makefile | 3 +-
src/include/catalog/meson.build | 1 +
src/include/catalog/namespace.h | 4 +
src/include/catalog/pg_default_acl.h | 1 +
src/include/catalog/pg_proc.dat | 3 +
src/include/catalog/pg_variable.h | 81 ++++++
src/include/nodes/parsenodes.h | 16 ++
src/include/parser/kwlist.h | 2 +
src/include/tcop/cmdtaglist.h | 3 +
src/include/utils/acl.h | 1 +
src/include/utils/lsyscache.h | 9 +
src/test/regress/expected/dependency.out | 17 ++
src/test/regress/expected/oidjoins.out | 4 +
src/test/regress/expected/psql.out | 50 ++++
.../regress/expected/session_variables.out | 57 ++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/dependency.sql | 14 +
src/test/regress/sql/psql.sql | 21 ++
src/test/regress/sql/session_variables.sql | 61 +++++
src/tools/pgindent/typedefs.list | 4 +
66 files changed, 2390 insertions(+), 27 deletions(-)
create mode 100644 doc/src/sgml/ref/alter_variable.sgml
create mode 100644 doc/src/sgml/ref/create_variable.sgml
create mode 100644 doc/src/sgml/ref/drop_variable.sgml
create mode 100644 src/backend/catalog/pg_variable.c
create mode 100644 src/include/catalog/pg_variable.h
create mode 100644 src/test/regress/expected/session_variables.out
create mode 100644 src/test/regress/sql/session_variables.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b654fae1b2..de122e543f 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -369,6 +369,11 @@
<entry><link linkend="catalog-pg-user-mapping"><structname>pg_user_mapping</structname></link></entry>
<entry>mappings of users to foreign servers</entry>
</row>
+
+ <row>
+ <entry><link linkend="catalog-pg-variable"><structname>pg_variable</structname></link></entry>
+ <entry>session variables</entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -9726,4 +9731,119 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</table>
</sect1>
+ <sect1 id="catalog-pg-variable">
+ <title><structname>pg_variable</structname></title>
+
+ <indexterm zone="catalog-pg-variable">
+ <primary>pg_variable</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_variable</structname> stores information about
+ session variables.
+ </para>
+
+ <table>
+ <title><structname>pg_variable</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>oid</structfield> <type>oid</type>
+ </para>
+ <para>
+ Row identifier
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vartype</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-type"><structname>pg_type</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the variable's data type
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varname</structfield> <type>name</type>
+ </para>
+ <para>
+ Name of the session variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varnamespace</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The OID of the namespace that contains this variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varowner</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Owner of the variable
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>vartypmod</structfield> <type>int4</type>
+ </para>
+ <para>
+ <structfield>vartypmod</structfield> records type-specific data
+ supplied at variable creation time (for example, the maximum
+ length of a <type>varchar</type> 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 <structfield>vartypmod</structfield>.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varcollation</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-collation"><structname>pg_collation</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ The defined collation of the variable, or zero if the variable is
+ not of a collatable data type.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>varacl</structfield> <type>aclitem[]</type>
+ </para>
+ <para>
+ Access privileges; see
+ <xref linkend="sql-grant"/> and
+ <xref linkend="sql-revoke"/>
+ for details
+ </para></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
</chapter>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index c5e11a6699..432184b4e5 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -5298,6 +5298,37 @@ EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
</para>
</sect1>
+ <sect1 id="ddl-session-variables">
+ <title>Session Variables</title>
+
+ <indexterm zone="ddl-session-variables">
+ <primary>Session variables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ </indexterm>
+
+ <para>
+ Session variables are database objects that can hold a value.
+ Session variables, like relations, exist within a schema and their access
+ is controlled via <command>GRANT</command> and <command>REVOKE</command>
+ commands. A session variable can be created by the <command>CREATE
+ VARIABLE</command> command.
+ </para>
+
+ <para>
+ Inside a query or an expression, a session variable can be
+ <quote>shadowed</quote> by a column with the same name. Similarly, the
+ name of a function or procedure argument or a PL/pgSQL variable (see
+ <xref linkend="plpgsql-declarations"/>) can shadow a session variable
+ in the routine's body. Such collisions of identifiers can be resolved
+ by using qualified identifiers: Session variables can be qualified with
+ the schema name, columns can use table aliases, routine variables can use
+ block labels, and routine arguments can use the routine name.
+ </para>
+ </sect1>
+
<sect1 id="ddl-others">
<title>Other Database Objects</title>
diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml
index 8e009cca05..15b2af99dc 100644
--- a/doc/src/sgml/event-trigger.sgml
+++ b/doc/src/sgml/event-trigger.sgml
@@ -424,6 +424,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>ALTER VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>ALTER VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
@@ -712,6 +720,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>CREATE VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>CREATE VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
@@ -1000,6 +1016,14 @@
<entry align="center"><literal>-</literal></entry>
<entry align="left"></entry>
</row>
+ <row>
+ <entry align="left"><literal>DROP VARIABLE</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>X</literal></entry>
+ <entry align="center"><literal>-</literal></entry>
+ <entry align="left"></entry>
+ </row>
<row>
<entry align="left"><literal>DROP VIEW</literal></entry>
<entry align="center"><literal>X</literal></entry>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 3f93c61aa3..ec3f5cbdb5 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25641,6 +25641,19 @@ SELECT relname FROM pg_class WHERE pg_table_is_visible(oid);
Is type (or domain) visible in search path?
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_variable_is_visible</primary>
+ </indexterm>
+ <function>pg_variable_is_visible</function> ( <parameter>variable</parameter> <type>oid</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is session variable visible in search path?
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/glossary.sgml b/doc/src/sgml/glossary.sgml
index 405fe6dc8b..d5f4bcc6e0 100644
--- a/doc/src/sgml/glossary.sgml
+++ b/doc/src/sgml/glossary.sgml
@@ -1654,6 +1654,21 @@
</glossdef>
</glossentry>
+ <glossentry id="glossary-session-variable">
+ <glossterm>Session variable</glossterm>
+ <glossdef>
+ <para>
+ A persistent database object that holds a value in session memory. This
+ value is private to each session and is released when the session ends.
+ Read or write access to session variables is controlled by privileges,
+ similar to other database objects.
+ </para>
+ <para>
+ For more information, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </glossdef>
+ </glossentry>
+
<glossentry id="glossary-shared-memory">
<glossterm>Shared memory</glossterm>
<glossdef>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index f5be638867..2f67de3e21 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.
<!ENTITY alterType SYSTEM "alter_type.sgml">
<!ENTITY alterUser SYSTEM "alter_user.sgml">
<!ENTITY alterUserMapping SYSTEM "alter_user_mapping.sgml">
+<!ENTITY alterVariable SYSTEM "alter_variable.sgml">
<!ENTITY alterView SYSTEM "alter_view.sgml">
<!ENTITY analyze SYSTEM "analyze.sgml">
<!ENTITY begin SYSTEM "begin.sgml">
@@ -99,6 +100,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY createType SYSTEM "create_type.sgml">
<!ENTITY createUser SYSTEM "create_user.sgml">
<!ENTITY createUserMapping SYSTEM "create_user_mapping.sgml">
+<!ENTITY createVariable SYSTEM "create_variable.sgml">
<!ENTITY createView SYSTEM "create_view.sgml">
<!ENTITY deallocate SYSTEM "deallocate.sgml">
<!ENTITY declare SYSTEM "declare.sgml">
@@ -147,6 +149,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY dropType SYSTEM "drop_type.sgml">
<!ENTITY dropUser SYSTEM "drop_user.sgml">
<!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml">
+<!ENTITY dropVariable SYSTEM "drop_variable.sgml">
<!ENTITY dropView SYSTEM "drop_view.sgml">
<!ENTITY end SYSTEM "end.sgml">
<!ENTITY execute SYSTEM "execute.sgml">
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index 89aacec4fa..041c78d432 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -51,6 +51,10 @@ GRANT { { USAGE | CREATE }
ON SCHEMAS
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
+
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
@@ -83,6 +87,12 @@ REVOKE [ GRANT OPTION FOR ]
ON SCHEMAS
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLES
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
@@ -117,14 +127,14 @@ REVOKE [ GRANT OPTION FOR ]
<para>
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.
- The words <literal>FUNCTIONS</literal> and <literal>ROUTINES</literal> are
- equivalent in this command. (<literal>ROUTINES</literal> is preferred
- going forward as the standard term for functions and procedures taken
- together. In earlier PostgreSQL releases, only the
- word <literal>FUNCTIONS</literal> was allowed. It is not possible to set
- default privileges for functions and procedures separately.)
+ tables), sequences, functions, types (including domains), and session
+ variables can be altered. For this command, functions include aggregates
+ and procedures. The words <literal>FUNCTIONS</literal> and
+ <literal>ROUTINES</literal> are equivalent in this command.
+ (<literal>ROUTINES</literal> is preferred going forward as the standard
+ term for functions and procedures taken together. In earlier PostgreSQL
+ releases, only the word <literal>FUNCTIONS</literal> was allowed. It is not
+ possible to set default privileges for functions and procedures separately.)
</para>
<para>
diff --git a/doc/src/sgml/ref/alter_variable.sgml b/doc/src/sgml/ref/alter_variable.sgml
new file mode 100644
index 0000000000..d87570e7d8
--- /dev/null
+++ b/doc/src/sgml/ref/alter_variable.sgml
@@ -0,0 +1,178 @@
+<!--
+doc/src/sgml/ref/alter_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-altervariable">
+ <indexterm zone="sql-altervariable">
+ <primary>ALTER VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>altering</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>ALTER VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>ALTER VARIABLE</refname>
+ <refpurpose>
+ change the definition of a session variable
+ </refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable class="parameter">new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable class="parameter">new_name</replaceable>
+ALTER VARIABLE <replaceable class="parameter">name</replaceable> SET SCHEMA <replaceable class="parameter">new_schema</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>ALTER VARIABLE</command> command changes the definition of an
+ existing session variable. There are several subforms:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>OWNER</literal></term>
+ <listitem>
+ <para>
+ This form changes the owner of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RENAME</literal></term>
+ <listitem>
+ <para>
+ This form changes the name of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SET SCHEMA</literal></term>
+ <listitem>
+ <para>
+ This form moves the session variable into another schema.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+
+ <para>
+ Only the owner or a superuser is allowed to alter a session variable.
+ In order to move a session variable from one schema to another, the user
+ must also have the <literal>CREATE</literal> privilege on the new schema (or
+ be a superuser).
+
+ In order to move the session 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 <literal>CREATE</literal> privilege
+ on the session 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 session variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <para>
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name (possibly schema-qualified) of the existing session variable
+ to alter.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_name</replaceable></term>
+ <listitem>
+ <para>
+ The new name for the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_owner</replaceable></term>
+ <listitem>
+ <para>
+ The user name of the new owner of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">new_schema</replaceable></term>
+ <listitem>
+ <para>
+ The new schema for the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To rename a session variable:
+<programlisting>
+ALTER VARIABLE foo RENAME TO boo;
+</programlisting>
+ </para>
+
+ <para>
+ To change the owner of the session variable <literal>boo</literal> to
+ <literal>joe</literal>:
+<programlisting>
+ALTER VARIABLE boo OWNER TO joe;
+</programlisting>
+ </para>
+
+ <para>
+ To change the schema of the session variable <literal>boo</literal> to
+ <literal>private</literal>:
+<programlisting>
+ALTER VARIABLE boo SET SCHEMA private;
+</programlisting>
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ Session variables and this command in particular are a PostgreSQL extension.
+ </para>
+ </refsect1>
+
+ <refsect1 id="sql-altervariable-see-also">
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-createvariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+</refentry>
diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml
index 5b43c56b13..21cd80818f 100644
--- a/doc/src/sgml/ref/comment.sgml
+++ b/doc/src/sgml/ref/comment.sgml
@@ -65,6 +65,7 @@ COMMENT ON
TRANSFORM FOR <replaceable>type_name</replaceable> LANGUAGE <replaceable>lang_name</replaceable> |
TRIGGER <replaceable class="parameter">trigger_name</replaceable> ON <replaceable class="parameter">table_name</replaceable> |
TYPE <replaceable class="parameter">object_name</replaceable> |
+ VARIABLE <replaceable class="parameter">object_name</replaceable> |
VIEW <replaceable class="parameter">object_name</replaceable>
} IS { <replaceable class="parameter">string_literal</replaceable> | NULL }
diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml
index ed69298ccc..a834c876bc 100644
--- a/doc/src/sgml/ref/create_schema.sgml
+++ b/doc/src/sgml/ref/create_schema.sgml
@@ -103,9 +103,10 @@ CREATE SCHEMA IF NOT EXISTS AUTHORIZATION <replaceable class="parameter">role_sp
schema. Currently, only <command>CREATE
TABLE</command>, <command>CREATE VIEW</command>, <command>CREATE
INDEX</command>, <command>CREATE SEQUENCE</command>, <command>CREATE
- TRIGGER</command> and <command>GRANT</command> are accepted as clauses
- within <command>CREATE SCHEMA</command>. Other kinds of objects may
- be created in separate commands after the schema is created.
+ TRIGGER</command>, <command>GRANT</command> and <command>CREATE
+ VARIABLE</command> are accepted as clauses within <command>CREATE
+ SCHEMA</command>. Other kinds of objects may be created in separate
+ commands after the schema is created.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml
new file mode 100644
index 0000000000..ec165ab96f
--- /dev/null
+++ b/doc/src/sgml/ref/create_variable.sgml
@@ -0,0 +1,151 @@
+<!--
+doc/src/sgml/ref/create_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-createvariable">
+ <indexterm zone="sql-createvariable">
+ <primary>CREATE VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>defining</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>CREATE VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>CREATE VARIABLE</refname>
+ <refpurpose>define a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+CREATE VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ]
+</synopsis>
+ </refsynopsisdiv>
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command creates a session variable.
+ Session variables, like relations, exist within a schema and their access is
+ controlled via the commands <command>GRANT</command> and <command>REVOKE</command>.
+ </para>
+
+ <para>
+ The value of a session variable is local to the current session. Retrieving
+ a session variable's value returns NULL, unless its value is set to
+ something else in the current session with a <command>LET</command> command.
+ The content of a session variable is not transactional. This is the same as
+ regular variables in procedural languages.
+ </para>
+
+ <para>
+ Session variables are retrieved by the <command>SELECT</command>
+ command. Their value is set with the <command>LET</command> command.
+ </para>
+
+ <note>
+ <para>
+ Session variables can be <quote>shadowed</quote> by other identifiers.
+ For details, see <xref linkend="ddl-session-variables"/>.
+ </para>
+ </note>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+
+ <varlistentry id="sql-createvariable-if-not-exists">
+ <term><literal>IF NOT EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the name already exists. A notice is issued in
+ this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createvariable-name">
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createvariable-data_type">
+ <term><replaceable class="parameter">data_type</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of the data type of the session
+ variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="sql-createvariable-collate">
+ <term><literal>COLLATE <replaceable>collation</replaceable></literal></term>
+ <listitem>
+ <para>
+ The <literal>COLLATE</literal> clause assigns a collation to the session
+ variable (which must be of a collatable data type). If not specified,
+ the data type's default collation is used.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+
+ <para>
+ Use the <command>DROP VARIABLE</command> command to remove a session
+ variable.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ Create an date session variable <literal>var1</literal>:
+<programlisting>
+CREATE VARIABLE var1 AS date;
+LET var1 = current_date;
+SELECT var1;
+</programlisting>
+ </para>
+
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>CREATE VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-altervariable"/></member>
+ <member><xref linkend="sql-dropvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml
new file mode 100644
index 0000000000..5bdb3560f0
--- /dev/null
+++ b/doc/src/sgml/ref/drop_variable.sgml
@@ -0,0 +1,117 @@
+<!--
+doc/src/sgml/ref/drop_variable.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-dropvariable">
+ <indexterm zone="sql-dropvariable">
+ <primary>DROP VARIABLE</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>session variable</primary>
+ <secondary>removing</secondary>
+ </indexterm>
+
+ <refmeta>
+ <refentrytitle>DROP VARIABLE</refentrytitle>
+ <manvolnum>7</manvolnum>
+ <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+ <refname>DROP VARIABLE</refname>
+ <refpurpose>remove a session variable</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+DROP VARIABLE [ IF EXISTS ] <replaceable class="parameter">name</replaceable> [, ...] [ CASCADE | RESTRICT ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DROP VARIABLE</command> removes a session variable.
+ A session variable can only be removed by its owner or a superuser.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>IF EXISTS</literal></term>
+ <listitem>
+ <para>
+ Do not throw an error if the session variable does not exist. A notice is
+ issued in this case.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="parameter">name</replaceable></term>
+ <listitem>
+ <para>
+ The name, optionally schema-qualified, of a session variable.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>CASCADE</literal></term>
+ <listitem>
+ <para>
+ Automatically drop objects that depend on the session variable (such as
+ views), and in turn all objects that depend on those objects
+ (see <xref linkend="ddl-depend"/>).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESTRICT</literal></term>
+ <listitem>
+ <para>
+ Refuse to drop the session variable if any objects depend on it. This is
+ the default.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+ <para>
+ To remove the session variable <literal>var1</literal>:
+
+<programlisting>
+DROP VARIABLE var1;
+</programlisting></para>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ The <command>DROP VARIABLE</command> command is a
+ <productname>PostgreSQL</productname> extension.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="sql-altervariable"/></member>
+ <member><xref linkend="sql-createvariable"/></member>
+ </simplelist>
+ </refsect1>
+
+</refentry>
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index 999f657d5c..78ff10fcf5 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -101,6 +101,12 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+GRANT { SELECT | UPDATE | ALL [ PRIVILEGES ] }
+ ON VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ]
+ [ GRANTED BY <replaceable class="parameter">role_specification</replaceable> ]
+
<phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
[ GROUP ] <replaceable class="parameter">role_name</replaceable>
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8df492281a..626c0231d0 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -137,6 +137,13 @@ REVOKE [ { ADMIN | INHERIT | SET } OPTION FOR ]
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
+
+REVOKE [ GRANT OPTION FOR ]
+ { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
+ ON VARIABLE <replaceable>variable_name</replaceable> [, ...]
+ | ALL VARIABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
+ FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
+ [ CASCADE | RESTRICT ]
</synopsis>
</refsynopsisdiv>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index ff85ace83f..25578f3946 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;
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 1589a75fd5..7a90fcfc45 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -45,6 +45,7 @@ OBJS = \
pg_shdepend.o \
pg_subscription.o \
pg_type.o \
+ pg_variable.o \
storage.o \
toasting.o
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index a44ccee3b6..6968b1e6eb 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -64,6 +64,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_variable.h"
#include "commands/dbcommands.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
@@ -290,6 +291,9 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs,
case OBJECT_PARAMETER_ACL:
whole_mask = ACL_ALL_RIGHTS_PARAMETER_ACL;
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 */
@@ -534,6 +538,10 @@ ExecuteGrantStmt(GrantStmt *stmt)
all_privileges = ACL_ALL_RIGHTS_PARAMETER_ACL;
errormsg = gettext_noop("invalid privilege type %s for parameter");
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);
@@ -639,6 +647,9 @@ ExecGrantStmt_oids(InternalGrant *istmt)
case OBJECT_PARAMETER_ACL:
ExecGrant_Parameter(istmt);
break;
+ case OBJECT_VARIABLE:
+ ExecGrant_common(istmt, VariableRelationId, ACL_ALL_RIGHTS_VARIABLE, NULL);
+ break;
default:
elog(ERROR, "unrecognized GrantStmt.objtype: %d",
(int) istmt->objtype);
@@ -829,6 +840,18 @@ objectNamesToOids(ObjectType objtype, List *objnames, bool is_grant)
objects = lappend_oid(objects, parameterId);
}
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);
@@ -918,6 +941,32 @@ 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",
@@ -1077,6 +1126,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);
@@ -1268,6 +1321,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 object type: %d",
(int) iacls->objtype);
@@ -1511,6 +1570,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",
@@ -1571,6 +1633,9 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid)
case ParameterAclRelationId:
istmt.objtype = OBJECT_PARAMETER_ACL;
break;
+ case VariableRelationId:
+ istmt.objtype = OBJECT_VARIABLE;
+ break;
default:
elog(ERROR, "unexpected object class %u", classid);
break;
@@ -2810,6 +2875,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;
@@ -2921,6 +2989,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;
@@ -3069,6 +3140,8 @@ pg_aclmask(ObjectType objtype, Oid object_oid, AttrNumber attnum, Oid roleid,
return ACL_NO_RIGHTS;
case OBJECT_TYPE:
return object_aclmask(TypeRelationId, object_oid, roleid, mask, how);
+ case OBJECT_VARIABLE:
+ return object_aclmask(VariableRelationId, object_oid, roleid, mask, how);
default:
elog(ERROR, "unrecognized object type: %d",
(int) objtype);
@@ -4336,6 +4409,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 0489cbabcb..c9d686665d 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -65,12 +65,14 @@
#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/seclabel.h"
#include "commands/sequence.h"
#include "commands/trigger.h"
@@ -1444,6 +1446,10 @@ doDeletion(const ObjectAddress *object, int flags)
RemovePublicationById(object->objectId);
break;
+ case VariableRelationId:
+ DropVariableById(object->objectId);
+ break;
+
case CastRelationId:
case CollationRelationId:
case ConversionRelationId:
diff --git a/src/backend/catalog/meson.build b/src/backend/catalog/meson.build
index 2f3ded8a0e..bfdb4da330 100644
--- a/src/backend/catalog/meson.build
+++ b/src/backend/catalog/meson.build
@@ -32,6 +32,7 @@ backend_sources += files(
'pg_shdepend.c',
'pg_subscription.c',
'pg_type.c',
+ 'pg_variable.c',
'storage.c',
'toasting.c',
)
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 43b707699d..8752e1646e 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -41,6 +41,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 "common/hashfn_unstable.h"
#include "funcapi.h"
@@ -971,6 +972,69 @@ RelationIsVisibleExt(Oid relid, bool *is_missing)
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 variable 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
@@ -3274,6 +3338,66 @@ TSConfigIsVisibleExt(Oid cfgid, bool *is_missing)
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);
+
+ /* if nspname is a known namespace, the variable must be there */
+ if (OidIsValid(namespaceId))
+ {
+ varoid = GetSysCacheOid2(VARIABLENAMENSP, Anum_pg_variable_oid,
+ PointerGetDatum(varname),
+ ObjectIdGetDatum(namespaceId));
+ }
+ }
+ else
+ {
+ /* iterate over the schemas on the 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;
+}
/*
* DeconstructQualifiedName
@@ -5075,3 +5199,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 85a7b7e641..ee70eeeed4 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"
@@ -636,6 +637,20 @@ static const ObjectPropertyType ObjectProperty[] =
OBJECT_USER_MAPPING,
false
},
+ {
+ "session variable",
+ VariableRelationId,
+ VariableOidIndexId,
+ 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
+ }
};
/*
@@ -831,6 +846,9 @@ static const struct object_type_map
},
{
"statistics object", OBJECT_STATISTIC_EXT
+ },
+ {
+ "session variable", OBJECT_VARIABLE
}
};
@@ -856,6 +874,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,
@@ -1126,6 +1145,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;
/* no default, to let compiler warn about missing case */
}
@@ -2000,16 +2022,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)));
}
/*
@@ -2093,6 +2119,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
*/
@@ -2287,6 +2331,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:
@@ -2458,6 +2503,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
case OBJECT_STATISTIC_EXT:
case OBJECT_TSDICTIONARY:
case OBJECT_TSCONFIGURATION:
+ case OBJECT_VARIABLE:
if (!object_ownercheck(address.classId, address.objectId, roleid))
aclcheck_error(ACLCHECK_NOT_OWNER, objtype,
NameListToString(castNode(List, object)));
@@ -3446,6 +3492,32 @@ getObjectDescription(const ObjectAddress *object, bool missing_ok)
break;
}
+ case VariableRelationId:
+ {
+ 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 TSParserRelationId:
{
HeapTuple tup;
@@ -3798,6 +3870,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)
@@ -4614,6 +4696,10 @@ getObjectTypeDescription(const ObjectAddress *object, bool missing_ok)
appendStringInfoString(&buffer, "transform");
break;
+ case VariableRelationId:
+ appendStringInfoString(&buffer, "session variable");
+ break;
+
default:
elog(ERROR, "unsupported object class: %u", object->classId);
}
@@ -5720,6 +5806,10 @@ getObjectIdentityParts(const ObjectAddress *object,
appendStringInfoString(&buffer,
" on schemas");
break;
+ case DEFACLOBJ_VARIABLE:
+ appendStringInfoString(&buffer,
+ " on session variables");
+ break;
}
if (objname)
@@ -5960,6 +6050,33 @@ getObjectIdentityParts(const ObjectAddress *object,
}
break;
+ case VariableRelationId:
+ {
+ 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;
+ }
+
default:
elog(ERROR, "unsupported object class: %u", object->classId);
}
diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c
index 753afb8845..1ea3d6db05 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/dbcommands.h"
#include "commands/defrem.h"
@@ -1714,6 +1715,7 @@ shdepReassignOwned_Owner(Form_pg_shdepend sdepForm, Oid newrole)
case DatabaseRelationId:
case TSConfigRelationId:
case TSDictionaryRelationId:
+ case VariableRelationId:
AlterObjectOwner_internal(sdepForm->classid,
sdepForm->objid,
newrole);
diff --git a/src/backend/catalog/pg_variable.c b/src/backend/catalog/pg_variable.c
new file mode 100644
index 0000000000..f65121440a
--- /dev/null
+++ b/src/backend/catalog/pg_variable.c
@@ -0,0 +1,255 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_variable.c
+ * session variables
+ *
+ * Portions Copyright (c) 1996-2024, 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 "catalog/dependency.h"
+#include "catalog/indexing.h"
+#include "catalog/namespace.h"
+#include "catalog/objectaccess.h"
+#include "catalog/pg_collation.h"
+#include "catalog/pg_namespace.h"
+#include "catalog/pg_variable.h"
+#include "miscadmin.h"
+#include "parser/parse_type.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+#include "utils/syscache.h"
+
+static ObjectAddress create_variable(const char *varName,
+ Oid varNamespace,
+ Oid varType,
+ int32 varTypmod,
+ Oid varOwner,
+ Oid varCollation,
+ bool if_not_exists);
+
+
+/*
+ * Creates entry in pg_variable table
+ */
+static ObjectAddress
+create_variable(const char *varName,
+ Oid varNamespace,
+ Oid varType,
+ int32 varTypmod,
+ Oid varOwner,
+ Oid varCollation,
+ bool if_not_exists)
+{
+ Acl *varacl;
+ NameData varname;
+ bool nulls[Natts_pg_variable];
+ Datum values[Natts_pg_variable];
+ Relation rel;
+ HeapTuple tup;
+ TupleDesc tupdesc;
+ ObjectAddress myself,
+ referenced;
+ ObjectAddresses *addrs;
+ Oid varid;
+
+ Assert(varName);
+ Assert(OidIsValid(varNamespace));
+ Assert(OidIsValid(varType));
+ Assert(OidIsValid(varOwner));
+
+ rel = table_open(VariableRelationId, RowExclusiveLock);
+
+ /*
+ * Check for duplicates. Note that this does not really prevent
+ * duplicates, it's here just to provide nicer error message in common
+ * case. The real protection is the unique key on the catalog.
+ */
+ if (SearchSysCacheExists2(VARIABLENAMENSP,
+ PointerGetDatum(varName),
+ ObjectIdGetDatum(varNamespace)))
+ {
+ 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)));
+
+ table_close(rel, RowExclusiveLock);
+
+ return InvalidObjectAddress;
+ }
+
+ memset(values, 0, sizeof(values));
+ memset(nulls, false, sizeof(nulls));
+
+ namestrcpy(&varname, varName);
+
+ varid = GetNewOidWithIndex(rel, VariableOidIndexId, 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);
+
+ 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;
+
+ tupdesc = RelationGetDescr(rel);
+
+ tup = heap_form_tuple(tupdesc, values, nulls);
+ CatalogTupleInsert(rel, tup);
+ Assert(OidIsValid(varid));
+
+ addrs = new_object_addresses();
+
+ ObjectAddressSet(myself, VariableRelationId, varid);
+
+ /* dependency on namespace */
+ ObjectAddressSet(referenced, NamespaceRelationId, varNamespace);
+ add_exact_object_address(&referenced, addrs);
+
+ /* dependency on used type */
+ ObjectAddressSet(referenced, TypeRelationId, varType);
+ add_exact_object_address(&referenced, addrs);
+
+ /* dependency on collation */
+ if (OidIsValid(varCollation) &&
+ varCollation != DEFAULT_COLLATION_OID)
+ {
+ ObjectAddressSet(referenced, CollationRelationId, varCollation);
+ add_exact_object_address(&referenced, addrs);
+ }
+
+ record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL);
+ free_object_addresses(addrs);
+
+ /* dependency on owner */
+ recordDependencyOnOwner(VariableRelationId, varid, varOwner);
+
+ /* dependencies on roles mentioned in default ACL */
+ recordDependencyOnNewAcl(VariableRelationId, varid, 0, varOwner, varacl);
+
+ /* dependency on extension */
+ recordDependencyOnCurrentExtension(&myself, false);
+
+ heap_freetuple(tup);
+
+ /* Post creation hook for new function */
+ InvokeObjectPostCreateHook(VariableRelationId, varid, 0);
+
+ table_close(rel, RowExclusiveLock);
+
+ return myself;
+}
+
+/*
+ * Creates a new variable
+ *
+ * Used by CREATE VARIABLE command
+ */
+ObjectAddress
+CreateVariable(ParseState *pstate, CreateSessionVarStmt *stmt)
+{
+ Oid namespaceid;
+ AclResult aclresult;
+ Oid typid;
+ int32 typmod;
+ Oid varowner = GetUserId();
+ Oid collation;
+ Oid typcollation;
+ ObjectAddress variable;
+
+ namespaceid =
+ RangeVarGetAndCheckCreationNamespace(stmt->variable, NoLock, NULL);
+
+ typenameTypeIdAndMod(pstate, stmt->typeName, &typid, &typmod);
+
+ /* Disallow pseudotypes */
+ if (get_typtype(typid) == TYPTYPE_PSEUDO)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("session variable cannot be pseudo-type %s",
+ format_type_be(typid))));
+
+ aclresult = object_aclcheck(TypeRelationId, typid, GetUserId(), ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error_type(aclresult, typid);
+
+ typcollation = get_typcollation(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)));
+
+ variable = create_variable(stmt->variable->relname,
+ namespaceid,
+ typid,
+ typmod,
+ varowner,
+ collation,
+ stmt->if_not_exists);
+
+ elog(DEBUG1, "record for session variable \"%s\" (oid:%d) was created in pg_variable",
+ stmt->variable->relname, variable.objectId);
+
+ /* We want SessionVariableCreatePostprocess to see the catalog changes. */
+ CommandCounterIncrement();
+
+ return variable;
+}
+
+/*
+ * Drop variable by OID, and register the needed session variable
+ * cleanup.
+ */
+void
+DropVariableById(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);
+}
diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c
index 4f99ebb447..63fa99fad3 100644
--- a/src/backend/commands/alter.c
+++ b/src/backend/commands/alter.c
@@ -41,6 +41,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/dbcommands.h"
@@ -139,6 +140,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;
@@ -418,6 +423,7 @@ ExecRenameStmt(RenameStmt *stmt)
case OBJECT_TSTEMPLATE:
case OBJECT_PUBLICATION:
case OBJECT_SUBSCRIPTION:
+ case OBJECT_VARIABLE:
{
ObjectAddress address;
Relation catalog;
@@ -561,6 +567,7 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt,
case OBJECT_TSDICTIONARY:
case OBJECT_TSPARSER:
case OBJECT_TSTEMPLATE:
+ case OBJECT_VARIABLE:
{
Relation catalog;
Relation relation;
@@ -645,6 +652,7 @@ AlterObjectNamespace_oid(Oid classId, Oid objid, Oid nspOid,
case TSDictionaryRelationId:
case TSTemplateRelationId:
case TSConfigRelationId:
+ case VariableRelationId:
{
Relation catalog;
@@ -875,6 +883,7 @@ ExecAlterOwnerStmt(AlterOwnerStmt *stmt)
case OBJECT_TABLESPACE:
case OBJECT_TSDICTIONARY:
case OBJECT_TSCONFIGURATION:
+ case OBJECT_VARIABLE:
{
Relation relation;
ObjectAddress address;
diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c
index 85eec7e394..3cce17e92a 100644
--- a/src/backend/commands/dropcmds.c
+++ b/src/backend/commands/dropcmds.c
@@ -476,6 +476,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;
case OBJECT_COLUMN:
case OBJECT_DATABASE:
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 7a5ed6b985..6d41aa7ec3 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -2156,6 +2156,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:
@@ -2239,6 +2241,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/seclabel.c b/src/backend/commands/seclabel.c
index 5607273bf9..c026ab5dcb 100644
--- a/src/backend/commands/seclabel.c
+++ b/src/backend/commands/seclabel.c
@@ -92,6 +92,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/tablecmds.c b/src/backend/commands/tablecmds.c
index 721d24783b..590cfa2406 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -51,6 +51,7 @@
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_variable.h"
#include "catalog/storage.h"
#include "catalog/storage_xlog.h"
#include "catalog/toasting.h"
@@ -6723,6 +6724,7 @@ ATTypedTableRecursion(List **wqueue, Relation rel, AlterTableCmd *cmd,
* (possibly nested several levels deep in composite types, arrays, etc!).
* Eventually, we'd like to propagate the check or rewrite operation
* into such tables, but for now, just error out if we find any.
+ * Also, check if "typeOid" is used as type of some session variable.
*
* Caller should provide either the associated relation of a rowtype,
* or a type name (not both) for use in the error message, if any.
@@ -6786,6 +6788,45 @@ find_composite_type_dependencies(Oid typeOid, Relation origRelation,
continue;
}
+ /* check if the type is used as type of some session variable */
+ if (pg_depend->classid == VariableRelationId)
+ {
+ Oid varid = pg_depend->objid;
+
+ if (origTypeName)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type \"%s\" because session variable \"%s.%s\" uses it",
+ origTypeName,
+ get_namespace_name(get_session_variable_namespace(varid)),
+ get_session_variable_name(varid))));
+ else if (origRelation->rd_rel->relkind == RELKIND_COMPOSITE_TYPE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type \"%s\" because session variable \"%s.%s\" uses it",
+ RelationGetRelationName(origRelation),
+ get_namespace_name(get_session_variable_namespace(varid)),
+ get_session_variable_name(varid))));
+ else if (origRelation->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter foreign table \"%s\" because session variable \"%s.%s\" uses it",
+ RelationGetRelationName(origRelation),
+ get_namespace_name(get_session_variable_namespace(varid)),
+ get_session_variable_name(varid))));
+ else if (origRelation->rd_rel->relkind == RELKIND_RELATION ||
+ origRelation->rd_rel->relkind == RELKIND_MATVIEW ||
+ origRelation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter table \"%s\" because session variable \"%s.%s\" uses it",
+ RelationGetRelationName(origRelation),
+ get_namespace_name(get_session_variable_namespace(varid)),
+ get_session_variable_name(varid))));
+
+ continue;
+ }
+
/* Else, ignore dependees that aren't relations */
if (pg_depend->classid != RelationRelationId)
continue;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a043fd4c66..3f2c590ce3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -53,6 +53,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
#include "catalog/pg_trigger.h"
+#include "catalog/pg_variable.h"
#include "commands/defrem.h"
#include "commands/trigger.h"
#include "gramparse.h"
@@ -295,8 +296,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 CreateSeqStmt CreateSessionVarStmt CreateStmt CreateStatsStmt
+ CreateTableSpaceStmt CreateFdwStmt CreateForeignServerStmt CreateForeignTableStmt
CreateAssertionStmt CreateTransformStmt CreateTrigStmt CreateEventTrigStmt
CreateUserStmt CreateUserMappingStmt CreateRoleStmt CreatePolicyStmt
CreatedbStmt DeclareCursorStmt DefineStmt DeleteStmt DiscardStmt DoStmt
@@ -790,8 +791,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
UESCAPE UNBOUNDED UNCONDITIONAL 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
@@ -1061,6 +1062,7 @@ stmt:
| CreatePolicyStmt
| CreatePLangStmt
| CreateSchemaStmt
+ | CreateSessionVarStmt
| CreateSeqStmt
| CreateStmt
| CreateSubscriptionStmt
@@ -1605,6 +1607,7 @@ schema_stmt:
| CreateTrigStmt
| GrantStmt
| ViewStmt
+ | CreateSessionVarStmt
;
@@ -5202,6 +5205,34 @@ create_extension_opt_item:
}
;
+/*****************************************************************************
+ *
+ * QUERY :
+ * CREATE VARIABLE varname [AS] type
+ *
+ *****************************************************************************/
+
+CreateSessionVarStmt:
+ CREATE VARIABLE qualified_name opt_as Typename opt_collate_clause
+ {
+ CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
+ n->variable = $3;
+ n->typeName = $5;
+ n->collClause = (CollateClause *) $6;
+ n->if_not_exists = false;
+ $$ = (Node *) n;
+ }
+ | CREATE VARIABLE IF_P NOT EXISTS qualified_name opt_as Typename opt_collate_clause
+ {
+ CreateSessionVarStmt *n = makeNode(CreateSessionVarStmt);
+ n->variable = $6;
+ n->typeName = $8;
+ n->collClause = (CollateClause *) $9;
+ n->if_not_exists = true;
+ $$ = (Node *) n;
+ }
+ ;
+
/*****************************************************************************
*
* ALTER EXTENSION name UPDATE [ TO version ]
@@ -7006,6 +7037,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; }
;
/*
@@ -7882,6 +7914,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));
@@ -7927,6 +7967,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;
+ }
;
@@ -8124,6 +8172,7 @@ defacl_privilege_target:
| SEQUENCES { $$ = OBJECT_SEQUENCE; }
| TYPES_P { $$ = OBJECT_TYPE; }
| SCHEMAS { $$ = OBJECT_SCHEMA; }
+ | VARIABLES { $$ = OBJECT_VARIABLE; }
;
@@ -9906,6 +9955,24 @@ 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
@@ -10267,6 +10334,24 @@ 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;
+ }
;
/*****************************************************************************
@@ -10548,6 +10633,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;
+ }
;
@@ -17878,6 +17971,8 @@ unreserved_keyword:
| VALIDATE
| VALIDATOR
| VALUE_P
+ | VARIABLE
+ | VARIABLES
| VARYING
| VERSION_P
| VIEW
@@ -18533,6 +18628,8 @@ bare_label_keyword:
| VALUE_P
| VALUES
| VARCHAR
+ | VARIABLE
+ | VARIABLES
| VARIADIC
| VERBOSE
| VERSION_P
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index d5c2b2ff0b..2fba90c95a 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -107,6 +107,7 @@ typedef struct
List *indexes; /* CREATE INDEX items */
List *triggers; /* CREATE TRIGGER items */
List *grants; /* GRANT items */
+ List *variables; /* CREATE VARIABLE items */
} CreateSchemaStmtContext;
@@ -3966,6 +3967,7 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
cxt.indexes = NIL;
cxt.triggers = NIL;
cxt.grants = NIL;
+ cxt.variables = NIL;
/*
* Run through each schema element in the schema element list. Separate
@@ -4034,6 +4036,15 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
cxt.grants = lappend(cxt.grants, element);
break;
+ case T_CreateSessionVarStmt:
+ {
+ CreateSessionVarStmt *elp = (CreateSessionVarStmt *) element;
+
+ setSchemaName(cxt.schemaname, &elp->variable->schemaname);
+ cxt.variables = lappend(cxt.variables, element);
+ }
+ break;
+
default:
elog(ERROR, "unrecognized node type: %d",
(int) nodeTag(element));
@@ -4047,6 +4058,7 @@ transformCreateSchemaStmtElements(List *schemaElts, const char *schemaName)
result = list_concat(result, cxt.indexes);
result = list_concat(result, cxt.triggers);
result = list_concat(result, cxt.grants);
+ result = list_concat(result, cxt.variables);
return result;
}
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index fa66b8017e..ce8db7cda4 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -23,6 +23,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_inherits.h"
+#include "catalog/pg_variable.h"
#include "catalog/toasting.h"
#include "commands/alter.h"
#include "commands/async.h"
@@ -182,6 +183,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:
@@ -1386,6 +1388,10 @@ ProcessUtilitySlow(ParseState *pstate,
}
break;
+ case T_CreateSessionVarStmt:
+ address = CreateVariable(pstate, (CreateSessionVarStmt *) parsetree);
+ break;
+
/*
* ************* object creation / destruction **************
*/
@@ -2338,6 +2344,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;
@@ -2646,6 +2655,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;
}
@@ -3222,6 +3234,10 @@ CreateCommandTag(Node *parsetree)
}
break;
+ case T_CreateSessionVarStmt:
+ tag = CMDTAG_CREATE_VARIABLE;
+ break;
+
default:
elog(WARNING, "unrecognized node type: %d",
(int) nodeTag(parsetree));
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index d7b39140b3..5f80af4f5e 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -850,6 +850,10 @@ acldefault(ObjectType objtype, Oid ownerId)
world_default = ACL_NO_RIGHTS;
owner_default = ACL_ALL_RIGHTS_PARAMETER_ACL;
break;
+ case OBJECT_VARIABLE:
+ world_default = ACL_NO_RIGHTS;
+ owner_default = ACL_ALL_RIGHTS_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type: %d", (int) objtype);
world_default = ACL_NO_RIGHTS; /* keep compiler quiet */
@@ -947,6 +951,9 @@ acldefault_sql(PG_FUNCTION_ARGS)
case 'T':
objtype = OBJECT_TYPE;
break;
+ case 'V':
+ objtype = OBJECT_VARIABLE;
+ break;
default:
elog(ERROR, "unrecognized object type abbreviation: %c", objtypec);
}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 48a280d089..2eed9489f9 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -38,6 +38,7 @@
#include "catalog/pg_subscription.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"
@@ -3714,3 +3715,115 @@ get_subscription_name(Oid subid, bool missing_ok)
return subname;
}
+
+/* ---------- PG_VARIABLE CACHE ---------- */
+
+/*
+ * 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));
+}
+
+/*
+ * get_session_variable_name
+ * Returns a palloc'd copy of the name of a given session 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 = pstrdup(NameStr(varform->varname));
+
+ ReleaseSysCache(tup);
+
+ return varname;
+}
+
+/*
+ * get_session_variable_namespace
+ * Returns the pg_namespace OID associated with a given session variable.
+ */
+Oid
+get_session_variable_namespace(Oid varid)
+{
+ HeapTuple tup;
+ Form_pg_variable varform;
+ Oid varnamespace;
+
+ tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid));
+
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for variable %u", varid);
+
+ varform = (Form_pg_variable) GETSTRUCT(tup);
+
+ varnamespace = varform->varnamespace;
+
+ ReleaseSysCache(tup);
+
+ return varnamespace;
+}
+
+/*
+ * Returns the type of the given session variable.
+ */
+Oid
+get_session_variable_type(Oid varid)
+{
+ HeapTuple tup;
+ Form_pg_variable varform;
+ Oid vartype;
+
+ tup = SearchSysCache1(VARIABLEOID, ObjectIdGetDatum(varid));
+
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for session variable %u", varid);
+
+ varform = (Form_pg_variable) GETSTRUCT(tup);
+
+ vartype = varform->vartype;
+
+ ReleaseSysCache(tup);
+
+ return vartype;
+}
+
+/*
+ * Returns the type, typmod and collid of the given 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);
+}
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index c323b5bd3d..4079ad603a 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -247,6 +247,9 @@ getSchemaData(Archive *fout, int *numTablesPtr)
pg_log_info("reading subscription membership of tables");
getSubscriptionTables(fout);
+ pg_log_info("reading variables");
+ getVariables(fout);
+
free(inhinfo); /* not needed any longer */
*numTablesPtr = numTables;
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 5649859aa1..50de5d0ea2 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -511,6 +511,12 @@ do { \
CONVERT_PRIV('r', "SELECT");
CONVERT_PRIV('w', "UPDATE");
}
+ else if (strcmp(type, "VARIABLE") == 0 ||
+ strcmp(type, "VARIABLES") == 0)
+ {
+ CONVERT_PRIV('r', "SELECT");
+ CONVERT_PRIV('w', "UPDATE");
+ }
else
abort();
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index fbf5f1c515..e81cfde274 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -133,12 +133,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 68e321212d..76846cc763 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3098,6 +3098,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;
}
@@ -3647,6 +3655,7 @@ _getObjectDescription(PQExpBuffer buf, const TocEntry *te)
strcmp(type, "TEXT SEARCH DICTIONARY") == 0 ||
strcmp(type, "TEXT SEARCH CONFIGURATION") == 0 ||
strcmp(type, "TYPE") == 0 ||
+ strcmp(type, "VARIABLE") == 0 ||
strcmp(type, "VIEW") == 0 ||
/* non-schema-specified objects */
strcmp(type, "DATABASE") == 0 ||
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b8b1888bd3..70e6e0c909 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -324,6 +324,7 @@ 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 dumpSubscriptionTable(Archive *fout, const SubRelInfo *subrinfo);
+static void dumpVariable(Archive *fout, const VariableInfo *varinfo);
static void dumpDatabase(Archive *fout);
static void dumpDatabaseConfig(Archive *AH, PQExpBuffer outbuf,
const char *dbname, Oid dboid);
@@ -5313,6 +5314,190 @@ 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_varowner;
+ int i_varcollation;
+ int i_varacl;
+ int i_acldefault;
+ int i,
+ ntups;
+
+ if (fout->remoteVersion < 180000)
+ return;
+
+ query = createPQExpBuffer();
+
+ resetPQExpBuffer(query);
+
+ /* Get the variables in current database. */
+ appendPQExpBuffer(query,
+ "SELECT v.tableoid, v.oid, v.varname,\n"
+ " v.varnamespace, v.vartype,\n"
+ " pg_catalog.format_type(v.vartype, v.vartypmod) as vartypname,\n"
+ " CASE WHEN v.varcollation <> t.typcollation "
+ " THEN v.varcollation\n"
+ " ELSE 0\n"
+ " END AS varcollation,\n"
+ " v.varowner, v.varacl,\n"
+ " acldefault('V', v.varowner) AS acldefault\n"
+ "FROM pg_catalog.pg_variable v\n"
+ "JOIN pg_catalog.pg_type t "
+ "ON (v.vartype = t.oid)");
+
+ 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_varcollation = PQfnumber(res, "varcollation");
+
+ i_varowner = PQfnumber(res, "varowner");
+ 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].varcollation = atooid(PQgetvalue(res, i, i_varcollation));
+
+ 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 = getRoleName(PQgetvalue(res, i, i_varowner));
+
+ /* Do not try to dump ACL if no ACL exists. */
+ if (!PQgetisnull(res, i, i_varacl))
+ varinfo[i].dobj.components |= DUMP_COMPONENT_ACL;
+
+ 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 session variable
+ */
+static void
+dumpVariable(Archive *fout, const VariableInfo *varinfo)
+{
+ DumpOptions *dopt = fout->dopt;
+
+ PQExpBuffer delq;
+ PQExpBuffer query;
+ char *qualvarname;
+ const char *vartypname;
+ Oid varcollation;
+
+ /* 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;
+ varcollation = varinfo->varcollation;
+
+ appendPQExpBuffer(delq, "DROP VARIABLE %s;\n",
+ qualvarname);
+
+ appendPQExpBuffer(query, "CREATE VARIABLE %s AS %s",
+ qualvarname, vartypname);
+
+ if (OidIsValid(varcollation))
+ {
+ CollInfo *coll;
+
+ coll = findCollationByOid(varcollation);
+ if (coll)
+ appendPQExpBuffer(query, " COLLATE %s",
+ fmtQualifiedDumpable(coll));
+ }
+
+ 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, NULL, 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,
@@ -9875,7 +10060,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;
@@ -10474,6 +10660,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
case DO_SUBSCRIPTION_REL:
dumpSubscriptionTable(fout, (const SubRelInfo *) 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 */
@@ -14923,6 +15112,9 @@ dumpDefaultACL(Archive *fout, const DefaultACLInfo *daclinfo)
case DEFACLOBJ_NAMESPACE:
type = "SCHEMAS";
break;
+ case DEFACLOBJ_VARIABLE:
+ type = "VARIABLES";
+ break;
default:
/* shouldn't get here */
pg_fatal("unrecognized object type in default privileges: %d",
@@ -18370,6 +18562,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 4b2e5870a9..36a69a0832 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,
@@ -692,6 +693,23 @@ typedef struct _SubRelInfo
char *srsublsn;
} SubRelInfo;
+/*
+ * The VariableInfo struct is used to represent session variables
+ */
+typedef struct _VariableInfo
+{
+ DumpableObject dobj;
+ DumpableAcl dacl;
+ Oid vartype;
+ char *vartypname;
+ char *varacl;
+ char *rvaracl;
+ char *initvaracl;
+ char *initrvaracl;
+ Oid varcollation;
+ const char *rolname; /* name of owner, or empty string */
+} VariableInfo;
+
/*
* common utility functions
*/
@@ -775,5 +793,6 @@ extern void getPublicationTables(Archive *fout, TableInfo tblinfo[],
int numTables);
extern void getSubscriptions(Archive *fout);
extern void getSubscriptionTables(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 4cb754caa5..ebb9bf037c 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -78,6 +78,7 @@ enum dbObjectTypePriorities
PRIO_DUMMY_TYPE,
PRIO_ATTRDEF,
PRIO_LARGE_OBJECT,
+ PRIO_VARIABLE,
PRIO_PRE_DATA_BOUNDARY, /* boundary! */
PRIO_TABLE_DATA,
PRIO_SEQUENCE_SET,
@@ -119,6 +120,7 @@ static const int dbObjectTypePriority[] =
[DO_TABLE] = PRIO_TABLE,
[DO_TABLE_ATTACH] = PRIO_TABLE_ATTACH,
[DO_ATTRDEF] = PRIO_ATTRDEF,
+ [DO_VARIABLE] = PRIO_VARIABLE,
[DO_INDEX] = PRIO_INDEX,
[DO_INDEX_ATTACH] = PRIO_INDEX_ATTACH,
[DO_STATSEXT] = PRIO_STATSEXT,
@@ -1490,6 +1492,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/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index d3dd8784d6..d62db24228 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -789,6 +789,16 @@ my %tests = (
unlike => { no_privs => 1, },
},
+ 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT SELECT ON VARIABLES TO PUBLIC'
+ => {
+ create_order => 56,
+ create_sql => 'ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT SELECT ON VARIABLES TO PUBLIC;',
+ regexp => qr/^
+ \QALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role GRANT SELECT ON VARIABLES 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
@@ -1638,6 +1648,23 @@ my %tests = (
},
},
+ '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,
+ only_dump_measurement => 1,
+ },
+ },
+
'COPY test_table' => {
create_order => 4,
create_sql => 'INSERT INTO dump_test.test_table (col1) '
@@ -3896,6 +3923,24 @@ 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;',
+ regexp => qr/^
+ \QCREATE VARIABLE dump_test.variable1 AS integer;\E/xm,
+ like => {
+ %full_runs,
+ %dump_test_schema_runs,
+ section_pre_data => 1,
+ },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_measurement => 1,
+ },
+ },
+
'CREATE VIEW test_view' => {
create_order => 61,
create_sql => 'CREATE VIEW dump_test.test_view
@@ -4358,6 +4403,25 @@ my %tests = (
like => {},
},
+ 'GRANT SELECT ON VARIABLE dump_test.variable1' => {
+ create_order => 73,
+ create_sql =>
+ 'GRANT SELECT ON VARIABLE dump_test.variable1 TO regress_dump_test_role;',
+ regexp => qr/^
+ \QGRANT SELECT 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,
+ only_dump_measurement => 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 180781ecd0..26a9a3fac7 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -986,6 +986,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 7c9a1f234c..adf83ed197 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -5148,6 +5148,102 @@ error_return:
return false;
}
+/*
+ * \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};
+
+ if (pset.sversion < 180000)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support session variables.",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ 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"
+ " (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type bt\n"
+ " WHERE c.oid = v.varcollation AND bt.oid = v.vartype AND v.varcollation <> bt.typcollation) as \"%s\",\n"
+ " pg_catalog.pg_get_userbyid(v.varowner) as \"%s\"\n",
+ gettext_noop("Schema"),
+ gettext_noop("Name"),
+ gettext_noop("Type"),
+ gettext_noop("Collation"),
+ gettext_noop("Owner"));
+
+ 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");
+
+ if (!validateSQLNamePattern(&buf, pattern, true, false,
+ "n.nspname", "v.varname", NULL,
+ "pg_catalog.pg_variable_is_visible(v.oid)",
+ NULL, 3))
+ return false;
+
+ 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 273f974538..4e15a180ea 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -149,4 +149,7 @@ extern bool listOpFamilyFunctions(const char *access_method_pattern,
/* \dl or \lo_list */
extern bool listLargeObjects(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 6f58a11074..bdc327ffe3 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -265,6 +265,7 @@ slashUsage(unsigned short int pager)
HELP0(" \\dT[S+] [PATTERN] list data types\n");
HELP0(" \\du[S+] [PATTERN] list roles\n");
HELP0(" \\dv[S+] [PATTERN] list views\n");
+ HELP0(" \\dV [PATTERN] list session variables\n");
HELP0(" \\dx[+] [PATTERN] list extensions\n");
HELP0(" \\dX [PATTERN] list extended statistics\n");
HELP0(" \\dy[+] [PATTERN] list event triggers\n");
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d453e224d9..a1aecf20b1 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -963,6 +963,13 @@ static const SchemaQuery Query_for_trigger_of_table = {
.refnamespace = "c1.relnamespace",
};
+static const SchemaQuery Query_for_list_of_variables = {
+ .min_server_version = 180000,
+ .catname = "pg_catalog.pg_variable v",
+ .viscondition = "pg_catalog.pg_variable_is_visible(v.oid)",
+ .namespace = "v.varnamespace",
+ .result = "v.varname",
+};
/*
* Queries to get lists of names of various kinds of things, possibly
@@ -1283,6 +1290,7 @@ static const pgsql_thing_t words_after_create[] = {
* TABLE ... */
{"USER", Query_for_list_of_roles, NULL, NULL, Keywords_for_user_thing},
{"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 */
};
@@ -1722,7 +1730,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", "\\drg", "\\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",
@@ -2224,6 +2232,9 @@ psql_completion(const char *text, int start, int end)
"ALL");
else if (Matches("ALTER", "SYSTEM", "SET", MatchAny))
COMPLETE_WITH("TO");
+ /* ALTER VARIABLE <name> */
+ else if (Matches("ALTER", "VARIABLE", MatchAny))
+ COMPLETE_WITH("OWNER TO", "RENAME TO", "SET SCHEMA");
/* ALTER VIEW <name> */
else if (Matches("ALTER", "VIEW", MatchAny))
COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME", "RESET", "SET");
@@ -2804,7 +2815,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"))
@@ -3266,7 +3277,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", "VARIABLE", "VIEW");
/* Complete "CREATE UNLOGGED" with TABLE, SEQUENCE or MATVIEW */
else if (TailMatches("CREATE", "UNLOGGED"))
{
@@ -3586,6 +3597,13 @@ 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 <name> with AS */
+ else if (TailMatches("CREATE", "VARIABLE", MatchAny))
+ COMPLETE_WITH("AS");
+ else if (TailMatches("VARIABLE", MatchAny, "AS"))
+ /* Complete CREATE VARIABLE <name> with AS types */
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_datatypes);
/* CREATE VIEW --- is allowed inside CREATE SCHEMA, so use TailMatches */
/* Complete CREATE [ OR REPLACE ] VIEW <name> with AS or WITH */
@@ -3849,6 +3867,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);
+ else if (Matches("DROP", "VARIABLE", MatchAny))
+ COMPLETE_WITH("CASCADE", "RESTRICT");
+
/* EXECUTE */
else if (Matches("EXECUTE"))
COMPLETE_WITH_QUERY(Query_for_list_of_prepared_statements);
@@ -4052,7 +4076,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_PLUS(Query_for_list_of_grantables,
"ALL FUNCTIONS IN SCHEMA",
@@ -4074,7 +4098,8 @@ psql_completion(const char *text, int start, int end)
"SEQUENCE",
"TABLE",
"TABLESPACE",
- "TYPE");
+ "TYPE",
+ "VARIABLE");
}
else if (TailMatches("GRANT|REVOKE", MatchAny, "ON", "ALL") ||
TailMatches("REVOKE", "GRANT", "OPTION", "FOR", MatchAny, "ON", "ALL"))
@@ -4082,7 +4107,8 @@ psql_completion(const char *text, int start, int end)
"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") ||
TailMatches("REVOKE", "GRANT", "OPTION", "FOR", MatchAny, "ON", "FOREIGN"))
COMPLETE_WITH("DATA WRAPPER", "SERVER");
@@ -4118,6 +4144,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);
+ else if (TailMatches("VARIABLE"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables);
else if (TailMatches("GRANT", MatchAny, MatchAny, MatchAny))
COMPLETE_WITH("TO");
else
@@ -4421,7 +4449,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
@@ -4889,6 +4917,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);
+ else if (TailMatchesCS("\\dV*"))
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_variables);
else if (TailMatchesCS("\\dx*"))
COMPLETE_WITH_QUERY(Query_for_list_of_extensions);
else if (TailMatchesCS("\\dX*"))
diff --git a/src/include/catalog/Makefile b/src/include/catalog/Makefile
index 167f91a6e3..507f380821 100644
--- a/src/include/catalog/Makefile
+++ b/src/include/catalog/Makefile
@@ -81,7 +81,8 @@ CATALOG_HEADERS := \
pg_publication_namespace.h \
pg_publication_rel.h \
pg_subscription.h \
- pg_subscription_rel.h
+ pg_subscription_rel.h \
+ pg_variable.h
GENERATED_HEADERS := $(CATALOG_HEADERS:%.h=%_d.h)
diff --git a/src/include/catalog/meson.build b/src/include/catalog/meson.build
index f70d1daba5..6eec79d2ee 100644
--- a/src/include/catalog/meson.build
+++ b/src/include/catalog/meson.build
@@ -69,6 +69,7 @@ catalog_headers = [
'pg_publication_rel.h',
'pg_subscription.h',
'pg_subscription_rel.h',
+ 'pg_variable.h',
]
# The .dat files we need can just be listed alphabetically.
diff --git a/src/include/catalog/namespace.h b/src/include/catalog/namespace.h
index 8d434d48d5..18ea6ac83a 100644
--- a/src/include/catalog/namespace.h
+++ b/src/include/catalog/namespace.h
@@ -97,6 +97,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,
@@ -169,6 +171,8 @@ extern SearchPathMatcher *GetSearchPathMatcher(MemoryContext context);
extern SearchPathMatcher *CopySearchPathMatcher(SearchPathMatcher *path);
extern bool SearchPathMatchesCurrentEnvironment(SearchPathMatcher *path);
+extern Oid LookupVariable(const char *nspname, const char *varname, bool missing_ok);
+
extern Oid get_collation_oid(List *collname, bool missing_ok);
extern Oid get_conversion_oid(List *conname, bool missing_ok);
extern Oid FindDefaultConversionProc(int32 for_encoding, int32 to_encoding);
diff --git a/src/include/catalog/pg_default_acl.h b/src/include/catalog/pg_default_acl.h
index d272cdf08b..529d53c6bb 100644
--- a/src/include/catalog/pg_default_acl.h
+++ b/src/include/catalog/pg_default_acl.h
@@ -68,6 +68,7 @@ MAKE_SYSCACHE(DEFACLROLENSPOBJ, pg_default_acl_role_nsp_obj_index, 8);
#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 73d9cf8582..644beb9043 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6445,6 +6445,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..3810e040f2
--- /dev/null
+++ b/src/include/catalog/pg_variable.h
@@ -0,0 +1,81 @@
+/*-------------------------------------------------------------------------
+ *
+ * pg_variable.h
+ * definition of session variables system catalog (pg_variables)
+ *
+ *
+ * Portions Copyright (c) 1996-2024, 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 */
+
+ /* OID of entry in pg_type for variable's type */
+ Oid vartype BKI_LOOKUP(pg_type);
+
+ /* variable name */
+ NameData varname;
+
+ /* OID of namespace containing variable class */
+ Oid varnamespace BKI_LOOKUP(pg_namespace);
+
+ /* variable owner */
+ Oid varowner BKI_LOOKUP(pg_authid);
+
+ /* typmod for variable's type */
+ int32 vartypmod BKI_DEFAULT(-1);
+
+ /* variable collation */
+ Oid varcollation BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_collation);
+
+
+#ifdef CATALOG_VARLEN /* variable-length fields start here */
+
+ /* access permissions */
+ aclitem varacl[1] BKI_DEFAULT(_null_);
+
+#endif
+} FormData_pg_variable;
+
+/* ----------------
+ * Form_pg_variable corresponds to a pointer to a tuple with
+ * the format of the pg_variable relation.
+ * ----------------
+ */
+typedef FormData_pg_variable *Form_pg_variable;
+
+DECLARE_TOAST(pg_variable, 9223, 9224);
+
+DECLARE_UNIQUE_INDEX_PKEY(pg_variable_oid_index, 9225, VariableOidIndexId, pg_variable, btree(oid oid_ops));
+DECLARE_UNIQUE_INDEX(pg_variable_varname_nsp_index, 9226, VariableNameNspIndexId, pg_variable, btree(varname name_ops, varnamespace oid_ops));
+
+extern ObjectAddress CreateVariable(ParseState *pstate,
+ CreateSessionVarStmt *stmt);
+extern void DropVariableById(Oid varid);
+
+MAKE_SYSCACHE(VARIABLENAMENSP, pg_variable_varname_nsp_index, 8);
+MAKE_SYSCACHE(VARIABLEOID, pg_variable_oid_index, 8);
+
+#endif /* PG_VARIABLE_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 85a62b538e..80ea5928ee 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2309,6 +2309,7 @@ typedef enum ObjectType
OBJECT_TSTEMPLATE,
OBJECT_TYPE,
OBJECT_USER_MAPPING,
+ OBJECT_VARIABLE,
OBJECT_VIEW,
} ObjectType;
@@ -3425,6 +3426,21 @@ typedef struct AlterStatsStmt
bool missing_ok; /* skip error if statistics object is missing */
} AlterStatsStmt;
+
+/* ----------------------
+ * {Create|Alter} VARIABLE Statement
+ * ----------------------
+ */
+typedef struct CreateSessionVarStmt
+{
+ NodeTag type;
+ RangeVar *variable; /* the variable to create */
+ TypeName *typeName; /* the type of variable */
+ CollateClause *collClause;
+ bool if_not_exists; /* do nothing if it already exists */
+} CreateSessionVarStmt;
+
+
/* ----------------------
* Create Function Statement
* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f7fe834cf4..c884cd5c46 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -486,6 +486,8 @@ PG_KEYWORD("validator", VALIDATOR, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("value", VALUE_P, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("values", VALUES, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("varchar", VARCHAR, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("variable", VARIABLE, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("variables", VARIABLES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("variadic", VARIADIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("varying", VARYING, UNRESERVED_KEYWORD, AS_LABEL)
PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/include/tcop/cmdtaglist.h b/src/include/tcop/cmdtaglist.h
index 7fdcec6dd9..9465df7b2f 100644
--- a/src/include/tcop/cmdtaglist.h
+++ b/src/include/tcop/cmdtaglist.h
@@ -68,6 +68,7 @@ PG_CMDTAG(CMDTAG_ALTER_TRANSFORM, "ALTER TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_TRIGGER, "ALTER TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_TYPE, "ALTER TYPE", true, true, false)
PG_CMDTAG(CMDTAG_ALTER_USER_MAPPING, "ALTER USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_ALTER_VARIABLE, "ALTER VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_ALTER_VIEW, "ALTER VIEW", true, false, false)
PG_CMDTAG(CMDTAG_ANALYZE, "ANALYZE", false, false, false)
PG_CMDTAG(CMDTAG_BEGIN, "BEGIN", false, false, false)
@@ -123,6 +124,7 @@ PG_CMDTAG(CMDTAG_CREATE_TRANSFORM, "CREATE TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_TRIGGER, "CREATE TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_TYPE, "CREATE TYPE", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_USER_MAPPING, "CREATE USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_CREATE_VARIABLE, "CREATE VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_CREATE_VIEW, "CREATE VIEW", true, false, false)
PG_CMDTAG(CMDTAG_DEALLOCATE, "DEALLOCATE", false, false, false)
PG_CMDTAG(CMDTAG_DEALLOCATE_ALL, "DEALLOCATE ALL", false, false, false)
@@ -175,6 +177,7 @@ PG_CMDTAG(CMDTAG_DROP_TRANSFORM, "DROP TRANSFORM", true, false, false)
PG_CMDTAG(CMDTAG_DROP_TRIGGER, "DROP TRIGGER", true, false, false)
PG_CMDTAG(CMDTAG_DROP_TYPE, "DROP TYPE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_USER_MAPPING, "DROP USER MAPPING", true, false, false)
+PG_CMDTAG(CMDTAG_DROP_VARIABLE, "DROP VARIABLE", true, false, false)
PG_CMDTAG(CMDTAG_DROP_VIEW, "DROP VIEW", true, false, false)
PG_CMDTAG(CMDTAG_EXECUTE, "EXECUTE", false, false, false)
PG_CMDTAG(CMDTAG_EXPLAIN, "EXPLAIN", false, false, false)
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 731d84b2a9..98aab98229 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -169,6 +169,7 @@ typedef struct ArrayType Acl;
#define ACL_ALL_RIGHTS_SCHEMA (ACL_USAGE|ACL_CREATE)
#define ACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)
#define ACL_ALL_RIGHTS_TYPE (ACL_USAGE)
+#define ACL_ALL_RIGHTS_VARIABLE (ACL_SELECT|ACL_UPDATE)
/* operation codes for pg_*_aclmask */
typedef enum
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 20446f6f83..8a3684e711 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -134,6 +134,7 @@ extern char get_func_prokind(Oid funcid);
extern bool get_func_leakproof(Oid funcid);
extern RegProcedure get_func_support(Oid funcid);
extern Oid get_relname_relid(const char *relname, Oid relnamespace);
+extern Oid get_varname_varid(const char *varname, Oid varnamespace);
extern char *get_rel_name(Oid relid);
extern Oid get_rel_namespace(Oid relid);
extern Oid get_rel_type_id(Oid relid);
@@ -206,6 +207,14 @@ extern char *get_publication_name(Oid pubid, bool missing_ok);
extern Oid get_subscription_oid(const char *subname, bool missing_ok);
extern char *get_subscription_name(Oid subid, bool missing_ok);
+extern char *get_session_variable_name(Oid varid);
+extern Oid get_session_variable_namespace(Oid varid);
+extern Oid get_session_variable_type(Oid varid);
+extern void get_session_variable_type_typmod_collid(Oid varid,
+ Oid *typid,
+ int32 *typmod,
+ Oid *collid);
+
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)
/* type_is_array_domain accepts both plain arrays and domains over arrays */
#define type_is_array_domain(typid) (get_base_element_type(typid) != InvalidOid)
diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out
index 74d9ff2998..c336f67a59 100644
--- a/src/test/regress/expected/dependency.out
+++ b/src/test/regress/expected/dependency.out
@@ -151,3 +151,20 @@ owner of type deptest_t
DROP OWNED BY regress_dep_user2, regress_dep_user0;
DROP USER regress_dep_user2;
DROP USER regress_dep_user0;
+-- dependency on type
+CREATE DOMAIN vardomain AS int;
+CREATE TYPE vartype AS (a int, b int, c vardomain);
+CREATE VARIABLE var1 AS vartype;
+-- should fail
+DROP DOMAIN vardomain;
+ERROR: cannot drop type vardomain because other objects depend on it
+DETAIL: column c of composite type vartype depends on type vardomain
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP TYPE vartype;
+ERROR: cannot drop type vartype because other objects depend on it
+DETAIL: session variable var1 depends on type vartype
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+-- clean up
+DROP VARIABLE var1;
+DROP TYPE vartype;
+DROP DOMAIN vardomain;
diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out
index 215eb899be..d995332140 100644
--- a/src/test/regress/expected/oidjoins.out
+++ b/src/test/regress/expected/oidjoins.out
@@ -266,3 +266,7 @@ NOTICE: checking pg_subscription {subdbid} => pg_database {oid}
NOTICE: checking pg_subscription {subowner} => pg_authid {oid}
NOTICE: checking pg_subscription_rel {srsubid} => pg_subscription {oid}
NOTICE: checking pg_subscription_rel {srrelid} => pg_class {oid}
+NOTICE: checking pg_variable {vartype} => pg_type {oid}
+NOTICE: checking pg_variable {varnamespace} => pg_namespace {oid}
+NOTICE: checking pg_variable {varowner} => pg_authid {oid}
+NOTICE: checking pg_variable {varcollation} => pg_collation {oid}
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index 3bbe4c5f97..c89067490f 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -5828,6 +5828,30 @@ COMMIT;
# final ON_ERROR_ROLLBACK: off
DROP TABLE bla;
DROP FUNCTION psql_error;
+-- session variable test
+CREATE ROLE regress_variable_owner;
+SET ROLE TO regress_variable_owner;
+CREATE VARIABLE var1 AS varchar COLLATE "C";
+\dV+ var1
+ List of variables
+ Schema | Name | Type | Collation | Owner | Access privileges | Description
+--------+------+-------------------+-----------+------------------------+-------------------+-------------
+ public | var1 | character varying | C | regress_variable_owner | |
+(1 row)
+
+GRANT SELECT ON VARIABLE var1 TO PUBLIC;
+COMMENT ON VARIABLE var1 IS 'some description';
+\dV+ var1
+ List of variables
+ Schema | Name | Type | Collation | Owner | Access privileges | Description
+--------+------+-------------------+-----------+------------------------+--------------------------------------------------+------------------
+ public | var1 | character varying | C | regress_variable_owner | regress_variable_owner=rw/regress_variable_owner+| some description
+ | | | | | =r/regress_variable_owner |
+(1 row)
+
+DROP VARIABLE var1;
+SET ROLE TO DEFAULT;
+DROP ROLE regress_variable_owner;
-- check describing invalid multipart names
\dA regression.heap
improper qualified name (too many dotted names): regression.heap
@@ -6049,6 +6073,12 @@ cross-database references are not implemented: nonesuch.public.func_deps_stat
improper qualified name (too many dotted names): regression.myevt
\dy nonesuch.myevt
improper qualified name (too many dotted names): nonesuch.myevt
+\dV host.regression.public.var
+improper qualified name (too many dotted names): host.regression.public.var
+\dV regression|mydb.public.var
+cross-database references are not implemented: regression|mydb.public.var
+\dV nonesuch.public.var
+cross-database references are not implemented: nonesuch.public.var
-- check that dots within quoted name segments are not counted
\dA "no.such.access.method"
List of access methods
@@ -6283,6 +6313,12 @@ List of schemas
------+-------+-------+---------+----------+------
(0 rows)
+\dV "no.such.variable"
+ List of variables
+ Schema | Name | Type | Collation | Owner
+--------+------+------+-----------+-------
+(0 rows)
+
-- again, but with dotted schema qualifications.
\dA "no.such.schema"."no.such.access.method"
improper qualified name (too many dotted names): "no.such.schema"."no.such.access.method"
@@ -6452,6 +6488,12 @@ improper qualified name (too many dotted names): "no.such.schema"."no.such.insta
\dy "no.such.schema"."no.such.event.trigger"
improper qualified name (too many dotted names): "no.such.schema"."no.such.event.trigger"
+\dV "no.such.schema"."no.such.variable"
+ List of variables
+ Schema | Name | Type | Collation | Owner
+--------+------+------+-----------+-------
+(0 rows)
+
-- again, but with current database and dotted schema qualifications.
\dt regression."no.such.schema"."no.such.table.relation"
List of relations
@@ -6585,6 +6627,12 @@ List of text search templates
--------+------+------------+-----------+--------------+-----
(0 rows)
+\dV regression."no.such.schema"."no.such.variable"
+ List of variables
+ Schema | Name | Type | Collation | Owner
+--------+------+------+-----------+-------
+(0 rows)
+
-- again, but with dotted database and dotted schema qualifications.
\dt "no.such.database"."no.such.schema"."no.such.table.relation"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.table.relation"
@@ -6632,6 +6680,8 @@ cross-database references are not implemented: "no.such.database"."no.such.schem
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.extended.statistics"
+\dV "no.such.database"."no.such.schema"."no.such.variable"
+cross-database references are not implemented: "no.such.database"."no.such.schema"."no.such.variable"
-- check \drg and \du
CREATE ROLE regress_du_role0;
CREATE ROLE regress_du_role1;
diff --git a/src/test/regress/expected/session_variables.out b/src/test/regress/expected/session_variables.out
new file mode 100644
index 0000000000..aca766042d
--- /dev/null
+++ b/src/test/regress/expected/session_variables.out
@@ -0,0 +1,57 @@
+CREATE ROLE regress_variable_owner;
+-- should be ok
+CREATE VARIABLE var1 AS int;
+-- should fail, pseudotypes are not allowed
+CREATE VARIABLE var2 AS anyelement;
+ERROR: session variable cannot be pseudo-type anyelement
+-- should be ok, do nothing
+DROP VARIABLE IF EXISTS var2;
+NOTICE: session variable "var2" does not exist, skipping
+-- do nothing
+CREATE VARIABLE IF NOT EXISTS var1 AS int;
+NOTICE: session variable "var1" already exists, skipping
+-- should fail
+CREATE VARIABLE var1 AS int;
+ERROR: session variable "var1" already exists
+-- should be ok
+DROP VARIABLE IF EXISTS var1;
+-- check comment on variable
+CREATE VARIABLE var1 AS int;
+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;
+--- check access rights and supported ALTER
+CREATE SCHEMA svartest;
+GRANT ALL ON SCHEMA svartest TO regress_variable_owner;
+CREATE VARIABLE svartest.var1 AS int;
+CREATE ROLE regress_variable_reader;
+GRANT SELECT ON VARIABLE svartest.var1 TO regress_variable_reader;
+REVOKE ALL ON VARIABLE svartest.var1 FROM regress_variable_reader;
+ALTER VARIABLE svartest.var1 OWNER TO regress_variable_owner;
+ALTER VARIABLE svartest.var1 RENAME TO varxx;
+ALTER VARIABLE svartest.varxx SET SCHEMA public;
+DROP VARIABLE public.varxx;
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner
+ IN SCHEMA svartest
+ GRANT SELECT ON VARIABLES TO regress_variable_reader;
+-- creating variable with default privileges
+SET ROLE TO regress_variable_owner;
+CREATE VARIABLE svartest.var1 AS int;
+SET ROLE TO DEFAULT;
+\dV+ svartest.var1
+ List of variables
+ Schema | Name | Type | Collation | Owner | Access privileges | Description
+----------+------+---------+-----------+------------------------+--------------------------------------------------+-------------
+ svartest | var1 | integer | | regress_variable_owner | regress_variable_owner=rw/regress_variable_owner+|
+ | | | | | regress_variable_reader=r/regress_variable_owner |
+(1 row)
+
+DROP VARIABLE svartest.var1;
+DROP SCHEMA svartest;
+DROP ROLE regress_variable_owner;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 2429ec2bba..ed6893d479 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -111,7 +111,7 @@ test: json jsonb json_encoding jsonpath jsonpath_encoding jsonb_jsonpath sqljson
# 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/dependency.sql b/src/test/regress/sql/dependency.sql
index 8d74ed7122..6c18b7f840 100644
--- a/src/test/regress/sql/dependency.sql
+++ b/src/test/regress/sql/dependency.sql
@@ -114,3 +114,17 @@ DROP USER regress_dep_user2;
DROP OWNED BY regress_dep_user2, regress_dep_user0;
DROP USER regress_dep_user2;
DROP USER regress_dep_user0;
+
+-- dependency on type
+CREATE DOMAIN vardomain AS int;
+CREATE TYPE vartype AS (a int, b int, c vardomain);
+CREATE VARIABLE var1 AS vartype;
+
+-- should fail
+DROP DOMAIN vardomain;
+DROP TYPE vartype;
+
+-- clean up
+DROP VARIABLE var1;
+DROP TYPE vartype;
+DROP DOMAIN vardomain;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index 3b3c6f6e29..b80d56a27a 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -1591,6 +1591,19 @@ COMMIT;
DROP TABLE bla;
DROP FUNCTION psql_error;
+-- session variable test
+CREATE ROLE regress_variable_owner;
+SET ROLE TO regress_variable_owner;
+CREATE VARIABLE var1 AS varchar COLLATE "C";
+\dV+ var1
+GRANT SELECT ON VARIABLE var1 TO PUBLIC;
+COMMENT ON VARIABLE var1 IS 'some description';
+\dV+ var1
+DROP VARIABLE var1;
+
+SET ROLE TO DEFAULT;
+DROP ROLE regress_variable_owner;
+
-- check describing invalid multipart names
\dA regression.heap
\dA nonesuch.heap
@@ -1702,6 +1715,9 @@ DROP FUNCTION psql_error;
\dX nonesuch.public.func_deps_stat
\dy regression.myevt
\dy nonesuch.myevt
+\dV host.regression.public.var
+\dV regression|mydb.public.var
+\dV nonesuch.public.var
-- check that dots within quoted name segments are not counted
\dA "no.such.access.method"
@@ -1743,6 +1759,8 @@ DROP FUNCTION psql_error;
\dx "no.such.installed.extension"
\dX "no.such.extended.statistics"
\dy "no.such.event.trigger"
+\dV "no.such.variable"
+
-- again, but with dotted schema qualifications.
\dA "no.such.schema"."no.such.access.method"
@@ -1783,6 +1801,7 @@ DROP FUNCTION psql_error;
\dx "no.such.schema"."no.such.installed.extension"
\dX "no.such.schema"."no.such.extended.statistics"
\dy "no.such.schema"."no.such.event.trigger"
+\dV "no.such.schema"."no.such.variable"
-- again, but with current database and dotted schema qualifications.
\dt regression."no.such.schema"."no.such.table.relation"
@@ -1807,6 +1826,7 @@ DROP FUNCTION psql_error;
\dP regression."no.such.schema"."no.such.partitioned.relation"
\dT regression."no.such.schema"."no.such.data.type"
\dX regression."no.such.schema"."no.such.extended.statistics"
+\dV regression."no.such.schema"."no.such.variable"
-- again, but with dotted database and dotted schema qualifications.
\dt "no.such.database"."no.such.schema"."no.such.table.relation"
@@ -1832,6 +1852,7 @@ DROP FUNCTION psql_error;
\dP "no.such.database"."no.such.schema"."no.such.partitioned.relation"
\dT "no.such.database"."no.such.schema"."no.such.data.type"
\dX "no.such.database"."no.such.schema"."no.such.extended.statistics"
+\dV "no.such.database"."no.such.schema"."no.such.variable"
-- check \drg and \du
CREATE ROLE regress_du_role0;
diff --git a/src/test/regress/sql/session_variables.sql b/src/test/regress/sql/session_variables.sql
new file mode 100644
index 0000000000..42dfb95a02
--- /dev/null
+++ b/src/test/regress/sql/session_variables.sql
@@ -0,0 +1,61 @@
+CREATE ROLE regress_variable_owner;
+
+-- should be ok
+CREATE VARIABLE var1 AS int;
+
+-- should fail, pseudotypes are not allowed
+CREATE VARIABLE var2 AS anyelement;
+
+-- should be ok, do nothing
+DROP VARIABLE IF EXISTS var2;
+
+-- do nothing
+CREATE VARIABLE IF NOT EXISTS var1 AS int;
+
+-- should fail
+CREATE VARIABLE var1 AS int;
+
+-- should be ok
+DROP VARIABLE IF EXISTS var1;
+
+-- check comment on variable
+CREATE VARIABLE var1 AS int;
+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;
+
+--- check access rights and supported ALTER
+CREATE SCHEMA svartest;
+GRANT ALL ON SCHEMA svartest TO regress_variable_owner;
+
+CREATE VARIABLE svartest.var1 AS int;
+
+CREATE ROLE regress_variable_reader;
+
+GRANT SELECT ON VARIABLE svartest.var1 TO regress_variable_reader;
+REVOKE ALL ON VARIABLE svartest.var1 FROM regress_variable_reader;
+
+ALTER VARIABLE svartest.var1 OWNER TO regress_variable_owner;
+ALTER VARIABLE svartest.var1 RENAME TO varxx;
+ALTER VARIABLE svartest.varxx SET SCHEMA public;
+
+DROP VARIABLE public.varxx;
+
+ALTER DEFAULT PRIVILEGES
+ FOR ROLE regress_variable_owner
+ IN SCHEMA svartest
+ GRANT SELECT ON VARIABLES TO regress_variable_reader;
+
+-- creating variable with default privileges
+SET ROLE TO regress_variable_owner;
+CREATE VARIABLE svartest.var1 AS int;
+SET ROLE TO DEFAULT;
+
+\dV+ svartest.var1
+
+DROP VARIABLE svartest.var1;
+
+DROP SCHEMA svartest;
+
+DROP ROLE regress_variable_owner;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index b4d7f9217c..a18e81125b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -533,6 +533,7 @@ CreateRoleStmt
CreateSchemaStmt
CreateSchemaStmtContext
CreateSeqStmt
+CreateSessionVarStmt
CreateStatsStmt
CreateStmt
CreateStmtContext
@@ -872,6 +873,7 @@ FormData_pg_ts_parser
FormData_pg_ts_template
FormData_pg_type
FormData_pg_user_mapping
+FormData_pg_variable
FormExtraData_pg_attribute
Form_pg_aggregate
Form_pg_am
@@ -931,6 +933,7 @@ Form_pg_ts_parser
Form_pg_ts_template
Form_pg_type
Form_pg_user_mapping
+Form_pg_variable
FormatNode
FreeBlockNumberArray
FreeListData
@@ -3061,6 +3064,7 @@ VarString
VarStringSortSupport
Variable
VariableAssignHook
+VariableInfo
VariableSetKind
VariableSetStmt
VariableShowStmt
--
2.45.2
view thread (439+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: proposal: schema variables
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox