Re: Autonomous Transaction is back - Mailing list pgsql-hackers

On Sat, Aug 15, 2015 at 6:47 PM, Noah Misch <noah@leadboat.com> wrote:
> 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.
>
> Suppose you make the autonomous transaction see tuples like in the savepoint
> case.  The c=3 update finds no rows to update, and the c=4 update changes one
> row.  When the outer transaction aborts, you have two live rows (c=1 and c=4).
> Suppose you instead make the autonomous transaction see tuples like in the
> dblink case, yet let c=3 ignore the lock and change a row.  If both the
> autonomous transaction and the outer transaction were to commit, then you get
> two live rows (c=2 and c=3).  Neither of those outcomes is acceptable, of
> course.  In today's tuple update rules, c=3 must deadlock[1].  Other credible
> tuple update rules may not have this problem, but nothing jumps to mind.
>
> [1] That's not to say it must use the shmem lock structures and deadlock
> detector.

This footnote goes to my point.

It seems clear to me that having the autonomous transaction "see" the
effects of the outer uncommitted transaction is a recipe for trouble.
If the autonomous transaction updates a row and commits, and the outer
transaction later aborts, the resulting state is inconsistent with any
serial history.  I'm fairly certain that's going to leave us in an
unhappy place.

Even more obviously, ending up with two committed row versions that
are both updates of a single ancestor version is no good.

So, I agree that this scenario should be an error.  What I don't agree
with is the idea that it should be the deadlock detector's job to
throw that error.  Rather, I think that when we examine the xmax of
the tuple we can see - which is the original one, not the one updated
by the outer transaction - we should check whether that XID belongs to
an outer transaction.  If it does, we should throw an error instead of
trying to lock it.  That way (1) the error message will be clear and
specific to the situation and (2) we don't need a separate PGPROC for
each autonomous transaction.  The first of those benefits is
agreeable; the second one is, in my opinion, a key design goal for
this feature.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company