Re: Prevent writes on large objects in read-only transactions

On Tue, May 31, 2022 at 3:49 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Yeah. Certainly we'd not want to back-patch this change, in case
> anyone is relying on the current behavior ... but it's hard to argue
> that it's not wrong.

Agreed.

> What I'm wondering about is how far the principle of read-only-ness
> ought to be expected to go. Should a read-only transaction fail
> to execute adminpack's pg_file_write(), for example? Should it
> refuse to execute random() on the grounds that that changes the
> session's PRNG state? The latter seems obviously silly, but
> I'm not very sure about pg_file_write(). Maybe the restriction
> should be "no changes to database state that's visible to other
> sessions", which would leave outside-the-DB changes out of the
> discussion.

Yeah, I think that's a pretty good idea. It's really pretty hard to
imagine preventing outside-the-database writes in any kind of
principled way. Somebody can install a C function that does anything,
and we can do a pretty fair job preventing it from e.g. acquiring a
transaction ID or writing WAL by making changes in PostgreSQL itself,
but we can't prevent it from doing whatever it wants outside the
database. Nor is it even a very clear concept definitionally. I
wouldn't consider a function read-write solely on the basis that it
can cause data to be written to the PostgreSQL log file, for instance,
so it doesn't seem correct to suppose that a C function provided by an
extension is read-write just because it calls write(2) -- not that we
can detect that anyway, but even if we could.

--
Robert Haas
EDB: http://www.enterprisedb.com