Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY

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