Re: RULE vs. SEQUENCE
От | Jan Wieck |
---|---|
Тема | Re: RULE vs. SEQUENCE |
Дата | |
Msg-id | 200009041416.JAA02205@jupiter.jw.home обсуждение исходный текст |
Ответ на | Re: RULE vs. SEQUENCE (Karel Zak <zakkr@zf.jcu.cz>) |
Список | pgsql-hackers |
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 #
В списке pgsql-hackers по дате отправления: