Обсуждение: 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|xxxx2|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|xxxx2|yyyy4|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: > > > 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 therule". In your example, it'll be a call to nextval(). The rule system doesn't know that this targetlistexpression 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 #
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: > > 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
> > 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
INTOnew_products VALUES (1, 'sofa'); INSERT INTO new_products VALUES (1, 'cupboard'); INSERT INTO
new_productsVALUES (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 #
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 #