Re: looking for a globally unique row ID

On 15 September 2017 at 14:45, Adam Brusselback
<adambrusselback(at)gmail(dot)com> wrote:
>> I cannot image a single postgres index covering more than one physical
>> table. Are you really asking for that?
>
>
> While not available yet, that is a feature that has had discussion before.
> Global indexes are what i've seen it called in those discussions. One of
> the main use cases is to provide uniqueness across multiple tables, which
> would also allow things like foreign keys on partitioned tables.

It certainly does come up periodically; it seems like a challengingly different
thing to implement (as compared to "regular" indexes), from two perspectives:

a) The present index implementation only needs to reference tuples from one
table, so the tuple references can be direct heap references.

If multiple tables (partitions being the most obvious case) were to be covered,
each index entry would also require indication of which table it comes from.

b) Referencing which index entries can be dropped (e.g. - vacuumed out)
is a fair bit more complex because the index entries depend on multiple
tables. This adds, um, concurrency complications, if data is being deleted
from multiple tables concurrently. (Over-simplifying question: "When
a table that participates in the sharing is vacuumed, does the shared
index get vacuumed? What if two such tables are vacuumed concurrently?")

This has added up to make it not an easy thing to implement.

To be sure, if a shared index required greatly worsened locking to do
maintenance, or suffered from inability to keep it tidy, that would make the
feature of rather less interest...
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"