From: chris+postgresql@qwirx.com
To: pgsql-docs@postgresql.org
Subject: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
Date: Fri, 09 Sep 2016 06:52:18 +0000
Message-ID: <20160909065218.20022.15589@wrigleys.postgresql.org> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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;
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: chris+postgresql@qwirx.com
Subject: Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
In-Reply-To: <20160909065218.20022.15589@wrigleys.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox