AutonomousTransactionsUnconference2015

Autonomous Transactions

Leaders: Simon Riggs, Rajeev

Notetakers: Josh Berkus

Attendees: add your name here if you want.

Currently, only top-level transactions can actually commit. The 2ndQuadrant team has a patch that can fully commit subtransactions. It's a big patch that touches a whole bunch of code.

One wrinkle with this is SSI (serializability). You have to decide at the start of the transaction if it's going to be autonomous or not.

These are autonomous sub transactions because of how Postgres works.

Is the subtransaction treated as a completely different transaction from the master transaction? Oracle does this, and allows you to deadlock against yourself. Some folks think that's not a problem. In order to support this method, we'd have to inherit the whole of locking. If the inner transaction can block against the outer transaction's locks. Discussion about how do people want this to work.

What are the use cases?

• Josh brings up backfilling data
• Audit triggers which need to write even if the outer transaction fails
• Functions with commits and rollbacks -- same as Josh's case.

For functions, we would read the body in the main transaction, and then execute it in an autonomous transaction.

But what if we have the XMIN horizon due to the outer transaction? We still have that issue.

Simon & Rajeev have a prototyle which kinda sorta works. What use case does it solve? What does it do that we can't do now?

Do we need 70 levels of nesting, which Oracle supports? No, but if we have 2 levels of nesting we might as well have 70.

Rajeev: use case. Customer wants to give personal information on a web page. User wants to order something, but they can't because it conflicts with other orders. But we want to commit the personal information. We want to get part of the request intact, and rollback the rest.

Do we want the outer transaction to be suspended while the inner is running? Yes. Kevin suggested some way to handle locks. More discussion about locks ensued.

How do we pass data from the outer transaction to the inner one? Can the outer see the work of the inner? That's one thing Simon wants to ask. It should work the same as using pg_background to execute it, just more efficient.

Substransactions should be autonomous of each other. More discussion about what autonomy means. Suggestion that this operate at the procedure level, you call a procedure as an autonomous procedure. If you do this, maybe it's not necessary to have autonomous subtransactions.

Jim suggests that the two items are completely different use cases, and should be resolved with different tools. Are the two cases different though? Well, in the audit log case you don't care about releasing locks.

We did a show of hands, and a lot more folks were interested in the procedure case than the audit log case.

What about exceptions? We already have a model for this in PLPGSQL. We have exception blocks, this seems like a good model. Amit asks what happens to the portals.

Are commit/rollback inside procedures a different thing from autonomous subtransactions? It certainly could be, if we just made the function. But that doesn't work for the audit case. This is where the two cases are different. Maybe we should have two different features:

• batch mode: zero-transaction context, enter and exit real transations. XMIN advances.
• audit log case: need to commit subtransactions, don't care about XMIN.

We need to share more use cases and see how people want to handle them.