Re: Autonomous Transaction is back

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.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services