Re: Inherited indexes.

Fredrik Olsson <fredrik(dot)olsson(at)treyst(dot)se> writes:
> To allow indexes to be inherited so unique, foreign keys and such works
> properly with inheritance has been on the todo for quite some time. I
> thought that most probably it is a very non trivial thing, perhaps
> completely rethinking how indexes are done.

Yup, you're right. There are a couple of major problems, to my mind:

1. A cross-table index would need to store a table OID as well as the
existing block/offset information in order to tell you what an entry is
pointing at. An extra 4 bytes per index entry (8 bytes if MAXALIGN is
8) is a lot of overhead, so you'd not want to pay that all the time.
Which means two index tuple header formats to support, which looks
painful. How can that be handled cleanly and efficiently?

2. Nobody has any idea how to handle the locking requirements. For the
most part, we assume that a lock on a table protects its associated
indexes too. What happens when an index is shared by multiple tables?
Are there deadlock problems? A particularly nasty example is that in a
unique index, inserting into one table may require visiting other tables
(that you've not even got lock on) to see if potentially conflicting
rows are still live.

> Or perhaps it is not a feature that is requested allot and therefor no
> one ever got around to it.

No, it's been requested plenty, but it looks hard. See the pghackers
archives for previous discussions.

regards, tom lane