Search code examples
postgresqlpg-promisenode-postgres

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?

Solution

  • 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.