Re: looking for a globally unique row ID

W dniu 15.09.2017 o 21:30, Christopher Browne pisze:
> 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...
>

I feel obliged to add my one final comment:

If the above rocks and logs in the way, so if all this is to be
implemented by an SQL level programmer as functions .... when
application level decisions requires it (say from the lack of
imagination or knowledge of SQL/RDBM proper designs), that SQL level
programmer will have to code most of those pitfalls at higher level -
most probably incompletely. (I'm in particular not able to pinpoint all
the cases that needs to be covered - like by triggers - to have the
global uniqueness checked in all the corner cases scenarios).

But following the signature remark of the email I'm currently replaying
to: is there a way to partition the problem?

-R