Re: Empty materialized view

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;