Re: [HACKERS] CONSTRAINTS...

>
> >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(at)debis(dot)com (Jan Wieck) #