From: Tom Lane <tgl@sss.pgh.pa.us>
To: chris+postgresql@qwirx.com
cc: pgsql-docs@postgresql.org
Subject: Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
In-reply-to: <20160909065218.20022.15589@wrigleys.postgresql.org>
References: <20160909065218.20022.15589@wrigleys.postgresql.org>
Comments: In-reply-to chris+postgresql@qwirx.com
	message dated "Fri, 09 Sep 2016 06:52:18 -0000"
Date: Fri, 09 Sep 2016 12:39:06 -0400
Message-ID: <5491.1473439146@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

chris+postgresql@qwirx.com writes:
> 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).

This seems like a truly bizarre expectation.

I wouldn't really imagine that it's sensible to set READ ONLY
mid-transaction at all, but if it means anything to do that, surely
it ought to mean that no updates can happen *after* you set it.
Which is what your examples seem to be doing.  (I'm assuming something
you didn't state, which is that you have a deferred constraint that
causes a commit-time update in reaction to the explicit UPDATE; otherwise
SET CONSTRAINTS ALL IMMEDIATE shouldn't have any effect at all.)

			regards, tom lane


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs