Обсуждение: [HACKERS] Is anything preventing us from allowing write to foreign tables from standby?

On 18 October 2017 at 02:01, Alexander Korotkov
<a.korotkov@postgrespro.ru> wrote:
> On Wed, Sep 6, 2017 at 4:42 PM, Alexander Korotkov
> <a.korotkov@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.

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

On Wed, Oct 18, 2017 at 9:14 AM, Craig Ringer <craig@2ndquadrant.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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Wed, Oct 18, 2017 at 5:44 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 18 October 2017 at 02:01, Alexander Korotkov
> <a.korotkov@postgrespro.ru> wrote:
>> On Wed, Sep 6, 2017 at 4:42 PM, Alexander Korotkov
>> <a.korotkov@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.
>
> 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

On Thu, Oct 26, 2017 at 11:14 AM, Ashutosh Bapat
<ashutosh.bapat@enterprisedb.com> 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

On Fri, Oct 27, 2017 at 4:44 PM, Robert Haas <robertmhaas@gmail.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.
-- 
Michael