Re: inherit support for foreign tables

(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(dot)hanada(at)gmail(dot)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 change the 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