Thread: Understanding autocommit

I'm looking at these two pages:

http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html

According to the first page, autocommit is off by default for
"embedded SQL programs". Does this mean everything except the 'psql'
command line interpreter, or is that some special case, and most
programs default to autocommit on?

Currently, I have explicit 'begin' statements in all our code. What
I'd like to achieve is DB2-style semantics where a transaction is
automatically and implicitly opened as soon as any query is performed,
and that transaction remains until committed or rolled back (or until
end of session implicit rollback).

I'm sure there's something really obvious here, but... how do I find
out whether my program's running in autocommit mode or not?

Thanks!

Chris Angelico

Chris Angelico wrote:
> I'm looking at these two pages:
>
> http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
> http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html
>
> According to the first page, autocommit is off by default for
> "embedded SQL programs". Does this mean everything except the 'psql'
> command line interpreter, or is that some special case, and most
> programs default to autocommit on?
>
> Currently, I have explicit 'begin' statements in all our code. What
> I'd like to achieve is DB2-style semantics where a transaction is
> automatically and implicitly opened as soon as any query is performed,
> and that transaction remains until committed or rolled back (or until
> end of session implicit rollback).
>
> I'm sure there's something really obvious here, but... how do I find
> out whether my program's running in autocommit mode or not?

An "embedded SQL program" is a program written in C that makes use
of ecpg.

The server itself has autocommit, so every program that uses
PostgreSQL will be in autocommit mode by default.

The only way around that are explicit BEGIN or START TRANSACTION
commands (which is what ecpg uses).

Yours,
Laurenz Albe

On Wed, Aug 22, 2012 at 6:52 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Chris Angelico wrote:
>> I'm looking at these two pages:
>>
>> http://www.postgresql.org/docs/9.1/static/ecpg-sql-set-autocommit.html
>> http://www.postgresql.org/docs/9.1/static/sql-start-transaction.html
>>
>> I'm sure there's something really obvious here, but... how do I find
>> out whether my program's running in autocommit mode or not?
>
> An "embedded SQL program" is a program written in C that makes use
> of ecpg.
>
> The server itself has autocommit, so every program that uses
> PostgreSQL will be in autocommit mode by default.
>
> The only way around that are explicit BEGIN or START TRANSACTION
> commands (which is what ecpg uses).

Thanks Albe.

I've poked around a bit more with this question, and it seems that the
functionality was moved out of the server and into the client. It's no
longer possible to have autocommit set to anything other than true.
(But in case anyone else is wondering, the query "show autocommit" is
what I was looking for, to find out what current status is.)

So I guess now I need to check out the PHP bindings, since all my C++
code uses libpqxx and much better handling of transactions. (And I
rather doubt that you guys will know what Pike is, much less whether
it can automatically open a transaction and not commit it when a query
comes in.) I'd really rather get off PHP altogether, but that isn't
looking likely at the moment!

ChrisA