2017-11-15 14:38 GMT+01:00 Merlin Moncure <mmonc...@gmail.com>: > 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;'. >
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 > >
