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

Greetings,

* Laurenz Albe (laurenz(dot)albe(at)cybertec(dot)at) wrote:
> On Fri, 2020-01-10 at 09:29 -0500, Tom Lane wrote:
> > > ALTER SYSTEM is read only in my mind.
> >
> > I'm still having trouble with this conclusion. I think it can only
> > be justified by a very narrow reading of "reflected in pg_dump"
> > that relies on the specific factorization we've chosen for upgrade
> > operations, ie that postgresql.conf mods have to be carried across
> > by hand. But that's mostly historical baggage, rather than a sane
> > basis for defining "read only". If somebody comes up with a patch
> > that causes "pg_dumpall -g" to include ALTER SYSTEM SET commands for
> > whatever is in postgresql.auto.conf (not an unreasonable idea BTW),
> > will you then decide that ALTER SYSTEM SET is no longer read-only?
>
> I think that having ALTER SYSTEM commands in pg_dumpall output
> would be a problem. It would cause all kinds of problems whenever
> parameters change. Thinking of the transition "checkpoint_segments"
> -> "max_wal_size", you'd have to build some translation magic into pg_dump.
> Besides, such a feature would make it harder to restore a dump taken
> with version x into version x + n for n > 0.

pg_dump already specifically has understanding of how to deal with old
options in other things when constructing a dump for a given version-
and we already have issues that a dump taken with pg_dump X has a good
chance of now being able to be restoreding into a PG X+1, that's why
it's recommended to use the pg_dump for the version of PG you're
intending to restore into, so I don't particularly agree with any of the
arguments presented above.

> > Or, perhaps, reject such a patch on the grounds that it breaks this
> > arbitrary definition of read-only-ness?
>
> I agree with Robert that such a patch should be rejected on other
> grounds.
>
> Concerning the topic of the thread, I personally have come to think
> that changing GUCs is *not* writing to the database. But that is based
> on the fact that you can change GUCs on streaming replication standbys,
> and it may be surprising to a newcomer.
>
> Perhaps it would be good to consider this question:
> Do we call something "read-only" if it changes nothing, or do we call it
> "read-only" if it is allowed on a streaming replication standby?
> The first would be more correct, but the second may be more convenient.

The two are distinct from each other and one doesn't imply the other. I
don't think we need to, or really want to, force them to be the same.

When we're talking about a "read-only" transaction that the user has
specifically asked be "read-only" then, imv anyway, we should be pretty
stringent regarding what that's allowed to do and shouldn't be allowing
that to change state in the system which other processes will see after
the transaction is over.

A transaction (on a primary or a replica) doesn't need to be started as
explicitly "read-only" and perhaps we should change the language when we
are starting up to say "database is ready to accept replica connections"
or something instead of "read-only" connections to clarify that.

Thanks,

Stephen