WIP: generalized index constraints - Mailing list pgsql-hackers

This is a follow up to my old proposal here:

http://archives.postgresql.org/pgsql-hackers/2008-06/msg00404.php

Top pointed out a few problems here:

http://archives.postgresql.org/pgsql-hackers/2008-06/msg00427.php

Here are my updated answers:

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

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).

(3)
  => 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
happens.

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

CREATE INDEX test_idx ON test USING gist
  (i CONSTRAINT =, c CONSTRAINT &&);

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

Limitations:

 * 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.

Regards,
    Jeff Davis