Обсуждение: EXPLAIN with view: bogus varno: 5

Running EXPLAIN on a view that has an aggregate and uses an index
results in the error "bogus varno: 5".  At least I think the aggregate
and index are necessary -- removing either from the following example
allows EXPLAIN to succeed:

test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> CREATE INDEX foo_x_idx ON foo (x);
CREATE INDEX
test=> CREATE VIEW fooview1 AS SELECT count(*) FROM foo WHERE x < 10;
CREATE VIEW
test=> CREATE VIEW fooview2 AS SELECT * FROM foo WHERE x < 10;
CREATE VIEW
test=> CREATE VIEW fooview3 AS SELECT count(*) FROM foo;
CREATE VIEW
test=> CREATE VIEW fooview4 AS SELECT * FROM foo;
CREATE VIEW

test=> \set VERBOSITY verbose

test=> EXPLAIN SELECT * FROM fooview1;
ERROR:  XX000: bogus varno: 5
LOCATION:  get_rte_for_var, ruleutils.c:2478

test=> EXPLAIN SELECT * FROM fooview2;                               QUERY PLAN                                
--------------------------------------------------------------------------Bitmap Heap Scan on foo  (cost=3.50..22.41
rows=713width=4)  Recheck Cond: (x < 10)  ->  Bitmap Index Scan on foo_x_idx  (cost=0.00..3.50 rows=713 width=0)
IndexCond: (x < 10)
 
(4 rows)

test=> EXPLAIN SELECT * FROM fooview3;                        QUERY PLAN                          
-------------------------------------------------------------Aggregate  (cost=36.75..36.75 rows=1 width=0)  ->  Seq
Scanon foo  (cost=0.00..31.40 rows=2140 width=0)
 
(2 rows)

test=> EXPLAIN SELECT * FROM fooview4;                     QUERY PLAN                       
-------------------------------------------------------Seq Scan on foo  (cost=0.00..31.40 rows=2140 width=4)
(1 row)

test=> DROP INDEX foo_x_idx;
DROP INDEX
test=> EXPLAIN SELECT * FROM fooview1;                        QUERY PLAN                         
------------------------------------------------------------Aggregate  (cost=38.53..38.53 rows=1 width=0)  ->  Seq Scan
onfoo  (cost=0.00..36.75 rows=713 width=0)        Filter: (x < 10)
 
(3 rows)

-- 
Michael Fuhr

I'm not sure it's relevant to Michaels' case, but I see message (8.1dev)

ERROR:  bogus varno: 2

tp=# explain select  name_qualified from place, to_tsquery('moscow') as query 
where fts_index @@ query;
ERROR:  bogus varno: 2

In my case, this select produces core dump while being rewritten works fine.

tp=# select  name_qualified from place
where fts_index @@  to_tsquery('moscow');

    Oleg

On Fri, 26 Aug 2005, Michael Fuhr wrote:

> Running EXPLAIN on a view that has an aggregate and uses an index
> results in the error "bogus varno: 5".  At least I think the aggregate
> and index are necessary -- removing either from the following example
> allows EXPLAIN to succeed:
>
> test=> CREATE TABLE foo (x integer);
> CREATE TABLE
> test=> CREATE INDEX foo_x_idx ON foo (x);
> CREATE INDEX
> test=> CREATE VIEW fooview1 AS SELECT count(*) FROM foo WHERE x < 10;
> CREATE VIEW
> test=> CREATE VIEW fooview2 AS SELECT * FROM foo WHERE x < 10;
> CREATE VIEW
> test=> CREATE VIEW fooview3 AS SELECT count(*) FROM foo;
> CREATE VIEW
> test=> CREATE VIEW fooview4 AS SELECT * FROM foo;
> CREATE VIEW
>
> test=> \set VERBOSITY verbose
>
> test=> EXPLAIN SELECT * FROM fooview1;
> ERROR:  XX000: bogus varno: 5
> LOCATION:  get_rte_for_var, ruleutils.c:2478
>
> test=> EXPLAIN SELECT * FROM fooview2;
>                                QUERY PLAN
> --------------------------------------------------------------------------
> Bitmap Heap Scan on foo  (cost=3.50..22.41 rows=713 width=4)
>   Recheck Cond: (x < 10)
>   ->  Bitmap Index Scan on foo_x_idx  (cost=0.00..3.50 rows=713 width=0)
>         Index Cond: (x < 10)
> (4 rows)
>
> test=> EXPLAIN SELECT * FROM fooview3;
>                         QUERY PLAN
> -------------------------------------------------------------
> Aggregate  (cost=36.75..36.75 rows=1 width=0)
>   ->  Seq Scan on foo  (cost=0.00..31.40 rows=2140 width=0)
> (2 rows)
>
> test=> EXPLAIN SELECT * FROM fooview4;
>                      QUERY PLAN
> -------------------------------------------------------
> Seq Scan on foo  (cost=0.00..31.40 rows=2140 width=4)
> (1 row)
>
> test=> DROP INDEX foo_x_idx;
> DROP INDEX
> test=> EXPLAIN SELECT * FROM fooview1;
>                         QUERY PLAN
> ------------------------------------------------------------
> Aggregate  (cost=38.53..38.53 rows=1 width=0)
>   ->  Seq Scan on foo  (cost=0.00..36.75 rows=713 width=0)
>         Filter: (x < 10)
> (3 rows)
>
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Michael Fuhr <mike@fuhr.org> writes:
> Running EXPLAIN on a view that has an aggregate and uses an index
> results in the error "bogus varno: 5".

I've committed a fix for this --- it was a bug in the recently added
code that eliminates useless SubqueryScan nodes.
        regards, tom lane