WIP: generalized index constraints

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: WIP: generalized index constraints
Date: 2009-07-06 00:28:39
Message-ID: 1246840119.19547.126.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

This is a follow up to my old proposal here:


Top pointed out a few problems here:


Here are my updated answers:

1. Not a problem with the new design, which checks the constraints from

2. Not a problem for similar reasons.

3. I don't have an answer here yet, but I have a few thoughts. I see it
as a separate proposal. My hand-waving answer is that it should be just
as possible as before to append index constraint failures to a big list,
and loop through it as long as we're making progress. If I need a more
solid proposal for this problem before my generalized constraints
proposal is considered, let me know.

To try out my patch:

(1) Apply patch to 8.5-devel and Init DB

(2) Install contrib/btree_gist (only necessary for this example, patch
works with Btree and GIN, too).

=> create table test(i int, c circle);
=> create index test_idx on test using gist(i, c);
=> UPDATE pg_index SET indconstrats = '3 3'
WHERE indexrelid='test_idx'::regclass;

In the above query, 3 is the equality strategy number for the GiST
opclass for integers, and 3 is also the "overlaps" strategy number for
the GiST opclass for circles, so we put a 3 for each attribute. What
this will mean is that it will reject any new tuple when there is
already another tuple in the table with an equal value of i AND an
overlapping value of c. Concurrency should behave identically to UNIQUE
on a btree.

(4) Now, try some inserts (concurrent or otherwise) and see what

Ultimately, I think the language for this might shape up something like:

CREATE INDEX test_idx ON test USING gist

which would avoid the need for updating the catalog, of course.


* Still not deferrable, even 'til the end of the command.
* Your constraint must be symmetric (if tuple A conflicts with tuple B,
tuple B must conflict with tuple A).
* The types have to match between the left and right arguments in the
operator class and the type of the column in the table. This is normally
true, but the GIN Array opclass works on type "anyarray", but the table
has a normal type, which causes a problem. Maybe it's possible to be
smarter about this, but the workaround is to just create more opclasses
(I believe).

Any input is appreciated (design problems, implementation, language
ideas, or anything else). I'd like to get it into shape for the July 15
commitfest if no major problems are found.

Jeff Davis

Attachment Content-Type Size
index-constraints-20090705.patch text/x-patch 15.4 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2009-07-06 06:53:03 Re: Synch Rep: direct transfer of WAL file from the primary to the standby
Previous Message Greg Stark 2009-07-06 00:23:50 Re: Merge Append Patch merged up to 85devel