Skip site navigation (1) Skip section navigation (2)

Re: Nested xacts: looking for testers and review

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl>
Cc: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Nested xacts: looking for testers and review
Date: 2004-05-28 17:43:16
Message-ID: 200405281743.i4SHhGm11431@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Alvaro Herrera wrote:
> On Fri, May 28, 2004 at 04:51:07PM +0900, Tatsuo Ishii wrote:
> 
> > 2) certain behavior was different from what I expected (please correct me
> >    if my expectation is wrong). 
> 
> Yes, the expected behavior is different: if an aborted subtransaction is
> closed with a COMMIT or END command, the parent transaction is aborted
> too.  This is to inhibit an application which blindly expects the
> subtransaction to succeed to reach an invalid state.  If you want to
> return to non-aborted state, end the subtransaction with ROLLBACK
> instead.

I am interested to know if people agree with this behavior, reproduced below:

	test=# begin;
	BEGIN
	test=# insert into t1 values(1);
	INSERT 17216 1
	test=# begin;
	BEGIN
	test=# aaa;
	ERROR:  syntax error at or near "aaa" at character 1
	ERROR:  syntax error at or near "aaa" at character 1
	LINE 1: aaa;
	        ^
	test=# end;
	COMMIT
	test=# select * from t1; <-- I thought this should work since
	subtransaction was closed
	ERROR:  current transaction is aborted, commands ignored until end of
	transaction block
	ERROR:  current transaction is aborted, commands ignored until end of
	transaction block
	test=# end;
	COMMIT

The problem I see with the behavior shown is that there is no way to use
subtransactions in scripts, where the queries can't be changed. 
Consider this:

	BEGIN;
	DO SOME WORK...
	BEGIN;
	INSERT ...
	COMMIT;
	BEGIN;
	INSERT ...
	COMMIT;
	BEGIN;
	INSERT ...
	COMMIT;
	BEGIN;
	INSERT ...
	COMMIT;
	DO MORE WORK...
	COMMIT;

In this case, I want to try all of the inserts, but any of them can
fail, then I want the bottom part done.

I guess my big question is that if they issue a COMMIT for a
subtransaction that failed, do we assume they made a mistake and fail
the outer transaction, or do we just accept it and not affect the outer
transaction.

In my logic, the subtransaction COMMIT is part of the subtransaction and
should not affect the outer transaction's state.

Unfortunately, we don't have any similar behavior in our 7.4 code
because whether you issue COMMIT or ABORT, it does not affect the outer
session.

Do any other databases have nested transactions, and how to they handle
it?

I think we should issue a warning but not affect the outer transaction.

> But Bob Henkel and you are right: there needs to be documentation.
> Initially I figured I could do that later because there is no new
> syntax, but it is obviously needed to explain all sorts of assumptions
> and behavior like this.

Right now I think just posting examples will work fine.  I think the
above case shows we are not ready for documentation yet.  What I would
like is for folks to focus on testing so we can find any open issues
like this one and address them.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2004-05-28 18:08:09
Subject: Re: Win32, PITR, nested transactions, tablespaces
Previous:From: Tom LaneDate: 2004-05-28 17:16:31
Subject: Re: contrib/ compile warnings

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group