Re: [HACKERS] Delaying insertion of default values

Vadim wrote:

>
> Jan Wieck wrote:
> >
> > Vadim wrote:
> >
> > > ALTER TABLE could (or should?) re-compile table' rules...
> >
> > Rules should be recompilable for various reasons. DROP/CREATE
> > of objects (relations, functions etc.) referenced in rules
> > changes their OID and needs recompilation too.
>
> Yes. And the same is true for stored procedures when we'll
> get them.

Don't we have some kind of them already with the PL
functions? They get compiled on each first use per backend,
and I think that for a database under development (usually
not a live system) it isn't too bad to need a reconnect after
schema changes.

> BTW, what's your plan for RI constraints, Jan?
> Did you see my letter about statement level triggers?
> If I'll get WAL implemented then it could be used for RI.
> In any case I believe that statement level triggers
> are very nice thing and they are better for RI than
> rules.

What's WAL?

Let's think about a foreign key constraint that must be
checked at transaction commit (deferred constraint), so
someone can do

BEGIN;
SET CONSTRAINT reftab_check_refkey DEFERRED;
UPDATE reftab SET refkey = 4711,
prodname = 'New product'
WHERE prodname = 'Temp product';
INSERT INTO keytab (keyval, prodname)
VALUES (4711, 'New product');
COMMIT;

The statement level trigger should not check all 25 million
rows of reftab against keytab. It should only check the 10
rows that got updated because they matched. How does the
statement level trigger get access to the qualification of
the query that fired it. And how does it find out which of
them WHERE meant because it will not be able to find them
again with the same qual.

Currently rules cannot do this job too. I planned to change
the handling of snapshot as discussed and to implement a
deferred querytree list ran at appropriate times (like
COMMIT). Plus a new RAISE command that's internally most of a
SELECT but throwing an elog if it finds some rows. Such a CI
rule would then look like:

CREATE RULE reftab_check_refkey AS ON UPDATE TO reftab DO
RAISE 'foreign key % not present', new.refkey
WHERE NOT EXISTS
(SELECT keyval FROM keytab WHERE keyval = new.refkey);

This rule will get expanded by the rewriter to do a scan with
the snapshot when the UPDATE ran against reftab and with the
qual expanded to match the updated old tuples only, but the
subselect will have the snapshot at commit time which will
find the newly inserted keytab row. I don't see how statement
level triggers can do it.

Jan

--

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