Re: FK's to refer to rows in inheritance child

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Yeb Havinga <yebhavinga(at)gmail(dot)com>, PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>, "w(dot)p(dot)dijkstra(at)mgrid(dot)net" <w(dot)p(dot)dijkstra(at)mgrid(dot)net>
Subject: Re: FK's to refer to rows in inheritance child
Date: 2010-12-04 14:35:29
Message-ID: AANLkTinbyfGg3GEfw-eA6xgk+gSvui1-PV5W0UTiqyLg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Wed, Dec 1, 2010 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Perhaps I should have said "possibly workable proposal".  What you wrote
> doesn't even begin to cover the interesting part of the problem, namely
> how to ensure uniqueness is preserved in the face of concurrent
> insertions.

I think it wouldn't be very hard to implement "global indexes" which
are just regular indexes that index records from multiple tables. The
index tuple would have to have a relid as well as a ctid. Of course it
would be a lot of work to touch up all the places in the source where
indexes are assumed to reference only one table, and there may be some
tricky parts, but the index itself wouldn't be too hard to implement.

> (My current feelings about this are that a general-purpose solution
> would probably cost more than it's worth.  What people really care
> about is FK to a partitioned table, which is a structure in which
> we don't have to solve the general problem: if we know that the
> partitioning prevents the same key from appearing in multiple
> partitions, then we only have to look into one partition.  So this
> is just another item that's pending introduction of real partitioning
> infrastructure.)

I agree 100% here. Global indexes are kind of a check-list item for
Oracle that reassures people that partitioned tables are fully
functional but that people usually end up not wanting to actually use.
They remove a lot of the benefit of having partitioned tables at all.

--
greg

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Treat 2010-12-04 15:42:22 Re: Patch to add a primary key using an existing index
Previous Message Dimitri Fontaine 2010-12-04 14:15:34 Re: pg_execute_from_file review