SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY

From: chris+postgresql(at)qwirx(dot)com
To: pgsql-docs(at)postgresql(dot)org
Subject: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
Date: 2016-09-09 06:52:18
Message-ID: 20160909065218.20022.15589@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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;

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Satoshi Nagayasu 2016-09-09 11:48:23 REINDEX and blocking SELECT queries
Previous Message Michael Paquier 2016-09-07 01:13:25 Re: Foreign Table docs are way to brief.