Subject: Re: autonomous transactions

From: Simon Riggs <[email-masked]>
Date: Thu, 6 Oct 2016 10:56:34 +0100

On 7 September 2016 at 20:46, Robert Haas <[email-masked]> wrote:
> On Sat, Sep 3, 2016 at 7:09 AM, Simon Riggs <[email-masked]> wrote:
>> On 2 September 2016 at 09:45, Robert Haas <[email-masked]> wrote:
>>> On Wed, Aug 31, 2016 at 7:20 AM, Peter Eisentraut
>>> <[email-masked]> 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

-- 
Sent via pgsql-hackers mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

---

From: Petr Jelinek <[email-masked]>
Date: Thu, 6 Oct 2016 14:12:03 +0200

> now and if we get a better one, switch it over.

+1 to this

-- 
  Petr Jelinek                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

---

From: Robert Haas <[email-masked]>
Date: Thu, 6 Oct 2016 16:27:42 -0400

> there, but Serge and Tom definitely were).

Where is the patch?

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

I don't think the posts on Craig and Serge explain why that approach
was rejected or would be a bad idea.

> 1. Use additional PGXACTs - rejected because it wouldn't provide enough room

Of course, a background worker uses a PGXACT too and a lot more, so if
you think extra PGXACTs are bad, you should *really* think background
workers are bad.

> different to what we might expect from ATs

In what way?  I think the questions of how you implement it and what
the semantics are are largely orthogonal questions.  To which proposal
is this referring?

> 3. Use background transactions (this thread)

Sure.

> 4. Use pause and resume so we don't use up too many PGXACTs

I don't know what "pause and resume" means.

> now and if we get a better one, switch it over.

I think we should implement background transactions and call them
background transactions.  That allows us to expose additional
functionality which is useful, like the ability to kick something off
and check back later for the results.  There's no reason to call it
background transactions and also call it autonomous transactions: one
feature doesn't need two names.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

---

From: Simon Riggs <[email-masked]>
Date: Thu, 6 Oct 2016 21:53:28 +0100

> feature doesn't need two names.

For myself, I don't care what you call it.

I just want to be able to invoke it by saying PRAGMA AUTONOMOUS_TRANSACTION;
and I know many others do also.
If a better implementation emerges I would happily replace this one
with another.

I'm happy to also invoke it via an alternate mechanism or API, so that
it can continue to be used even if the above mechanism changes.

We have no need to wait for the perfect solution, even assuming we
would ever agree that just one exists.