Re: [HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?

On Wed, Nov 29, 2017 at 5:07 AM, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
wrote:

> On Fri, Oct 27, 2017 at 4:44 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> > However, as Michael also points out, it's arguably wrong to allow a
> > nominally read-only transaction to write data regardless of whether it
> > works. In the case of a standby it could be argued that your
> > transaction is only read-only because you had no other choice, but
> > nonetheless that's how it is marked. I have a feeling that if we
> > extend the definition of "read only" to mean "sometimes allow writes",
> > we may regret it.
>
> I still have the same feeling. What I am sure of is that this patch is
> not the correct way to do things. So I am marking it as returned with
> feedback. This is not a rejection from my side, as I think that this
> feature could be useful in some cases, but its design needs way more
> thoughts.
>

Righ, my initial proposal was naive. This discussion raised at least two
design issues.

1) It doesn't seem OK from the design point of view to allow read-only
transaction to write, even if it's write to foreign server. One possible
solution is to distinguish three transaction types: real-only, write only
to foreign servers, read-write.
2) We have upcoming patch implementing atomic commit to multiple foreign
servers. This atomic commit feature wouldn't work on standby, since it
writes to local tables. We should detect foreign server need in real
read-write transaction for writing to foreign server, and prevent such
writes on standby.

I'll try to propose patch addressing these two issues to the next
commitfest.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company