All geek questions in one place

PHP: PGSQL driver and AutoCommit?

I use pg_connect and pg_query in the project. But am I really not sure if pg_connect is using AutoCommit mode or not?

This is an important question because I need to write some kind of transaction block, and if one of the statements is ignored by the server, the database will be inconsistent ...

Also an interesting question that pg_query commit does after execution?

For instance:

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

coincides with

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

and is

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

works in AutoCommit mode, why start and complete anyway?

Thanks for your help: dd

+4
source share
1 answer

Firstly, PostgreSQL does not have AutoCommit mode, and the pg_ * PHP API functions do not try to mimic it.

pg_query doc says

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

Thus, it guarantees that pg_query("UPDATE1 ..; UPDATE2...") is executed in one transaction and renders all or nothing on the data.

Sequence

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

equivalent to pg_query("UPDATE1 ..; UPDATE2...") with respect to data integrity (unreachable state cannot be executed).

As for the note “if there are no explicit BEGIN / COMMIT ...”, this only matters if they are not at the beginning and end of the entire chain of SQL statements. That is, pg_query("BEGIN; update1; update2; COMMIT;"); equivalent to pg_query("update1; update2;") , but (obviously) not equivalent to pg_query("update1; COMMIT; update2;")

+8
source

Source: https://habr.com/ru/post/1401403/

More articles:


All Articles