Thread: BEGIN inside transaction should be an error

BEGIN inside transaction should be an error

From
Dennis Bjorklund
Date:
Hi

Yesterday I helped a guy on irc with a locking problem, he thought
that locking in postgresql was broken. It turned out that he had a PHP
function that he called inside his transaction and the function did BEGIN
and COMMIT. Since BEGIN inside a transaction is just a warning what
happend was that the inner COMMIT ended the transaction and
released the locks. The rest of his commands ran with autocommit
and no locks and he got broken data into the database.

Could we make BEGIN fail when we already are in a transaction?

Looking it up in the sql99 standard I find this:

"If a <start transaction statement> statement is executed when an
SQL-transaction is currently active, then an exception condition is
raised: invalid transaction state - active SQL-transaction."

/Dennis


Re: BEGIN inside transaction should be an error

From
Tom Lane
Date:
Dennis Bjorklund <db@zigo.dhs.org> writes:
> Yesterday I helped a guy on irc with a locking problem, he thought
> that locking in postgresql was broken. It turned out that he had a PHP
> function that he called inside his transaction and the function did BEGIN
> and COMMIT. Since BEGIN inside a transaction is just a warning what
> happend was that the inner COMMIT ended the transaction and
> released the locks. The rest of his commands ran with autocommit
> and no locks and he got broken data into the database.

> Could we make BEGIN fail when we already are in a transaction?

We could, but it'd probably break about as many apps as it fixed.
I wonder whether php shouldn't be complaining about this, instead
--- doesn't php have its own ideas about controlling where the
transaction commit points are?
        regards, tom lane


Re: BEGIN inside transaction should be an error

From
Christopher Kings-Lynne
Date:
> We could, but it'd probably break about as many apps as it fixed.
> I wonder whether php shouldn't be complaining about this, instead
> --- doesn't php have its own ideas about controlling where the
> transaction commit points are?

All PHP does is when the connection is returned to the pool, if it is 
still in a transaction, a rollback is issued.

The guy needs to do his own tracking of transaction state if he wants to 
avoid these problems...

Chris



Re: BEGIN inside transaction should be an error

From
Lukas Smith
Date:
Tom Lane wrote:
> Dennis Bjorklund <db@zigo.dhs.org> writes:
>> Yesterday I helped a guy on irc with a locking problem, he thought
>> that locking in postgresql was broken. It turned out that he had a PHP
>> function that he called inside his transaction and the function did BEGIN
>> and COMMIT. Since BEGIN inside a transaction is just a warning what
>> happend was that the inner COMMIT ended the transaction and
>> released the locks. The rest of his commands ran with autocommit
>> and no locks and he got broken data into the database.
> 
>> Could we make BEGIN fail when we already are in a transaction?
> 
> We could, but it'd probably break about as many apps as it fixed.
> I wonder whether php shouldn't be complaining about this, instead
> --- doesn't php have its own ideas about controlling where the
> transaction commit points are?

There are no API calls to start/end transactions in php. However there 
is a way to get the current transaction status:
http://de3.php.net/manual/en/function.pg-transaction-status.php

Also whatever decision is made one day PostGreSQL might want to 
supported nested transactions similar to firebird.

regards,
Lukas


Re: BEGIN inside transaction should be an error

From
"Jaime Casanova"
Date:
On 5/10/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dennis Bjorklund <db@zigo.dhs.org> writes:
> > Yesterday I helped a guy on irc with a locking problem, he thought
> > that locking in postgresql was broken. It turned out that he had a PHP
> > function that he called inside his transaction and the function did BEGIN
> > and COMMIT. Since BEGIN inside a transaction is just a warning what
> > happend was that the inner COMMIT ended the transaction and
> > released the locks. The rest of his commands ran with autocommit
> > and no locks and he got broken data into the database.
>
> > Could we make BEGIN fail when we already are in a transaction?
>
> We could, but it'd probably break about as many apps as it fixed.
> I wonder whether php shouldn't be complaining about this, instead
> --- doesn't php have its own ideas about controlling where the
> transaction commit points are?
>
>                        regards, tom lane
>

AFAIK php doesn't care about that... it just see for success or
failure conditions, so if postgres said everything is ok it will
continue...

--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook


Re: BEGIN inside transaction should be an error

From
"Albe Laurenz"
Date:
Tom Lane wrote:
> Dennis Bjorklund <db@zigo.dhs.org> writes:
>> Could we make BEGIN fail when we already are in a transaction?
>
> We could, but it'd probably break about as many apps as it fixed.

I'd say that a program that issues BEGIN inside a transaction is
already broken, if only by design.

I think that the benefit of forced consistency in your transaction
handling
and standard compliance outweighs the disadvantage of breaking
compatibility.

Yours,
Laurenz Albe


Re: BEGIN inside transaction should be an error

From
Mario Weilguni
Date:
Am Mittwoch, 10. Mai 2006 08:19 schrieb Tom Lane:
> Dennis Bjorklund <db@zigo.dhs.org> writes:
> > Yesterday I helped a guy on irc with a locking problem, he thought
> > that locking in postgresql was broken. It turned out that he had a PHP
> > function that he called inside his transaction and the function did BEGIN
> > and COMMIT. Since BEGIN inside a transaction is just a warning what
> > happend was that the inner COMMIT ended the transaction and
> > released the locks. The rest of his commands ran with autocommit
> > and no locks and he got broken data into the database.
> >
> > Could we make BEGIN fail when we already are in a transaction?
>
> We could, but it'd probably break about as many apps as it fixed.
> I wonder whether php shouldn't be complaining about this, instead
> --- doesn't php have its own ideas about controlling where the
> transaction commit points are?

In fact it would break many application, so it should be at least controllable 
by a setting or GUC.


Re: BEGIN inside transaction should be an error

From
Martijn van Oosterhout
Date:
On Wed, May 10, 2006 at 09:41:46AM +0200, Mario Weilguni wrote:
> > > Could we make BEGIN fail when we already are in a transaction?
> >
> > We could, but it'd probably break about as many apps as it fixed.
> > I wonder whether php shouldn't be complaining about this, instead
> > --- doesn't php have its own ideas about controlling where the
> > transaction commit points are?
>
> In fact it would break many application, so it should be at least controllable
> by a setting or GUC.

You want to make a GUC that makes:

BEGIN;
BEGIN;

Leave you with an aborted transaction? That seems like a singularly
useless feature...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: BEGIN inside transaction should be an error

From
Mario Weilguni
Date:
Am Mittwoch, 10. Mai 2006 09:41 schrieb Mario Weilguni:
> Am Mittwoch, 10. Mai 2006 08:19 schrieb Tom Lane:
> > Dennis Bjorklund <db@zigo.dhs.org> writes:
> > > Yesterday I helped a guy on irc with a locking problem, he thought
> > > that locking in postgresql was broken. It turned out that he had a PHP
> > > function that he called inside his transaction and the function did
> > > BEGIN and COMMIT. Since BEGIN inside a transaction is just a warning
> > > what happend was that the inner COMMIT ended the transaction and
> > > released the locks. The rest of his commands ran with autocommit
> > > and no locks and he got broken data into the database.
> > >
> > > Could we make BEGIN fail when we already are in a transaction?
> >
> > We could, but it'd probably break about as many apps as it fixed.
> > I wonder whether php shouldn't be complaining about this, instead
> > --- doesn't php have its own ideas about controlling where the
> > transaction commit points are?
>
> In fact it would break many application, so it should be at least
> controllable by a setting or GUC.
>

No, I want that there is a setting or GUC that controls whether an error or a 
warning is raised when "begin" is executed within a transaction. I know of 
several php database wrappers that will be seriously broken when errors are 
raised...


Re: BEGIN inside transaction should be an error

From
Peter Eisentraut
Date:
Am Mittwoch, 10. Mai 2006 10:10 schrieb Martijn van Oosterhout:
> You want to make a GUC that makes:
>
> BEGIN;
> BEGIN;
>
> Leave you with an aborted transaction? That seems like a singularly
> useless feature...

If a command doesn't do what it is supposed to do, then it should be an error.  
That seems like a throroughly useful feature to me.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


Re: BEGIN inside transaction should be an error

From
Bernd Helmle
Date:

--On Mittwoch, Mai 10, 2006 10:14:22 +0200 Mario Weilguni 
<mweilguni@sime.com> wrote:

> No, I want that there is a setting or GUC that controls whether an error
> or a  warning is raised when "begin" is executed within a transaction. I
> know of  several php database wrappers that will be seriously broken when
> errors are  raised...

Such a behavior is already broken by design. I think it's not desirable to 
blindly do
transaction start or commit without tracking the current transaction state. 
So these wrappers
need to be fixed first.

-- 
                   Bernd


Re: BEGIN inside transaction should be an error

From
Mario Weilguni
Date:
Am Mittwoch, 10. Mai 2006 10:59 schrieb Peter Eisentraut:
> Am Mittwoch, 10. Mai 2006 10:10 schrieb Martijn van Oosterhout:
> > You want to make a GUC that makes:
> >
> > BEGIN;
> > BEGIN;
> >
> > Leave you with an aborted transaction? That seems like a singularly
> > useless feature...
>
> If a command doesn't do what it is supposed to do, then it should be an
> error. That seems like a throroughly useful feature to me.

Maybe. I just want to emphasize that it will break existing applications.



Re: BEGIN inside transaction should be an error

From
Mario Weilguni
Date:
Am Mittwoch, 10. Mai 2006 11:44 schrieb Bernd Helmle:
> --On Mittwoch, Mai 10, 2006 10:14:22 +0200 Mario Weilguni
>
> <mweilguni@sime.com> wrote:
> > No, I want that there is a setting or GUC that controls whether an error
> > or a  warning is raised when "begin" is executed within a transaction. I
> > know of  several php database wrappers that will be seriously broken when
> > errors are  raised...
>
> Such a behavior is already broken by design. I think it's not desirable to
> blindly do
> transaction start or commit without tracking the current transaction state.
> So these wrappers
> need to be fixed first.

You mean broken like "transform_null_equals"? Or "add_missing_from"? 


Re: BEGIN inside transaction should be an error

From
Bernd Helmle
Date:

--On Mittwoch, Mai 10, 2006 12:36:07 +0200 Mario Weilguni 
<mweilguni@sime.com> wrote:

>> Such a behavior is already broken by design. I think it's not desirable
>> to blindly do
>> transaction start or commit without tracking the current transaction
>> state. So these wrappers
>> need to be fixed first.
>
> You mean broken like "transform_null_equals"? Or "add_missing_from"?

You missed my point. I don't say that such a GUC won't be useful, but 
applications which
don't care about what they are currently doing with a database are broken.


-- 
                   Bernd


Re: BEGIN inside transaction should be an error

From
Dennis Bjorklund
Date:
Peter Eisentraut skrev:
> Am Mittwoch, 10. Mai 2006 10:10 schrieb Martijn van Oosterhout:
>   
>> You want to make a GUC that makes:
>>
>> BEGIN;
>> BEGIN;
>>
>> Leave you with an aborted transaction? That seems like a singularly
>> useless feature...
>>     
>
> If a command doesn't do what it is supposed to do, then it should be an error.  
> That seems like a throroughly useful feature to me.
>
>   
And it would follow sql99 that demand an error. I'm surprised
everyone seems to ignore that part (except maybe Peter who is the
one I happend to reply to :-).

A guc that people can turn off if they have old broken code, that
would work for me.

/Dennis



Re: BEGIN inside transaction should be an error

From
"Gurjeet Singh"
Date:
    I dont think anyone is arguing that such an application is not
broken. We should see how we can stop a developer from writing buggy
code.
   IMO, such a GUC variable _should_ be created and turned on by default.
   In case an application fails, at the least, the developer knows
that his application is broken; then he can choose to turn off the GUC
variable to let the old behaviour prevail (he might want to do this to
let a production env. continue).
   In the absence of such a feature, we are encouraging developers to
write buggy code. This GUC variable can be removed and the behaviour
can be made default over the next couple of releases.

My two paise...

On 5/10/06, Bernd Helmle <mailings@oopsware.de> wrote:
>
>
> --On Mittwoch, Mai 10, 2006 12:36:07 +0200 Mario Weilguni
> <mweilguni@sime.com> wrote:
>
> >> Such a behavior is already broken by design. I think it's not desirable
> >> to blindly do
> >> transaction start or commit without tracking the current transaction
> >> state. So these wrappers
> >> need to be fixed first.
> >
> > You mean broken like "transform_null_equals"? Or "add_missing_from"?
>
> You missed my point. I don't say that such a GUC won't be useful, but
> applications which
> don't care about what they are currently doing with a database are broken.
>
>
> --
>
>                     Bernd
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>


Re: BEGIN inside transaction should be an error

From
Mike Benoit
Date:
I would suggest the guy simply use the popular ADODB package for his
database abstraction layer so he can make use of its "Smart Transaction"
feature.

http://phplens.com/lens/adodb/docs-adodb.htm#ex11

<quote>
Lastly, StartTrans/CompleteTrans is nestable, and only the outermost
block is executed. In contrast, BeginTrans/CommitTrans/RollbackTrans is
NOT nestable.

$conn->StartTrans();
$conn->Execute($sql); $conn->StartTrans();    # ignored <-------------- if (!CheckRecords()) $conn->FailTrans();
$conn->CompleteTrans();# ignored <-------------- 
$conn->Execute($Sql2);
$conn->CompleteTrans();
</quote>

The commands marked "ignored" aren't really ignored, since it keeps
track of what level the transactions are nested to, and won't actually
commit the transaction until the StartTrans() calls == CompleteTrans()
calls.

Its worked great for me for many years now.

On Wed, 2006-05-10 at 06:19 +0200, Dennis Bjorklund wrote:
> Hi
>
> Yesterday I helped a guy on irc with a locking problem, he thought
> that locking in postgresql was broken. It turned out that he had a PHP
> function that he called inside his transaction and the function did BEGIN
> and COMMIT. Since BEGIN inside a transaction is just a warning what
> happend was that the inner COMMIT ended the transaction and
> released the locks. The rest of his commands ran with autocommit
> and no locks and he got broken data into the database.
>
> Could we make BEGIN fail when we already are in a transaction?
>
> Looking it up in the sql99 standard I find this:
>
> "If a <start transaction statement> statement is executed when an
> SQL-transaction is currently active, then an exception condition is
> raised: invalid transaction state - active SQL-transaction."
>
> /Dennis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
--
Mike Benoit <ipso@snappymail.ca>

Re: BEGIN inside transaction should be an error

From
Mark Dilger
Date:
Martijn van Oosterhout wrote:
> On Wed, May 10, 2006 at 09:41:46AM +0200, Mario Weilguni wrote:
> 
>>>>Could we make BEGIN fail when we already are in a transaction?
>>>
>>>We could, but it'd probably break about as many apps as it fixed.
>>>I wonder whether php shouldn't be complaining about this, instead
>>>--- doesn't php have its own ideas about controlling where the
>>>transaction commit points are?
>>
>>In fact it would break many application, so it should be at least controllable 
>>by a setting or GUC.
> 
> 
> You want to make a GUC that makes:
> 
> BEGIN;
> BEGIN;
> 
> Leave you with an aborted transaction? That seems like a singularly
> useless feature...
> 
> Have a nice day,

Or if you really want to screw things up, you could require COMMIT; COMMIT; to
finish off the transaction started by BEGIN; BEGIN;  We could just silently keep
the transaction alive after the first COMMIT;  ;)


Re: BEGIN inside transaction should be an error

From
"Jim C. Nasby"
Date:
On Wed, May 10, 2006 at 12:31:52PM +0200, Mario Weilguni wrote:
> Am Mittwoch, 10. Mai 2006 10:59 schrieb Peter Eisentraut:
> > Am Mittwoch, 10. Mai 2006 10:10 schrieb Martijn van Oosterhout:
> > > You want to make a GUC that makes:
> > >
> > > BEGIN;
> > > BEGIN;
> > >
> > > Leave you with an aborted transaction? That seems like a singularly
> > > useless feature...
> >
> > If a command doesn't do what it is supposed to do, then it should be an
> > error. That seems like a throroughly useful feature to me.
> 
> Maybe. I just want to emphasize that it will break existing applications.

If the existing application is trying to start a new transaction from
within an existing one, I'd say it's already broken and we're just
hiding that fact.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: BEGIN inside transaction should be an error

From
Martijn van Oosterhout
Date:
On Wed, May 10, 2006 at 04:03:51PM -0500, Jim C. Nasby wrote:
> On Wed, May 10, 2006 at 12:31:52PM +0200, Mario Weilguni wrote:
> > Maybe. I just want to emphasize that it will break existing applications.
>
> If the existing application is trying to start a new transaction from
> within an existing one, I'd say it's already broken and we're just
> hiding that fact.

Well maybe, except the extra BEGIN is harmless. I'm thinking of the
situation where a connection library sends a BEGIN on startup because
it wants to emulate a non-autocommit mode. The application then
proceeds to handle transactions itself, sending another BEGIN and going
from there.

We'll have just broken this perfectly working application because it
failed the purity test. The backward compatability issues are huge and
it doesn't actually bring any benefits.

How do other database deal with this? Either they nest BEGIN/COMMIT or
they probably throw an error without aborting the transaction, which is
pretty much what we do. Is there a database that actually aborts a
whole transaction just for an extraneous begin?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Re: BEGIN inside transaction should be an error

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> How do other database deal with this? Either they nest BEGIN/COMMIT or
> they probably throw an error without aborting the transaction, which is
> pretty much what we do. Is there a database that actually aborts a
> whole transaction just for an extraneous begin?

Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
which is the standard spelling of BEGIN)
        1) If a <start transaction statement> statement is executed when an           SQL-transaction is currently
active,then an exception condition           is raised: invalid transaction state - active SQL-transaction.
 

*However*, they are almost certainly expecting that that condition only
causes the START command to be ignored; not that it should bounce the
whole transaction.  So I think the argument that this is required by
the spec is a bit off base.
        regards, tom lane


Re: BEGIN inside transaction should be an error

From
"Jaime Casanova"
Date:
On 5/10/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > How do other database deal with this? Either they nest BEGIN/COMMIT or
> > they probably throw an error without aborting the transaction, which is
> > pretty much what we do. Is there a database that actually aborts a
> > whole transaction just for an extraneous begin?
>
> Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
> which is the standard spelling of BEGIN)
>
>          1) If a <start transaction statement> statement is executed when an
>             SQL-transaction is currently active, then an exception condition
>             is raised: invalid transaction state - active SQL-transaction.
>
> *However*, they are almost certainly expecting that that condition only
> causes the START command to be ignored; not that it should bounce the
> whole transaction.  So I think the argument that this is required by
> the spec is a bit off base.
>
>                         regards, tom lane
>

Well, actually informix throw an error... at least, my 4gl programs
always abort when a second "begin work" is found inside a
transaction...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook


Re: BEGIN inside transaction should be an error

From
Dennis Bjorklund
Date:
Tom Lane skrev:
> The SQL99 spec does say (in describing START TRANSACTION,
> which is the standard spelling of BEGIN)
>
>          1) If a <start transaction statement> statement is executed when an
>             SQL-transaction is currently active, then an exception condition
>             is raised: invalid transaction state - active SQL-transaction.
>
> *However*, they are almost certainly expecting that that condition only
> causes the START command to be ignored; not that it should bounce the
> whole transaction.

What is the definition of an "exception condition"?

I thought that it ment that a transaction should fail and that 
"completion condition" are
used for warnings that doesn't abort transactions. As an example I 
looked up division
by zero in sql99 and it say this:
 "If the value of a divisor is zero, then an exception condition  is raised: data exception - division by zero."

Do you mean that some exception conditions fail transactions and some 
doesn't?

/Dennis



Re: BEGIN inside transaction should be an error

From
Tommi Maekitalo
Date:
Am Mittwoch, 10. Mai 2006 22:23 schrieb Mark Dilger:
> Martijn van Oosterhout wrote:
> > On Wed, May 10, 2006 at 09:41:46AM +0200, Mario Weilguni wrote:
> >>>>Could we make BEGIN fail when we already are in a transaction?
...
>
> Or if you really want to screw things up, you could require COMMIT; COMMIT;
> to finish off the transaction started by BEGIN; BEGIN;  We could just
> silently keep the transaction alive after the first COMMIT;  ;)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

I would expect after a COMMIT without an error, that my transaction is 
committed. When the system accidently issued a second BEGIN, this would not 
be the case.

And what about BEGIN; BEGIN; ROLLBACK; COMMIT; then? Should the rollback be 
ignored also?

I'd vote for breaking broken applications and leave the database-administrator 
reactivate this currently broken behavior of postgresql via GUC.

Tommi


Re: BEGIN inside transaction should be an error

From
"Marko Kreen"
Date:
On 5/11/06, Martijn van Oosterhout <kleptog@svana.org> wrote:
> On Wed, May 10, 2006 at 04:03:51PM -0500, Jim C. Nasby wrote:
> > If the existing application is trying to start a new transaction from
> > within an existing one, I'd say it's already broken and we're just
> > hiding that fact.
>
> Well maybe, except the extra BEGIN is harmless.

It _not_ harmless as it will be probably followed by 'extra' commit.
Those few cases where it does not happen do not matter in light
of cases where it happens.


--
marko


Re: BEGIN inside transaction should be an error

From
"Jim C. Nasby"
Date:
On Thu, May 11, 2006 at 08:05:57AM +0200, Tommi Maekitalo wrote:
> I'd vote for breaking broken applications and leave the database-administrator 
> reactivate this currently broken behavior of postgresql via GUC.

+1...

As for whether this should or shouldn't abort the current transaction,
I'd argue that it should. Otherwise it's likely that your first commit
is actually bogus, which means you just hosed yourself.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461


Re: BEGIN inside transaction should be an error

From
Simon Riggs
Date:
On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > How do other database deal with this? Either they nest BEGIN/COMMIT or
> > they probably throw an error without aborting the transaction, which is
> > pretty much what we do. Is there a database that actually aborts a
> > whole transaction just for an extraneous begin?
> 
> Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
> which is the standard spelling of BEGIN)
> 
>          1) If a <start transaction statement> statement is executed when an
>             SQL-transaction is currently active, then an exception condition
>             is raised: invalid transaction state - active SQL-transaction.
> 
> *However*, they are almost certainly expecting that that condition only
> causes the START command to be ignored; not that it should bounce the
> whole transaction.  So I think the argument that this is required by
> the spec is a bit off base.

If you interpret the standard that way then the correct behaviour in the
face of *any* exception condition should be *not* abort the transaction.
In PostgreSQL, all exception conditions do abort the transaction, so why
not this one? Why would we special-case this?

--  Simon Riggs EnterpriseDB          http://www.enterprisedb.com



Re: BEGIN inside transaction should be an error

From
Mario Weilguni
Date:
Am Donnerstag, 11. Mai 2006 22:16 schrieb Simon Riggs:
> On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote:
> > Martijn van Oosterhout <kleptog@svana.org> writes:
> > > How do other database deal with this? Either they nest BEGIN/COMMIT or
> > > they probably throw an error without aborting the transaction, which is
> > > pretty much what we do. Is there a database that actually aborts a
> > > whole transaction just for an extraneous begin?
> >
> > Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
> > which is the standard spelling of BEGIN)
> >
> >          1) If a <start transaction statement> statement is executed when
> > an SQL-transaction is currently active, then an exception condition is
> > raised: invalid transaction state - active SQL-transaction.
> >
> > *However*, they are almost certainly expecting that that condition only
> > causes the START command to be ignored; not that it should bounce the
> > whole transaction.  So I think the argument that this is required by
> > the spec is a bit off base.
>
> If you interpret the standard that way then the correct behaviour in the
> face of *any* exception condition should be *not* abort the transaction.
> In PostgreSQL, all exception conditions do abort the transaction, so why
> not this one? Why would we special-case this?

IMO it's ok to raise an exception - if this is configurable for at least one 
releasy cycle - giving developers time to fix applications. It's no good 
behaviour to change something like this without any (at least time-limited ) 
backward compatible option.

regardsmario weilguni


Re: BEGIN inside transaction should be an error

From
"Jaime Casanova"
Date:
On 5/12/06, Mario Weilguni <mweilguni@sime.com> wrote:
> Am Donnerstag, 11. Mai 2006 22:16 schrieb Simon Riggs:
> > On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote:
> > > Martijn van Oosterhout <kleptog@svana.org> writes:
> > > > How do other database deal with this? Either they nest BEGIN/COMMIT or
> > > > they probably throw an error without aborting the transaction, which is
> > > > pretty much what we do. Is there a database that actually aborts a
> > > > whole transaction just for an extraneous begin?
> > >
> > > Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
> > > which is the standard spelling of BEGIN)
> > >
> > >          1) If a <start transaction statement> statement is executed when
> > > an SQL-transaction is currently active, then an exception condition is
> > > raised: invalid transaction state - active SQL-transaction.
> > >
> > > *However*, they are almost certainly expecting that that condition only
> > > causes the START command to be ignored; not that it should bounce the
> > > whole transaction.  So I think the argument that this is required by
> > > the spec is a bit off base.
> >
> > If you interpret the standard that way then the correct behaviour in the
> > face of *any* exception condition should be *not* abort the transaction.
> > In PostgreSQL, all exception conditions do abort the transaction, so why
> > not this one? Why would we special-case this?
>
> IMO it's ok to raise an exception - if this is configurable for at least one
> releasy cycle - giving developers time to fix applications. It's no good
> behaviour to change something like this without any (at least time-limited )
> backward compatible option.
>

if an option to change it is put in place, maybe it will be there
forever (with a different default behavior)...

i am all in favor of a second begin to throw an exception "already in
transaction" or something else
(http://archives.postgresql.org/pgsql-hackers/2005-12/msg00813.php),
but if we do it we should do it the only behavior... i don't think
it's good to introduce a new GUC for that things (we will finish with
GUCs to turn off every fix)

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."                                      Richard Cook


Re: BEGIN inside transaction should be an error

From
Bruce Momjian
Date:
Added to TODO:
* Add a GUC to control whether BEGIN inside a transcation should abort  the transaction.


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

Jaime Casanova wrote:
> On 5/12/06, Mario Weilguni <mweilguni@sime.com> wrote:
> > Am Donnerstag, 11. Mai 2006 22:16 schrieb Simon Riggs:
> > > On Wed, 2006-05-10 at 21:24 -0400, Tom Lane wrote:
> > > > Martijn van Oosterhout <kleptog@svana.org> writes:
> > > > > How do other database deal with this? Either they nest BEGIN/COMMIT or
> > > > > they probably throw an error without aborting the transaction, which is
> > > > > pretty much what we do. Is there a database that actually aborts a
> > > > > whole transaction just for an extraneous begin?
> > > >
> > > > Probably not.  The SQL99 spec does say (in describing START TRANSACTION,
> > > > which is the standard spelling of BEGIN)
> > > >
> > > >          1) If a <start transaction statement> statement is executed when
> > > > an SQL-transaction is currently active, then an exception condition is
> > > > raised: invalid transaction state - active SQL-transaction.
> > > >
> > > > *However*, they are almost certainly expecting that that condition only
> > > > causes the START command to be ignored; not that it should bounce the
> > > > whole transaction.  So I think the argument that this is required by
> > > > the spec is a bit off base.
> > >
> > > If you interpret the standard that way then the correct behaviour in the
> > > face of *any* exception condition should be *not* abort the transaction.
> > > In PostgreSQL, all exception conditions do abort the transaction, so why
> > > not this one? Why would we special-case this?
> >
> > IMO it's ok to raise an exception - if this is configurable for at least one
> > releasy cycle - giving developers time to fix applications. It's no good
> > behaviour to change something like this without any (at least time-limited )
> > backward compatible option.
> >
> 
> if an option to change it is put in place, maybe it will be there
> forever (with a different default behavior)...
> 
> i am all in favor of a second begin to throw an exception "already in
> transaction" or something else
> (http://archives.postgresql.org/pgsql-hackers/2005-12/msg00813.php),
> but if we do it we should do it the only behavior... i don't think
> it's good to introduce a new GUC for that things (we will finish with
> GUCs to turn off every fix)
> 
> -- 
> regards,
> Jaime Casanova
> 
> "Programming today is a race between software engineers striving to
> build bigger and better idiot-proof programs and the universe trying
> to produce bigger and better idiots.
> So far, the universe is winning."
>                                        Richard Cook
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
> 

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +