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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
