Re: Missing importing option of postgres_fdw

From: Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Missing importing option of postgres_fdw
Date: 2015-05-25 10:35:40
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2015/05/22 23:50, Tom Lane wrote:
> Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> I agree with you on that point. And I think one option for that is that
>> IMPORT FOREIGN SCHEMA only imports CHECK constraints of remote tables
>> from a remote server that have convalidated = true. (If doing so, we
>> wouldn't need to allow IMPORT FOREIGN SCHEMA to return ALTER FOREIGN
>> TABLE statements.) But I'm not sure that that is a good idea. ISTM it
>> would be better for IMPORT FOREIGN SCHEMA just to imports all CHECK
>> constraints of remote tables, reflecting convalidated, and that we leave
>> it to the user to do VALIDATE CONSTRAINT for locally defined constraints
>> that have convalidated = false when matching constraints are validated
>> on the remote server.
> It would only be safe to automatically import CHECK constraints if they
> contain no expressions that could evaluate differently on remote and local
> --- IOW, only expressions that would be safe to transmit as remote query
> conditions. I don't really think we should try to do that at all.
> For starters, while it might seem like we could use is_foreign_expr()
> on the conditions, there's no guarantee that the local server accurately
> knows what functions on the foreign server are really safe. The "is safe
> to transmit" condition isn't symmetric.
> For that matter, there's no guarantee that we could even parse the
> remote's constraint condition text without failing --- it might use SQL
> features we don't have. (We have that risk already for DEFAULT
> expressions, of course, but those tend to be a lot simpler.)

I missed that point (because I was only thinking to use this in a
sharding environment where all the servers have the same OS and PG).
Thanks for pointing it out!

> So I think worrying about convalidated is pretty pointless. Really,
> it is up to the user to determine what constraints should be attached
> to the foreign table, and IMPORT FOREIGN SCHEMA can't help much.

Agreed. So, I'd like to propose to update the docs as above. Please
find attached a patch. The existing sentence "Checking other types of
constraints is always left to the remote server." sounds to me that
constraints on foreign tables are enforced locally when updating the
foreign tables. So, I'd also like to propose to remove that sentence.
Maybe I'm missing something though.

I'll change the name and category of this in CF 2015-06, accordingly.

Best regards,
Etsuro Fujita

Attachment Content-Type Size
doc-postgres-fdw.patch text/x-diff 869 bytes

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2015-05-25 10:48:38 Re: Order of columns in query is important?!
Previous Message Emre Hasegeli 2015-05-25 10:04:18 Re: best place for "rtree" strategy numbers