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