public inbox for [email protected]help / color / mirror / Atom feed
Re: is ErrorResponse possible on Sync? 4+ messages / 4 participants [nested] [flat]
* Re: is ErrorResponse possible on Sync? @ 2022-01-12 18:05 Tom Lane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Tom Lane @ 2022-01-12 18:05 UTC (permalink / raw) To: Andrei Matei <[email protected]>; +Cc: [email protected] Andrei Matei <[email protected]> writes: > I've got a question about the wire protocol; the relevant text in the docs > seems a bit ambiguous to me. If the processing of a Sync message fails > (e.g. because the commit of the current transaction fails), is the backend > allowed to respond with an ErrorResponse, in addition to the ReadyForQuery > message? Or, does the backend swallow the error, and return only the > ReadyForQuery (I hope not). Uh ... I don't think Sync itself can fail. Any ErrorResponse you see there is really from failure of some prior command. The Sync is really delimiting how much stuff you'd like to skip in case of a failure. Basically this is to allow pipelining of commands, with the ability to discard later commands if an earlier one fails. But in any case, no, Sync would not suppress an error message if one is needed. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is ErrorResponse possible on Sync? @ 2022-01-12 18:18 Andrei Matei <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Andrei Matei @ 2022-01-12 18:18 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] Thanks! I work on CockroachDB - which is wire-compatible with Postgres - so I'm interested in what the server can and cannot do. > Uh ... I don't think Sync itself can fail. Any ErrorResponse you see > there is really from failure of some prior command. Hmm, this got me curious. If Sync itself cannot fail, then what is this sentence really saying: "This parameterless message (ed. Sync) causes the backend to close the current transaction if it's not inside a BEGIN/COMMIT transaction block (“close” meaning to commit if no error, or roll back if error)." ? This seems to say that, outside of BEGIN/END, the transaction is committed at Sync time (i.e. if the Sync is never sent, nothing is committed). Presumably, committing a transaction can fail even if no previous command/statement failed, right? > The Sync is really > delimiting how much stuff you'd like to skip in case of a failure. > Basically this is to allow pipelining of commands, with the ability to > discard later commands if an earlier one fails. > > But in any case, no, Sync would not suppress an error message if > one is needed. > > regards, tom lane > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: is ErrorResponse possible on Sync? @ 2022-01-12 23:51 Tatsuo Ishii <[email protected]> parent: Andrei Matei <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Tatsuo Ishii @ 2022-01-12 23:51 UTC (permalink / raw) To: [email protected]; +Cc: [email protected]; [email protected] > Hmm, this got me curious. If Sync itself cannot fail, then what is this > sentence really saying: "This parameterless message (ed. Sync) causes the > backend to close the current transaction if it's not inside a BEGIN/COMMIT > transaction block (“close” meaning to commit if no error, or roll back if > error)." ? > This seems to say that, outside of BEGIN/END, the transaction is committed > at Sync time (i.e. if the Sync is never sent, nothing is committed). Yes, if you do not send Sync and terminate the session, then the transaction will not be committed. FE=> Parse(stmt="", query="INSERT INTO t1 VALUES(2)") FE=> Bind(stmt="", portal="") FE=> Execute(portal="") FE=> Terminate After this, I don't see the row (2) in table t1. > Presumably, committing a transaction can fail even if no > previous command/statement failed, right? Right. Alvaro gave an excellent example. Best reagards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH 11/11] documentation @ 2022-04-04 18:23 [email protected] <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: [email protected] @ 2022-04-04 18:23 UTC (permalink / raw) Documentation for CREATE VARIABLE, DROP VARIABLE and LET commands. Update of GRANT, REVOKE, DISCARD, ALTER commands related to support of session variables. --- doc/src/sgml/advanced.sgml | 62 +++++ doc/src/sgml/catalogs.sgml | 158 +++++++++++++ doc/src/sgml/config.sgml | 15 ++ doc/src/sgml/event-trigger.sgml | 24 ++ doc/src/sgml/glossary.sgml | 16 ++ doc/src/sgml/plpgsql.sgml | 12 + doc/src/sgml/ref/allfiles.sgml | 4 + .../sgml/ref/alter_default_privileges.sgml | 26 ++- doc/src/sgml/ref/alter_variable.sgml | 179 +++++++++++++++ doc/src/sgml/ref/comment.sgml | 1 + doc/src/sgml/ref/create_variable.sgml | 214 ++++++++++++++++++ doc/src/sgml/ref/discard.sgml | 14 +- doc/src/sgml/ref/drop_variable.sgml | 118 ++++++++++ doc/src/sgml/ref/grant.sgml | 9 + doc/src/sgml/ref/let.sgml | 109 +++++++++ doc/src/sgml/ref/pg_restore.sgml | 11 + doc/src/sgml/ref/revoke.sgml | 7 + doc/src/sgml/reference.sgml | 4 + 18 files changed, 974 insertions(+), 9 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 doc/src/sgml/ref/let.sgml diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml index 755c9f1485..c65b2bb336 100644 --- a/doc/src/sgml/advanced.sgml +++ b/doc/src/sgml/advanced.sgml @@ -700,6 +700,68 @@ SELECT name, elevation </sect1> + <sect1 id="tutorial-session-variables"> + <title>Session Variables</title> + + <indexterm zone="tutorial-session-variables"> + <primary>Session variables</primary> + </indexterm> + + <indexterm> + <primary>session variable</primary> + <secondary>introduction</secondary> + </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> + The value of a session variable is set with the <command>LET</command> SQL + command. While session variables share properties with tables, their value + cannot be updated with an <command>UPDATE</command> command. The value of a + session variable may be retrieved by the <command>SELECT</command> SQL + command. +<programlisting> +CREATE VARIABLE var1 AS date; +LET var1 = current_date; +SELECT var1; +</programlisting> + + or + +<programlisting> +CREATE VARIABLE public.current_user_id AS integer; +GRANT READ ON VARIABLE public.current_user_id TO PUBLIC; +LET current_user_id = (SELECT id FROM users WHERE usename = session_user); +SELECT current_user_id; +</programlisting> + </para> + + <para> + The value of a session variable is local to the current session. Retrieving + a variable's value returns either a <literal>NULL</literal> or a default + value, unless its value has been set to something else in the current + session using the <command>LET</command> command. The content of a variable + is not transactional. This is the same as regular variables in PL + languages. + </para> + + <para> + The session variables can be shadowed by column references in a query. When + a query contains identifiers or qualified identifiers that could be used as + both a session variable identifiers and as column identifier, then the + column identifier is preferred every time. Warnings can be emitted when + this situation happens by enabling configuration parameter <xref + linkend="guc-session-variables-ambiguity-warning"/>. + </para> + </sect1> + + <sect1 id="tutorial-conclusion"> <title>Conclusion</title> diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 00f833d210..e5294be4a2 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> @@ -9613,4 +9618,157 @@ 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 table <structname>pg_variable</structname> provides 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>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>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>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>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>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>varisnotnull</structfield> <type>boolean</type> + </para> + <para> + True if the session variable doesn't allow null value. The default value is false. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>varisimmutable</structfield> <type>boolean</type> + </para> + <para> + True if the variable is <link linkend="sql-createvariable-immutable">immutable</link> (cannot be modified). The default value is false. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>vareoxaction</structfield> <type>char</type> + </para> + <para> + Action performed at end of transaction: + <literal>n</literal> = no action, <literal>d</literal> = drop the variable, + <literal>r</literal> = reset the variable to its default value. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>vardefexpr</structfield> <type>pg_node_tree</type> + </para> + <para> + The internal representation of the variable default value + </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/config.sgml b/doc/src/sgml/config.sgml index a5cd4e44c7..6bf0f59998 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -10239,6 +10239,21 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' </listitem> </varlistentry> + <varlistentry id="guc-session-variables-ambiguity-warning" xreflabel="session_variables_ambiguity_warning"> + <term><varname>session_variables_ambiguity_warning</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>session_variables_ambiguity_warning</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + When on, a warning is raised when any identifier in a query could be + used as both a column identifier, routine variable or a session + variable identifier. The default is <literal>off</literal>. + </para> + </listitem> + </varlistentry> + <varlistentry id="guc-standard-conforming-strings" xreflabel="standard_conforming_strings"> <term><varname>standard_conforming_strings</varname> (<type>boolean</type>) <indexterm><primary>strings</primary><secondary>standard conforming</secondary></indexterm> diff --git a/doc/src/sgml/event-trigger.sgml b/doc/src/sgml/event-trigger.sgml index f1235a2c9f..a8f303b474 100644 --- a/doc/src/sgml/event-trigger.sgml +++ b/doc/src/sgml/event-trigger.sgml @@ -405,6 +405,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> @@ -693,6 +701,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> @@ -981,6 +997,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/glossary.sgml b/doc/src/sgml/glossary.sgml index d6d0a3a814..a46baa0c17 100644 --- a/doc/src/sgml/glossary.sgml +++ b/doc/src/sgml/glossary.sgml @@ -1477,6 +1477,22 @@ </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 + memory is not shared across sessions, and after session end, this memory + (the value) is released. The access (read or write) to session variables + is controlled by access rights similarly to other database object access + rights. + </para> + <para> + For more information, see <xref linkend="tutorial-session-variables"/>. + </para> + </glossdef> + </glossentry> + <glossentry id="glossary-shared-memory"> <glossterm>Shared memory</glossterm> <glossdef> diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index cf387dfc3f..3dfe4c8a2e 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -5953,6 +5953,18 @@ $$ LANGUAGE plpgsql STRICT IMMUTABLE; </programlisting> </para> </sect3> + + <sect3> + <title><command>Session variables</command></title> + + <para> + The <application>PL/pgSQL</application> language has no packages, and + therefore no package variables or package constants. + <productname>PostgreSQL</productname> has session variables and immutable + session variables. Session variables can be created by <command>CREATE + VARIABLE</command>, as described in <xref linkend="sql-createvariable"/>. + </para> + </sect3> </sect2> <sect2 id="plpgsql-porting-appendix"> diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml index e90a0e1f83..24e30fdd97 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"> @@ -148,6 +150,7 @@ Complete list of usable sgml source files in this directory. <!ENTITY dropUser SYSTEM "drop_user.sgml"> <!ENTITY dropUserMapping SYSTEM "drop_user_mapping.sgml"> <!ENTITY dropView SYSTEM "drop_view.sgml"> +<!ENTITY dropVariable SYSTEM "drop_variable.sgml"> <!ENTITY end SYSTEM "end.sgml"> <!ENTITY execute SYSTEM "execute.sgml"> <!ENTITY explain SYSTEM "explain.sgml"> @@ -155,6 +158,7 @@ Complete list of usable sgml source files in this directory. <!ENTITY grant SYSTEM "grant.sgml"> <!ENTITY importForeignSchema SYSTEM "import_foreign_schema.sgml"> <!ENTITY insert SYSTEM "insert.sgml"> +<!ENTITY let SYSTEM "let.sgml"> <!ENTITY listen SYSTEM "listen.sgml"> <!ENTITY load SYSTEM "load.sgml"> <!ENTITY lock SYSTEM "lock.sgml"> diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml index f1d54f5aa3..354991b849 100644 --- a/doc/src/sgml/ref/alter_default_privileges.sgml +++ b/doc/src/sgml/ref/alter_default_privileges.sgml @@ -50,6 +50,10 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] } ON SCHEMAS TO { [ GROUP ] <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 } [, ...] | ALL [ PRIVILEGES ] } @@ -81,6 +85,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> @@ -92,14 +102,14 @@ REVOKE [ GRANT OPTION FOR ] that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the privileges for schemas, tables (including views and foreign - tables), sequences, functions, and types (including domains) can be - altered. For this command, functions include aggregates and procedures. - 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..d2036351e5 --- /dev/null +++ b/doc/src/sgml/ref/alter_variable.sgml @@ -0,0 +1,179 @@ +<!-- +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> + <member><xref linkend="sql-let"/></member> + </simplelist> + </refsect1> +</refentry> diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index 23d9029af9..50bfec6f28 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">text</replaceable>' diff --git a/doc/src/sgml/ref/create_variable.sgml b/doc/src/sgml/ref/create_variable.sgml new file mode 100644 index 0000000000..70c87968ce --- /dev/null +++ b/doc/src/sgml/ref/create_variable.sgml @@ -0,0 +1,214 @@ +<!-- +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 [ { TEMPORARY | TEMP } ] [ IMMUTABLE ] VARIABLE [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> [ AS ] <replaceable class="parameter">data_type</replaceable> ] [ COLLATE <replaceable class="parameter">collation</replaceable> ] + [ NOT NULL ] [ DEFAULT <replaceable class="parameter">default_expr</replaceable> ] [ { ON COMMIT DROP | ON TRANSACTION END RESET } ] +</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 <command>GRANT</command> and <command>REVOKE</command> + commands. + </para> + + <para> + The value of a session variable is local to the current session. Retrieving + a session variable's value returns either a NULL or a default value, unless + its value is set to something else in the current session with a LET + command. The content of a session variable is not transactional. This is the + same as regular variables in PL languages. + </para> + + <para> + Session variables are retrieved by the <command>SELECT</command> SQL + command. Their value is set with the <command>LET</command> SQL command. + While session variables share properties with tables, their value cannot be + changed with an <command>UPDATE</command> command. + </para> + + <note> + <para> + Inside a query or an expression, the session variable can be shadowed by + column or by routine's variable or routine argument. Such collisions of + identifiers can be resolved by using qualified identifiers. Session variables + can use schema name, columns can use table aliases, routine variables + can use block labels, and routine arguments can use the routine name. + </para> + </note> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry id="sql-createvariable-immutable"> + <term><literal>IMMUTABLE</literal></term> + <listitem> + <para> + The assigned value of the session variable can not be changed. + Only if the session variable doesn't have a default value, a single + initialization is allowed using the <command>LET</command> command. Once + done, no further change is allowed until end of transaction + if the session variable was created with clause <literal>ON TRANSACTION + END RESET</literal>, or until reset of all session variables by + <command>DISCARD VARIABLES</command>, or until reset of all session + objects by command <command>DISCARD ALL</command>. + </para> + + </listitem> + </varlistentry> + + <varlistentry> + <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> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name, optionally schema-qualified, of the session variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <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> + <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> + + <varlistentry> + <term><literal>NOT NULL</literal></term> + <listitem> + <para> + The <literal>NOT NULL</literal> clause forbids setting the session + variable to a null value. A session variable created as NOT NULL and + without an explicitly declared default value cannot be read until it is + initialized by a LET command. This requires the user to explicitly + initialize the session variable content before reading it, otherwise an + error will be thrown. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT <replaceable>default_expr</replaceable></literal></term> + <listitem> + <para> + The <literal>DEFAULT</literal> clause can be used to assign a default + value to a session variable. This expression is evaluated when the session + variable is first accessed for reading and had not yet been assigned a value. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ON COMMIT DROP</literal>, <literal>ON TRANSACTION END RESET</literal></term> + <listitem> + <para> + The <literal>ON COMMIT DROP</literal> clause specifies the behaviour of a + temporary session variable at transaction commit. With this clause, the + session variable is dropped at commit time. The clause is only allowed + for temporary variables. The <literal>ON TRANSACTION END RESET</literal> + clause causes the session variable to be reset to its default value when + the transaction is committed or rolled back. + </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> + <member><xref linkend="sql-let"/></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/discard.sgml b/doc/src/sgml/ref/discard.sgml index bf44c523ca..6f90672afa 100644 --- a/doc/src/sgml/ref/discard.sgml +++ b/doc/src/sgml/ref/discard.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP } +DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP | VARIABLES } </synopsis> </refsynopsisdiv> @@ -75,6 +75,17 @@ DISCARD { ALL | PLANS | SEQUENCES | TEMPORARY | TEMP } </listitem> </varlistentry> + <varlistentry> + <term><literal>VARIABLES</literal></term> + <listitem> + <para> + Resets the value of all session variables. If a variable + is later reused, it is re-initialized to either + <literal>NULL</literal> or its default value. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>ALL</literal></term> <listitem> @@ -93,6 +104,7 @@ SELECT pg_advisory_unlock_all(); DISCARD PLANS; DISCARD TEMP; DISCARD SEQUENCES; +DISCARD VARIABLES; </programlisting></para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/drop_variable.sgml b/doc/src/sgml/ref/drop_variable.sgml new file mode 100644 index 0000000000..67988b5fcd --- /dev/null +++ b/doc/src/sgml/ref/drop_variable.sgml @@ -0,0 +1,118 @@ +<!-- +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> + <member><xref linkend="sql-let"/></member> + </simplelist> + </refsect1> + +</refentry> diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml index dea19cd348..e2191b7767 100644 --- a/doc/src/sgml/ref/grant.sgml +++ b/doc/src/sgml/ref/grant.sgml @@ -108,6 +108,15 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace | CURRENT_ROLE | CURRENT_USER | SESSION_USER + +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> ] + + TO <replaceable class="parameter">role_specification</replaceable> [, ...] [ WITH GRANT OPTION ] + </synopsis> </refsynopsisdiv> diff --git a/doc/src/sgml/ref/let.sgml b/doc/src/sgml/ref/let.sgml new file mode 100644 index 0000000000..c5d1e4d962 --- /dev/null +++ b/doc/src/sgml/ref/let.sgml @@ -0,0 +1,109 @@ +<!-- +doc/src/sgml/ref/let.sgml +PostgreSQL documentation +--> + +<refentry id="sql-let"> + <indexterm zone="sql-let"> + <primary>LET</primary> + </indexterm> + + <indexterm> + <primary>session variable</primary> + <secondary>changing</secondary> + </indexterm> + + <refmeta> + <refentrytitle>LET</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>LET</refname> + <refpurpose>change a session variable's value</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +LET <replaceable class="parameter">session_variable</replaceable> = <replaceable class="parameter">sql_expression</replaceable> +LET <replaceable class="parameter">session_variable</replaceable> = DEFAULT +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + The <command>LET</command> command assigns a value to the specified session + variable. + </para> + + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><literal>session_variable</literal></term> + <listitem> + <para> + The name of the session variable. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>sql_expression</literal></term> + <listitem> + <para> + An SQL expression (can be subquery in parenthesis). The result must + be of castable to the same data type as the session variable (in + implicit or assignment context). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT</literal></term> + <listitem> + <para> + Reset the session variable to its default value, if that is defined. + If no explicit default value has been declared, the session variable + is set to NULL. + </para> + </listitem> + </varlistentry> + </variablelist> + + <para> + Example: +<programlisting> +CREATE VARIABLE myvar AS integer; +LET myvar = 10; +LET myvar = (SELECT sum(val) FROM tab); +LET myvar = DEFAULT; +</programlisting> + </para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + The <command>LET</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> + <member><xref linkend="sql-dropvariable"/></member> + </simplelist> + </refsect1> +</refentry> diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 47bd7dbda0..39cb595647 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -106,6 +106,17 @@ PostgreSQL documentation </listitem> </varlistentry> + <varlistentry> + <term><option>-A <replaceable class="parameter">session_variable</replaceable></option></term> + <term><option>--variable=<replaceable class="parameter">session_variable</replaceable></option></term> + <listitem> + <para> + Restore a named session variable only. Multiple session variables may + be specified with multiple <option>-A</option> switches. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><option>-c</option></term> <term><option>--clean</option></term> diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml index 4fd4bfb3d7..0579b4599d 100644 --- a/doc/src/sgml/ref/revoke.sgml +++ b/doc/src/sgml/ref/revoke.sgml @@ -137,6 +137,13 @@ REVOKE [ { ADMIN | INHERIT } 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 a3b743e8c1..f5ec3c987b 100644 --- a/doc/src/sgml/reference.sgml +++ b/doc/src/sgml/reference.sgml @@ -75,6 +75,7 @@ &alterType; &alterUser; &alterUserMapping; + &alterVariable; &alterView; &analyze; &begin; @@ -127,6 +128,7 @@ &createType; &createUser; &createUserMapping; + &createVariable; &createView; &deallocate; &declare; @@ -175,6 +177,7 @@ &dropType; &dropUser; &dropUserMapping; + &dropVariable; &dropView; &end; &execute; @@ -183,6 +186,7 @@ &grant; &importForeignSchema; &insert; + &let; &listen; &load; &lock; -- 2.37.2 --gcms4zhgklsgym3k-- ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-04-04 18:23 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-01-12 18:05 Re: is ErrorResponse possible on Sync? Tom Lane <[email protected]> 2022-01-12 18:18 ` Andrei Matei <[email protected]> 2022-01-12 23:51 ` Tatsuo Ishii <[email protected]> 2022-04-04 18:23 [PATCH 11/11] documentation [email protected] <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox