Re: [HACKERS] Re: [SQL] RULE questions.

"D'Arcy" "J.M." Cain <darcy(at)druid(dot)net> writes:
> Second, an option to CREATE INDEX to make the index case insensitive.

That, at least, we can already do: build the index on lower(field) not
just field. Or upper(field) if that seems more natural to you.

> Also, in a primary key field (or
> unique index) it would be nice if "A" was rejected if "a" already was
> in the database.

Making either of the above a UNIQUE index should accomplish that.

regards, tom lane

Thus spake Tom Lane
> "D'Arcy" "J.M." Cain <darcy(at)druid(dot)net> writes:
> > Second, an option to CREATE INDEX to make the index case insensitive.
>
> That, at least, we can already do: build the index on lower(field) not
> just field. Or upper(field) if that seems more natural to you.

Almost. I guess I wasn't completely clear. Here's an example.

darcy=> create table x (a int, t text);
CREATE
darcy=> create unique index ti on x (lower(t) text_ops);
CREATE
darcy=> insert into x values (1, 'abc');
INSERT 19021 1
darcy=> insert into x values (2, 'ABC');
ERROR: Cannot insert a duplicate key into a unique index
darcy=> insert into x values (2, 'Def');
INSERT 19023 1
darcy=> select * from x;
a|t
-+---
1|abc
2|Def
(2 rows)

darcy=> select * from x where t = 'ABC';
a|t
-+-
(0 rows)

Note that it prevented me from adding the upper case dup just fine. The
last select is the issue. It's necessary for the user to know how it is
stored before doing the select. I realize that you can do this.

darcy=> select * from x where lower(t) = 'abc';

But other systems make this more convenient by just making 'ABC' and 'abc'
equivalent.

Mind you, it may not be possible in our system without creating a new,
case-insensitive type.

> > Also, in a primary key field (or
> > unique index) it would be nice if "A" was rejected if "a" already was
> > in the database.
>
> Making either of the above a UNIQUE index should accomplish that.

True. I'm thinking of the situation where you want the primary key to
be case-insensitive. You can't control that on the auto-generated
unique index so you have to add a second unique index on the same
field. Again, perhaps a new type is the proper way to handle this.

Speaking of primary keys, there's one more thing needed to make primary
support complete, I think. Didn't we originally say that a primary
key field was immutable? We should be able to delete the record but
not change the value of the field in an update. Would this be hard
to do?

--
D'Arcy J.M. Cain <darcy(at){druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.

D'Arcy J.M. Cain wrote:

> But other systems make this more convenient by just making 'ABC' and 'abc'
> equivalent.
>
> Mind you, it may not be possible in our system without creating a new,
> case-insensitive type.

And that wouldn't be too hard. For example, implementing
citext (case insensitive text) could use text's input/output
functions and all the things for lower/upper case conversion,
concatenation, substring etc (these are SQL language wrappers
as we already have tons of). Only new comparision operators
have to be built that compare case insensitive and then
creating a new operator class for it. All qualifications and
the sorting in indices, order by, group by are done with the
operators defined for the type.

Also comparision wrappers like to compare text = citext would
be useful, which simply uses citext_eq().

> > Making either of the above a UNIQUE index should accomplish that.
>
> True. I'm thinking of the situation where you want the primary key to
> be case-insensitive. You can't control that on the auto-generated
> unique index so you have to add a second unique index on the same
> field. Again, perhaps a new type is the proper way to handle this.

The above citext type would inherit this auto.

>
> Speaking of primary keys, there's one more thing needed to make primary
> support complete, I think. Didn't we originally say that a primary
> key field was immutable? We should be able to delete the record but
> not change the value of the field in an update. Would this be hard
> to do?

No efford on that. I'm planning to reincarnate attribute
specification for rules and implement a RAISE statement. The
attributes (this time it will be multiple) suppress rule
action completely if none of the attributes appear in the
queries targetlist (what they must on UPDATE to change).

So at create table time, a rule like

CREATE RULE somename AS ON UPDATE TO table
ATTRIBUTE att1, att2
WHERE old.att1 != new.att1 OR old.att2 != old.att2
DO RAISE EXCEPTION 'Primary key of "table" cannot be changed';

could be installed. As long as nobody specifies the fields of
the primary key in it's update, the rewrite system will not
add the RAISE query to the querytree list, so no checking is
done at all.

But as soon as one of the attributes appears in the UPDATE,
there will be one extra query RAISE executed prior to the
UPDATE itself and check that all the new values are the old
ones. This would have the extra benefit, that the transaction
would abort BEFORE any changes have been made to the table at
all (remember that UPDATE in Postgres means another heap
tuple for each touched row and one more invalid tuple for
vacuum to throw away and for in-the-middle-aborted updates it
means so-far-I-came more never committed heap tuples that
vacuum has to send to byte-hell).

This will not appear in v6.5 (hopefully in v6.6). But it's
IMHO the best solution. With the mentioned RAISE, plus the
currently discussed deferred queries etc. we would have the
rule system ready to support ALL the constraint stuff
(cascaded delete, foreign key). But the more we use the rule
system, the more important it becomes that we get rid of the
block limit for tuples.

I think it would be better to spend your efford on that
issue.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck(at)debis(dot)com (Jan Wieck) #