pgsql-4434

Version:

8.3

Bug Link:

http://postgresql.1045698.n5.nabble.com/BUG-4434-Error-inserting-into-view-unrecognized-node-type-313-td2127762.html

Patch Link:

http://archives.postgresql.org/pgsql-corewriteHandler.cmmitters/2008-09/msg00236.php

Symptom:

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:

Reproduced!

postgres=# DROP TABLE IF EXISTS foo CASCADE;

NOTICE:  table "foo" does not exist, skipping

DROP TABLE

postgres=# CREATE TABLE foo(a int, b int);

CREATE TABLE

postgres=# CREATE VIEW foo_v AS SELECT * FROM foo;

CREATE VIEW

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

VALUES(NEW.a, NEW.b);

CREATE RULE

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

Root Cause:

Brief:

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.

Where the log message is printed?

src/backend/executor/execQual.c

/*

 * ExecInitExpr: prepare an expression tree for execution

 *

/

ExprState *

ExecInitExpr(Expr *node, PlanState *parent)

{

        ExprState  *state;

        if (node == NULL)

                return NULL;

        /* Guard against stack overflow due to overly complex expressions */

        check_stack_depth();

           /* 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. */

        switch (nodeTag(node))

        {

                ...

                default:

                        elog(ERROR, "unrecognized node type: %d",

                                 (int) nodeTag(node));

                        state = NULL;                /* keep compiler quiet */

                        break;

        }

}

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

{

        int                        current_varno,

                                new_varno;

        int                        rt_length;

        Query           *sub_action;

        Query          **sub_action_ptr;

        …

        /*

         * Generate expanded rtable consisting of main parsetree's rtable plus

         * rule action's rtable; this becomes the complete rtable for the rule

         * action.

         */

        sub_action->rtable = list_concat((List *) copyObject(parsetree->rtable),

                                                                         sub_action->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 =
+                                                 checkExprHasSubLink(rte->funcexpr);
+                                         break;
+                                 case RTE_VALUES:
+                                         sub_action->hasSubLinks =
+                                                 checkExprHasSubLink((Node *) rte->values_lists);
+                                         break;
+                                 default:
+                                         /* other RTE types don't contain bare expressions */
+                                         break;
+                         }
+                         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)

                        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)

subquery_planner(...)

{

        …

        if (parse->hasSubLinks)

                parse->jointree->quals = pull_up_IN_clauses(root,                        

parse->jointree->quals);

        …

        … = 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.*/

        if (root->parse->hasSubLinks)

                expr = SS_process_sublinks(root, expr, (kind == EXPRKIND_QUAL));

        …

        return expr; // didn’t get processed!

}

Is there any log message?

Yes

How can it be automatically inserted?

the existing error message is printed under switch-default pattern