pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: jwieck@debis.com
To: darcy@druid.net
Cc: tgl@sss.pgh.pa.us
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [SQL] RULE questions.
Date: Thu, 11 Feb 1999 21:04:42 +0100 (MET)
Message-ID: <m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de> (raw)
In-Reply-To: <m10B1Ou-0000c1C@druid.net>

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@debis.com (Jan Wieck) #





view thread (10+ messages)

Message-ID: <m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de>
Permalink:  ../m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de/
Also on:    postgresql.org/message-id/m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jwieck@debis.com, darcy@druid.net, tgl@sss.pgh.pa.us, pgsql-hackers@postgreSQL.org
  Subject: Re: [HACKERS] Re: [SQL] RULE questions.
  In-Reply-To: <m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de>
  References: <m10B1Ou-0000c1C@druid.net> <m10B2Lq-000EBRC@orion.SAPserv.Hamburg.dsh.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox