Thread: Explain analyze gives bogus varno for dblink views

Using the old < 7.3 version of dblink on 7.4devel gives a
"get_names_for_var: bogus varno 5" error.

Install the deprecated version of dblink by following the directions in
the dblink.sql file to comment / uncomment various parts of the script.

DROP TABLE t1;
CREATE TABLE t1 (a int);

DROP TABLE t2;
CREATE TABLE t2 (a int, b int);

INSERT INTO t1 (a) VALUES (1);

INSERT INTO t2 (a,b) VALUES (1,1);
INSERT INTO t2 (a,b) VALUES (1,2);

DROP VIEW v1;
CREATE VIEW v1 AS
    SELECT dblink_tok(t1.dblink_p,0) as a
    FROM (SELECT dblink('hostaddr=127.0.0.1 port=5740 dbname=dblink
user=jurka password=','SELECT a FROM t1') as dblink_p) AS t1;

DROP VIEW v2;
CREATE VIEW v2 AS
    SELECT dblink_tok(t2.dblink_p,0) as a,dblink_tok(t2.dblink_p,1) as b
    FROM (SELECT dblink('hostaddr=127.0.0.1 port=5740 dbname=dblink
user=jurka password=','SELECT a,b FROM t2') as dblink_p) AS t2;

SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.a=1;

EXPLAIN ANALYZE
SELECT * FROM v1,v2 WHERE v1.a=v2.a AND v1.a=1;

This behavior is present in 7.3 as well.

On Thu, 5 Dec 2002, Kris Jurka wrote:

> Using the old < 7.3 version of dblink on 7.4devel gives a
> "get_names_for_var: bogus varno 5" error.
>
> Install the deprecated version of dblink by following the directions in
> the dblink.sql file to comment / uncomment various parts of the script.
>
> DROP TABLE t1;
> CREATE TABLE t1 (a int);
>
> DROP TABLE t2;
> CREATE TABLE t2 (a int, b int);
>
> INSERT INTO t1 (a) VALUES (1);
>
> INSERT INTO t2 (a,b) VALUES (1,1);
> INSERT INTO t2 (a,b) VALUES (1,2);
>
> DROP VIEW v1;
> CREATE VIEW v1 AS
>     SELECT dblink_tok(t1.dblink_p,0) as a
>     FROM (SELECT dblink('hostaddr=127.0.0.1 port=5740 dbname=dblink
> user=jurka password=','SELECT a FROM t1') as dblink_p) AS t1;
>
> DROP VIEW v2;
> CREATE VIEW v2 AS
>     SELECT dblink_tok(t2.dblink_p,0) as a,dblink_tok(t2.dblink_p,1) as b
>     FROM (SELECT dblink('hostaddr=127.0.0.1 port=5740 dbname=dblink
> user=jurka password=','SELECT a,b FROM t2') as dblink_p) AS t2;
>
> SELECT * FROM t1,t2 WHERE t1.a=t2.a AND t1.a=1;
>
> EXPLAIN ANALYZE
> SELECT * FROM v1,v2 WHERE v1.a=v2.a AND v1.a=1;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Kris Jurka wrote:
> This behavior is present in 7.3 as well.
>
> On Thu, 5 Dec 2002, Kris Jurka wrote:
>>Using the old < 7.3 version of dblink on 7.4devel gives a
>>"get_names_for_var: bogus varno 5" error.

I can confirm this both on cvs tip (pulled after noon PST today) and 7.3
stable branch. It is not related to dblink, but rather the backend. Here's a
(contrived) script based on Kris's example to trigger it:

CREATE TABLE table1 (a int);
CREATE TABLE table2 (a int, b int);
INSERT INTO table1 (a) VALUES (1);
INSERT INTO table2 (a,b) VALUES (1,1);
INSERT INTO table2 (a,b) VALUES (1,2);

CREATE OR REPLACE FUNCTION func1(int) RETURNS setof int AS '
select a from table2 where a = $1
' LANGUAGE 'sql' WITH (isstrict);

CREATE OR REPLACE FUNCTION func2(int,int) RETURNS int AS '
select $1 * $2
' LANGUAGE 'sql' WITH (isstrict);

CREATE VIEW v1 AS
    SELECT func2(t1.f1,3) as a
    FROM (SELECT func1(1) as f1) AS t1;

DROP VIEW v2;
CREATE VIEW v2 AS
    SELECT func2(t2.f1,3) as a, func2(t2.f1,5) as b
    FROM (SELECT func1(1) as f1) AS t2;

SELECT * FROM v1,v2 WHERE v1.a=v2.a AND v1.a=3;
EXPLAIN ANALYZE SELECT * FROM v1,v2 WHERE v1.a=v2.a AND v1.a=3;

Here's a backtrace:

#0  elog (lev=20, fmt=0x8211800 "get_names_for_var: bogus varno %d") at elog.c:114
#1  0x0815e53c in get_names_for_var (var=0x82d07ec, context=0xbfffe9c0,
schemaname=0xbfffe8b0, refname=0xbfffe8b4,
     attname=0xbfffe8b8) at ruleutils.c:1806
#2  0x0815e6ed in get_rule_expr (node=0x82d07ec, context=0xbfffe9c0,
showimplicit=1 '\001') at ruleutils.c:1938
#3  0x0815eed3 in get_oper_expr (expr=0x0, context=0xbfffe9c0) at ruleutils.c:2282
#4  0x0815e7de in get_rule_expr (node=0x82d0b54, context=0xbfffe9c0,
showimplicit=1 '\001') at ruleutils.c:1972
#5  0x0815eed3 in get_oper_expr (expr=0x0, context=0xbfffe9c0) at ruleutils.c:2282
#6  0x0815e7de in get_rule_expr (node=0x82d0b9c, context=0xbfffe9c0,
showimplicit=0 '\0') at ruleutils.c:1972
#7  0x0815cfef in deparse_expression (expr=0x82d0b9c, dpcontext=0x0,
forceprefix=0 '\0', showimplicit=0 '\0')
     at ruleutils.c:872
#8  0x080ca75a in show_upper_qual (qual=0x82d1d50, qlabel=0x81df318 "Filter",
outer_name=0x5 <Address 0x5 out of bounds>,
     outer_varno=1, outer_plan=0x0, inner_name=0x819479b "", inner_varno=0,
inner_plan=0x0, str=0x82d7668, indent=3,
     es=0x82e4b58) at explain.c:812
#9  0x080ca01e in explain_outNode (str=0x82d7668, plan=0x82d1d6c,
planstate=0x82d4674, outer_plan=0x0, indent=3,
     es=0x82d7a58) at explain.c:570
#10 0x080c9d3a in explain_outNode (str=0x82d7668, plan=0x82d2098,
planstate=0x82d2560, outer_plan=0x0, indent=0,
     es=0x82d7a58) at explain.c:614
#11 0x080c992b in ExplainOneQuery (query=0x82d7668, stmt=0x82bb9e8,
tstate=0x82c06c8) at explain.c:198
#12 0x080c9745 in ExplainQuery (stmt=0x82bb9e8, dest=Remote) at explain.c:102
#13 0x081388a3 in pg_exec_query_string (query_string=0x82bb9e8, dest=Remote,
parse_context=0x8287574) at postgres.c:789
#14 0x0813976c in PostgresMain (argc=5, argv=0xbfffee70, username=0x8279f19
"postgres") at postgres.c:2016
#15 0x0811e30e in DoBackend (port=0x8279de8) at postmaster.c:2293
#16 0x0811de7a in BackendStartup (port=0x8279de8) at postmaster.c:1915
#17 0x0811cf9d in ServerLoop () at postmaster.c:1002
#18 0x0811c915 in PostmasterMain (argc=3, argv=0x825cc78) at postmaster.c:781
#19 0x080f930f in main (argc=3, argv=0xbffff7e4) at main.c:209

Note the line:
#8  0x080ca75a in show_upper_qual (qual=0x82d1d50, qlabel=0x81df318 "Filter",
outer_name=0x5 <Address 0x5 out of bounds>,

I'm still trying to understand the root cause, but any pointers would be
appreciated.

Thanks,

Joe

Joe Conway <mail@joeconway.com> writes:
> I can confirm this both on cvs tip (pulled after noon PST today) and 7.3
> stable branch. It is not related to dblink, but rather the backend. Here's a
> (contrived) script based on Kris's example to trigger it:

Ah, thanks for the simplified test case.  This is undoubtedly my fault
... will look into it.  It is probably somewhat related to the join
alias problem found yesterday (ie, somebody somewhere is trying to use
the wrong rangetable list to interpret a Var node).

            regards, tom lane

Tom Lane wrote:
> Ah, thanks for the simplified test case.  This is undoubtedly my fault
> ... will look into it.  It is probably somewhat related to the join
> alias problem found yesterday (ie, somebody somewhere is trying to use
> the wrong rangetable list to interpret a Var node).

I spent a bit more time on it last night. Here's an even simpler example:

CREATE TABLE table1 (a int);
CREATE TABLE table2 (a int, b int);
INSERT INTO table1 (a) VALUES (1);
INSERT INTO table2 (a,b) VALUES (1,1);
INSERT INTO table2 (a,b) VALUES (1,2);

CREATE OR REPLACE FUNCTION func1(int) RETURNS setof int AS '
select a from table2 where a = $1
' LANGUAGE 'sql' WITH (isstrict);

CREATE OR REPLACE FUNCTION func2(int,int) RETURNS int AS '
select $1 * $2
' LANGUAGE 'sql' WITH (isstrict);

regression=# SELECT t2.a1 FROM (SELECT func2(t1.f1,3) as a1 FROM (SELECT
func1(1) as f1) AS t1) AS t2 WHERE t2.a1 = 3;
  a1
----
   3
   3
(2 rows)

regression=# EXPLAIN SELECT t2.a1 FROM (SELECT func2(t1.f1,3) as a1 FROM
(SELECT func1(1) as f1) AS t1) AS t2 WHERE t2.a1 = 3;
ERROR:  get_names_for_var: bogus varno 2

regression=# EXPLAIN SELECT t2.a1 FROM (SELECT func2(t1.f1,3) as a1 FROM
(SELECT func1(1) as f1) AS t1) AS t2;                                    QUERY
PLAN
----------------------------------------------------
  Subquery Scan t1  (cost=0.00..0.01 rows=1 width=0)
    ->  Result  (cost=0.00..0.01 rows=1 width=0)
(2 rows)

The problem is triggered by the WHERE clause. I was struggling as to where to
be looking. BTW, it was still there after I sync'd up with cvs last night.

Joe

Joe Conway <mail@joeconway.com> writes:
> Tom Lane wrote:
>> Ah, thanks for the simplified test case.  This is undoubtedly my fault
>> ... will look into it.  It is probably somewhat related to the join
>> alias problem found yesterday (ie, somebody somewhere is trying to use
>> the wrong rangetable list to interpret a Var node).

> I spent a bit more time on it last night. Here's an even simpler example:

The answer is that it was brain fade on my part when I wrote the code
for showing plan qualification expressions in EXPLAIN.  SubqueryScan
should be classed as a scan node, not an upper node --- it *is* a
primitive scan, from the point of view of the upper query.  So any Vars
in its qual have to be resolved against the upper rangetable, not the
subplan's rangetable.

The diff against 7.3 is attached.

            regards, tom lane

*** src/backend/commands/explain.c.orig    Mon Oct 14 00:26:54 2002
--- src/backend/commands/explain.c    Fri Dec  6 14:16:48 2002
***************
*** 432,437 ****
--- 432,438 ----
              break;
          case T_SeqScan:
          case T_TidScan:
+         case T_SubqueryScan:
          case T_FunctionScan:
              show_scan_qual(plan->qual, false,
                             "Filter",
***************
*** 483,495 ****
                              "Filter",
                              "outer", OUTER, outerPlan(plan),
                              "inner", INNER, innerPlan(plan),
-                             str, indent, es);
-             break;
-         case T_SubqueryScan:
-             show_upper_qual(plan->qual,
-                             "Filter",
-                           "subplan", 1, ((SubqueryScan *) plan)->subplan,
-                             "", 0, NULL,
                              str, indent, es);
              break;
          case T_Agg:
--- 484,489 ----