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

Hi guys,
please help me to understand the proposal.
Take a simple configuration: Two "live" systems S1 and S2 and for each of them a Replica R1 and R2. So S1 sends data to R1 and S2 to R2.
S1 has foreign tables on S2 with write access, meaning you can change a few data from S1 where information is actually in the foreign table sitting as real table on S2.

So what does the system after a change and committing it do? S1 would write to S2, R1 to R2. Assuming that I'd switch off replication from S2 to R2 everything would be fine. That is what you want, don't you?What would happen when the DBA forgets to switch of the replication from S2 to R2 in your scenario? A higher workload?

What happens when R2 fails? In the S2 -> R2 configuration the changes of S2 are saved until R2 is up again, isn't it?What would happen in the case that R1 should write to R2? Is there a write or does it fail because the foreign table on R2 isn't available?

Regards,Wolfgang

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> schrieb am 3:49 Mittwoch, 18.Oktober 2017:

On Wed, Oct 18, 2017 at 9:14 AM, Craig Ringer <craig(at)2ndquadrant(dot)com> wrote:
> Superficially at least, it sounds like a good idea.

Indeed.

> We should only need a virtual xid when we're working with foreign
> tables since we don't do any local heap changes.
>
> How's it work with savepoints?

That's one thing to worry about.

At least to me, it feels like cheating to allow an INSERT query to
happen for a transaction which is read-only actually read-only because
XactReadOnly is set to true when the transaction starts. I am
wondering if we should extend BEGIN TRANSACTION with a sort of "WRITE
ONLY FOREIGN" mode, which allows read queries as well as write queries
for foreign tables, because we know that those will not generate WAL
locally. This way it would be possible to block as well INSERT queries
happening in a transaction which should be intrinsically read-only.

+        if (rte->relkind == 'f')
+            continue;
Better to use RELKIND_FOREIGN_TABLE here.
--
Michael