On Fri, Jan 10, 2020 at 7:23 AM Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > I don't really remember, but that was basically the opinion I had > arrived at as I was reading through this current thread. Roughly > speaking, anything that changes the database state (data or schema) in a > way that would be reflected in a pg_dump output is not read-only.
This rule very nearly matches the current behavior: it explains why temp table operations are allowed, and why ALTER SYSTEM is allowed, and why REINDEX etc. are allowed. However, there's a notable exception: PREPARE, COMMIT PREPARED, and ROLLBACK PREPARED are allowed in a read-only transaction. Under the "doesn't change pg_dump output" criteria, the first and third ones should be permitted but COMMIT PREPARED should be denied, except maybe if the prepared transaction didn't do any writes (and in that case, why did we bother preparing it?). Despite that, this rule does a way better job explaining the current behavior than anything else suggested so far. Accordingly, here's v3, with comments adjusted to match this new explanation for the current behavior. This seems way better than what I had before, because it actually explains why stuff is the way it is rather than just appealing to history. BTW, there's a pending patch that allows CLUSTER to change the tablespace of an object while rewriting it. If we want to be strict about it, that variant would need to be disallowed in read only mode, under this definition. (I also think that it's lousy syntax and ought to be spelled ALTER TABLE x SET TABLESPACE foo, CLUSTER or something like that rather than anything beginning with CLUSTER, but I seem to be losing that argument.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
v3-0001-Fix-problems-with-read-only-query-checks-and-refa.patch
Description: Binary data
