On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs <si...@2ndquadrant.com> wrote: > On 2 September 2016 at 09:45, Robert Haas <robertmh...@gmail.com> wrote: >> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut >> <peter.eisentr...@2ndquadrant.com> wrote: >>> I would like to propose the attached patch implementing autonomous >>> transactions for discussion and review. >> >> I'm pretty skeptical of this approach. Like Greg Stark, Serge Rielau, >> and Constantin Pan, I had expected that autonomous transactions should >> be implemented inside of a single backend, without relying on workers. > > The problem is that we have limits on the number of concurrent > transactions, which is currently limited by the number of procs.
True. I believe this issue has been discussed before, and I think it's a solvable issue. I believe that an autonomous transaction could be made to appear to the rest of the system (outside the affected backend) as if it were a subtransaction, but then get committed before the containing transaction gets committed. This would avoid needing more PGPROCs (but getting deadlock detection to work is hard). > So doing autonomous transactions inside a single backend doesn't gain > you very much, yet it is an enormously invasive patch to do it that > way, not least because it requires you to rewrite locking and > deadlocks to make them work correctly when proc is not 1:1 with xid. > And as Serge points out it introduces other restrictions that we know > about now, perhaps more as well. Yes, but I think that if we want to really meet the needs of Oracle users who are used to being able to slap an autonomous transaction pragma on any function that they like, we're going to need a solution that is far lighter-weight than starting up a new backend. Any approach based on background workers is going to make the cost of a function call something like 4ms, which I suspect is going to make it useless for a pretty significant percentage of the cases where users want to use autonomous transactions. For example, if you want to log every attempt to access an object, this is a phenomenally expensive way to get there. Calling a per-row trigger is already pretty expensive; calling a per-row trigger that has to *launch a process for every row* is going to be insanely bad. >> That approach would be much less likely to run afoul of limits on the >> number of background workers > > That would also be an argument against using them for parallelism, yet > we used background workers there. I guess that's sort of true, but parallel query intrinsically requires multiple processes or threads, whereas autonomous transactions only require that if you pick an implementation that requires that. Also, the parallel query facility is designed to only apply to operations that are already pretty expensive, namely long-running queries, but there are lots of use cases where an autonomous transaction gets spawned to do a very small amount of work, and not infrequently in a loop. So the startup cost is a significantly more serious problem for this use case. Of course, if we could decrease the startup cost of a bgworker, that'd be good for parallel query, too, because then we could deploy it on shorter queries. But the point remains that for parallel query the planner can always decide to use a non-parallel plan if the query is cheap enough that the startup cost of a worker will be a problem. That isn't the case here; if the worker startup cost is a problem, then you'll just end up with really slow SQL code. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
