Re: A bad behavior under autocommit off mode - Mailing list pgsql-hackers

OK, I have a patch to fix this bug.  The basic problem is that when a
multi-query string is passed to the backend, it is treated as a single
transaction _unless_ a transaction or GUC command appears in the string.
When they appear, a transaction is forced, but the normal transaction
state machine has been bypassed, meaning in:

    SET autocommit TO off; SELECT 1; COMMIT;

when the COMMIT arrives, the transaction state machines hasn't seen the
SELECT because the mechanism is bypassing the state machine to try and
get everything into the same transaction.

This patch removes that "stuff all queries into a single transaction"
behavior and makes them function just like queries arriving separately.
This does BREAK BACKWARD COMPATIBILITY.  However, if they want the old
behavior, they just need to wrap BEGIN/COMMIT around the query string.

I could have fixed it with a hack to the transaction state machine, but
this seems like the proper fix.  I never liked that single-transaction
query string behavior anyway.  It seemed too strange.

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

Hiroshi Inoue wrote:
> Hi all,
>
> There seems a bad behavior under autocommit off mode.
>
>   1) psql -c 'set autocommit to off;select 1;commit'
> causes a WARNING:  COMMIT: no transaction in progress
> whereas
>   2) psql -c 'begin;select 1;commit'
> causes no error/warning.
>
> Note that the result is the same even when you issue
> the first set/begin command separately using the client
> softwares other than psql.
>
> The problem here is that the transaction is cancelled
> in case of 1) though no error is reported.
> Shouldn't we avoid the warning and the cancellation ?
> Or should an error be reported instead of the warning ?
>
> regards,
> Hiroshi Inoue
>     http://www.geocities.jp/inocchichichi/psqlodbc/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

--
  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: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/tcop/postgres.c,v
retrieving revision 1.317
diff -c -c -r1.317 postgres.c
*** src/backend/tcop/postgres.c    10 Mar 2003 03:53:51 -0000    1.317
--- src/backend/tcop/postgres.c    19 Mar 2003 16:54:53 -0000
***************
*** 880,899 ****
                  finish_xact_command(true);
                  xact_started = false;
              }
!         }                        /* end loop over queries generated from a
!                                  * parsetree */

!         /*
!          * If this is the last parsetree of the query string, close down
!          * transaction statement before reporting command-complete.  This
!          * is so that any end-of-transaction errors are reported before
!          * the command-complete message is issued, to avoid confusing
!          * clients who will expect either a command-complete message or an
!          * error, not one and then the other.  But for compatibility with
!          * historical Postgres behavior, we do not force a transaction
!          * boundary between queries appearing in a single query string.
!          */
!         if (lnext(parsetree_item) == NIL && xact_started)
          {
              finish_xact_command(false);
              xact_started = false;
--- 880,888 ----
                  finish_xact_command(true);
                  xact_started = false;
              }
!         }    /* end loop over queries generated from a parsetree */

!         if (xact_started)
          {
              finish_xact_command(false);
              xact_started = false;