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