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
