Re: RULE vs. SEQUENCE

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

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

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

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(at)Yahoo(dot)com #

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

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(at)Yahoo(dot)com #

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(at)Yahoo(dot)com #