Re: [HACKERS] Transaction control in procedures

On Tue, Nov 14, 2017 at 5:27 PM, Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> 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.

Hm, OK. Well, SQL Server (which is pretty far from the SQL standard)
works that way. See here:
http://www.4guysfromrolla.com/webtech/080305-1.shtml. DB2, which is
very close to the SQL standard, only supports COMMIT/ROLLBACK (not
begin/start etc)
https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.0/com.ibm.nz.sproc.doc/c_sproc_transaction_commits_and_rollbacks.html.
Either approach is ok I guess, and always being in a transaction
probably has some advantages. performance being an obvious one. With
DB2, the COMMIT statement acts as kind of a flush, or a paired
'commit;begin;'.

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

I'll try this out myself, but as long as we can have a *bounded*
transaction lifetime (basically the time to do stuff + 1 second) via
something like:
LOOP
<do stuff>
COMMIT;
PERFORM pg_sleep(1);
END LOOP;

... I'm good. I'll try your patch out ASAP. Thanks for answering all
my questions.

merlin