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

Re: Nested transactions

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Barry Lind <blind(at)xythos(dot)com>
Cc: simon(at)2ndquadrant(dot)com, Patches <pgsql-patches(at)postgresql(dot)org>,alvherre(at)dcc(dot)uchile(dot)cl
Subject: Re: Nested transactions
Date: 2004-06-17 01:36:33
Message-ID: 200406170136.i5H1aX727064@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-jdbcpgsql-patches
Barry Lind wrote:
> I agree with Simon's comments.  And to them I would add:  I had assumed 
> that the requirements for 'nested transactions' was following some 
> standard definition or specification (i.e. the ANSI SQL spec).  But from 
> what I can tell, we are rolling our own definition here, not following a 
> specification or standard, and not following any of the other major 
> commercial databases lead.
> 
> I think venturing out on our own and inventing new symantics for 
> transactions and sql syntax to support them without giving this a lot of 
> thought is bound to lead to problems.
> 
> Perhaps I am completely wrong here and there is a clear standard or spec 
> that is being implemented, if so, please let me know what that is as it 
> would help me in better understanding this patch.
> 
> I have been reviewing what Oracle does in this area and it doesn't at 
> all resemble what this patch is exposing (especially as far as syntax 
> goes).  I plan to look at DB2 and MSSQL next.

I realized about three weeks ago that our syntax for nested transactions
doesn't follow anyone else's method.  I think I might be the person who
suggested the idea because it seemed more logical to me to allow
BEGIN;BEGIN;COMMIT;COMMIT rather than naming arbitrary locations as
savepoints and doing a rollback to that savepoint name.

And consider this case:

	BEGIN;
	...
	SAVEPOINT x;
	SELECT func_call();
	SELECT func_call();
	COMMIT;

Now if func_call has a savepoint, it is really nested because it can't
know whether the savepoint X will be used to roll back, so its status is
dependent on the status of X.  Now, if we used savepoints in func_call,
what happens in the second function call when we define a savepoint with
the same name?  I assume we overwrite the original, but using nested
transaction syntax seems much clearer.  

Basically, we have to implement this in a nested way.  Once it is done,
we can add the window dressing to support the ANSI syntax.

-- 
  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-patches by date

Next:From: Alvaro HerreraDate: 2004-06-17 01:44:00
Subject: Re: Nested transactions
Previous:From: Claudio NatoliDate: 2004-06-17 01:00:13
Subject: Re: pg_ctl service integration for WIN32

pgsql-jdbc by date

Next:From: Alvaro HerreraDate: 2004-06-17 01:44:00
Subject: Re: Nested transactions
Previous:From: Barry LindDate: 2004-06-17 00:59:43
Subject: Re: Nested transactions

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