Re: BEGIN inside transaction should be an error

Tom Lane wrote:
> Dennis Bjorklund <db(at)zigo(dot)dhs(dot)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