pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedSET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
5+ messages / 4 participants
[nested] [flat]
* SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
@ 2016-09-09 06:52 chris+postgresql@qwirx.com
2016-09-09 16:39 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: chris+postgresql@qwirx.com @ 2016-09-09 06:52 UTC (permalink / raw)
To: 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;
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
2016-09-09 06:52 SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY chris+postgresql@qwirx.com
@ 2016-09-09 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-10-11 00:47 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2016-09-09 16:39 UTC (permalink / raw)
To: chris+postgresql@qwirx.com; +Cc: pgsql-docs
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
2016-09-09 06:52 SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY chris+postgresql@qwirx.com
2016-09-09 16:39 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-10-11 00:47 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-10-11 00:59 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Eisentraut @ 2016-10-11 00:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; chris+postgresql@qwirx.com; +Cc: pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
2016-09-09 06:52 SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY chris+postgresql@qwirx.com
2016-09-09 16:39 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Tom Lane <tgl@sss.pgh.pa.us>
2016-10-11 00:47 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
@ 2016-10-11 00:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-10-11 19:15 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Kevin Grittner <kgrittn@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2016-10-11 00:59 UTC (permalink / raw)
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; +Cc: chris+postgresql@qwirx.com; pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY
2016-09-09 06:52 SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY chris+postgresql@qwirx.com
2016-09-09 16:39 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Tom Lane <tgl@sss.pgh.pa.us>
2016-10-11 00:47 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-10-11 00:59 ` Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY Tom Lane <tgl@sss.pgh.pa.us>
@ 2016-10-11 19:15 ` Kevin Grittner <kgrittn@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2016-10-11 19:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>; pgsql-docs
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
--
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-10-11 19:15 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-09-09 06:52 SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY chris+postgresql@qwirx.com
2016-09-09 16:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-10-11 00:47 ` Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
2016-10-11 00:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-10-11 19:15 ` Kevin Grittner <kgrittn@gmail.com>
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