Re: [HACKERS] Transaction control in procedures

On 11/14/17 16:33, Merlin Moncure wrote:
>> One detail in your example is that when you enter the procedure, you are
>> already in a transaction, so you would have to run either COMMIT or
>> ROLLBACK before the START TRANSACTION.
>
> Ok, that's good, but it seems a little wonky to me to have to issue
> COMMIT first. Shouldn't that be the default? Meaning you would not
> be *in* a transaction unless you specified to be in one.

But that's not how this feature is defined in the SQL standard and AFAIK
other implementations. When you enter the procedure call, you are in a
transaction. For one thing, a procedure does not *have* to do
transaction control. So if it's a plain old procedure like a function
that just runs a few statements, there needs to be a transaction. We
can't know ahead of time whether the procedure will execute a
transaction control statement and then retroactively change when the
transaction should have started. Something like an autonomous
transaction procedure might work more like that, but not this feature.

>> Also, you can't run SET TRANSACTION ISOLATION through SPI, so one would
>> have to implement a separate code path for that, but that would just be
>> a bit of leg work.
>
> Roger -- I'm more interested in if your design generally supports this
> being able to this (either now or in the future...).

Nothing in this patch really changes anything about how transactions
themselves work. So I don't see why any of this shouldn't work. As I
fill in the gaps in the code, I'll make sure to come back around to
this, but for the time being I can't say anything more.

> Can we zero in on this? The question implied, 'can you do this
> without being in a transaction'? PERFORM do_stuff() is a implicit
> transaction, so it ought to end when the function returns right?
> Meaning, assuming I was not already in a transaction when hitting this
> block, I would not be subject to an endless transaction duration?

In the server, you are always in a transaction, so that's not how this
works. I think this also ties into my first response above.

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