Re: inherit support for foreign tables - Mailing list pgsql-hackers

(2014/02/19 12:12), Kyotaro HORIGUCHI wrote:
> At Tue, 18 Feb 2014 19:24:50 +0900, "Etsuro Fujita" wrote
>>> From: Shigeru Hanada [mailto:shigeru.hanada@gmail.com]

>>> I'm not sure that allowing ALTER TABLE against parent table affects
>>> descendants even some of them are foreign table.  I think the rule should
>>> be simple enough to understand for users, of course it should be also
>>> consistent and have backward compatibility.
>>
>> Yeah, the understandability is important.  But I think the
>> flexibility is also important.  In other words, I think it is a
>> bit too inflexible that we disallow e.g., SET STORAGE to be set
>> on an inheritance tree that contains foreign table(s) because
>> we disallow SET STORAGE to be set on foreign tables directly.
>
> What use case needs such a flexibility precedes the lost behavior
> predictivity of ALTER TABLE and/or code "maintenancibility"(more
> ordinally words must be...) ? I don't agree with the idea that
> ALTER TABLE implicitly affects foreign children for the reason in
> the upthread. Also turning on/off feature implemented as special
> syntax seems little hope.

It is just my personal opinion, but I think it would be convenient for 
users to alter inheritance trees that contain foreign tables the same 
way as inheritance trees that don't contain any foreign tables, without 
making user conscious of the inheritance trees contains foreign tables 
or not.  Imagine we have an inheritance tree that contains only plain 
tables and then add a foreign table as a child of the inheritance tree.  Without the flexiblity, we would need to
changethe way of altering 
 
the structure of the inheritance tree (e.g., ADD CONSTRAINT) to a 
totally different one, immediately when adding the foreign table.  I 
don't think that would be easy to use.

>> What I think should be newly allowed to be set on foreign tables is
>>
>> * ADD table_constraint
>> * DROP CONSTRAINT
>
> As of 9.3
>
> http://www.postgresql.org/docs/9.3/static/sql-alterforeigntable.html
>
>> Consistency with the foreign server is not checked when a
>> column is added or removed with ADD COLUMN or DROP COLUMN, a
>> NOT NULL constraint is added, or a column type is changed with
>> SET DATA TYPE. It is the user's responsibility to ensure that
>> the table definition matches the remote side.
>
> So I belive implicit and automatic application of any constraint
> on foreign childs are considerably danger.

We spent a lot of time discussing this issue, and the consensus is that 
it's users' fault if there are some tuples on the remote side violating 
a given constraint, as mentioned in the documentation.

>> * [NO] INHERIT parent_table
>
> Is this usable for inheritance foreign children? NO INHERIT
> removes all foreign children but INHERIT is nop.

I didn't express clearly.  Sorry for that.  Let me explain about it.

* ALTER FOREIGN TABLE target_table *INHERIT* parent_table: Add the 
target table as a new child of the parent table.
* ALTER FOREIGN TABLE target_table *NO INHERIT* parent_table: Remove the 
target table from the list of children of the parent table.

Thanks,

Best regards,
Etsuro Fujita