Re: autonomous transactions

On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> On 2 September 2016 at 09:45, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
>> <peter.eisentraut@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