Re: WIP: generalized index constraints

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: WIP: generalized index constraints
Date: 2009-08-17 05:26:20
Message-ID: 1250486780.28099.3.camel@jdavis
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

I'm going to try to get this patch ready for the 9-15 commitfest. Here
are a few design updates:

(1) Language:

I think that the new language should be a table constraint, and I think
there's a consensus on that. The specific language I have in mind is:

INDEX CONSTRAINT (<attname> <op>, ...) USING INDEX <indexname>

USING INDEX <indexname>;

Where <op> is the constraint operator. For example, if all <op>s are
"=" (or whatever the operator for BTEqualStragey is for that type), that
would be semantically identical to a UNIQUE constraint.

(2) Enforce while creating constraint:

To enforce the constraint while adding it to a table with existing data,
I'd treat it somewhat similar to a CHECK constraint: exclusive lock on
the table, and check every tuple.

Note that this is going to be O( n * log n ), assuming a search time of
O( log n ) on the index. A CHECK constraint is obviously just O(n).

(3) Catalog change:

I'll need to add a column pg_constraint.constrategies of type
int2vector. This will hold the strategy numbers of the constraint
operators. For instance, if there's a 2-column index constraint on a
BTree index, and both constraint operators are "=", it will be a vector
containing [BTEqualStrategy, BTEqualStrategy].

I will add a column pg_class.relindconstraints which will be similar to
relchecks (see below).

Also, introduce a new "contype 'i'" meaning that it's an index

The patch relies on the existence of pg_constraint.conindid, which is
already committed for 8.5.

(4) Enforce on insert procedure:

This is mostly the same as the previous patch. However, I think I need
to avoid catalog lookups in the index insert path, which is a problem
Tom pointed out in the deferrable unique constraints discussion.

My plan is to make it a part of ResultRelInfo and initialize it in a way
similar to a CHECK constraint (in ExecRelCheck() if it's not already

I would use relindconstraints to prevent going into that path during the
common case where there is no index constraint (in particular, when

Comments, suggestions, ideas?

Jeff Davis

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2009-08-17 07:34:28 Re: opportunistic tuple freezing
Previous Message Jeff Davis 2009-08-17 04:26:33 Re: opportunistic tuple freezing