Re: [HACKERS] Delaying insertion of default values - Mailing list pgsql-hackers

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