Re: Request: set opclass for generated unique and primary

On Thu, 23 Feb 2006, Pavel Stehule wrote:

> >
> >Right, but does the pattern_ops one have to be unique?
> Sorry, I don't uderstand

Are you trying to guarantee uniqueness on the pattern_ops rules.  My
understanding is that pattern_ops is not just a special index that allows
like comparisons using the index while necessarily giving the same rules
throughout, it uses a different set of comparison rules from the default
index type.

I think this means that you could have a locale in which the insertion of
sequence a and then b would fail for a normal unique constraint and
succeed for one based on pattern_ops.

If the rule you are trying to guarantee is that the two values are unique
using the standard definition of unique, the latter index is insufficient
for doing so and having a unique index on (foo) and an non-unique index on
(foo whatever_pattern_ops) is not duplication.

If the rule you are trying to guarantee is that the two values are unique
using the rules as defined by pattern ops, then a unique index on (foo
whatever_pattern_ops) and an index on (foo) is not duplication, and it's
not really a unique constraint by a strict definition, and as such I'm not
convinced it should use the unique constraint syntax.

If the rule you are trying to guarantee that it's unique to both rules
(ie if either fails it's disallowed), you need both unique indexes in
order to guarantee that.

> I looked to source code and I propouse syntax:
>
> CREATE TABLE name ..
>   colname type PRIMARY KEY | UNIQUE [USING INDEX [TABLESPACE ...] [OPERATOR
> CLASS opclass]
>
> CREATE CONSTRAINT name
>   PRIMARY KEY | UNIQUE '(' colname opt_class, ....
>
> I don't think so this need big patch.
> What do you think?

I don't think it's that easy. In general I don't think there's a guarantee
that the rules the opclass uses are directly comparable to those of the
equality operator.  As such, the constraint doesn't necessarily have the
same guarantees as a standard unique constraint as given by spec.  That
means we need to be careful whenever we're making assumptions about a
unique constraint.