Re: Unintuitive behavior regarding inheritance

>
> create table invoice_attachment (
> check (file_class = 1),
> foreign key(ref_key) references invoice(id),
> ) inherits (file_attachment);
>
> create table order_attachment (
> check(file_class = 2),
> foreign key (ref_key) references order(id),
> ) inherits (file_attachment);
>
>

While I get your proposal I am not a developer so I cannot really comment as to the cost-benefit of implementing it but my take is that there is too much variety to effectively code the automation you desire (ignoring the fact that you'd want the indexes to remain independent) and so it is left to the developer to specify exactly what is desired.

The real issue is that your child tables are distinct sub-types of the parent as opposed to being identical to the parent in all ways except for the range of allowable values- which what a partition is and the driver behind the current inheritance implementation. It would have been better if they had restricted partitions such that you could not add columns and used a syntax such as "Create Table () PARTITION OF (parent_table)" and not even attempt to support object-like inheritance. As it is now object-inheritance is only partially supported and so while you can fake sub-classing the database is incapable of properly enforcing the normal use cases.

A generally better way to implement object-inheritance is to use one-to-one tables and encapsulate using functions/view/triggers and possibly rules.

Suggestions and ideas are encouraged but are more readily received if they at least acknowledge that there are reasons for the existing behavior and that any solution needs to addres more than the single problem that is driving the suggestion, and that it generally wants to solve the problem without introducing more problems/complexity.

David J.