Re: our checks for read-only queries are not great

On 2020-01-09 21:52, Tom Lane wrote:
> Peter might remember more clearly, but I have a feeling that we
> concluded that the intent of the spec was for read-only-ness to
> disallow globally-visible changes in the visible database contents.

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.

> VACUUM, for example, does not cause any visible change, so it
> should be admissible. REINDEX ditto. (We ignore here the possibility
> of such things causing, say, a change in the order in which rows are
> returned, since that's beneath the spec's notice to begin with.)

agreed

> ANALYZE ditto, except to the extent that if you look at pg_stats
> you might see something different --- but again, system catalog
> contents are outside the spec's purview.

agreed

> You could extend this line of argument, perhaps, far enough to justify
> ALTER SYSTEM SET as well. But I don't like that because some GUCs have
> visible effects on the results that an ordinary query minding its own
> business can get. Timezone is perhaps the poster child there, or
> search_path. If we were to subdivide the GUCs into "affects
> implementation details only" vs "can affect query semantics",
> I'd hold still for allowing ALTER SYSTEM SET on the former group.
> Doubt it's worth the trouble to distinguish, though.

ALTER SYSTEM is read only in my mind.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services