Re: [HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?
On Thu, Oct 26, 2017 at 11:14 AM, Ashutosh Bapat wrote: > In a nearby thread, we are discussing about atomic commit of > transactions involving foreign transactions. For maintaining > consistency, atomicity of transactions writing to foreign server, we > will need to create local transactions. Will that be possible on > standby? Obviously, we could add a restriction that no consistency and > atomic commit is guaranteed when foreign servers are written from a > standby. I am not sure how easy would be to impose such a restriction > and whether such a restriction would be practical. Yeah, that feature definitely can't work on a standby. But we could probably allow writes when that feature is not in use. One thing that bothers me about Alexander's patch is that there wouldn't be any way to distinguish between those two cases. Maybe we need a callback to ask the FDW "given the configured options, could you work on a standby?" 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?
On Wed, Oct 18, 2017 at 5:44 AM, Craig Ringer wrote: > On 18 October 2017 at 02:01, Alexander Korotkov > wrote: >> On Wed, Sep 6, 2017 at 4:42 PM, Alexander Korotkov >> wrote: >>> >>> We're currently blocking writing queries on standby if even they are >>> modifying contents of foreign tables. But do we have serious reasons for >>> that? >>> Keeping in the mind FDW-sharding, making FDW-tables writable from standby >>> would be good to prevent single-master bottleneck. >>> I wrote simple patch enabling writing to foreign tables from standbys. It >>> works from the first glance for me. >> >> >> No interest yet, but no objections too :-) >> I'm going to add this to next commitfest. > > Superficially at least, it sounds like a good idea. > > 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? > In a nearby thread, we are discussing about atomic commit of transactions involving foreign transactions. For maintaining consistency, atomicity of transactions writing to foreign server, we will need to create local transactions. Will that be possible on standby? Obviously, we could add a restriction that no consistency and atomic commit is guaranteed when foreign servers are written from a standby. I am not sure how easy would be to impose such a restriction and whether such a restriction would be practical. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 schrieb am 3:49 Mittwoch, 18.Oktober 2017: On Wed, Oct 18, 2017 at 9:14 AM, Craig Ringer 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?
On Wed, Oct 18, 2017 at 9:14 AM, Craig Ringer 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?
On 18 October 2017 at 02:01, Alexander Korotkov wrote: > On Wed, Sep 6, 2017 at 4:42 PM, Alexander Korotkov > wrote: >> >> We're currently blocking writing queries on standby if even they are >> modifying contents of foreign tables. But do we have serious reasons for >> that? >> Keeping in the mind FDW-sharding, making FDW-tables writable from standby >> would be good to prevent single-master bottleneck. >> I wrote simple patch enabling writing to foreign tables from standbys. It >> works from the first glance for me. > > > No interest yet, but no objections too :-) > I'm going to add this to next commitfest. Superficially at least, it sounds like a good idea. 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? -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Is anything preventing us from allowing write to foreign tables from standby?
On Wed, Sep 6, 2017 at 4:42 PM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > We're currently blocking writing queries on standby if even they are > modifying contents of foreign tables. But do we have serious reasons for > that? > Keeping in the mind FDW-sharding, making FDW-tables writable from standby > would be good to prevent single-master bottleneck. > I wrote simple patch enabling writing to foreign tables from standbys. It > works from the first glance for me. > No interest yet, but no objections too :-) I'm going to add this to next commitfest. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company