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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
