Re: SET CONSTRAINTS ALL IMMEDIATE affects SET TRANSACTION READ ONLY

chris+postgresql(at)qwirx(dot)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