CODEWITHSUNDEEP
postgresqlpostgresql-9.1plpgsql
ADTC
ADTC

Reputation: 10121

Subtransactions in functions that can commit

I read that I can use a BEGIN-EXCEPTION block to have a subtransaction in a FUNCTION that can be rolled back. But why is it not possible to commit this subtransaction?

How can I circumvent the "all-or-nothing" transaction behavior of functions written in PL/pgSQL? Is it possible to have the function make commits using subtransactions while the outer transaction could be rolled back?

Upvotes: 2

Views: 2168

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78523

You can't circumvent it at the time of writing (PG 9.3).

Or more precisely, not directly. You can mimic autonomous subtransactions by using dblink, but be wary that doing so is a can of worms: what's supposed to happen, for instance, if your outer transaction is rolled back?

For background and references to discussions related to the topic on the PG-Hackers list, see:

http://wiki.postgresql.org/wiki/Autonomous_subtransactions

http://www.postgresql.org/message-id/[email protected]

Upvotes: 3

Related Questions