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

[Robert Haas]

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?

[Ashutosh Bapat]

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?

[Wolfgang Wilhelm]

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?

[Michael Paquier]

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?

[Craig Ringer]

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?

[Alexander Korotkov]

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