Обсуждение: Transaction started test
Hi All,
I'm new to Postgres and need a way to test if a transaction is already in progress.
The test will take place inside a trigger function in pl/pgsql and will start a new transaction only if one is not in progress ie started by a previous trigger that cascaded through to this trigger. Cannot find any such function in the docs.
Any help much appreciated.
Best regards
Larry Anderson
On 20/12/2009 9:02 PM, Larry Anderson wrote: > Hi All, > > I'm new to Postgres and need a way to test if a transaction is already > in progress. > > The test will take place inside a trigger function in pl/pgsql and will > start a new transaction only if one is not in progress You can't do that, I'm afraid. A PL/PgSQL function cannot be called without already being in a transaction. Absolutely every regular SQL statement in PostgreSQL runs in a transction. If there isn't already an open transaction, the top-level statement will start one. So: SELECT fred(); outside a transaction is equivalent to: BEGIN; SELECT fred(); COMMIT; Note that PostgreSQL supports functions, but not true stored procedures that can manipulate transactions. A Pl/PgSQL function can't commit or roll back a transaction. PostgreSQL has no support for autonomous transactions either, so you can't start a new separate transaction inside a function and commit that whether the surrounding transaction commits or rolls back. What it *does* have is subtransactions. If you need nested transactions, you can use subtransactions to get the same effect. > ie started by a > previous trigger that cascaded through to this trigger. Cannot find any > such function in the docs. In either case, the statement that caused the trigger to be invoked will have started a transaction if one was not already in progress. So you are _always_ in a transaction. (Hmm... I think this needs to be in the FAQ. Added to my TODO.). -- Craig Ringer
Hi Craig, Many thanks for the detailed and quick reply. Must admit although I'd read that every statement was implicitly in a transaction I hadn't connected that through to the operations in any associated triggers. Best regards Larry Anderson Craig Ringer wrote: > On 20/12/2009 9:02 PM, Larry Anderson wrote: >> Hi All, >> >> I'm new to Postgres and need a way to test if a transaction is already >> in progress. >> >> The test will take place inside a trigger function in pl/pgsql and will >> start a new transaction only if one is not in progress > > You can't do that, I'm afraid. > > A PL/PgSQL function cannot be called without already being in a > transaction. Absolutely every regular SQL statement in PostgreSQL runs > in a transction. If there isn't already an open transaction, the > top-level statement will start one. > > So: > > SELECT fred(); > > outside a transaction is equivalent to: > > BEGIN; > SELECT fred(); > COMMIT; > > Note that PostgreSQL supports functions, but not true stored > procedures that can manipulate transactions. A Pl/PgSQL function can't > commit or roll back a transaction. PostgreSQL has no support for > autonomous transactions either, so you can't start a new separate > transaction inside a function and commit that whether the surrounding > transaction commits or rolls back. > > What it *does* have is subtransactions. If you need nested > transactions, you can use subtransactions to get the same effect. > >> ie started by a >> previous trigger that cascaded through to this trigger. Cannot find any >> such function in the docs. > > In either case, the statement that caused the trigger to be invoked > will have started a transaction if one was not already in progress. So > you are _always_ in a transaction. > > (Hmm... I think this needs to be in the FAQ. Added to my TODO.). > > -- > Craig Ringer > > >