On Tue, Nov 14, 2017 at 5:27 PM, Peter Eisentraut <peter.eisentr...@2ndquadrant.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
