From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | larrya(at)blueyonder(dot)co(dot)uk |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transaction started test |
Date: | 2009-12-20 14:01:45 |
Message-ID: | 4B2E2E49.6090805@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lincoln Yeoh | 2009-12-20 15:04:57 | Re: Justifying a PG over MySQL approach to a project |
Previous Message | Craig Ringer | 2009-12-20 13:19:13 | Re: Extracting SQL from logs in a usable format |