Re: Unintuitive behavior regarding inheritance

On Jul 9, 2011, at 9:21, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:

> On Sat, Jul 9, 2011 at 6:09 AM, Guillaume Lelarge
> <guillaume(at)lelarge(dot)info> wrote:
>
>>
>> To have a primary key or a unique key on an partitioned table, it would
>> mean that we should be able to have one index on multiple tables.
>> Because primary key and unique constraints are enforced with an index.
>> That's not something easy to do, and I guess it would make the index
>> bigger, which isn't performance savvy.
>
> I don't think you necessarily have to go as far as creating
> cross-table indexes. In the case I am looking at, one of the values I
> partition on is part of the primary key, so collisions across
> partitions can be avoided in this way.
>
> However, the problem here is that this also makes it far more
> difficult to create partitioned tables which reference keys on table
> partitions because those keys have to be defined on each partition.
> Simply creating per-partition indexes would be sufficient for that use
> case. Otherwise it becomes relatively maintenance heavy and quite
> error prone.
>
> I agree that this wouldn't get us to what would make everyone happy,
> but it would create reasonable workarounds for when one needs fkeys
> against partitioned tables....
>
>
Table inheritance has it's flaws. They are well-documented and often discussed.

Your last two paragraphs and unintelligible to me. If you FK the parent table in an Inheritance scheme the system will only check the parent table and not any inheritors.

While I am unfamiliar with why indexes and FK constraints are not copied they can be added quite easily manually to any table I create, and from the docs I am fully aware they are indeed not copied.

And yes, I understand that because something is written doesn't mean it is understood. Understanding usually comes via experience which you either earn or recruit. Partitioning is an advanced feature with, in it's current implementation, enough caveats to make it dangerous to use. But, band-aids are not going to be enough. A full graft is needed in order to maintain backward compatibility while implementing a system that meets the identified needs of the community.

David J.