Re: Missing importing option of postgres_fdw

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