The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/9.1/static/sql-set-constraints.html
Description:
We found that normally, if you execute SET TRANSACTION READ ONLY, it
prevents COMMIT from happening if any data has been changed in the
transaction (and we have been relying on this for safety).
However, SET CONSTRAINTS ALL IMMEDIATE causes this not to apply to any
subsequent changes. So it appears that the READ ONLY nature of the
transaction is implemented like a constraint.
This fails as expected:
BEGIN;
UPDATE foo SET contact='{"asdas": "1235435343"}' WHERE foo.id = 1;
SET TRANSACTION READ ONLY;
COMMIT;
This passes unexpectedly:
BEGIN;
SET CONSTRAINTS ALL IMMEDIATE;
UPDATE foo SET contact='{"asdas": "1235435343"}' WHERE foo.id = 1;
SET TRANSACTION READ ONLY;
COMMIT;
This fails as expected:
BEGIN;
SET TRANSACTION READ ONLY;
SET CONSTRAINTS ALL IMMEDIATE;
UPDATE foo SET contact='{"asdas": "1235435343"}' WHERE foo.id = 1;
COMMIT;
