Обсуждение: 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