Re: [GENERAL] Transaction started test
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Transaction started test
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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