Insert on view (using rules) with multi-row sublink (A SubLink represents a subselect appearing in an expression) values a time will fail.
Background and How it is diagnosed:
postgres=# DROP TABLE IF EXISTS foo CASCADE;
NOTICE: table "foo" does not exist, skipping
postgres=# CREATE TABLE foo(a int, b int);
postgres=# CREATE VIEW foo_v AS SELECT * FROM foo;
//view over the query, which gives a name to the query that you can refer
//to like an ordinary table.
postgres=# CREATE RULE foo_r AS ON INSERT TO foo_v DO INSTEAD INSERT INTO foo
//Currently, views are read only: the system will not allow an insert, update, or //delete on a view. You can get the effect of an updatable view by creating rules //that rewrite inserts, etc. on the view into appropriate actions on other tables.
// The above RULE foo_r means that when user tries to insert data into view:
// foo_v, postgres should insert data into table foo instead.
//This will insert into the original table the values each time of a insert into the view
postgres=# INSERT INTO foo_v VALUES ((SELECT 1), (SELECT 2)), ((SELECT 3), (SELECT 4));
ERROR: unrecognized node type: 313
// This error type 313 is T_SUBLINK = 313, which indicates that the query node does not
// have SUBLINK (SELECT within insert).
//This query should be able to insert into the original table two entries: (1,2) and (3,4)
//if we insert it const values, it works:
postgres=# INSERT INTO foo_v VALUES (1, 2), (3, 4);
INSERT 0 2
//or if we insert it directly, it also works:
postgres=# INSERT INTO foo VALUES ((SELECT 1), (SELECT 2)), ((SELECT 3), (SELECT 4));
INSERT 0 2
//or if we insert one value a time, it also works:
postgres=# INSERT INTO foo_v VALUES ((SELECT 1), (SELECT 2));
INSERT 0 1
postgres=# INSERT INTO foo_v VALUES ((SELECT 3), (SELECT 4));
INSERT 0 1
rewriter(In pgsql the rewriter applies rewrite rules: view definitions and
ordinary rules. Input is a Query, output is zero or more Querys) didn’t set Query.hasSubLinks (select within INSERT) correctly during rewrite the query, which will take a long way to let the query be processed and detect something wrong until the execution plan is run.
* ExecInitExpr: prepare an expression tree for execution
ExecInitExpr(Expr *node, PlanState *parent)
if (node == NULL)
/* Guard against stack overflow due to overly complex expressions */
/* nodeTag checks if node has a subplan. It is error at this point if the node does
* not have an appropriately set subplan, thus after enumerate all types of valid
* subplan, postgres caches the error at the default case and print the error msg. */
elog(ERROR, "unrecognized node type: %d",
state = NULL; /* keep compiler quiet */
// This is where the variable ‘query.hasSubLinks’ should have been set,
// which indicates whether the query has sublinks.
// Since it is not set properly, eventually it would lead to the failure above.
* rewriteRuleAction -
* Rewrite the rule action with appropriate qualifiers (taken from
* the triggering query).
static Query *
rewriteRuleAction(Query *parsetree, Query *rule_action, Node *rule_qual,int rt_index,
CmdType event, bool *returning_flag)
* Generate expanded rtable consisting of main parsetree's rtable plus
* rule action's rtable; this becomes the complete rtable for the rule
sub_action->rtable = list_concat((List *) copyObject(parsetree->rtable),
* There could have been some SubLinks in parsetree's rtable, in which
* case we'd better mark the sub_action correctly.
* Setting sub_action->hasSubLinks appropriately below.
+ if (parsetree->hasSubLinks && !sub_action->hasSubLinks)
+ ListCell *lc;
+ foreach(lc, parsetree->rtable)
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+ switch (rte->rtekind)
+ case RTE_FUNCTION:
+ sub_action->hasSubLinks =
+ case RTE_VALUES:
+ sub_action->hasSubLinks =
+ checkExprHasSubLink((Node *) rte->values_lists);
+ /* other RTE types don't contain bare expressions */
+ if (sub_action->hasSubLinks)
+ break; /* no need to keep scanning rtable */
if (sub_action->commandType != CMD_UTILITY)
/* Without the above patch, sub_action->hasSubLinks is not set properly. */
if (parsetree->hasSubLinks && !sub_action->hasSubLinks)
checkExprHasSubLink((Node *) newjointree);
//How hasSubLinks affect the execution path?
//Basically, it affects the query plan generated(sublink node didn’t get //generated a subplan)
parse->jointree->quals = pull_up_IN_clauses(root,
… = preprocess_expression(...);
preprocess_expression(..Node * expr)
/* Expand SubLinks to SubPlans */
/* this root->parse->hasSubLinks has the same value as sub_action->hasSubLinks above. But since it was false, SS_process_sublinks was not called, thus the t_sublink node does not have a subplan, which will result the subsequent failure.*/
expr = SS_process_sublinks(root, expr, (kind == EXPRKIND_QUAL));
return expr; // didn’t get processed!
Is there any log message?
How can it be automatically inserted?
the existing error message is printed under switch-default pattern