PHP: PGSQL driver and AutoCommit?

Question

I use pg_connect, and pg_query in a project. But I'm really not sure that is pg_connect using AutoCommit mode or not?

It is important question, because I need to write some block under transaction, and if one of the statements would be ignored by the server, the database would be inconsistent...

Also interesting question that do pg_query a commit after execution?

For example:

pg_query('begin; update table1...; update table2...; commit');

is same as

pg_query('begin;');
pg_query('update table1...;');
pg_query('update table2...;');
pg_query('commit');

and is the

pg_query('begin; update table1...; update table2...; commit');

working in AutoCommit mode, so begin and commit is nevertheless?

Thanks for your help: dd

Answer 1

First, there is no AutoCommit mode in PostgreSQL and the pg_* functions of the PHP API do not try to emulate one.

pg_query's doc says

When multiple statements are passed to the function, they are automatically executed as one transaction, unless there are explicit BEGIN/COMMIT commands included in the query string

So it guarantees that pg_query("UPDATE1 ..; UPDATE2...") executes in one transaction and has an all-or-nothing effect on the data.

The sequence

pg_query("BEGIN");
pg_query("UPDATE1...");
pg_query("UPDATE2..");
pg_query("COMMIT");

is equivalent to pg_query("UPDATE1 ..; UPDATE2...") with regard to data integrity (half-finished state cannot happen).

As for the note "unless there are explicit BEGIN/COMMIT...", it is relevant only if these are not at the beginning and end of the entire chain of SQL statements. That is, pg_query("BEGIN; update1; update2; COMMIT;"); is equivalent to pg_query("update1; update2;") but (obviously) not equivalent to pg_query("update1; COMMIT; update2;")