Re: [HACKERS] Transaction control in procedures

2017-11-15 14:38 GMT+01:00 Merlin Moncure <mmoncure(at)gmail(dot)com>:

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

same in Oracle PL/SQL

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