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

On 2020-01-13 20:14, Robert Haas wrote:
> 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.

Well, if the transaction was declared read-only, then committing it
(directly or 2PC) shouldn't change anything. This appears to be a
circular argument.

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