Re: EXPLAIN with view: bogus varno: 5

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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83