Re: Allow SET to not start a transaction - Mailing list pgsql-patches

OK, I have finished the patch and applied it, with doc updates.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> We have on the open items list:
>
>     Make SET not start a transaction with autocommit off, document it
>
> The attached patch does exactly that (though without documentation).  It
> checks for the TRANS_DEFAULT transaction state, which is the state right
> after a COMMIT.  If the backend is in that state, and autocommit is off,
> and the command is SET/SHOW/RESET, then a COMMIT will be forced, rather
> than keeping the transaction open for the next command.  It seems to
> work, but i want to test it more before applying to CVS.
>
> ---------------------------------------------------------------------------
>
> test=> set autocommit = off;
> SET
> test=> commit;
> WARNING:  COMMIT: no transaction in progress
> COMMIT
> test=> set statement_timeout = 9999999;
> SET
> test=> select 1;
>  ?column?
> ----------
>         1
> (1 row)
>
> test=> abort;
> ROLLBACK
> test=> show statement_timeout;
>  statement_timeout
> -------------------
>  9999999
> (1 row)
>

--
  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-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.143
diff -c -c -r1.143 runtime.sgml
*** doc/src/sgml/runtime.sgml    3 Oct 2002 02:26:49 -0000    1.143
--- doc/src/sgml/runtime.sgml    9 Oct 2002 04:55:22 -0000
***************
*** 1235,1250 ****
      that is not inside an explicit transaction block (that is, unless a
      <command>BEGIN</> with no matching <command>COMMIT</> has been
      given).
!     If set to false, <productname>PostgreSQL</productname> will commit
!     the effects of commands only on receiving an explicit
!     <command>COMMIT</> command.  This mode can also be thought of as
!     implicitly issuing <command>BEGIN</> whenever a command is received
!     and <productname>PostgreSQL</productname> is not already inside
!     a transaction block.
!     The default is true, for compatibility with historical
!     <productname>PostgreSQL</productname> behavior.  But for maximum
!     compatibility with the SQL specification, set it to false.
         </para>
        </listitem>
       </varlistentry>

--- 1235,1266 ----
      that is not inside an explicit transaction block (that is, unless a
      <command>BEGIN</> with no matching <command>COMMIT</> has been
      given).
!     If set to false, <productname>PostgreSQL</productname> will
!     commit the commands only when receiving an explicit
!     <command>COMMIT</> command. This mode can also be thought of as
!     implicitly issuing <command>BEGIN</> whenever a command is
!     received that is not already inside a transaction block. The
!     default is true, for compatibility with historical
!     <productname>PostgreSQL</productname> behavior. However, for
!     maximum compatibility with the SQL specification, set it to
!     false.
         </para>
+        <note>
+         <para>
+      With <varname>autocommit</> set to false, <command>SET</>,
+      <command>SHOW</>, and <command>RESET</> do not start new
+      transaction blocks. They are run in their own transactions.
+      Once another command is issued, multi-statement transaction
+      behavior begins and any <command>SET</>, <command>SHOW</>, or
+      <command>RESET</> commands are considered to be part of the
+      transaction, i.e. they are committed or rolled back depending
+      on the completion status of the transaction. To have
+      <command>SET</>, <command>SHOW</>, and <command>RESET</>
+      commands at the start of a transaction, use <command>BEGIN</>
+      first.
+         </para>
+        </note>
+
        </listitem>
       </varlistentry>

Index: doc/src/sgml/ref/reset.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/reset.sgml,v
retrieving revision 1.15
diff -c -c -r1.15 reset.sgml
*** doc/src/sgml/ref/reset.sgml    21 Sep 2002 18:32:54 -0000    1.15
--- doc/src/sgml/ref/reset.sgml    9 Oct 2002 04:55:23 -0000
***************
*** 66,71 ****
--- 66,77 ----
     switches, or per-database or per-user default settings.  See the
     <citetitle>Administrator's Guide</citetitle> for details.
    </para>
+
+   <para>
+    See the <command>SHOW</> manual page for details on the transaction
+    behavior of <command>RESET</>.
+   </para>
+
   </refsect1>

   <refsect1>
Index: doc/src/sgml/ref/set.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/set.sgml,v
retrieving revision 1.65
diff -c -c -r1.65 set.sgml
*** doc/src/sgml/ref/set.sgml    21 Sep 2002 18:32:54 -0000    1.65
--- doc/src/sgml/ref/set.sgml    9 Oct 2002 04:55:25 -0000
***************
*** 109,114 ****
--- 109,120 ----
    </para>

    <para>
+    With <literal>autocommit</> set to <literal>off</>, <command>SET</>
+    does not start a new transaction block. See the
+    <literal>autocommit</> section of the documentation for details.
+   </para>
+
+   <para>
     Here are additional details about a few of the parameters that can be set:

     <variablelist>
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/tcop/postgres.c,v
retrieving revision 1.299
diff -c -c -r1.299 postgres.c
*** src/backend/tcop/postgres.c    8 Oct 2002 17:17:19 -0000    1.299
--- src/backend/tcop/postgres.c    9 Oct 2002 04:55:35 -0000
***************
*** 76,81 ****
--- 76,82 ----
  CommandDest whereToSendOutput = Debug;

  extern int    StatementTimeout;
+ extern bool autocommit;

  static bool dontExecute = false;

***************
*** 122,128 ****
  static List *pg_parse_query(StringInfo query_string, Oid *typev, int nargs);
  static List *pg_analyze_and_rewrite(Node *parsetree);
  static void start_xact_command(void);
! static void finish_xact_command(void);
  static void SigHupHandler(SIGNAL_ARGS);
  static void FloatExceptionHandler(SIGNAL_ARGS);
  static const char *CreateCommandTag(Node *parsetree);
--- 123,129 ----
  static List *pg_parse_query(StringInfo query_string, Oid *typev, int nargs);
  static List *pg_analyze_and_rewrite(Node *parsetree);
  static void start_xact_command(void);
! static void finish_xact_command(bool forceCommit);
  static void SigHupHandler(SIGNAL_ARGS);
  static void FloatExceptionHandler(SIGNAL_ARGS);
  static const char *CreateCommandTag(Node *parsetree);
***************
*** 825,831 ****
               */
              if (isTransactionStmt)
              {
!                 finish_xact_command();
                  xact_started = false;
              }
          }                        /* end loop over queries generated from a
--- 826,832 ----
               */
              if (isTransactionStmt)
              {
!                 finish_xact_command(false);
                  xact_started = false;
              }
          }                        /* end loop over queries generated from a
***************
*** 843,849 ****
           */
          if (lnext(parsetree_item) == NIL && xact_started)
          {
!             finish_xact_command();
              xact_started = false;
          }

--- 844,862 ----
           */
          if (lnext(parsetree_item) == NIL && xact_started)
          {
!             /*
!              *    Don't allow SET/SHOW/RESET to start a new transaction
!              *    with autocommit off.  We do this by forcing a COMMIT
!              *    when these commands start a transaction.
!              */
!             if (autocommit ||
!                 IsTransactionState() ||
!                 (strcmp(commandTag, "SET") != 0 &&
!                  strcmp(commandTag, "SHOW") != 0 &&
!                  strcmp(commandTag, "RESET") != 0))
!                 finish_xact_command(false);
!             else
!                 finish_xact_command(true);
              xact_started = false;
          }

***************
*** 878,884 ****
       * will only happen if the querystring was empty.)
       */
      if (xact_started)
!         finish_xact_command();

      if (save_Log_duration)
      {
--- 891,897 ----
       * will only happen if the querystring was empty.)
       */
      if (xact_started)
!         finish_xact_command(false);

      if (save_Log_duration)
      {
***************
*** 907,913 ****
  }

  static void
! finish_xact_command(void)
  {
      /* Invoke IMMEDIATE constraint triggers */
      DeferredTriggerEndQuery();
--- 920,926 ----
  }

  static void
! finish_xact_command(bool forceCommit)
  {
      /* Invoke IMMEDIATE constraint triggers */
      DeferredTriggerEndQuery();
***************
*** 915,921 ****
      /* Now commit the command */
      elog(DEBUG1, "CommitTransactionCommand");

!     CommitTransactionCommand(false);

  #ifdef SHOW_MEMORY_STATS
      /* Print mem stats at each commit for leak tracking */
--- 928,934 ----
      /* Now commit the command */
      elog(DEBUG1, "CommitTransactionCommand");

!     CommitTransactionCommand(forceCommit);

  #ifdef SHOW_MEMORY_STATS
      /* Print mem stats at each commit for leak tracking */
***************
*** 1923,1929 ****
                  }

                  /* commit the function-invocation transaction */
!                 finish_xact_command();
                  break;

                  /*
--- 1936,1942 ----
                  }

                  /* commit the function-invocation transaction */
!                 finish_xact_command(false);
                  break;

                  /*