Re: 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