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

On Mon, Jan 13, 2020 at 5:57 AM Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 2020-01-10 14:41, Robert Haas wrote:
> > 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.
>
> I don't follow. Does pg_dump dump prepared transactions?

No, but committing one changes the database contents as seen by a
subsequent pg_dump.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company