BOUNCE pgsql-hackers@postgreSQL.org: Imbalanced parentheses or angle brackets

>From scrappy@postgreSQL.org  Thu Feb 11 14:58:39 1999
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de [53.2.131.8])by hub.org (8.9.2/8.9.1) with SMTP
idOAA55995for <pgsql-hackers@postgreSQL.org>; Thu, 11 Feb 1999 14:58:38 -0500 (EST)(envelope-from
wieck@sapserv.debis.de)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org id m10B2Lq-000EBRC; Thu, 11 Feb 99 21:04
MET
Message-Id: <m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de>
From: jwieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Re: [SQL] RULE questions.
To: darcy@druid.net (D'Arcy" "J.M." Cain)
Date: Thu, 11 Feb 1999 21:04:42 +0100 (MET)
Cc: tgl@sss.pgh.pa.us, pgsql-hackers@postgreSQL.org
Reply-To: jwieck@debis.com (Jan Wieck)
In-Reply-To: <m10B1Ou-0000c1C@druid.net> from "D'Arcy" "J.M." Cain" at Feb 11, 99 02:03:48 pm
X-Mailer: ELM [version 2.4 PL25]
Content-Type: text

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
SQLlanguage 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,
orderby, 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
attributesappear 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
ORold.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
willnot   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
thetransaction   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
andfor in-the-middle-aborted updates it   means  so-far-I-came  more  never  committed heap tuples that   vacuum has to
sendto byte-hell).
 
   This will not appear in v6.5 (hopefully in  v6.6).  But  it's   IMHO  the  best  solution. With the mentioned RAISE,
plusthe   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
becomesthat 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@debis.com (Jan Wieck) #