Re: Empty materialized view - Mailing list pgsql-general

From Thiemo Kellner
Subject Re: Empty materialized view
Date
Msg-id ef0db0e6-4abe-44de-aabb-5255c4624bbf@gelassene-pferde.biz
Whole thread Raw
In response to Re: Empty materialized view  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Empty materialized view
List pgsql-general
Am 24.03.2024 um 21:30 schrieb Adrian Klaver:
> On 3/24/24 13:11, Thiemo Kellner wrote:
> Confirmed in the same session that created it or in a different session?

Different session, not knowing what that mattered.

Excerpt of the installation protocol:
…
## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen
DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT
# insert data #
…

Check was done by DbVisualizer.

I was not able to create a test case. I tried

drop table if exists TEST_T cascade;
create table TEST_T (ID smallint);
insert into TEST_T (ID) values (1);
commit;

drop materialized view if exists TEST_MV_ON_TABLE;
create materialized view TEST_MV_ON_TABLE as select * from TEST_T with 
data; -- on table
commit;

select * from TEST_MV_ON_TABLE;

commit;
create or replace view VIEW_LEVEL_1 as select * from TEST_T;
create or replace view VIEW_LEVEL_2 as select v.id from VIEW_LEVEL_1 v 
cross join TEST_T;
create or replace view VIEW_LEVEL_3 as select v.id from VIEW_LEVEL_2 v 
cross join VIEW_LEVEL_1;
create or replace view VIEW_LEVEL_4 as select v.id from VIEW_LEVEL_3 v 
cross join VIEW_LEVEL_2;
create or replace view VIEW_LEVEL_5 as select v.id from VIEW_LEVEL_4 v 
cross join VIEW_LEVEL_3;
create or replace view VIEW_LEVEL_6 as select v.id from VIEW_LEVEL_5 v 
cross join VIEW_LEVEL_4;
create or replace view VIEW_LEVEL_7 as select v.id from VIEW_LEVEL_6 v 
cross join VIEW_LEVEL_5;
create or replace view VIEW_LEVEL_8 as select v.id from VIEW_LEVEL_7 v 
cross join VIEW_LEVEL_6;
create or replace view VIEW_LEVEL_9 as select v.id from VIEW_LEVEL_8 v 
cross join VIEW_LEVEL_7;
create or replace view VIEW_LEVEL_10 as select v.id from VIEW_LEVEL_9 v 
cross join VIEW_LEVEL_8;
commit;

drop materialized view if exists TEST_MV_ON_VIEWS;
create materialized view TEST_MV_ON_VIEWS as select * from VIEW_LEVEL_10 
with data; -- on views
commit;

select * from TEST_MV_ON_VIEWS;



But this works as expected.

Ok, I just added the following to my install script and there the data 
is visible.

select count(*) from SNOWRUNNER.TASK_DEPENDENCY⠒V;






pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Empty materialized view
Next
From: Adrian Klaver
Date:
Subject: Re: Empty materialized view