CODEWITHSUNDEEP
postgresqlpg-promisenode-postgres
Code
Code

Reputation: 6251

node-postgres: multi-query is atomic?

When using pg-promise (based on node-postgres), a multi-query seems to be atomic.

For example, the following PostgreSQL query does not insert any rows at all even though only the second INSERT fails due to a duplicate id. No transactions are used.

insert into mytable (id) values (1); insert into mytable (id) values (1)

This behavior seems counter-intuitive and differs from that of psql. Is this a bug?

Upvotes: 1

Views: 640

Answers (1)

vitaly-t
vitaly-t

Reputation: 25840

My tests indicate that yes, surprisingly, it is atomic, i.e. if one query fails, they all fail, same as inside a transaction.

I will investigate why that is, and post an update, if I find anything. See the open issue.

UPDATE

The investigation has confirmed that it is indeed how PostgreSQL works when multiple queries are sent in a single string.

Documentation for methods multi and multiResult has been amended accordingly:

The operation is atomic, i.e. all queries are executed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions.

Upvotes: 1

Related Questions