BEGIN inside transaction should be an error
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
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
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
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
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
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
Import Notes
Resolved by subject fallback
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.
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
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...
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/
--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
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.
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"?
--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
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
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...
Show quoted text
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
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>
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; ;)
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
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/
Show quoted text
From each according to his ability. To each according to his ability to litigate.
