Thread: session-wide autocommit off
Looks like I asked the wrong question the other day. How can I achieve a session-wide autocommit off? As it is possible inside a psql session via \unset AUTOCOMMIT I am using the libpq interface. TIA Regards, Christoph
am 30.11.2004, um 9:40:21 +0100 mailte Christoph Haller folgendes: > Looks like I asked the wrong question the other day. > How can I achieve a session-wide autocommit off? > As it is possible inside a psql session via > \unset AUTOCOMMIT For psql i have a /etc/psqlrc: \unset AUTOCOMMIT \set AUTOCOMMIT FALSE begin; But this works only one transaction. regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Tel. NL Heynitz: 035242/47212 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net=== Schollglas Unternehmensgruppe ===
Christoph Haller wrote: > Looks like I asked the wrong question the other day. > How can I achieve a session-wide autocommit off? > As it is possible inside a psql session via > \unset AUTOCOMMIT > I am using the libpq interface. Just BEGIN a transaction after connection? Or am I missing something here? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Christoph Haller wrote: > > Looks like I asked the wrong question the other day. > > How can I achieve a session-wide autocommit off? > > As it is possible inside a psql session via > > \unset AUTOCOMMIT > > I am using the libpq interface. > > Just BEGIN a transaction after connection? Or am I missing something here? > Since the server-side autocommit off was abolished in 7.4 I am looking for a session-wide replacement, meaning as long as one connection is running, after every COMMIT the next SQL-command triggers an implicit BEGIN. I know the usual answer to requests like this is "Fix Your Code", but the Release Notes on 7.4 state The server-side autocommit setting was removed and reimplemented in client applications and languages. Server-side autocommit was causing too many problems with languages and applications that wanted to control their own autocommit behavior, so autocommit was removed from the server and added to individual client APIs as appropriate. So basically, I cannot find the autocommit-off-switch within the libpg interface. TIA Regards, Christoph
Please, could someone point me to the right list or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq. The case is pretty urgent, as we have to move our applications to Linux and thought it's better to use PG7.4. We used to have PG7.3 under HPUX, and we think of a temporary downgrade back to 7.3 under Linux, if we cannot solve the SET AUTOCOMMIT TO OFF case. A simple "No, it's no longer available" or "Yes, it goes like ..." by someone from the core developers would be greatly appreciated. Following what I was asking the other day: Since the server-side autocommit off was abolished in 7.4 I am looking for a session-wide replacement, meaning as long as one connection is running, after every COMMIT the next SQL-command triggers an implicit BEGIN. The Release Notes on 7.4 state The server-side autocommit setting was removed and reimplemented in client applications and languages. Server-side autocommit was causing too many problems with languages and applications that wanted to control their own autocommit behavior, so autocommit was removed from the server and added to individual client APIs as appropriate. So basically, I cannot find the autocommit-off-switch within the libpq interface. TIA Regards, Christoph BTW In ./postgresql-7.4.5/doc/html/ecpg-commands.html there is still a line saying This mode can be explicitly turned off using EXEC SQL SET AUTOCOMMIT TO OFF.
Christoph Haller <ch@rodos.fzk.de> writes:
> Please, could someone point me to the right list
> or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq.
libpq does not have any support for that.
regards, tom lane
On Wednesday 01 December 2004 18:42, Tom Lane wrote: > Christoph Haller <ch@rodos.fzk.de> writes: > > Please, could someone point me to the right list > > or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq. > > libpq does not have any support for that. Does this mean libpq calls always uncommited or commited ?
sad wrote: > On Wednesday 01 December 2004 18:42, Tom Lane wrote: > >>Christoph Haller <ch@rodos.fzk.de> writes: >> >>>Please, could someone point me to the right list >>>or tell me how to do a "SET AUTOCOMMIT TO OFF" within libpq. >> >>libpq does not have any support for that. > > > Does this mean libpq calls always uncommited or commited ? 1. Connect 2. Issue "INSERT ..." The insert will be committed. 1. Connect 2. Issue "INSERT...; UPDATE...; INSERT...;" in one PQexec All 3 will be committed in one transaction 1. Connect 2. Issue "BEGIN" 3. Issue "INSERT..." 4. Issue "UPDATE..." 5. Issue "INSERT..." 6. etc Until you issue COMMIT/ROLLBACK the transaction remains open. If you had an error at step 3, then 4,5 would fail because the transaction would be aborted (but waiting for you to issue ROLLBACK). I think that covers all the combinations. -- Richard Huxton Archonet Ltd