Proposed patch: synchronized_scanning GUC variable - Mailing list pgsql-patches

Per today's -hackers discussion, add a GUC variable to allow clients to
disable the new synchronized-scanning behavior, and make pg_dump disable
sync scans so that it will reliably preserve row ordering.  This is a
pretty trivial patch, but seeing how late we are in the 8.3 release
cycle, I thought I'd better post it for comment anyway.

            regards, tom lane

Index: doc/src/sgml/config.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.162
diff -c -r1.162 config.sgml
*** doc/src/sgml/config.sgml    27 Jan 2008 19:12:28 -0000    1.162
--- doc/src/sgml/config.sgml    27 Jan 2008 20:00:16 -0000
***************
*** 4611,4616 ****
--- 4611,4638 ----
        </listitem>
       </varlistentry>

+      <varlistentry id="guc-synchronized-scanning" xreflabel="synchronized_scanning">
+       <term><varname>synchronized_scanning</varname> (<type>boolean</type>)</term>
+       <indexterm>
+        <primary><varname>synchronized_scanning</> configuration parameter</primary>
+       </indexterm>
+       <listitem>
+        <para>
+         This allows sequential scans of large tables to synchronize with each
+         other, so that concurrent scans read the same block at about the
+         same time and hence share the I/O workload.  When this is enabled,
+         a scan might start in the middle of the table and then <quote>wrap
+         around</> the end to cover all rows, so as to synchronize with the
+         activity of scans already in progress.  This can result in
+         unpredictable changes in the row ordering returned by queries that
+         have no <literal>ORDER BY</> clause.  Setting this parameter to
+         <literal>off</> ensures the pre-8.3 behavior in which a sequential
+         scan always starts from the beginning of the table.  The default
+         is <literal>on</>.
+        </para>
+       </listitem>
+      </varlistentry>
+
       </variablelist>
      </sect2>

Index: src/backend/access/heap/heapam.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/heap/heapam.c,v
retrieving revision 1.248
diff -c -r1.248 heapam.c
*** src/backend/access/heap/heapam.c    14 Jan 2008 01:39:09 -0000    1.248
--- src/backend/access/heap/heapam.c    27 Jan 2008 20:00:18 -0000
***************
*** 59,64 ****
--- 59,68 ----
  #include "utils/syscache.h"


+ /* GUC variable */
+ bool    synchronized_scanning = true;
+
+
  static HeapScanDesc heap_beginscan_internal(Relation relation,
                          Snapshot snapshot,
                          int nkeys, ScanKey key,
***************
*** 104,110 ****
       * the thresholds for these features could be different, we make them the
       * same so that there are only two behaviors to tune rather than four.
       * (However, some callers need to be able to disable one or both of
!      * these behaviors, independently of the size of the table.)
       *
       * During a rescan, don't make a new strategy object if we don't have to.
       */
--- 108,115 ----
       * the thresholds for these features could be different, we make them the
       * same so that there are only two behaviors to tune rather than four.
       * (However, some callers need to be able to disable one or both of
!      * these behaviors, independently of the size of the table; also there
!      * is a GUC variable that can disable synchronized scanning.)
       *
       * During a rescan, don't make a new strategy object if we don't have to.
       */
***************
*** 129,135 ****
          scan->rs_strategy = NULL;
      }

!     if (allow_sync)
      {
          scan->rs_syncscan = true;
          scan->rs_startblock = ss_get_location(scan->rs_rd, scan->rs_nblocks);
--- 134,140 ----
          scan->rs_strategy = NULL;
      }

!     if (allow_sync && synchronized_scanning)
      {
          scan->rs_syncscan = true;
          scan->rs_startblock = ss_get_location(scan->rs_rd, scan->rs_nblocks);
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.431
diff -c -r1.431 guc.c
*** src/backend/utils/misc/guc.c    27 Jan 2008 19:12:28 -0000    1.431
--- src/backend/utils/misc/guc.c    27 Jan 2008 20:00:18 -0000
***************
*** 110,115 ****
--- 110,116 ----
  extern int    CommitSiblings;
  extern char *default_tablespace;
  extern char *temp_tablespaces;
+ extern bool synchronized_scanning;
  extern bool fullPageWrites;

  #ifdef TRACE_SORT
***************
*** 1053,1058 ****
--- 1054,1068 ----
      },

      {
+         {"synchronized_scanning", PGC_USERSET, COMPAT_OPTIONS_PREVIOUS,
+             gettext_noop("Enable synchronized scans."),
+             NULL
+         },
+         &synchronized_scanning,
+         true, NULL, NULL
+     },
+
+     {
          {"archive_mode", PGC_POSTMASTER, WAL_SETTINGS,
              gettext_noop("Allows archiving of WAL files using archive_command."),
              NULL
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.235
diff -c -r1.235 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample    27 Jan 2008 19:12:28 -0000    1.235
--- src/backend/utils/misc/postgresql.conf.sample    27 Jan 2008 20:00:18 -0000
***************
*** 476,484 ****
  #backslash_quote = safe_encoding    # on, off, or safe_encoding
  #default_with_oids = off
  #escape_string_warning = on
- #standard_conforming_strings = off
  #regex_flavor = advanced        # advanced, extended, or basic
  #sql_inheritance = on

  # - Other Platforms and Clients -

--- 476,485 ----
  #backslash_quote = safe_encoding    # on, off, or safe_encoding
  #default_with_oids = off
  #escape_string_warning = on
  #regex_flavor = advanced        # advanced, extended, or basic
  #sql_inheritance = on
+ #standard_conforming_strings = off
+ #synchronized_scanning = on

  # - Other Platforms and Clients -

Index: src/bin/pg_dump/pg_dump.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.481
diff -c -r1.481 pg_dump.c
*** src/bin/pg_dump/pg_dump.c    1 Jan 2008 19:45:55 -0000    1.481
--- src/bin/pg_dump/pg_dump.c    27 Jan 2008 20:00:18 -0000
***************
*** 553,558 ****
--- 553,572 ----
      do_sql_command(g_conn, "SET DATESTYLE = ISO");

      /*
+      * If supported, set extra_float_digits so that we can dump float data
+      * exactly (given correctly implemented float I/O code, anyway)
+      */
+     if (g_fout->remoteVersion >= 70400)
+         do_sql_command(g_conn, "SET extra_float_digits TO 2");
+
+     /*
+      * If synchronized scanning is supported, disable it, to prevent
+      * unpredictable changes in row ordering across a dump and reload.
+      */
+     if (g_fout->remoteVersion >= 80300)
+         do_sql_command(g_conn, "SET synchronized_scanning TO off");
+
+     /*
       * Start serializable transaction to dump consistent data.
       */
      do_sql_command(g_conn, "BEGIN");
***************
*** 567,579 ****
      else
          username_subquery = "SELECT usename FROM pg_user WHERE usesysid =";

-     /*
-      * If supported, set extra_float_digits so that we can dump float data
-      * exactly (given correctly implemented float I/O code, anyway)
-      */
-     if (g_fout->remoteVersion >= 70400)
-         do_sql_command(g_conn, "SET extra_float_digits TO 2");
-
      /* Find the last built-in OID, if needed */
      if (g_fout->remoteVersion < 70300)
      {
--- 581,586 ----