Thread: session-wide autocommit off

session-wide autocommit off

From
Christoph Haller
Date:
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




Re: [despammed] session-wide autocommit off

From
Andreas Kretschmer
Date:
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    === 


Re: session-wide autocommit off

From
Richard Huxton
Date:
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


Re: session-wide autocommit off

From
Christoph Haller
Date:
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




SET AUTOCOMMIT TO OFF

From
Christoph Haller
Date:
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.


Re: SET AUTOCOMMIT TO OFF

From
Tom Lane
Date:
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


Re: SET AUTOCOMMIT TO OFF

From
sad
Date:
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 ?


Re: SET AUTOCOMMIT TO OFF

From
Richard Huxton
Date:
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