Re: autonomous transactions

On 2 September 2016 at 09:45, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)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.

We could expand that but given the current way we do snapshots there
will be realistically low limits however we do this.

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.

Given that I've already looked into the "single backend" approach in
detail, it looks to me that Peter's approach is viable and robust.

> 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.

> , and it will probably perform
> considerably better too, especially when the autonomous transaction
> does only a small amount of work, like inserting a log message
> someplace. That is not to say that providing an interface to some
> pg_background-like functionality is a bad idea; there's been enough
> interest in that from various quarters to suggest that it's actually
> quite useful, and I don't even think that it's a bad plan to integrate
> that with the PLs in some way. However, I think that it's a different
> feature than autonomous transactions. As others have also noted, it
> can be used to fire-and-forget a command, or to run a command while
> foreground processing continues, both of which would be out of scope
> for an autonomous transaction facility per se. So my suggestion is
> that you pursue the work but change the name.

We agree that we like the infrastructure Peter has provided is useful,
so that part can go ahead.

If that infrastructure can be used in a simple syntactic way to
provide Autonomous Transactions, then I say let that happen. Peter,
please comment on how much infrastructure is required to implement ATs
this way?

If somebody believes there is a better way for ATs, that is just an
optimization of the limits and can occur later, if the people who
believe there is a better way can prove that is the case and come up
with a patch. It's OK for features to go in now and have better
infrastructure later, e.g. LISTEN/NOTIFY.

I would very much like to see ATs in v10 and this is a viable approach
with working code.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services