Thread: don't see materialized views in information_schema
Hi
create table omega(a int);
create view omega_view as select * from omega;
insert into omega values(10);
postgres=# select table_type, table_name from information_schema.tables where table_name like 'omega%';
┌────────────┬────────────┐
│ table_type │ table_name │
╞════════════╪════════════╡
│ BASE TABLE │ omega │
│ VIEW │ omega_view │
└────────────┴────────────┘
(2 rows)
┌────────────┬────────────┐
│ table_type │ table_name │
╞════════════╪════════════╡
│ BASE TABLE │ omega │
│ VIEW │ omega_view │
└────────────┴────────────┘
(2 rows)
postgres=# create materialized view omega_m_view as select * from omega;
SELECT 1
postgres=# select table_type, table_name from information_schema.tables where table_name like 'omega%';
┌────────────┬────────────┐
│ table_type │ table_name │
╞════════════╪════════════╡
│ BASE TABLE │ omega │
│ VIEW │ omega_view │
└────────────┴────────────┘
(2 rows)
postgres=# refresh materialized view omega_m_view ;
REFRESH MATERIALIZED VIEW
postgres=# select table_type, table_name from information_schema.tables where table_name like 'omega%';
┌────────────┬────────────┐
│ table_type │ table_name │
╞════════════╪════════════╡
│ BASE TABLE │ omega │
│ VIEW │ omega_view │
└────────────┴────────────┘
(2 rows)
SELECT 1
postgres=# select table_type, table_name from information_schema.tables where table_name like 'omega%';
┌────────────┬────────────┐
│ table_type │ table_name │
╞════════════╪════════════╡
│ BASE TABLE │ omega │
│ VIEW │ omega_view │
└────────────┴────────────┘
(2 rows)
postgres=# refresh materialized view omega_m_view ;
REFRESH MATERIALIZED VIEW
postgres=# select table_type, table_name from information_schema.tables where table_name like 'omega%';
┌────────────┬────────────┐
│ table_type │ table_name │
╞════════════╪════════════╡
│ BASE TABLE │ omega │
│ VIEW │ omega_view │
└────────────┴────────────┘
(2 rows)
Is it expected behave? Tested on master branch.
Pavel
On 2019-09-11 08:14, Pavel Stehule wrote: > Hi > > [matviews not showing up in information_schema.tables] > > Is it expected behave? Tested on master branch. I think it is; it has been like this all along. ( matviews are in pg_matviews. )
st 11. 9. 2019 v 9:49 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
On 2019-09-11 08:14, Pavel Stehule wrote:
> Hi
>
> [matviews not showing up in information_schema.tables]
>
> Is it expected behave? Tested on master branch.
I think it is; it has been like this all along.
( matviews are in pg_matviews. )
Minimally I miss a entry in information_schema.views
To today I expected so any object should be listed somewhere in information_schema.
Pavel
On Wed, Sep 11, 2019 at 10:03 AM Pavel Stehule <pavel.stehule@gmail.com> wrote: > > st 11. 9. 2019 v 9:49 odesílatel Erik Rijkers <er@xs4all.nl> napsal: >> >> On 2019-09-11 08:14, Pavel Stehule wrote: >> > Hi >> > >> > [matviews not showing up in information_schema.tables] >> > >> > Is it expected behave? Tested on master branch. >> >> I think it is; it has been like this all along. >> >> ( matviews are in pg_matviews. ) > > > Minimally I miss a entry in information_schema.views > > To today I expected so any object should be listed somewhere in information_schema. > There has been previous discussion about this topic: https://www.postgresql.org/message-id/3794.1412980686@sss.pgh.pa.us Regards, Juan José Santamaría Flecha
st 11. 9. 2019 v 10:52 odesílatel Juan José Santamaría Flecha <juanjo.santamaria@gmail.com> napsal:
On Wed, Sep 11, 2019 at 10:03 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> st 11. 9. 2019 v 9:49 odesílatel Erik Rijkers <er@xs4all.nl> napsal:
>>
>> On 2019-09-11 08:14, Pavel Stehule wrote:
>> > Hi
>> >
>> > [matviews not showing up in information_schema.tables]
>> >
>> > Is it expected behave? Tested on master branch.
>>
>> I think it is; it has been like this all along.
>>
>> ( matviews are in pg_matviews. )
>
>
> Minimally I miss a entry in information_schema.views
>
> To today I expected so any object should be listed somewhere in information_schema.
>
There has been previous discussion about this topic:
https://www.postgresql.org/message-id/3794.1412980686@sss.pgh.pa.us
understand now.
Thank you
Pavel
Regards,
Juan José Santamaría Flecha