A bad behavior under autocommit off mode

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

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

Surely that's a bug: the SELECT ought to start a transaction block.

Barry Lind reported what is probably a closely related issue:
http://archives.postgresql.org/pgsql-hackers/2003-01/msg00592.php

I haven't gotten around to looking at this, but I suspect postgres.c
is doing something inside the per-querytree loop that it should be
doing outside it, or vice versa. Or possibly the problem is with
the klugy way that we hacked autocommit-off into the xact.c state
machine. Do you have time to look at it?

regards, tom lane

On Thursday 20 February 2003 10:38, Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

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

Surely that's a bug: the SELECT ought to start a transaction block.

Sure but doesn't it also commit it? There's still no transaction open coming
out of the SELECT.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

"D'Arcy J.M. Cain" <darcy@druid.net> writes:

On Thursday 20 February 2003 10:38, Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

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

Surely that's a bug: the SELECT ought to start a transaction block.

Sure but doesn't it also commit it?

Not in autocommit-off mode.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

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

Surely that's a bug: the SELECT ought to start a transaction block.

Barry Lind reported what is probably a closely related issue:
http://archives.postgresql.org/pgsql-hackers/2003-01/msg00592.php

I haven't gotten around to looking at this, but I suspect postgres.c
is doing something inside the per-querytree loop that it should be
doing outside it, or vice versa. Or possibly the problem is with
the klugy way that we hacked autocommit-off into the xact.c state
machine. Do you have time to look at it?

I have little time.

The transaction block state seems to be set just before returning from
the chained query. I don't know if it's bad or not.
The simplest way seems to accept COMMIT any time under autocommit
off mode.

regards,
Hiroshi Inoue

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

The simplest way seems to accept COMMIT any time under autocommit
off mode.

That's just hiding the most visible symptom. The real problem here is
that the SELECT is already committed, when it shouldn't be.

regards, tom lane

Tom Lane wrote:

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

The simplest way seems to accept COMMIT any time under autocommit
off mode.

That's just hiding the most visible symptom. The real problem here is
that the SELECT is already committed, when it shouldn't be.

The warning means that the transaction is not yet begun
before the chained query is issued. The check seems originally
for COMMIT without BEGIN under autocommit on mode. It also
cancels a transaction for the query '..;..;commit;..' under
autocommit on mode. It's also bad because it only reports a
warning. Anyway should 'set autocommit to off;commit' cause
a warning or an error in the first place ?

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Anyway should 'set autocommit to off;commit' cause
a warning or an error in the first place ?

IIRC, the SET does *not* start a transaction, so the COMMIT should raise
a warning.

I do not believe that eliminating the warning from COMMIT is a good
idea. If we didn't have that warning in place, we'd have not known that
we had a bug here. (On the other hand, I'm not in favor of making it
a hard error, either.)

regards, tom lane

Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Anyway should 'set autocommit to off;commit' cause
a warning or an error in the first place ?

IIRC, the SET does *not* start a transaction,

Yes but doesn't autocommit-off mode mean that
it implicitly begins a transaction in suitable
places ? For example, 'set autocommit to off;
declare .. cursor ..' works though it never
work without BEGIN under autocommit-on mode.

so the COMMIT should raise
a warning.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Tom Lane wrote:

IIRC, the SET does *not* start a transaction,

Yes but doesn't autocommit-off mode mean that
it implicitly begins a transaction in suitable
places ? For example, 'set autocommit to off;
declare .. cursor ..' works though it never
work without BEGIN under autocommit-on mode.

But the DECLARE would start a transaction --- AFAIR, pretty much
everything except SET, COMMIT, ROLLBACK will start a transaction
in autocommit=off mode. I'm not sure what your point is?

regards, tom lane

Tom Lane wrote:

Hiroshi Inoue <Inoue@tpf.co.jp> writes:

Tom Lane wrote:

IIRC, the SET does *not* start a transaction,

Yes but doesn't autocommit-off mode mean that
it implicitly begins a transaction in suitable
places ? For example, 'set autocommit to off;
declare .. cursor ..' works though it never
work without BEGIN under autocommit-on mode.

But the DECLARE would start a transaction --- AFAIR,

Yes it's only because the behavior is useful for us.

So isn't the problem if the warning message for
'set autocommit to off;commit'
is useful or not ?
IMHO it's rather a harmful message.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Can someone point me to the documentation for the new autocommit mode
behaviour, I must be doing something wrong

I do a set autocommit=off;

begin;
create table foo ...
insert into foo
end;

and if I look for the table foo from another connection, it isn't there?

my source tree is REL7_3_STABLE

--
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting

Dave Cramer <dave@fastcrypt.com> writes:

Can someone point me to the documentation for the new autocommit mode
behaviour, I must be doing something wrong

Must be :-(. I repeated your example as best I could, and it behaved
as expected.

<< session 1 >>

regression=# set autocommit=off;
SET
regression=# begin;
BEGIN
regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values (1);
INSERT 933631 1
regression=# end;
COMMIT
regression=#

<< session 2 >>

regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |

regression=# select * from foo;
f1
----
1
(1 row)

regards, tom lane

what cvs version are you working with?

Dave
On Wed, 2003-02-26 at 01:11, Tom Lane wrote:

Dave Cramer <dave@fastcrypt.com> writes:

Can someone point me to the documentation for the new autocommit mode
behaviour, I must be doing something wrong

Must be :-(. I repeated your example as best I could, and it behaved
as expected.

<< session 1 >>

regression=# set autocommit=off;
SET
regression=# begin;
BEGIN
regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values (1);
INSERT 933631 1
regression=# end;
COMMIT
regression=#

<< session 2 >>

regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |

regression=# select * from foo;
f1
----
1
(1 row)

regards, tom lane

--
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting

Sorry for the noise, my instance of postgres was broken.

Dave
On Wed, 2003-02-26 at 02:19, Dave Cramer wrote:

what cvs version are you working with?

Dave
On Wed, 2003-02-26 at 01:11, Tom Lane wrote:

Dave Cramer <dave@fastcrypt.com> writes:

Can someone point me to the documentation for the new autocommit mode
behaviour, I must be doing something wrong

Must be :-(. I repeated your example as best I could, and it behaved
as expected.

<< session 1 >>

regression=# set autocommit=off;
SET
regression=# begin;
BEGIN
regression=# create table foo (f1 int);
CREATE TABLE
regression=# insert into foo values (1);
INSERT 933631 1
regression=# end;
COMMIT
regression=#

<< session 2 >>

regression=# \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |

regression=# select * from foo;
f1
----
1
(1 row)

regards, tom lane

--
Dave Cramer <dave@fastcrypt.com>
Cramer Consulting

Dave Cramer <dave@fastcrypt.com> writes:

what cvs version are you working with?

CVS tip in either HEAD or REL7_3_STABLE branches works for me.

There are some known issues with autocommit getting turned off
as part of a multi-statement command that's sent to tbe backend
in a single query string. But your example in psql wouldn't have
triggered that problem.

regards, tom lane

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

Bruce Momjian wrote:

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.

Does the change worth the trouble ?
Please don't break BACKWARD COMPATIBILITY easily.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Hiroshi Inoue wrote:

Bruce Momjian wrote:

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.

Does the change worth the trouble ?
Please don't break BACKWARD COMPATIBILITY easily.

It clearly fixes an existing bug, and I asked on general to see if
anyone has any problem with the change. My guess is that more people
are surprised by the group-string-as-a-single-transaction as people who
use the feature, so I see it as the removal of surprising functionality.

We will mention it in the release notes, and I can even supply a patch
for those who want it kept. In fact, I can easily make it a compile
option --- the change is only a single conditional test.

-- 
  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

Bruce Momjian wrote:

Hiroshi Inoue wrote:

Bruce Momjian wrote:

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.

Does the change worth the trouble ?
Please don't break BACKWARD COMPATIBILITY easily.

It clearly fixes an existing bug,

My proposal also fixes the bug though Tom objected to it.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/