Re: Autonomous Transaction is back

On Sat, Aug 15, 2015 at 10:20:55PM -0300, Alvaro Herrera wrote:
> Noah Misch wrote:
>
> > In today's scenarios, the later query cannot commit unless the suspended query
> > also commits. (Changing that is the raison d'être of autonomous
> > transactions.) If the autonomous transaction can interact with uncommitted
> > work in a way that other backends could not, crazy things happen when the
> > autonomous transaction commits and the suspended transaction aborts:
> >
> > CREATE TABLE t (c) AS SELECT 1;
> > BEGIN;
> > UPDATE t SET c = 2 WHERE c = 1;
> > BEGIN_AUTONOMOUS;
> > UPDATE t SET c = 3 WHERE c = 1;
> > UPDATE t SET c = 4 WHERE c = 2;
> > COMMIT_AUTONOMOUS;
> > ROLLBACK;
> >
> > If you replace the autonomous transaction with a savepoint, the c=3 update
> > finds no rows, and the c=4 update changes one row. When the outer transaction
> > aborts, only the original c=1 row remains live. If you replace the autonomous
> > transaction with a dblink/pg_background call, the c=3 update waits
> > indefinitely for c=2 to commit or abort, an undetected deadlock.
>
> Maybe what we need to solve this is to restrict what the autonomous
> transaction can do; for instance, make it so that the autonomous
> transaction can see all rows of the outer transaction as if the outer
> transaction were committed, but trying to update any such row raises an
> error. As far as I can see, this closes this particular problem. (We
> likely need additional rules to close all holes, but hopefully you get
> the idea.)
>
> Perhaps there exists a set of rules strong enough to eliminate all
> problematic visibility scenarios, but which still enables behavior
> useful enough to cover the proposed use cases. The audit scenario is
> covered because the audit trail doesn't need to modify the audited
> tuples themselves, only read them.

My starting expectation is that the semantics of an autonomous transaction
will be exactly those of dblink/pg_background. (I said that during the
unconference session.) The application would need to read data from tables
before switching to the autonomous section. Autonomous transactions are then
a performance and syntactic help, not a source of new semantics. Does any
database have autonomous transactions that do otherwise?