From 338c54cbc10ed9ed7f7216f839b9b14e5e42b7db Mon Sep 17 00:00:00 2001 From: "Marc G. Fournier" Date: Tue, 18 Aug 1998 00:49:04 +0000 Subject: [PATCH] From: Jan Wieck Hi, as proposed here comes the first patch for the query rewrite system. --- src/backend/parser/analyze.c | 28 +- src/backend/parser/gram.y | 3 +- src/backend/parser/parse_relation.c | 9 +- src/backend/rewrite/rewriteDefine.c | 7 +- src/backend/rewrite/rewriteHandler.c | 254 ++++++++++-- src/include/nodes/nodes.h | 8 +- src/test/regress/expected/run_ruletest.out | 585 +++++++++++++++++++++++++++ src/test/regress/expected/setup_ruletest.out | 88 ++++ 8 files changed, 942 insertions(+), 40 deletions(-) create mode 100644 src/test/regress/expected/run_ruletest.out create mode 100644 src/test/regress/expected/setup_ruletest.out diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 65f40f4cad..2bdcef170f 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.79 1998/07/20 20:48:51 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/analyze.c,v 1.80 1998/08/18 00:48:54 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -742,11 +742,33 @@ static Query * transformRuleStmt(ParseState *pstate, RuleStmt *stmt) { Query *qry; + Query *action; List *actions; qry = makeNode(Query); qry->commandType = CMD_UTILITY; + /* + * 'instead nothing' rules with a qualification need a + * query a rangetable so the rewrite handler can add the + * negated rule qualification to the original query. We + * create a query with the new command type CMD_NOTHING + * here that is treated special by the rewrite system. + */ + if (stmt->actions == NIL) { + Query *nothing_qry = makeNode(Query); + nothing_qry->commandType = CMD_NOTHING; + + addRangeTableEntry(pstate, stmt->object->relname, "*CURRENT*", + FALSE, FALSE); + addRangeTableEntry(pstate, stmt->object->relname, "*NEW*", + FALSE, FALSE); + + nothing_qry->rtable = pstate->p_rtable; + + stmt->actions = lappend(NIL, nothing_qry); + } + actions = stmt->actions; /* @@ -768,7 +790,9 @@ transformRuleStmt(ParseState *pstate, RuleStmt *stmt) pstate->p_is_rule = true; /* for expand all */ pstate->p_hasAggs = false; - lfirst(actions) = transformStmt(pstate, lfirst(actions)); + action = (Query *)lfirst(actions); + if (action->commandType != CMD_NOTHING) + lfirst(actions) = transformStmt(pstate, lfirst(actions)); actions = lnext(actions); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0e0015f3e4..23db7a1073 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.22 1998/08/17 16:08:34 thomas Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.23 1998/08/18 00:48:55 scrappy Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -1955,6 +1955,7 @@ RuleStmt: CREATE RULE name AS OptStmtList: NOTHING { $$ = NIL; } | OptimizableStmt { $$ = lcons($1, NIL); } | '[' OptStmtBlock ']' { $$ = $2; } + | '(' OptStmtBlock ')' { $$ = $2; } ; OptStmtBlock: OptStmtMulti diff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.c index fef834d2c6..181152bfa5 100644 --- a/src/backend/parser/parse_relation.c +++ b/src/backend/parser/parse_relation.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/parse_relation.c,v 1.12 1998/07/08 14:04:11 thomas Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/parse_relation.c,v 1.13 1998/08/18 00:48:57 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -191,8 +191,13 @@ addRangeTableEntry(ParseState *pstate, if (pstate != NULL) { if (refnameRangeTablePosn(pstate, refname, &sublevels_up) != 0 && - (!inFromCl || sublevels_up == 0)) + (!inFromCl || sublevels_up == 0)) { + if (!strcmp(refname, "*CURRENT*") || !strcmp(refname, "*NEW*")) { + int rt_index = refnameRangeTablePosn(pstate, refname, &sublevels_up); + return (RangeTblEntry *)nth(rt_index - 1, pstate->p_rtable); + } elog(ERROR, "Table name %s specified more than once", refname); + } } rte->relname = pstrdup(relname); diff --git a/src/backend/rewrite/rewriteDefine.c b/src/backend/rewrite/rewriteDefine.c index cb1bbd5bcf..3c1b652860 100644 --- a/src/backend/rewrite/rewriteDefine.c +++ b/src/backend/rewrite/rewriteDefine.c @@ -7,7 +7,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.16 1998/06/15 19:29:06 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteDefine.c,v 1.17 1998/08/18 00:48:58 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -155,12 +155,7 @@ ValidateRule(int event_type, "rules not allowed for insert or delete events to an attribute"); } - if (event_qual && !*action && is_instead) - elog(ERROR, - "event_quals on 'instead nothing' rules not currently supported"); - #if 0 - /* * on retrieve to class.attribute do instead nothing is converted to * 'on retrieve to class.attribute do instead retrieve (attribute = diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index aff40937ed..e80fc4cf55 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -6,7 +6,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v 1.17 1998/07/19 05:49:24 momjian Exp $ + * $Header: /cvsroot/pgsql/src/backend/rewrite/rewriteHandler.c,v 1.18 1998/08/18 00:48:59 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -20,6 +20,7 @@ #include "nodes/primnodes.h" #include "parser/parsetree.h" /* for parsetree manipulation */ +#include "parser/parse_relation.h" #include "nodes/parsenodes.h" #include "rewrite/rewriteSupport.h" @@ -45,6 +46,8 @@ static void QueryRewriteSubLink(Node *node); static List *QueryRewriteOne(Query *parsetree); static List *deepRewriteQuery(Query *parsetree); static void CheckViewPerms(Relation view, List *rtable); +static void RewritePreprocessQuery(Query *parsetree); +static Query *RewritePostprocessNonSelect(Query *parsetree); /* * gatherRewriteMeta - @@ -138,25 +141,30 @@ OptimizeRIRRules(List *locks) } /* - * idea is to put instead rules before regular rules so that - * excess semantically queasy queries aren't processed + * idea is to fire regular rules first, then qualified instead + * rules and unqualified instead rules last. Any lemming is counted for. */ static List * orderRules(List *locks) { - List *regular = NIL, - *i; - List *instead_rules = NIL; + List *regular = NIL; + List *instead_rules = NIL; + List *instead_qualified = NIL; + List *i; foreach(i, locks) { RewriteRule *rule_lock = (RewriteRule *) lfirst(i); - if (rule_lock->isInstead) - instead_rules = lappend(instead_rules, rule_lock); - else + if (rule_lock->isInstead) { + if (rule_lock->qual == NULL) + instead_rules = lappend(instead_rules, rule_lock); + else + instead_qualified = lappend(instead_qualified, rule_lock); + } else regular = lappend(regular, rule_lock); } + regular = nconc(regular, instead_qualified); return nconc(regular, instead_rules); } @@ -234,7 +242,6 @@ FireRetrieveRulesAtQuery(Query *parsetree, { *instead_flag = TRUE; FixResdomTypes(parsetree->targetList); - return lcons(parsetree, NIL); } } @@ -411,8 +418,9 @@ ProcessRetrieveQuery(Query *parsetree, rule); } heap_close(rt_entry_relation); - if (*instead_flag) + if (*instead_flag) { return result; + } } if (rule) return NIL; @@ -486,10 +494,13 @@ CopyAndAddQual(Query *parsetree, /* * fireRules - - * Iterate through rule locks applying rules. After an instead rule - * rule has been applied, return just new parsetree and let RewriteQuery - * start the process all over again. The locks are reordered to maintain - * sensible semantics. remember: reality is for dead birds -- glass + * Iterate through rule locks applying rules. + * All rules create their own parsetrees. Instead rules + * with rule qualification save the original parsetree + * and add their negated qualification to it. Real instead + * rules finally throw away the original parsetree. + * + * remember: reality is for dead birds -- glass * */ static List * @@ -516,7 +527,7 @@ fireRules(Query *parsetree, return NIL; } - locks = orderRules(locks); /* instead rules first */ + locks = orderRules(locks); /* real instead rules last */ foreach(i, locks) { RewriteRule *rule_lock = (RewriteRule *) lfirst(i); @@ -530,16 +541,52 @@ fireRules(Query *parsetree, *instead_flag = rule_lock->isInstead; event_qual = rule_lock->qual; actions = rule_lock->actions; - if (event_qual != NULL && *instead_flag) - *qual_products = - lappend(*qual_products, - CopyAndAddQual(parsetree, actions, event_qual, - rt_index, event)); + if (event_qual != NULL && *instead_flag) { + Query *qual_product; + RewriteInfo qual_info; + + /* ---------- + * If there are instead rules with qualifications, + * the original query is still performed. But all + * the negated rule qualifications of the instead + * rules are added so it does it's actions only + * in cases where the rule quals of all instead + * rules are false. Think of it as the default + * action in a case. We save this in *qual_products + * so deepRewriteQuery() can add it to the query + * list after we mangled it up enough. + * ---------- + */ + if (*qual_products == NIL) { + qual_product = parsetree; + } else { + qual_product = (Query *)nth(0, *qual_products); + } + + qual_info.event = qual_product->commandType; + qual_info.new_varno = length(qual_product->rtable) + 2; + qual_product = CopyAndAddQual(qual_product, + actions, + event_qual, + rt_index, + event); + + qual_info.rule_action = qual_product; + + if (event == CMD_INSERT || event == CMD_UPDATE) + FixNew(&qual_info, qual_product); + + *qual_products = lappend(NIL, qual_product); + } + foreach(r, actions) { Query *rule_action = lfirst(r); Node *rule_qual = copyObject(event_qual); + if (rule_action->commandType == CMD_NOTHING) + continue; + /*-------------------------------------------------- * Step 1: * Rewrite current.attribute or current to tuple variable @@ -563,7 +610,7 @@ fireRules(Query *parsetree, continue; /* - * Event Qualification forces copying of parsetree --- XXX and + * Event Qualification forces copying of parsetree and * splitting into two queries one w/rule_qual, one w/NOT * rule_qual. Also add user query qual onto rule action */ @@ -601,12 +648,126 @@ fireRules(Query *parsetree, pfree(info); } - if (*instead_flag) - break; + + /* ---------- + * If this was an unqualified instead rule, + * throw away an eventually saved 'default' parsetree + * ---------- + */ + if (event_qual == NULL && *instead_flag) { + *qual_products = NIL; + } } return results; } +/* ---------- + * RewritePreprocessQuery - + * adjust details in the parsetree, the rule system + * depends on + * ---------- + */ +static void +RewritePreprocessQuery(Query *parsetree) +{ + /* ---------- + * if the query has a resultRelation, reassign the + * result domain numbers to the attribute numbers in the + * target relation. FixNew() depends on it when replacing + * *new* references in a rule action by the expressions + * from the rewritten query. + * ---------- + */ + if (parsetree->resultRelation > 0) { + RangeTblEntry *rte; + Relation rd; + List *tl; + TargetEntry *tle; + int resdomno; + + rte = (RangeTblEntry *)nth(parsetree->resultRelation - 1, + parsetree->rtable); + rd = heap_openr(rte->relname); + + foreach (tl, parsetree->targetList) { + tle = (TargetEntry *)lfirst(tl); + resdomno = attnameAttNum(rd, tle->resdom->resname); + tle->resdom->resno = resdomno; + } + + heap_close(rd); + } +} + + +/* ---------- + * RewritePostprocessNonSelect - + * apply instead select rules on a query fired in by + * the rewrite system + * ---------- + */ +static Query * +RewritePostprocessNonSelect(Query *parsetree) +{ + List *rt; + int rt_index = 0; + Query *newtree = copyObject(parsetree); + + foreach(rt, parsetree->rtable) + { + RangeTblEntry *rt_entry = lfirst(rt); + Relation rt_entry_relation = NULL; + RuleLock *rt_entry_locks = NULL; + List *locks = NIL; + List *instead_locks = NIL; + List *lock; + RewriteRule *rule; + + rt_index++; + rt_entry_relation = heap_openr(rt_entry->relname); + rt_entry_locks = rt_entry_relation->rd_rules; + + if (rt_entry_locks) + { + int origcmdtype = newtree->commandType; + newtree->commandType = CMD_SELECT; + locks = + matchLocks(CMD_SELECT, rt_entry_locks, rt_index, newtree); + newtree->commandType = origcmdtype; + } + if (locks != NIL) + { + foreach (lock, locks) { + rule = (RewriteRule *)lfirst(lock); + if (rule->isInstead) { + instead_locks = nconc(instead_locks, lock); + } + } + } + if (instead_locks != NIL) + { + foreach (lock, instead_locks) { + int relation_level; + int modified = 0; + + rule = (RewriteRule *)lfirst(lock); + relation_level = (rule->attrno == -1); + + ApplyRetrieveRule(newtree, + rule, + rt_index, + relation_level, + rt_entry_relation, + &modified); + } + } + + heap_close(rt_entry_relation); + } + + return newtree; +} + static List * RewriteQuery(Query *parsetree, bool *instead_flag, List **qual_products) { @@ -648,7 +809,6 @@ RewriteQuery(Query *parsetree, bool *instead_flag, List **qual_products) { List *locks = matchLocks(event, rt_entry_locks, result_relation, parsetree); - product_queries = fireRules(parsetree, result_relation, @@ -657,6 +817,27 @@ RewriteQuery(Query *parsetree, bool *instead_flag, List **qual_products) locks, qual_products); } + + /* ---------- + * deepRewriteQuery does not handle the situation + * where a query fired by a rule uses relations that + * have instead select rules defined (views and the like). + * So we care for them here. + * ---------- + */ + if (product_queries != NIL) { + List *pq; + Query *tmp; + List *new_products = NIL; + + foreach (pq, product_queries) { + tmp = (Query *)lfirst(pq); + tmp = RewritePostprocessNonSelect(tmp); + new_products = lappend(new_products, tmp); + } + product_queries = new_products; + } + return product_queries; } else @@ -697,6 +878,8 @@ static int numQueryRewriteInvoked = 0; List * QueryRewrite(Query *parsetree) { + RewritePreprocessQuery(parsetree); + QueryRewriteSubLink(parsetree->qual); QueryRewriteSubLink(parsetree->havingQual); @@ -807,8 +990,6 @@ deepRewriteQuery(Query *parsetree) instead = FALSE; result = RewriteQuery(parsetree, &instead, &qual_products); - if (!instead) - rewritten = lcons(parsetree, NIL); foreach(n, result) { @@ -819,9 +1000,28 @@ deepRewriteQuery(Query *parsetree) if (newstuff != NIL) rewritten = nconc(rewritten, newstuff); } + + /* ---------- + * qual_products are the original query with the negated + * rule qualification of an instead rule + * ---------- + */ if (qual_products != NIL) rewritten = nconc(rewritten, qual_products); + /* ---------- + * The original query is appended last if not instead + * because update and delete rule actions might not do + * anything if they are invoked after the update or + * delete is performed. The command counter increment + * between the query execution makes the deleted (and + * maybe the updated) tuples disappear so the scans + * for them in the rule actions cannot find them. + * ---------- + */ + if (!instead) + rewritten = lappend(rewritten, parsetree); + return rewritten; } diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index a73907f257..8ab0f5e3f5 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -6,7 +6,7 @@ * * Copyright (c) 1994, Regents of the University of California * - * $Id: nodes.h,v 1.26 1998/08/05 04:49:12 scrappy Exp $ + * $Id: nodes.h,v 1.27 1998/08/18 00:49:01 scrappy Exp $ * *------------------------------------------------------------------------- */ @@ -305,8 +305,12 @@ typedef enum CmdType CMD_INSERT, /* insert stmt (formerly append) */ CMD_DELETE, CMD_NOTIFY, - CMD_UTILITY /* cmds like create, destroy, copy, + CMD_UTILITY, /* cmds like create, destroy, copy, * vacuum, etc. */ + CMD_NOTHING /* dummy command for + * instead nothing + * rules with qual + */ } CmdType; diff --git a/src/test/regress/expected/run_ruletest.out b/src/test/regress/expected/run_ruletest.out new file mode 100644 index 0000000000..0421ad47b5 --- /dev/null +++ b/src/test/regress/expected/run_ruletest.out @@ -0,0 +1,585 @@ +QUERY: insert into rtest_t2 values (1, 21); +QUERY: insert into rtest_t2 values (2, 22); +QUERY: insert into rtest_t2 values (3, 23); +QUERY: insert into rtest_t3 values (1, 31); +QUERY: insert into rtest_t3 values (2, 32); +QUERY: insert into rtest_t3 values (3, 33); +QUERY: insert into rtest_t3 values (4, 34); +QUERY: insert into rtest_t3 values (5, 35); +QUERY: insert into rtest_v1 values (1, 11); +QUERY: insert into rtest_v1 values (2, 12); +QUERY: select * from rtest_v1; +a| b +-+-- +1|11 +2|12 +(2 rows) + +QUERY: delete from rtest_v1 where a = 1; +QUERY: select * from rtest_v1; +a| b +-+-- +2|12 +(1 row) + +QUERY: insert into rtest_v1 values (1, 11); +QUERY: delete from rtest_v1 where b = 12; +QUERY: select * from rtest_v1; +a| b +-+-- +1|11 +(1 row) + +QUERY: insert into rtest_v1 values (2, 12); +QUERY: insert into rtest_v1 values (2, 13); +QUERY: select * from rtest_v1; +a| b +-+-- +1|11 +2|12 +2|13 +(3 rows) + +QUERY: delete from rtest_v1 where b = 12; +QUERY: select * from rtest_v1; +** Remember the delete rule on rtest_v1: It says +** DO INSTEAD DELETE FROM rtest_t1 WHERE a = current.a +** So this time both rows with a = 2 must get deleted +a| b +-+-- +1|11 +(1 row) + +QUERY: delete from rtest_v1; +QUERY: insert into rtest_v1 select * from rtest_t2; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: delete from rtest_v1; +QUERY: insert into rtest_v1 (b, a) select b, a from rtest_t2; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: insert into rtest_v1 (a) select a from rtest_t3; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +1| +2| +3| +4| +5| +(8 rows) + +QUERY: select * from rtest_v1 where b isnull; +a|b +-+- +1| +2| +3| +4| +5| +(5 rows) + +QUERY: update rtest_t1 set a = a + 10 where b isnull; +QUERY: delete from rtest_v1 where b isnull; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: update rtest_v1 set b = 42 where a = 2; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +3|23 +2|42 +(3 rows) + +QUERY: update rtest_v1 set b = 99 where b = 42; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +3|23 +2|99 +(3 rows) + +QUERY: update rtest_v1 set b = 88 where b < 50; +QUERY: select * from rtest_v1; +a| b +-+-- +2|99 +1|88 +3|88 +(3 rows) + +QUERY: delete from rtest_v1; +QUERY: insert into rtest_v1 select rtest_t2.a, rtest_t3.b where rtest_t2.a = rtest_t3.a; +QUERY: select * from rtest_v1; +a| b +-+-- +1|31 +2|32 +3|33 +(3 rows) + +QUERY: update rtest_v1 set b = rtest_t2.b where a = rtest_t2.a; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +(3 rows) + +QUERY: insert into rtest_v1 select * from rtest_t3; +QUERY: select * from rtest_v1; +a| b +-+-- +1|21 +2|22 +3|23 +1|31 +2|32 +3|33 +4|34 +5|35 +(8 rows) + +QUERY: update rtest_t1 set a = a + 10 where b > 30; +QUERY: select * from rtest_v1; + a| b +--+-- + 1|21 + 2|22 + 3|23 +11|31 +12|32 +13|33 +14|34 +15|35 +(8 rows) + +QUERY: update rtest_v1 set a = rtest_t3.a + 20 where b = rtest_t3.b; +QUERY: select * from rtest_v1; + a| b +--+-- + 1|21 + 2|22 + 3|23 +21|31 +22|32 +23|33 +24|34 +25|35 +(8 rows) + +QUERY: insert into rtest_system values ('orion', 'Linux Jan Wieck'); +QUERY: insert into rtest_system values ('notjw', 'WinNT Jan Wieck (notebook)'); +QUERY: insert into rtest_system values ('neptun', 'Fileserver'); +QUERY: insert into rtest_interface values ('orion', 'eth0'); +QUERY: insert into rtest_interface values ('orion', 'eth1'); +QUERY: insert into rtest_interface values ('notjw', 'eth0'); +QUERY: insert into rtest_interface values ('neptun', 'eth0'); +QUERY: insert into rtest_person values ('jw', 'Jan Wieck'); +QUERY: insert into rtest_person values ('bm', 'Bruce Momjian'); +QUERY: insert into rtest_admin values ('jw', 'orion'); +QUERY: insert into rtest_admin values ('jw', 'notjw'); +QUERY: insert into rtest_admin values ('bm', 'neptun'); +QUERY: update rtest_system set sysname = 'pluto' where sysname = 'neptun'; +QUERY: select * from rtest_interface; +sysname|ifname +-------+------ +orion |eth0 +orion |eth1 +notjw |eth0 +pluto |eth0 +(4 rows) + +QUERY: select * from rtest_admin; +pname|sysname +-----+------- +jw |orion +jw |notjw +bm |pluto +(3 rows) + +QUERY: update rtest_person set pname = 'jwieck' where pdesc = 'Jan Wieck'; +QUERY: select * from rtest_admin; +pname |sysname +------+------- +bm |pluto +jwieck|orion +jwieck|notjw +(3 rows) + +QUERY: delete from rtest_system where sysname = 'orion'; +QUERY: select * from rtest_interface; +sysname|ifname +-------+------ +notjw |eth0 +pluto |eth0 +(2 rows) + +QUERY: select * from rtest_admin; +pname |sysname +------+------- +bm |pluto +jwieck|notjw +(2 rows) + +QUERY: insert into rtest_emp values ('wiech', '5000.00'); +QUERY: insert into rtest_emp values ('gates', '80000.00'); +QUERY: update rtest_emp set ename = 'wiecx' where ename = 'wiech'; +QUERY: update rtest_emp set ename = 'wieck', salary = '6000.00' where ename = 'wiecx'; +QUERY: update rtest_emp set salary = '7000.00' where ename = 'wieck'; +QUERY: delete from rtest_emp where ename = 'gates'; +QUERY: select * from rtest_emplog; +ename |who |action |newsal |oldsal +--------------------+-----+----------+----------+---------- +wiech |pgsql|hired |$5,000.00 |$0.00 +gates |pgsql|hired |$80,000.00|$0.00 +wieck |pgsql|honored |$6,000.00 |$5,000.00 +wieck |pgsql|honored |$7,000.00 |$6,000.00 +gates |pgsql|fired |$0.00 |$80,000.00 +(5 rows) + +QUERY: insert into rtest_empmass values ('meyer', '4000.00'); +QUERY: insert into rtest_empmass values ('maier', '5000.00'); +QUERY: insert into rtest_empmass values ('mayr', '6000.00'); +QUERY: insert into rtest_emp select * from rtest_empmass; +QUERY: select * from rtest_emplog; +ename |who |action |newsal |oldsal +--------------------+-----+----------+----------+---------- +wiech |pgsql|hired |$5,000.00 |$0.00 +gates |pgsql|hired |$80,000.00|$0.00 +wieck |pgsql|honored |$6,000.00 |$5,000.00 +wieck |pgsql|honored |$7,000.00 |$6,000.00 +gates |pgsql|fired |$0.00 |$80,000.00 +meyer |pgsql|hired |$4,000.00 |$0.00 +maier |pgsql|hired |$5,000.00 |$0.00 +mayr |pgsql|hired |$6,000.00 |$0.00 +(8 rows) + +QUERY: update rtest_empmass set salary = salary + '1000.00'; +QUERY: update rtest_emp set salary = rtest_empmass.salary where ename = rtest_empmass.ename; +QUERY: select * from rtest_emplog; +ename |who |action |newsal |oldsal +--------------------+-----+----------+----------+---------- +wiech |pgsql|hired |$5,000.00 |$0.00 +gates |pgsql|hired |$80,000.00|$0.00 +wieck |pgsql|honored |$6,000.00 |$5,000.00 +wieck |pgsql|honored |$7,000.00 |$6,000.00 +gates |pgsql|fired |$0.00 |$80,000.00 +meyer |pgsql|hired |$4,000.00 |$0.00 +maier |pgsql|hired |$5,000.00 |$0.00 +mayr |pgsql|hired |$6,000.00 |$0.00 +maier |pgsql|honored |$6,000.00 |$5,000.00 +mayr |pgsql|honored |$7,000.00 |$6,000.00 +meyer |pgsql|honored |$5,000.00 |$4,000.00 +(11 rows) + +QUERY: delete from rtest_emp where ename = rtest_empmass.ename; +QUERY: select * from rtest_emplog; +ename |who |action |newsal |oldsal +--------------------+-----+----------+----------+---------- +wiech |pgsql|hired |$5,000.00 |$0.00 +gates |pgsql|hired |$80,000.00|$0.00 +wieck |pgsql|honored |$6,000.00 |$5,000.00 +wieck |pgsql|honored |$7,000.00 |$6,000.00 +gates |pgsql|fired |$0.00 |$80,000.00 +meyer |pgsql|hired |$4,000.00 |$0.00 +maier |pgsql|hired |$5,000.00 |$0.00 +mayr |pgsql|hired |$6,000.00 |$0.00 +maier |pgsql|honored |$6,000.00 |$5,000.00 +mayr |pgsql|honored |$7,000.00 |$6,000.00 +meyer |pgsql|honored |$5,000.00 |$4,000.00 +maier |pgsql|fired |$0.00 |$6,000.00 +mayr |pgsql|fired |$0.00 |$7,000.00 +meyer |pgsql|fired |$0.00 |$5,000.00 +(14 rows) + +QUERY: insert into rtest_t4 values (1, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 values (2, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 values (10, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t4 values (15, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t4 values (19, 'Record should go to rtest_t5 and t7'); +QUERY: insert into rtest_t4 values (20, 'Record should go to rtest_t4 and t6'); +QUERY: insert into rtest_t4 values (26, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t4 values (28, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t4 values (30, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 values (40, 'Record should go to rtest_t4'); +QUERY: select * from rtest_t4; + a|b +--+----------------------------------- + 1|Record should go to rtest_t4 + 2|Record should go to rtest_t4 +20|Record should go to rtest_t4 and t6 +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +30|Record should go to rtest_t4 +40|Record should go to rtest_t4 +(7 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; + a|b +--+----------------------------------- +20|Record should go to rtest_t4 and t6 +(1 row) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; + a|b +--+----------------------------------- +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(2 rows) + +QUERY: delete from rtest_t4; +QUERY: delete from rtest_t5; +QUERY: delete from rtest_t6; +QUERY: delete from rtest_t7; +QUERY: delete from rtest_t8; +QUERY: insert into rtest_t9 values (1, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t9 values (2, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t9 values (10, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t9 values (15, 'Record should go to rtest_t5'); +QUERY: insert into rtest_t9 values (19, 'Record should go to rtest_t5 and t7'); +QUERY: insert into rtest_t9 values (20, 'Record should go to rtest_t4 and t6'); +QUERY: insert into rtest_t9 values (26, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t9 values (28, 'Record should go to rtest_t4 and t8'); +QUERY: insert into rtest_t9 values (30, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t9 values (40, 'Record should go to rtest_t4'); +QUERY: insert into rtest_t4 select * from rtest_t9 where a < 20; +QUERY: select * from rtest_t4; +a|b +-+---------------------------- +1|Record should go to rtest_t4 +2|Record should go to rtest_t4 +(2 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; +a|b +-+- +(0 rows) + +QUERY: insert into rtest_t4 select * from rtest_t9 where b ~ 'and t8'; +QUERY: select * from rtest_t4; + a|b +--+----------------------------------- + 1|Record should go to rtest_t4 + 2|Record should go to rtest_t4 +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(4 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; + a|b +--+----------------------------------- +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(2 rows) + +QUERY: insert into rtest_t4 select a + 1, b from rtest_t9 where a in (20, 30, 40); +QUERY: select * from rtest_t4; + a|b +--+----------------------------------- + 1|Record should go to rtest_t4 + 2|Record should go to rtest_t4 +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +21|Record should go to rtest_t4 and t6 +31|Record should go to rtest_t4 +41|Record should go to rtest_t4 +(7 rows) + +QUERY: select * from rtest_t5; + a|b +--+----------------------------------- +10|Record should go to rtest_t5 +15|Record should go to rtest_t5 +19|Record should go to rtest_t5 and t7 +(3 rows) + +QUERY: select * from rtest_t6; + a|b +--+----------------------------------- +21|Record should go to rtest_t4 and t6 +(1 row) + +QUERY: select * from rtest_t7; + a|b +--+----------------------------------- +19|Record should go to rtest_t5 and t7 +(1 row) + +QUERY: select * from rtest_t8; + a|b +--+----------------------------------- +26|Record should go to rtest_t4 and t8 +28|Record should go to rtest_t4 and t8 +(2 rows) + +QUERY: insert into rtest_order1 values (1); +QUERY: select * from rtest_order2; +a|b|c +-+-+----------------------------------- +1|1|rule 2 - this should run 1st +1|2|rule 4 - this should run 2nd +1|3|rule 3 - this should run 3rd or 4th +1|4|rule 1 - this should run 3rd or 4th +(4 rows) + +QUERY: insert into rtest_nothn1 values (1, 'want this'); +QUERY: insert into rtest_nothn1 values (2, 'want this'); +QUERY: insert into rtest_nothn1 values (10, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (19, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (20, 'want this'); +QUERY: insert into rtest_nothn1 values (29, 'want this'); +QUERY: insert into rtest_nothn1 values (30, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (39, 'don''t want this'); +QUERY: insert into rtest_nothn1 values (40, 'want this'); +QUERY: insert into rtest_nothn1 values (50, 'want this'); +QUERY: insert into rtest_nothn1 values (60, 'want this'); +QUERY: select * from rtest_nothn1; + a|b +--+--------- + 1|want this + 2|want this +20|want this +29|want this +40|want this +50|want this +60|want this +(7 rows) + +QUERY: insert into rtest_nothn2 values (10, 'too small'); +QUERY: insert into rtest_nothn2 values (50, 'too small'); +QUERY: insert into rtest_nothn2 values (100, 'OK'); +QUERY: insert into rtest_nothn2 values (200, 'OK'); +QUERY: select * from rtest_nothn2; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_nothn3; + a|b +---+-- +100|OK +200|OK +(2 rows) + +QUERY: delete from rtest_nothn1; +QUERY: delete from rtest_nothn2; +QUERY: delete from rtest_nothn3; +QUERY: insert into rtest_nothn4 values (1, 'want this'); +QUERY: insert into rtest_nothn4 values (2, 'want this'); +QUERY: insert into rtest_nothn4 values (10, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (19, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (20, 'want this'); +QUERY: insert into rtest_nothn4 values (29, 'want this'); +QUERY: insert into rtest_nothn4 values (30, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (39, 'don''t want this'); +QUERY: insert into rtest_nothn4 values (40, 'want this'); +QUERY: insert into rtest_nothn4 values (50, 'want this'); +QUERY: insert into rtest_nothn4 values (60, 'want this'); +QUERY: insert into rtest_nothn1 select * from rtest_nothn4; +QUERY: select * from rtest_nothn1; + a|b +--+--------- + 1|want this + 2|want this +20|want this +29|want this +40|want this +50|want this +60|want this +(7 rows) + +QUERY: delete from rtest_nothn4; +QUERY: insert into rtest_nothn4 values (10, 'too small'); +QUERY: insert into rtest_nothn4 values (50, 'too small'); +QUERY: insert into rtest_nothn4 values (100, 'OK'); +QUERY: insert into rtest_nothn4 values (200, 'OK'); +QUERY: insert into rtest_nothn2 select * from rtest_nothn4; +QUERY: select * from rtest_nothn2; +a|b +-+- +(0 rows) + +QUERY: select * from rtest_nothn3; + a|b +---+-- +100|OK +200|OK +(2 rows) + diff --git a/src/test/regress/expected/setup_ruletest.out b/src/test/regress/expected/setup_ruletest.out new file mode 100644 index 0000000000..9cd3c09026 --- /dev/null +++ b/src/test/regress/expected/setup_ruletest.out @@ -0,0 +1,88 @@ +QUERY: create table rtest_t1 (a int4, b int4); +QUERY: create table rtest_t2 (a int4, b int4); +QUERY: create table rtest_t3 (a int4, b int4); +QUERY: create view rtest_v1 as select * from rtest_t1; +QUERY: create rule rtest_v1_ins as on insert to rtest_v1 do instead + insert into rtest_t1 values (new.a, new.b); +QUERY: create rule rtest_v1_upd as on update to rtest_v1 do instead + update rtest_t1 set a = new.a, b = new.b + where a = current.a; +QUERY: create rule rtest_v1_del as on delete to rtest_v1 do instead + delete from rtest_t1 where a = current.a; +QUERY: create table rtest_system (sysname text, sysdesc text); +QUERY: create table rtest_interface (sysname text, ifname text); +QUERY: create table rtest_person (pname text, pdesc text); +QUERY: create table rtest_admin (pname text, sysname text); +QUERY: create rule rtest_sys_upd1 as on update to rtest_system do + update rtest_interface set sysname = new.sysname + where sysname = current.sysname; +QUERY: create rule rtest_sys_upd2 as on update to rtest_system do + update rtest_admin set sysname = new.sysname + where sysname = current.sysname; +QUERY: create rule rtest_sys_del1 as on delete to rtest_system do + delete from rtest_interface where sysname = current.sysname; +QUERY: create rule rtest_sys_del2 as on delete to rtest_system do + delete from rtest_admin where sysname = current.sysname; +QUERY: create rule rtest_pers_upd as on update to rtest_person do + update rtest_admin set pname = new.pname where pname = current.pname; +QUERY: create rule rtest_pers_del as on delete to rtest_person do + delete from rtest_admin where pname = current.pname; +QUERY: create table rtest_emp (ename char(20), salary money); +QUERY: create table rtest_emplog (ename char(20), who name, action char(10), newsal money, oldsal money); +QUERY: create table rtest_empmass (ename char(20), salary money); +QUERY: create rule rtest_emp_ins as on insert to rtest_emp do + insert into rtest_emplog values (new.ename, getpgusername(), + 'hired', new.salary, '0.00'); +QUERY: create rule rtest_emp_upd as on update to rtest_emp where new.salary != current.salary do + insert into rtest_emplog values (new.ename, getpgusername(), + 'honored', new.salary, current.salary); +QUERY: create rule rtest_emp_del as on delete to rtest_emp do + insert into rtest_emplog values (current.ename, getpgusername(), + 'fired', '0.00', current.salary); +QUERY: create table rtest_t4 (a int4, b text); +QUERY: create table rtest_t5 (a int4, b text); +QUERY: create table rtest_t6 (a int4, b text); +QUERY: create table rtest_t7 (a int4, b text); +QUERY: create table rtest_t8 (a int4, b text); +QUERY: create table rtest_t9 (a int4, b text); +QUERY: create rule rtest_t4_ins1 as on insert to rtest_t4 + where new.a >= 10 and new.a < 20 do instead + insert into rtest_t5 values (new.a, new.b); +QUERY: create rule rtest_t4_ins2 as on insert to rtest_t4 + where new.a >= 20 and new.a < 30 do + insert into rtest_t6 values (new.a, new.b); +QUERY: create rule rtest_t5_ins as on insert to rtest_t5 + where new.a > 15 do + insert into rtest_t7 values (new.a, new.b); +QUERY: create rule rtest_t6_ins as on insert to rtest_t6 + where new.a > 25 do instead + insert into rtest_t8 values (new.a, new.b); +QUERY: create table rtest_order1 (a int4); +QUERY: create table rtest_order2 (a int4, b int4, c text); +QUERY: create sequence rtest_seq; +QUERY: create rule rtest_order_r3 as on insert to rtest_order1 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 3 - this should run 3rd or 4th'); +QUERY: create rule rtest_order_r4 as on insert to rtest_order1 + where a < 100 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 4 - this should run 2nd'); +QUERY: create rule rtest_order_r2 as on insert to rtest_order1 do + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 2 - this should run 1st'); +QUERY: create rule rtest_order_r1 as on insert to rtest_order1 do instead + insert into rtest_order2 values (new.a, nextval('rtest_seq'), + 'rule 1 - this should run 3rd or 4th'); +QUERY: create table rtest_nothn1 (a int4, b text); +QUERY: create table rtest_nothn2 (a int4, b text); +QUERY: create table rtest_nothn3 (a int4, b text); +QUERY: create table rtest_nothn4 (a int4, b text); +QUERY: create rule rtest_nothn_r1 as on insert to rtest_nothn1 + where new.a >= 10 and new.a < 20 do instead (select 1); +QUERY: create rule rtest_nothn_r2 as on insert to rtest_nothn1 + where new.a >= 30 and new.a < 40 do instead nothing; +QUERY: create rule rtest_nothn_r3 as on insert to rtest_nothn2 + where new.a >= 100 do instead + insert into rtest_nothn3 values (new.a, new.b); +QUERY: create rule rtest_nothn_r4 as on insert to rtest_nothn2 + do instead nothing; -- 2.11.0