Re: Partitioning/inherited tables vs FKs - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Partitioning/inherited tables vs FKs
Date
Msg-id 16409.1273156692@sss.pgh.pa.us
Whole thread Raw
In response to Re: Partitioning/inherited tables vs FKs  (Florian Pflug <fgp@phlo.org>)
Responses Re: Partitioning/inherited tables vs FKs
Re: Partitioning/inherited tables vs FKs
List pgsql-hackers
Florian Pflug <fgp@phlo.org> writes:
> What lies at the heart of this problem is the lack of multi-table
> indices and hence multi-table unique constraints in postgres. AFAIK
> with those in place the rest amounts to the removal of ONLY from the
> constraint check queries plus some code to propagate constraint
> triggers to child tables.

Well, the lack of multi-table indexes certainly is the heart of the
problem, but I'm not sure that inventing such a thing is the solution.
Quite aside from the implementation difficulties involved in it,
doing things that way would destroy some of the major reasons to
partition tables at all:

* the index grows as the size of the total data set, it's not limited
by partition size

* can't cheaply drop one partition any more, you have to vacuum the
(big) index first

* probably some other things I'm not thinking of at the moment.

I think the real solution is to upgrade the partitioning infrastructure
so that we can understand that columns are unique across the whole
partitioned table, when the partitioning is done on that column and each
partition has a unique index.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: possible memory leak with SRFs
Next
From: Peter Eisentraut
Date:
Subject: Re: LD_LIBRARY_PATH versus rpath