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;
/*