

 Hi,

 I have a question... why RULE call nexval() and data in RULE statement are
differend than data in original stmt.

 An example:

create sequence a;
create table aa (id int DEFAULT nextval('a'), data text);

insert into aa (data) values ('xxxx');
insert into aa (data) values ('yyyy');

select * from aa;
id|data
--+----
 1|xxxx
 2|yyyy
(2 rows)

... all is right.

create table log (aid int, act text);

create rule a_ins as 
	on insert to aa 
	do insert into log (aid, act) values (NEW.id, 'INSERT');

insert into aa (data) values ('zzzz');
insert into aa (data) values ('qqqq');

test=> select * from aa;
id|data
--+----
 1|xxxx
 2|yyyy
 4|zzzz          <----------
 6|qqqq
(4 rows)

select * from log;
aid|act
---+------
  3|INSERT       <----------
  5|INSERT
(2 rows)

 But I expect in 'log' table as 'aid' same number as numbers for 'zzzz' and
'qqqq'...
 
 It's interesting feature (6.5, 7.0, 7.1...). How is a possible in RULE
obtain same data as in 'aa' table for a default data from the sequence.

					Karel


Mon, 4 Sep 2000 12:09:40 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
RULE vs. SEQUENCE
Karel Zak wrote:
> 
> On Mon, 4 Sep 2000, Jan Wieck wrote:
> 
> > >  I have a question... why RULE call nexval() and data in RULE statement are
> > > differend than data in original stmt.
> >
...
> 
>  But executor can knows that somethig was already executed, we can mark
> some already executed expressions in rewriter and not execute it again in
> final executor... like:
...
> 
>  IMHO this is a good point for 7.2 ...

But if instead of nextval() you had random(), would you still want to execute
it 
only once ? And how should postgres know ?

----------
Hannu

Mon, 04 Sep 2000 14:01:14 +0300
Hannu Krosing <hannu@tm.ee>
Re: RULE vs. SEQUENCE

On Mon, 4 Sep 2000, Jan Wieck wrote:

> >  I have a question... why RULE call nexval() and data in RULE statement are
> > differend than data in original stmt.
> 
>     It's  a known "feature", and I don't know any way of changing
>     it.

 IMHO docs is quiet about it... 

>     The problem is, that NEW.attname in a rule  means,  "whatever
>     is  in  the targetlist of the INSERT when applying the rule".
>     In your example, it'll be  a  call  to  nextval().  The  rule
>     system  doesn't  know  that  this targetlist expression has a
>     side-effect (incrementing the sequence).

 But, why 'NEW' tuple is in the rewriter created again, why is not used 
original tuple from original statement ... like in triggers? 

 Ooops yes, rewriter is before executor...hmm...

>     Thus, the rule creates a second query  which  does  it's  own
>     calls to nextval() when executed.

 But executor can knows that somethig was already executed, we can mark 
some already executed expressions in rewriter and not execute it again in 
final executor... like:

typedef  some_expr {
	bool	executed;
	Datum	*result;
	....
} some_expr;


 IMHO this is a good point for 7.2 ...


						Karel


Mon, 4 Sep 2000 13:16:45 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: RULE vs. SEQUENCE
Karel Zak wrote:
>
>
>  Hi,
>
>  I have a question... why RULE call nexval() and data in RULE statement are
> differend than data in original stmt.

    It's  a known "feature", and I don't know any way of changing
    it.

    The problem is, that NEW.attname in a rule  means,  "whatever
    is  in  the targetlist of the INSERT when applying the rule".
    In your example, it'll be  a  call  to  nextval().  The  rule
    system  doesn't  know  that  this targetlist expression has a
    side-effect (incrementing the sequence).

    Thus, the rule creates a second query  which  does  it's  own
    calls to nextval() when executed.

>  It's interesting feature (6.5, 7.0, 7.1...). How is a possible in RULE
> obtain same data as in 'aa' table for a default data from the sequence.

    The query rewrite rule system behaves like this since 4.2 (or
    even earlier). Since 6.4 it does the right things  on  UPDATE
    and  DELETE  too.  Don't know when we introduced sequences or
    better "functions that have such nasty side-effects".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Mon, 4 Sep 2000 07:02:46 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: RULE vs. SEQUENCE
> >  But executor can knows that somethig was already executed, we can mark
> > some already executed expressions in rewriter and not execute it again in
> > final executor... like:
> ...
> > 
> >  IMHO this is a good point for 7.2 ...
> 
> But if instead of nextval() you had random(), would you still want to execute
> it 
> only once ? And how should postgres know ?

 Talking you still about RULEs? 

 ...I don't undestand you. What is a 'NEW' in RULE? I (and probably more 
users) expect that new data from tuple which go into original table. Right?

Not ... if you use sequence. IMHO it's not "feature" but nice bug that
crash your data integrity...

				Karel 


Mon, 4 Sep 2000 14:05:05 +0200 (CEST)
Karel Zak <zakkr@zf.jcu.cz>
Re: RULE vs. SEQUENCE
Karel Zak wrote:
>
> On Mon, 4 Sep 2000, Jan Wieck wrote:
>
> >     The problem is, that NEW.attname in a rule  means,  "whatever
> >     is  in  the targetlist of the INSERT when applying the rule".
> >     In your example, it'll be  a  call  to  nextval().  The  rule
> >     system  doesn't  know  that  this targetlist expression has a
> >     side-effect (incrementing the sequence).
>
>  But, why 'NEW' tuple is in the rewriter created again, why is not used
> original tuple from original statement ... like in triggers?
>
>  Ooops yes, rewriter is before executor...hmm...

    More  Ooops:  the  rewriter  doesn't  create  any  tuples. He
    creates another query tree, which is then optimized,  planned
    and finally executed (to produce tuples).

>
> >     Thus, the rule creates a second query  which  does  it's  own
> >     calls to nextval() when executed.
>
>  But executor can knows that somethig was already executed, we can mark
> some already executed expressions in rewriter and not execute it again in
> final executor... like:
>
> typedef  some_expr {
>    bool executed;
>    Datum     *result;
>    ....
> } some_expr;
>
>
>  IMHO this is a good point for 7.2 ...

    Impossible - period.

    Think about this (a little longer - sorry):

        CREATE TABLE category (
            cat_id        serial,
            cat_name      text
        );

        CREATE TABLE prod_attrs (
            pa_prodid     integer,
            pa_attkey     integer,
            pa_attval     text
        );

        CREATE TABLE prod_attdefaults (
            pdef_catid    integer,
            pdef_attkey   integer,
            pdef_attval   text,
        );

        CREATE TABLE product (
            prod_id       serial,
            prod_category integer,
            prod_name     text
        );

        CREATE TABLE new_products (
            new_category  integer,
            new_name      text
        );

    So  far,  so  good. For each product we store in "product", a
    variable number of attributes can be stored in  "prod_attrs".
    At   the  time  of  "INSERT  INTO  product",  the  rows  from
    "prod_attdefaults"  where  "pdef_catid  =  NEW.prod_category"
    should be copied into "prod_attrs".

    The "NOT WORKING" rule for doing so would look like

        CREATE RULE attdefaults AS ON INSERT TO product DO
            INSERT INTO prod_attrs
            SELECT NEW.prod_id, D.pdef_attkey, D.pdef_attval
              FROM prod_attdefaults D
              WHERE D.pdef_catid = NEW.prod_category;

    Now let's have in "prod_attdefaults" 7 rows for category 1, 5
    rows for category 2, 6 rows for category 3 and  no  rows  for
    category 4. And we do

        INSERT INTO new_products VALUES (1, 'chair');
        INSERT INTO new_products VALUES (1, 'table');
        INSERT INTO new_products VALUES (1, 'sofa');
        INSERT INTO new_products VALUES (1, 'cupboard');
        INSERT INTO new_products VALUES (2, 'shirt');
        INSERT INTO new_products VALUES (2, 'shoe');
        INSERT INTO new_products VALUES (3, 'butter');
        INSERT INTO new_products VALUES (4, 'shampoo');

    The query

        INSERT INTO product (prod_category, prod_name)
            SELECT new_category, new_name FROM new_product;

    must  then  create  8  new  rows  in "product" and 44 rows in
    "prod_attrs".  The first 7 with the nextval()  allocated  for
    the  chair, the next 7 with the nextval() for the table, etc.

    I can't see how this should be doable with  the  rewriter  on
    the querylevel.

    This is something for a trigger.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Mon, 4 Sep 2000 09:16:42 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: RULE vs. SEQUENCE
Karel Zak wrote:
> > >  But executor can knows that somethig was already executed, we can mark
> > > some already executed expressions in rewriter and not execute it again in
> > > final executor... like:
> > ...
> > >
> > >  IMHO this is a good point for 7.2 ...
> >
> > But if instead of nextval() you had random(), would you still want to execute
> > it
> > only once ? And how should postgres know ?
>
>  Talking you still about RULEs?

    Yes, he is.

>
>  ...I don't undestand you. What is a 'NEW' in RULE? I (and probably more
> users) expect that new data from tuple which go into original table. Right?

    Most  people  would  expect  that  - but it is the targetlist
    expression of this column from  the  query  which  fired  the
    rule! That's a little difference.

> Not ... if you use sequence. IMHO it's not "feature" but nice bug that
> crash your data integrity...

    The PostgreSQL rule system is based on a general productional
    query rewrite  rule  system,  designed  decades  ago  without
    thinking  about column values like nextval() or random(). The
    usage of those expressions in a query firing rules  leads  to
    unpredictable results.

    To  understand  how  rules  work  in  detail  you should read
    chapter 8 of the programmers manual.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Mon, 4 Sep 2000 09:34:03 -0500 (EST)
Jan Wieck <janwieck@Yahoo.com>
Re: RULE vs. SEQUENCE