Re: WIP: generalized index constraints

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Sat, 2009-09-19 at 14:05 -0400, Tom Lane wrote:
>> What about them? It's not clear why you think this requires anything
>> special.

>> From a syntax standpoint, I need to represent one operator for every
> index column involved in the constraint. So, if there's a functional
> index on ((a||b)::circle), I clearly can't have an exclusion constraint
> like (a =, b =).

> I see two options:

> 1. (<expr> <op>), where <expr> is an expression over table attributes
> that must have the exact signature as the expression for the index.
> 2. (<index_col> <op>), and then read the expression from the index

You need to do (1), I think, because (2) seems to require using the
index column name. We have generally felt that the names assigned
to index columns are implementation artifacts that the user ought not
rely on in SQL commands.

> and in either case, use that expression for the extra checking that I
> need to do: I need to check whether the input heap tuple conflicts with
> concurrently inserting heap tuples, and I also need to do a recheck
> step.

I haven't read the patch, but this whole discussion sounds to me like
it means you're trying to plug things in at the wrong level. Indexes
generally don't care where the values they are storing came from ---
whether it's a simple column or a expression result, it's all the same
to the index. I don't see why that shouldn't be true for exclusion
constraints too.

BTW, further betraying that I've not read the patch: what exactly are
you doing about the information_schema views? If we are treating these
things as SQL constraints, one would expect them to show up in
information_schema; but I don't see how to represent them there in any
adequate fashion, even without the expression-index angle. On the whole
I think we'd be a lot better off to NOT consider them to be constraints,
but just another option for CREATE INDEX.

regards, tom lane