Re: FDW for PostgreSQL

Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> On 2013-02-21 14:23:35 +0000, Albe Laurenz wrote:
>> Tom Lane wrote:
>>> Another thing I was wondering about, but did not change, is that if we're
>>> having the remote transaction inherit the local transaction's isolation
>>> level, shouldn't it inherit the READ ONLY property as well?

>> That seems to me like it would be the right thing to do.

> I am not 100% convinced of that. There might be valid usecases where a
> standby executes queries on the primary that executes that do DML. And
> there would be no way out of it I think?

How exactly would it do that via an FDW? Surely if the user tries to
execute INSERT/UPDATE/DELETE against a foreign table, the command would
get rejected in a read-only transaction, long before we even figure out
that the target is a foreign table?

Even granting that there's some loophole that lets the command get sent
to the foreign server, why's it a good idea to allow that? I rather
thought the idea of READ ONLY was to prevent the transaction from making
any permanent changes. It's not clear why changes on a remote database
would be exempted from that.

(Doubtless you could escape the restriction anyway with dblink, but that
doesn't mean that postgres_fdw should be similarly ill-defined.)

regards, tom lane