Обсуждение: Request: set opclass for generated unique and primary key indexes

Hello

There isn't possibility change opclass for generated UNIQUE indexes. I found 
syntax for CREATE TABLE command USING INDEX TABLESPACE ..., This form can be 
enhanced to USING INDEX [TABLESPACE ..] [OPCLASS ..]

What do you think about it?

Regards
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. 
http://messenger.msn.cz/

"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> There isn't possibility change opclass for generated UNIQUE indexes.

Why would you need it?

>   USING INDEX [TABLESPACE ..] [OPCLASS ..]

This is unworkable --- consider a table with more than one unique
constraint and/or multiple-column constraints.

I seem to recall someone proposing extending the syntax of the UNIQUE
constraints themselves, but there really isn't enough use-case to
justify it AFAICS.  Especially not when you can always use CREATE UNIQUE
INDEX.
        regards, tom lane

>Why would you need it?
I can't to use unique index for like_op without setting opclass, because I 
have to use czech locale. I can create second index, but then I have two 
equal indexes. Example:

number |  description
000102  blabla bla
000103  bbbb fooo

number: varchar primary key.

Sometimes I need search all values with one prefix ~ like '0001%'. That's 
all.

>
> >   USING INDEX [TABLESPACE ..] [OPCLASS ..]
>
>This is unworkable --- consider a table with more than one unique
>constraint and/or multiple-column constraints.
>
I forgot (full syntax is):
CREATE TABLE ....  number varchar PRIMARY KEY USING OPCLAS varchar_pattern_ops, ...
>I seem to recall someone proposing extending the syntax of the UNIQUE
>constraints themselves, but there really isn't enough use-case to
>justify it AFAICS.  Especially not when you can always use CREATE UNIQUE
>INDEX.

I can always use second unique index. But it's redundant. This problem is 
related to using nonC locale.

Regards
Pavel Stehule

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/

On Wed, 22 Feb 2006, Pavel Stehule wrote:

>
>
> >Why would you need it?
> I can't to use unique index for like_op without setting opclass, because I
> have to use czech locale. I can create second index, but then I have two
> equal indexes. Example:
>
> number |  description
> 000102  blabla bla
> 000103  bbbb fooo
>
> number: varchar primary key.
>
> Sometimes I need search all values with one prefix ~ like '0001%'. That's
> all.
>
> >
> > >   USING INDEX [TABLESPACE ..] [OPCLASS ..]
> >
> >This is unworkable --- consider a table with more than one unique
> >constraint and/or multiple-column constraints.
> >
> I forgot (full syntax is):
> CREATE TABLE ....
>    number varchar PRIMARY KEY USING OPCLAS varchar_pattern_ops,

My problem with this is that the above isn't a primary key by a strict
definition and may not have the normal semantics for primary keys.  For
example, is said key a valid target for foreign keys?  I don't think it
necessarily is, unless we can always guarantee that it's unique for normal
equality as well or we allow you to specify a different equality operator
for that case which matches the one in the opclass.

> >I seem to recall someone proposing extending the syntax of the UNIQUE
> >constraints themselves, but there really isn't enough use-case to
> >justify it AFAICS.  Especially not when you can always use CREATE UNIQUE
> >INDEX.
>
> I can always use second unique index. But it's redundant. This problem is
> related to using nonC locale.

Why do you need both the unique index with varchar_pattern_ops and one
with the default ops?

> > >I seem to recall someone proposing extending the syntax of the UNIQUE
> > >constraints themselves, but there really isn't enough use-case to
> > >justify it AFAICS.  Especially not when you can always use CREATE 
>UNIQUE
> > >INDEX.
> >
> > I can always use second unique index. But it's redundant. This problem 
>is
> > related to using nonC locale.
>
>Why do you need both the unique index with varchar_pattern_ops and one
>with the default ops?

Because LIKE op don't use index on default ops with non C locale. I found it 
on tables of czech communities. Primary key is NUTS - 4..6 numbers.  I have 
to search values with some prefix -> op Like and on primary key can't to use 
std. index.

Pavel

_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

On Wed, 22 Feb 2006, Pavel Stehule wrote:

> > > >I seem to recall someone proposing extending the syntax of the UNIQUE
> > > >constraints themselves, but there really isn't enough use-case to
> > > >justify it AFAICS.  Especially not when you can always use CREATE
> >UNIQUE
> > > >INDEX.
> > >
> > > I can always use second unique index. But it's redundant. This problem
> >is
> > > related to using nonC locale.
> >
> >Why do you need both the unique index with varchar_pattern_ops and one
> >with the default ops?
>
> Because LIKE op don't use index on default ops with non C locale. I found it
> on tables of czech communities. Primary key is NUTS - 4..6 numbers.  I have
> to search values with some prefix -> op Like and on primary key can't to use
> std. index.

Right, but does the pattern_ops one have to be unique?
And if it does, do you need the normal unique constraint as well?

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

>And if it does, do you need the normal unique constraint as well?
Sometime yes. It's about using natural or generated unique values.

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?

Regards, nice a day
Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com. 
http://www.msn.cz/

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.