view + explain + index scan -> bogus varno: 65001 (with some variations)

Поиск
Список
Период
Сортировка
От Jon Nelson
Тема view + explain + index scan -> bogus varno: 65001 (with some variations)
Дата
Msg-id BANLkTin7T900fE+2RmZNayT-gNp1URW24Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: view + explain + index scan -> bogus varno: 65001 (with some variations)  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
The problem was observed on CentOS 5.6 using postgresql 8.4.7 and
Scientific Linux 6.0 also using postgresql 8.4.7.
The problem could not be replicated on openSUSE 11.4 which has postgresql 9.0.3.

With 8.4.7, I ran into an issue trying to explain a VIEW query.
After much effort, I distilled the query down and was able to
replicate the issue with a test script, included below.
A few notes:

1. if I set enable_indexscan to false, the problem goes away.
2. if I remove the "and table_date" clause, the problem goes away
3. I have also seen this error: ERROR:  bogus varattno for OUTER var: 1
   under the same conditions.
4. 9.0.3 on openSUSE 11.4 does *not* show the problem (at least, I'm
not able to replicate it there).


begin;

set enable_seqscan = false;

drop view if exists foobar;
drop table if exists foo;
drop table if exists bar;
drop table if exists baz;
create table foo (column1 int);
create index foo_column1_idx on foo (column1);
insert into foo select generate_series(1,100000);

create table bar (column1 int);
create index bar_column1_idx on bar (column1);
insert into bar select generate_series(100000,200000);

create table baz (column1 int);
create index baz_column1_idx on baz (column1);
insert into baz select generate_series(50000,150000);

create view FOOBAR AS
select *, DATE '2011-01-01' as table_date from foo UNION ALL select *,
DATE '2011-01-02' as table_date  FROM bar ;


explain verbose SELECT foobar.* FROM foobar, baz
WHERE foobar.column1 = baz.column1 AND table_date >= now() ;

rollback;


--
Jon

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: BUG #6063: compatability issues
Следующее
От: heasley
Дата:
Сообщение: Re: BUG #5741: syslog line length