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
