Re: Patch: Global Unique Index

On Tue, 29 Nov 2022 at 21:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I actually think that that problem should be soluble with a
> slightly different approach. The thing that feels insoluble
> is that you can't do this without acquiring sufficient locks
> to prevent addition of new partitions while the insertion is
> in progress. That will be expensive in itself, and it will
> turn ATTACH PARTITION into a performance disaster.

I think there`s a lot of room to manoeuvre here. This is a new feature
that doesn't need to be 100% complete or satisfy any existing
standard. There are lots of options for compromises that leave room
for future improvements.

1) We could just say sure ATTACH is slow if you're attaching an
non-empty partition
2) We could invent a concept like convalidated and let people attach a
partition without validating the uniqueness and then validate it later
concurrently
3) We could say ATTACH doesn't work now and come up with a better
strategy in the future

Also, don't I vaguely recall something in exclusion constraints about
having some kind of in-memory "intent" list where you declared that
you're about to insert a value, you validate it doesn't violate the
constraint and then you're free to insert it because anyone else will
see your intent in memory? There might be a need for some kind of
global object that only holds inserted keys long enough that other
sessions are guaranteed to see the key in the correct index. And that
could maybe even be in memory rather than on disk.

This isn't a simple project but I don't think it's impossible as long
as we keep an open mind about the requirements.

--
greg