Re: [HACKERS] read-only database - Mailing list pgsql-patches

Satoshi Nagayasu wrote:
>
> Tom Lane wrote:
> > I'd view this as a postmaster state that propagates to backends.
> > Probably you'd enable it by means of a postmaster option, and the
> > only way to get out of it is to shut down and restart the postmaster
> > without the option.
>
> I've created a patch to make a postmaster read-only.
> (attached patch can be applied to 8.0.1)
>
> Read-only state can be enabled/disabled by the postmaster option,
> or the postgresql.conf option.
>
> If you start the postmaster with "-r" options,
> the cluster will go to read-only.
>
> % pg_ctl -o "-i -r" -D $PGDATA start
>
> Or if you set "readonly_cluster = true" in the postgresql.conf,
> the cluster will also become read-only.

Nice idea.  I have attached a new patch which has a few adjustments.

First, we are moving away from using postmaster flags, and instead
encouraging people to use postgresql.conf, so I removed the -r flag but
added an entry in postgresql.conf for this.  I can see why it might be
nice to have it as a postmaster flag, but at a certain point the number
of flags gets too confusing so postgresql.conf is better.  Second, I
changed it so it can be modified by a sighup to the postmaster, which
can't be done with a postmaster flag.

Also, I renamed it to server_read_only because that seems more
consistent than readonly_cluster.

Also, I added documentation for this postgresql.conf variable.

With this change, I see we now have three read_only options:

    transaction_read_only
    default_transaction_read_only
    server_read_only

The first one is not documented (should it be?) and I assume allows you
to query and change the READ ONLY status of a single transaction, while
default_transaction_read_only affects all new transactions for the
session, and server_read_only is for all transactions on the server.

It seems server_read_only is the same as default_transaction_read_only
except it can't be changed.  It seems more like a secure version of
default_transaction_read_only rather than something new.

If we set default_transaction_read_only to true in postgresql.conf,
could we just prevent that from being changed by a session.  As I
remember we have abandoned the idea of trying to limit session changes
to postgresql.conf values so maybe this is the way we have to go.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.315
diff -c -c -r1.315 runtime.sgml
*** doc/src/sgml/runtime.sgml    23 Apr 2005 03:27:40 -0000    1.315
--- doc/src/sgml/runtime.sgml    6 May 2005 22:48:41 -0000
***************
*** 3224,3229 ****
--- 3224,3247 ----
        </listitem>
       </varlistentry>

+      <varlistentry id="guc-server-read-only" xreflabel="server_read_only">
+       <indexterm>
+        <primary>force read-only transaction</primary>
+       </indexterm>
+       <indexterm>
+        <primary><varname>server_read_only</> configuration parameter</primary>
+       </indexterm>
+
+       <term><varname>server_read_only</varname> (<type>boolean</type>)</term>
+       <listitem>
+        <para>
+         This parameter behaves just like <varname>default_read_only</>
+         except it can only be set from <filename>postgresql.conf</> and is server-wide.
+         The default is false (read/write).
+        </para>
+       </listitem>
+      </varlistentry>
+
       <varlistentry id="guc-statement-timeout" xreflabel="statement_timeout">
        <term><varname>statement_timeout</varname> (<type>integer</type>)</term>
        <indexterm>
Index: src/backend/executor/execMain.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/executor/execMain.c,v
retrieving revision 1.248
diff -c -c -r1.248 execMain.c
*** src/backend/executor/execMain.c    6 May 2005 17:24:53 -0000    1.248
--- src/backend/executor/execMain.c    6 May 2005 22:48:42 -0000
***************
*** 130,136 ****
       * If the transaction is read-only, we need to check if any writes are
       * planned to non-temporary tables.
       */
!     if (XactReadOnly && !explainOnly)
          ExecCheckXactReadOnly(queryDesc->parsetree);

      /*
--- 130,136 ----
       * If the transaction is read-only, we need to check if any writes are
       * planned to non-temporary tables.
       */
!     if ( (XactReadOnly || ServerReadOnly) && !explainOnly)
          ExecCheckXactReadOnly(queryDesc->parsetree);

      /*
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.236
diff -c -c -r1.236 utility.c
*** src/backend/tcop/utility.c    28 Apr 2005 21:47:15 -0000    1.236
--- src/backend/tcop/utility.c    6 May 2005 22:48:45 -0000
***************
*** 265,271 ****
  static void
  check_xact_readonly(Node *parsetree)
  {
!     if (!XactReadOnly)
          return;

      /*
--- 265,271 ----
  static void
  check_xact_readonly(Node *parsetree)
  {
!     if (!XactReadOnly && !ServerReadOnly)
          return;

      /*
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.261
diff -c -c -r1.261 guc.c
*** src/backend/utils/misc/guc.c    1 May 2005 18:56:19 -0000    1.261
--- src/backend/utils/misc/guc.c    6 May 2005 22:48:48 -0000
***************
*** 142,147 ****
--- 142,149 ----

  bool        default_with_oids = false;

+ bool        ServerReadOnly = false;
+
  int            log_min_error_statement = PANIC;
  int            log_min_messages = NOTICE;
  int            client_min_messages = NOTICE;
***************
*** 794,799 ****
--- 796,809 ----
          false, assign_transaction_read_only, NULL
      },
      {
+         {"server_read_only", PGC_SIGHUP, CLIENT_CONN_STATEMENT,
+             gettext_noop("Forces all transactions to be read-only."),
+             NULL
+         },
+         &ServerReadOnly,
+         false, NULL, NULL
+     },
+     {
          {"add_missing_from", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
              gettext_noop("Automatically adds missing table references to FROM clauses."),
              NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.140
diff -c -c -r1.140 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    21 Apr 2005 19:18:13 -0000    1.140
--- src/backend/utils/misc/postgresql.conf.sample    6 May 2005 22:48:48 -0000
***************
*** 278,283 ****
--- 278,284 ----
  #check_function_bodies = true
  #default_transaction_isolation = 'read committed'
  #default_transaction_read_only = false
+ #server_read_only = false
  #statement_timeout = 0        # 0 is disabled, in milliseconds

  # - Locale and Formatting -
Index: src/include/postmaster/postmaster.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/postmaster/postmaster.h,v
retrieving revision 1.9
diff -c -c -r1.9 postmaster.h
*** src/include/postmaster/postmaster.h    31 Dec 2004 22:03:39 -0000    1.9
--- src/include/postmaster/postmaster.h    6 May 2005 22:48:49 -0000
***************
*** 34,40 ****
  extern HANDLE PostmasterHandle;
  #endif

-
  extern int    PostmasterMain(int argc, char *argv[]);
  extern void ClosePostmasterPorts(bool am_syslogger);

--- 34,39 ----
Index: src/include/utils/guc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/guc.h,v
retrieving revision 1.60
diff -c -c -r1.60 guc.h
*** src/include/utils/guc.h    25 Mar 2005 16:17:28 -0000    1.60
--- src/include/utils/guc.h    6 May 2005 22:48:49 -0000
***************
*** 121,126 ****
--- 121,128 ----

  extern bool default_with_oids;

+ extern bool ServerReadOnly;
+
  extern int    log_min_error_statement;
  extern int    log_min_messages;
  extern int    client_min_messages;