Обсуждение: 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.

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