I've just installed v9.0.3 on Windows XP and taking a quick look at the documentation I notice that
COMMIT is now listed as functional. I recall in earlier versions explicit COMMIT didn't actually do
anything because all commits were implicit by session (in fact issuing COMMIT used to raise an error
when using ODBC).

Can somebody please confirm that under 9.0.3 the COMMIT statement is now explicit?
Thanks

gargoyle60, 11.03.2011 12:35:

The setting for autocommit has always (at least since 8.x) been controlled by the client. I do recall that some ancient versions did allow to control the autocommit behaviour on the server side.

Regards
Thomas

On Fri, 11 Mar 2011 13:00:24 +0100, Harry Tuttle <OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

Yes, that's right for autocommit. But that's my issue really. I don't want to autocommit, I want
individual control so that I can issue an explicit commit. I can't clearly remember what the
database industry standard approach has been, but I vageuly remember that if an explicit commit was
not issued at the end of a transaction, then effectively the transaction would roll back. However,
this may vary by database vendor.

All I really want to know is can I disable autocommit and issue my own explicit commits/rollbacks as
and when I need to, thereby managing my transaction myself rather than having postgres do it
automatically?

(I will try myself later when I get the time, I just hoped somebody would know for sure)

gargoyle60, 11.03.2011 13:18:
How you control autocommit depends on the client and/or the programming language.

In psql you need to issue the command
\set AUTOCOMMIT off

In Java/JDBC, you simply call setAutocommit(false) on the connection.

So there is no general answer to your question. You mentioned ODBC, so you will need to check the ODBC documentation, but I'm sure there is an equivalent to JDBC's setAutocommit() there.

IIRC, you can alway "request" an explicit transaction by using BEGIN, even if autocommit is enabled:

BEGIN;
UPDATE ...;
INSERT ...;
DELETE ...;
COMMIT;

http://www.postgresql.org/docs/curre...sql-begin.html

How you execute these steps using your client/programming language I don't know.

Regards
Thomas

On Fri, 11 Mar 2011 13:45:58 +0100, Harry Tuttle <OTPXDAJCSJVU (AT) spammotel (DOT) com> wrote:

Okay, thanks, I'll investigate later.
FI. my client is VisualWorks Smalltalk, connecting via ODBC or the PostgreSQL EXDI.