2

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?

ADTC
  • 8,999
  • 5
  • 68
  • 93

1 Answers1

3

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/20111218082812.GA14355@leggeri.gi.lan

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154