User and database-specific session defaults for run-time configuration

variables.  New commands ALTER DATABASE ... SET and ALTER USER ... SET.

7 files changed, 248 insertions, 11 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 20adc19bab8..aab0a906d9b 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
- $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.30 2002/02/18 23:10:59 petere Exp $
+ $Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.31 2002/03/01 22:45:03 petere Exp $
  -->
 
 <chapter id="catalogs">
@@ -889,6 +889,13 @@
        or an absolute path, depending how it was entered.
       </entry>
      </row>
+
+     <row>
+      <entry>datconfig</entry>
+      <entry><type>text[]</type></entry>
+      <entry></entry>
+      <entry>Session defaults for run-time configuration variables</entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -1980,6 +1987,13 @@
       <entry></entry>
       <entry>Account expiry time (only used for password authentication)</entry>
      </row>
+
+     <row>
+      <entry>useconfig</entry>
+      <entry><type>text[]</type></entry>
+      <entry></entry>
+      <entry>Session defaults for run-time configuration variables</entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index ef29c0e8b98..94f1226c24f 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.32 2002/01/15 05:05:49 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.33 2002/03/01 22:45:07 petere Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -37,6 +37,7 @@ Complete list of usable sgml source files in this directory.
 
 <!-- SQL commands -->
 <!entity abort              system "abort.sgml">
+<!entity alterDatabase      system "alter_database.sgml">
 <!entity alterGroup         system "alter_group.sgml">
 <!entity alterTable         system "alter_table.sgml">
 <!entity alterUser          system "alter_user.sgml">
diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml
new file mode 100644
index 00000000000..b1aa34262a1
--- /dev/null
+++ b/doc/src/sgml/ref/alter_database.sgml
@@ -0,0 +1,169 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_database.sgml,v 1.1 2002/03/01 22:45:07 petere Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-ALTERDATABASE">
+ <refmeta>
+  <refentrytitle id="sql-alterdatabase-title">ALTER DATABASE</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>ALTER DATABASE</refname>
+  <refpurpose>change a database</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>variable</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER DATABASE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>variable</replaceable>
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <command>ALTER DATABASE</command> is used to change the session
+   default of a run-time configuration variable for a
+   <productname>PostgreSQL</productname> database. Whenever a new
+   session is subsequently started, <literal>SET
+   <replaceable>variable</replaceable> TO
+   <replaceable>value</replaceable></literal> is effectively executed
+   before the start of the session.
+  </para>
+
+  <para>
+   Only a database owner can change the session defaults for a
+   database.  Superusers can change the session defaults of any
+   database.
+  </para>
+
+  <refsect2>
+   <title>Parameters</title>
+
+   <para>
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="PARAMETER">name</replaceable></term>
+      <listitem>
+       <para>
+	The name of the database whose session defaults are to be altered.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable>variable</replaceable></term>
+      <term><replaceable>value</replaceable></term>
+      <listitem>
+       <para>
+        Set the session default for this database of the specified
+        configuration variable to the given value.  If
+        <replaceable>value</replaceable> is <literal>DEFAULT</literal>
+        or, equivalently, <literal>RESET</literal> is used, the
+        database-specific variable setting is removed and the default
+        setting will be inherited in new sessions.  Use <literal>RESET
+        ALL</literal> to clear all settings.
+       </para>
+
+       <para>
+        See <xref linkend="sql-set" endterm="sql-set-title"> and the
+        <citetitle>Administrator's Guide</citetitle> for more
+        information about allowed variable names and values.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+ </refsect1>
+
+ <refsect1>
+  <title>Diagnostics</title>
+
+  <para>
+   <variablelist>
+    <varlistentry>
+     <term><computeroutput>ALTER DATABASE</computeroutput></term>
+     <listitem>
+      <para>
+       Message returned if the alteration was successful.
+      </para>
+     </listitem>
+    </varlistentry>
+     
+    <varlistentry>
+     <term><computeroutput>ERROR:  database "dbname" does not exist</computeroutput></term>
+     <listitem>
+      <para>
+       Error message returned if the specified database is not known
+       to the system.
+      </para>
+     </listitem>
+    </varlistentry>
+   </variablelist>
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   Using <xref linkend="sql-alteruser" endterm="sql-alteruser-title">,
+   it is also possible to tie a session default to a specific user
+   rather than a database.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Examples</title>
+
+  <para>
+   To disable index scans by default in the database
+   <literal>test</literal>:
+
+<programlisting>
+ALTER DATABASE test SET enable_indexscan TO off;
+</programlisting>
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+    
+  <para>
+   The <command>ALTER DATABASE</command> statement is a
+   <productname>PostgreSQL</productname> extension.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-alteruser" endterm="sql-alteruser-title"></member>
+   <member><xref linkend="sql-createdatabase" endterm="sql-createdatabase-title"></member>
+   <member><xref linkend="sql-dropdatabase" endterm="sql-dropdatabase-title"></member>
+   <member><xref linkend="sql-set" endterm="sql-set-title"></member>
+  </simplelist>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml
index 0156565eb50..3819bf8d964 100644
--- a/doc/src/sgml/ref/alter_user.sgml
+++ b/doc/src/sgml/ref/alter_user.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_user.sgml,v 1.19 2002/02/27 21:14:53 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/alter_user.sgml,v 1.20 2002/03/01 22:45:07 petere Exp $
 PostgreSQL documentation
 -->
 
@@ -24,6 +24,9 @@ where <replaceable class="PARAMETER">option</replaceable> can be:
     | CREATEDB | NOCREATEDB
     | CREATEUSER | NOCREATEUSER 
     | VALID UNTIL '<replaceable class="PARAMETER">abstime</replaceable>'
+
+ALTER USER <replaceable class="PARAMETER">username</replaceable> SET <replaceable>variable</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
+ALTER USER <replaceable class="PARAMETER">username</replaceable> RESET <replaceable>variable</replaceable>
 </synopsis>
  </refsynopsisdiv>
 
@@ -37,9 +40,22 @@ where <replaceable class="PARAMETER">option</replaceable> can be:
   </para>
 
   <para>
-   Only a database superuser can change privileges and password
-   expiration with this command. Ordinary users can only change their
-   own password.
+   The first variant of this command in the synopsis changes certain
+   global user privileges and authentication settings.  (See below for
+   details.)  Only a database superuser can change privileges and
+   password expiration with this command.  Ordinary users can only
+   change their own password.
+  </para>
+
+  <para>
+   The second and the third variant change a user's session default of
+   a specified configuration variable.  Whenever the user subsequently
+   starts a new session, <literal>SET
+   <replaceable>variable</replaceable> TO
+   <replaceable>value</replaceable></literal> is effectively executed
+   before the start of the session.  Ordinary users can change their
+   own session defaults.  Superusers can change anyone's session
+   defaults.
   </para>
 
   <refsect2>
@@ -113,6 +129,28 @@ where <replaceable class="PARAMETER">option</replaceable> can be:
        </para>
       </listitem>
      </varlistentry>
+
+     <varlistentry>
+      <term><replaceable>variable</replaceable></term>
+      <term><replaceable>value</replaceable></term>
+      <listitem>
+       <para>
+        Set this user's session default of the specified configuration
+        variable to the given value.  If
+        <replaceable>value</replaceable> is <literal>DEFAULT</literal>
+        or, equivalently, <literal>RESET</literal> is used, the
+        user-specific variable setting is removed and the user will
+        inherit the default setting in new sessions.  Use
+        <literal>RESET ALL</literal> to clear all settings.
+       </para>
+
+       <para>
+        See <xref linkend="sql-set" endterm="sql-set-title"> and the
+        <citetitle>Administrator's Guide</citetitle> for more
+        information about allowed variable names and values.
+       </para>
+      </listitem>
+     </varlistentry>
     </variablelist>
    </para>
   </refsect2>
@@ -159,6 +197,12 @@ where <replaceable class="PARAMETER">option</replaceable> can be:
    Use <xref linkend="SQL-ALTERGROUP" endterm="SQL-ALTERGROUP-title">
    to do that.
   </para>
+
+  <para>
+   Using <xref linkend="sql-alterdatabase"
+   endterm="sql-alterdatabase-title">, it is also possible to tie a
+   session default to a specific database rather than a user.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -214,6 +258,7 @@ ALTER USER miriam CREATEUSER CREATEDB;
   <simplelist type="inline">
    <member><xref linkend="sql-createuser" endterm="sql-createuser-title"></member>
    <member><xref linkend="sql-dropuser" endterm="sql-dropuser-title"></member>
+   <member><xref linkend="sql-set" endterm="sql-set-title"></member>
   </simplelist>
  </refsect1>
 </refentry>
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index a5b8cfbf163..27f218d33fb 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
 <!-- reference.sgml
-$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.20 2002/01/15 05:05:49 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.21 2002/03/01 22:45:04 petere Exp $
 
 PostgreSQL Reference Manual
 -->
@@ -46,6 +46,7 @@ PostgreSQL Reference Manual
   </partintro>
 
    &abort;
+   &alterDatabase;
    &alterGroup;
    &alterTable;
    &alterUser;
diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml
index edcdc9405aa..3801b8d1eab 100644
--- a/doc/src/sgml/release.sgml
+++ b/doc/src/sgml/release.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.118 2002/02/24 20:20:19 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.119 2002/03/01 22:45:04 petere Exp $
 -->
 
 <appendix id="release">
@@ -28,6 +28,8 @@ Access privileges on functions
 Access privileges on procedural languages
 CREATE DATABASE has OWNER option so superuser can create DB for someone else
 Kerberos 5 support now works with Heimdal
+Database and user-specific session defaults of run-time configurations variables
+    (ALTER DATABASE ... SET and ALTER USER ... SET)
 ]]></literallayout>
 
  </sect1>
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 2c0441fdcc9..3f46a3e9e4f 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.103 2002/01/20 22:19:56 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.104 2002/03/01 22:45:05 petere Exp $
 -->
 
 <Chapter Id="runtime">
@@ -537,12 +537,17 @@ env PGOPTIONS='-c geqo=off' psql
    </para>
 
    <para>
-    Finally, some options can be changed in individual SQL sessions
-    with the <command>SET</command> command, for example
+    Some options can be changed in individual SQL sessions with the
+    <command>SET</command> command, for example
 <screen>
 =&gt; <userinput>SET ENABLE_SEQSCAN TO OFF;</userinput>
 </screen>
     See the SQL command language reference for details on the syntax.
+    Furthermore, it is possible to assign a set of option settings to
+    a user or a database.  Whenever a session is started, the default
+    settings for the user and database involved are loaded.  The
+    commands <literal>ALTER DATABASE</literal> and <literal>ALTER
+    USER</literal>, respectively, are used to set this up.
    </para>
 
    <sect2 id="runtime-config-optimizer">