Re: Proposal: GiST constraints

> I would like to consider adding constraints to GiST indexes. I think it
> is possible to add constraints that are more sophisticated than just
> UNIQUE. My use case is a non-overlapping constraint, but I think it's
> possible for this to be more general.
Sounds good

> The idea is to make an array in shared memory with size equal to
> max_connections. Each element of the array would hold the oid of an
> index, and a tid.

Just notice: due to GiST's organization it can store type not the same as in
indexed column and even type is the same, value may be lossy compressed. So
comparing two values from index it's possible to say that they are not
overlapped or MAY be overlapped.

> Then, search the GiST index using the same function to determine if
> conflicts exist in the index. If conflicts exist in the index, check the
> visibility information for the tuple and proceed, wait or abort (in the
> same way as above). If no conflicts exist, insert.
>

Again, table for matching two value by equality and overlap operator, each value
is taken from index or from heap:
| heap value | index value
------------+-------------+-------------
heap value | yes | yes(lossy!)
index value | yes(lossy!) |yes-equality, no-overlap

equality(index value, index value) - is a GISTSTATE->equalFn method.

Next, you will need to define by some ways about semantic meaning of operation,
i.e. '=' - is equal operation, '&&' - is overlap operation.
Equal operation can be determined by corresponding BTree opclass, but I don't
have an idea how to determine overlap operation.

> This should work fine for multi-column indexes where the constraints for
> each column are different. For instance, unique and non-overlapping
> could be mixed.

Hmm, isn't it too complicated? Look, you suggest something like following (Btree
example):
CREATE INDEX idx ON table USING BTREE ( col1, col2 unique);

Is col2 should be unique across the whole index or just for the same col1?

--
Teodor Sigaev E-mail: teodor(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/