Re: [HACKERS] CONSTRAINTS... - Mailing list pgsql-hackers

>
> >From "A Guide to The SQL standard" C.J.DATE:
>
> FOREIGN KEY Syntax:
>
> [Good description of foreign key constraints - tnx]
>
>
> Jackson, DeJuan wrote:
> >
> > So, could someone send me the SQL92 constraints syntax as well as the
> > definition of what a deferrable constraint is supposed to be?
> > ADVthanksANCE
> >         -DEJ
>
> -Jose'-

    This  reminds  me  on  one  of my personal TODO's, because it
    show's to me that  an  implementation  of  constraints  using
    triggers or the like wouldn't be such a good idea. Especially
    the part on deferred constraint checks would mean  a  lot  of
    buffering to do the checks at the end.

    My idea on constraints was to use the rewrite rule system for
    them.   I  wanted  first  to  implement  another  optimizable
    statement - RAISE.  RAISE is mostly the same as a SELECT, but
    the result will not be sent to the frontend. Instead it  will
    produce some formatted elog message(s?).

    The syntax of RAISE I have in mind is:

        RAISE [ALL | FIRST [n]] expr [, expr ...] FROM ...

    Anything after FROM is exactly the same as for a SELECT.

    If  the  first result attribute of RAISE is a (var|bp)char or
    text field, single occurences of % in it will be  substituted
    by  the  following  attributes.   Otherwise all the attrs are
    simply concatenated with a padding blank to  form  the  error
    message.

    ALL or FIRST n means, that not only the first error should be
    shown.  A bit tricky to implement but  I  think  a  bunch  of
    NOTICE  and  a  final  "ERROR:  5  errors  counted"  would be
    possible.

    Having this, a foreign key constraint rule  could  look  like
    this:

        CREATE RULE _CIconstraint_name AS ON INSERT TO mytab DO
            RAISE 'Key "%" not in keytab', new.myatt FROM keytab
            WHERE NOT EXISTS (SELECT * FROM keytab WHERE keyatt = new.myatt);

    Similar  rules for update are simple and an ON DELETE CASCADE
    rule isn't that hard too.



    For  the  deferred  constraints  we  now   need   some   more
    informations  on  the  rules  themself. Currently all queries
    thrown in by the  rule  system  are  executed  prior  to  the
    original  query.  If  we add some syntax to CREATE RULE so we
    can tell

        CREATE [DEFERRABLE] [INITIALLY DEFERRED] RULE ...

    the rule system would be able to collect those queries  (they
    all  would be RAISE statements) to a global querytree list if
    they should be deferred.  This global  list  is  drained  out
    (all  queries run) when either the transaction commits or the
    SET ... IMMEDIATE is executed.

    Well, the information to remember isn't a small  amount.  Per
    constraint  that  is  to  be  deferred,  there  will  be  one
    querytree. And that for  every  single  INSERT/UPDATE/DELETE.
    And  if  a  table  has 5 constraints, it will be 5 remembered
    querytrees per operation. But  the  information  to  remember
    doesn't  depend  on  the  amount  of  data  affected  in  the
    statement (like it would be in a trigger implementation).  So
    it will work in a situation like

        BEGIN TRANSACTION;
        SET CONSTRAINST ALL DEFERRED;
        UPDATE tab1 SET ref1 = ref1 + 1900;
        UPDATE tab2 SET key1 = key1 + 1900;
        COMMIT TRANSACTION;

    even if there are millions of rows in the tables.



    As  Bruce  said  once  I  mentioned using the rule system for
    constraints: "It's a tempting solution". And I'm glad to have
    the  work  delayed until now because yet the DEFERRED problem
    surfaced and could be taken into account too.

    Comments? (sure :-)


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) #