Обсуждение: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY

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;

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

On 9/9/16 12:39 PM, Tom Lane wrote:
> 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.

I think there is a bit of code missing in check_transaction_read_only().
 We prevent changing from read-only to read-write after the first query
but not vice versa.  That seems like an oversight.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 9/9/16 12:39 PM, Tom Lane wrote:
>> 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.

> I think there is a bit of code missing in check_transaction_read_only().
>  We prevent changing from read-only to read-write after the first query
> but not vice versa.  That seems like an oversight.

The comments around the code make it absolutely clear that it's
intentional, not an "oversight".  Whether it's a good idea is open
for discussion, certainly, but I don't see how you can imagine that
it wasn't considered.

            regards, tom lane

On Mon, Oct 10, 2016 at 7:59 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
>> On 9/9/16 12:39 PM, Tom Lane wrote:
>>> 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.
>
>> I think there is a bit of code missing in check_transaction_read_only().
>>  We prevent changing from read-only to read-write after the first query
>> but not vice versa.  That seems like an oversight.
>
> The comments around the code make it absolutely clear that it's
> intentional, not an "oversight".  Whether it's a good idea is open
> for discussion, certainly, but I don't see how you can imagine that
> it wasn't considered.

I seem to remember that the current state of affairs evolved near
the end of 9.1 development, when it surfaced that this GUC could be
changed at will during a transaction and that made some nice SSI
optimizations impossible.  If memory serves, Tom preferred that we
not lose the ability to change from READ WRITE to READ ONLY within
a transaction, and that wasn't hard to accommodate (we capture the
state of the flag at the start of a serializable transaction and
use that for determining serializable optimizations), so I didn't
really care.  I don't remember anyone arguing against this way at
the time.

Current behavior seems harmless and possibly useful to me, but it
seems marginal enough I wouldn't care if the change from READ WRITE
to READ ONLY was also prohibited.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company