Re: [Review] inherit support for foreign tables

(2014/01/25 11:27), Shigeru Hanada wrote:
> 2014/1/23 Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>:
>> Shigeru Hanada wrote:
>>> Though this would be debatable, in current implementation, constraints
>>> defined on a foreign table (now only NOT NULL and CHECK are supported)
>>> are not enforced during INSERT or UPDATE executed against foreign
>>> tables. This means that retrieved data might violates the constraints
>>> defined on local side. This is debatable issue because integrity of
>>> data is important for DBMS, but in the first cut, it is just
>>> documented as a note.
>>
>> I agree with you, but we should introduce a new keyword such as
>> ASSERTIVE or something in 9.4, in preparation for the enforcement of
>> constraints on the local side in a future release? What I'm concerned
>> about is, otherwise, users will have to rewrite those DDL queries at
>> that point. No?
>
> In the original thread, whether the new syntax is necessary (maybe
> ASSERTIVE will not be used though) is under discussion.  Anyway, your
> point, decrease user's DDL rewrite less as possible is important.
> Could you post review result and/or your opinion as the reply to the
> original thread, then we include other people interested in this
> feature can share discussion.

OK  I'll give my opinion in that thread.

>>> For other commands recursively processed such as ANALYZE, foreign
>>> tables in the leaf of inheritance tree are ignored.
>>
>> I'm not sure that in the processing of the ANALYZE command, we should
>> ignore child foreign tables. It seems to me that the recursive
>> processing is not that hard. Rather what I'm concerned about is that if
>> the recursive processing is allowed, then autovacuum will probably have
>> to access to forign tables on the far side in order to acquire those
>> sample rows. It might be undesirable from the viewpoint of security or
>> from the viewpoint of efficiency.
>
> As you say, it's not difficult to do recursive ANALYZE including
> foreign tables.  The reason why ANALYZE ignores descendant foreign
> tables is consistent behavior.  At the moment, ANALYZE ignores foreign
> tables in top-level (non-child) when no table name was given, and if
> we want to ANALYZE foreign tables we need to specify explicitly.
>
> I think it's not so bad to show WARNING or INFO message about foreign
> table ignorance, including which is not a child.

Yeah, the consistency is essential for its ease of use.  But I'm not 
sure that inherited stats ignoring foreign tables is actually useful for 
query optimization.  What I think about the consistency is a) the 
ANALYZE command with no table names skips ANALYZEing inheritance trees 
that include at least one foreign table as a child, but b) the ANALYZE 
command with a table name indicating an inheritance tree that includes 
any foreign tables does compute the inherited stats in the same way as 
an inheritance tree consiting of only ordinary tables by acquiring the 
sample rows from each foreign table on the far side.

Thanks,

Best regards,
Etsuro Fujita