Re: autonomous transactions

On 7 September 2016 at 20:46, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> 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.
>
> 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).

Just to point out that I've actually written this approach already.
The patch is available, Autonomous Subtransactions.
We discussed it in Ottawa and it was rejected. (I thought Robert was
there, but Serge and Tom definitely were).

See other posts in this thread by Serge and Craig to explain why.

I take your point that startup time may be not as good as it can be.
But what Peter has works and is useful, whatever we call it.

We have various approaches... summarised in chronological order of
their suggestion

1. Use additional PGXACTs - rejected because it wouldn't provide enough room
2. Use Autonomous SubTransactions - rejected because the semantics are
different to what we might expect from ATs
3. Use background transactions (this thread)
4. Use pause and resume so we don't use up too many PGXACTs

What I see is that there are some valid and useful features here and
we should be developing them further because they have other benefits.

* Autonomous Subtransactions might not work for Autonomous
Transactions, but they are useful for incremental loading of large
data, which then allows easier logical decoding. So that sounds like
we should do that anyway, even if they are not ATs. They can also be
used within parallel tasks.

* Background Transactions are useful and we should have them.

* The labelling "Autonomous Transaction" is a simple coat of paint,
which can easily be transferred to a better implementation if one
comes. If one doesn't then its better to have something than nothing.
So I suggest we commit Background Transactions first and then in a
fairly thin commit, implement Autonomous Transactions on top of it for
now and if we get a better one, switch it over.

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