Thread: matview join view error

With 9.3devel, I can't seem to join a matview to a view; surely that should be allowed?

Here is an example:

-----8<----------
#!/bin/sh

echo "
drop table if exists t1 cascade;
drop table if exists t2 cascade;
drop materialized view if exists mv ;
create table t1 as select chr(i) as c1, i from generate_series(65, 90) as f(i);
create table t2 as select chr(i) as c2, i from generate_series(65, 90) as f(i);
create materialized view mv as select c1, i from t1 where i between 75 and 85;
create or replace view v as select c2, i from t2;
" | psql -qX

echo "
select  m.*, v.*
from mv m left join v on  v.i = m.i
" | psql -qXa
-----8<----------

This results in:

ERROR:  could not open file "base/21282/15840421": No such file or directory

(15840421 is the pg_class.relfilenode of view 'v').


Thanks,

Erik Rijkers

"Erikjan Rijkers" <er@xs4all.nl> writes:
> With 9.3devel, I can't seem to join a matview to a view; surely that should be allowed?

Fixed, thanks for the report.
        regards, tom lane

Erikjan Rijkers <er@xs4all.nl> wrote:

> With 9.3devel, I can't seem to join a matview to a view; surely
> that should be allowed?

Yes.

> Here is an example:

I expect to add a regression test based on that, once the bug is
fixed.

Thanks!

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Erikjan Rijkers" <er@xs4all.nl> writes:
>> With 9.3devel, I can't seem to join a matview to a view; surely
>> that should be allowed?
>
> Fixed, thanks for the report.

Thanks for the fix while I was away.

Regression test for this case added.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company