Thread: Empty materialized view

Empty materialized view

From

Thiemo Kellner

Date:

24 March, 21:12:58

Hi

I have created a materialized view with "with data". And I refreshed it 
with "with data". The query of the mv returns records when executed 
outside the mv. I would appreciate help with respect to what I miss that 
my mv is empty. You might want to have a look at the code attached.

Kind regards

Thiemo

Attachment

snowrunner.7z

Re: Empty materialized view

From

Erik Wienhold

Date:

24 March, 22:56:11

On 2024-03-24 19:12 +0100, Thiemo Kellner wrote:
> I have created a materialized view with "with data". And I refreshed it with
> "with data". The query of the mv returns records when executed outside the
> mv. I would appreciate help with respect to what I miss that my mv is empty.

Maybe you executed REFRESH in a transaction but did not commit it?

> You might want to have a look at the code attached.

I can't find any materialized view in your archive.

-- 
Erik

Re: Empty materialized view

From

Adrian Klaver

Date:

24 March, 23:07:08

On 3/24/24 11:12, Thiemo Kellner wrote:
> Hi
> 
> I have created a materialized view with "with data". And I refreshed it 
> with "with data". The query of the mv returns records when executed 
> outside the mv. I would appreciate help with respect to what I miss that 
> my mv is empty. You might want to have a look at the code attached.

That does not contain the statements mentioned above. Provide a simple 
test case as code inline to your reply.

> 
> Kind regards
> 
> Thiemo

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Empty materialized view

From

Thiemo Kellner

Date:

24 March, 23:11:52


Am 24.03.2024 um 20:56 schrieb Erik Wienhold:
> Maybe you executed REFRESH in a transaction but did not commit it?

While I can see the point for the refresh (but there actually is a 
commit), I cannot hold it valid for a create with data when the mv 
actually is created (confirmed by being empty).

> I can't find any materialized view in your archive.

Oh sh*. That is the file, I forgot to commit. Please find it attached now.

Attachment

QUERY_PER_TASK.pg_

Re: Empty materialized view

From

Adrian Klaver

Date:

24 March, 23:30:28

On 3/24/24 13:11, Thiemo Kellner wrote:
> 
> 
> Am 24.03.2024 um 20:56 schrieb Erik Wienhold:
>> Maybe you executed REFRESH in a transaction but did not commit it?
> 
> While I can see the point for the refresh (but there actually is a 
> commit), I cannot hold it valid for a create with data when the mv 
> actually is created (confirmed by being empty).

Confirmed in the same session that created it or in a different session?

> 
>> I can't find any materialized view in your archive.
> 
> Oh sh*. That is the file, I forgot to commit. Please find it attached now.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Empty materialized view

From

Thiemo Kellner

Date:

24 March, 23:36:55

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;

Re: Empty materialized view

From

Adrian Klaver

Date:

24 March, 23:50:06

On 3/24/24 13:36, Thiemo Kellner wrote:
> 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

It does depending on the order of viewing. Namely if you viewed the 
'old' empty MV in the outside session before you dropped/created the 
'new' MV and committed the changes.

> 
> 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 #
> …
> 

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

That is not the view you showed in your attached SQL in your previous 
post nor what is mentioned above. Also if I am following your naming 
scheme it is a regular view not a materialized view.

> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Empty materialized view

From

Thiemo Kellner

Date:

24 March, 23:58:52

Am 24.03.2024 um 21:50 schrieb Adrian Klaver:
> On 3/24/24 13:36, Thiemo Kellner wrote:
> It does depending on the order of viewing. Namely if you viewed the 
> 'old' empty MV in the outside session before you dropped/created the 
> 'new' MV and committed the changes.

Something like the viewing session is in a transaction before the 
(re-)creation of the mv?

The view session is on auto commit. (It's sole purpose to query stuff 
and not to have explicitly terminate transactions do to syntax errors 
and so on.)

>>
>> 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 #
>> …
>>
> 
> 
>> select count(*) from SNOWRUNNER.TASK_DEPENDENCY⠒V;
> 
> That is not the view you showed in your attached SQL in your previous 
> post nor what is mentioned above. Also if I am following your naming 
> scheme it is a regular view not a materialized view.

Feeling quite dumb now. But then, there neither is data visible in the 
install session.

insert data into TASK_DEPENDENCY⠒V
INSERT 0 34
COMMIT
  count
-------
      0
(1 row)


Thanks for taking care.

Re: Empty materialized view

From

Adrian Klaver

Date:

25 March, 00:06:19

On 3/24/24 13:58, Thiemo Kellner wrote:
> Am 24.03.2024 um 21:50 schrieb Adrian Klaver:
>> On 3/24/24 13:36, Thiemo Kellner wrote:
>> It does depending on the order of viewing. Namely if you viewed the 
>> 'old' empty MV in the outside session before you dropped/created the 
>> 'new' MV and committed the changes.
> 
> Something like the viewing session is in a transaction before the 
> (re-)creation of the mv?
> 
> The view session is on auto commit. (It's sole purpose to query stuff 
> and not to have explicitly terminate transactions do to syntax errors 
> and so on.)

Autocommit will only affect actions in that session, it will not make 
the other sessions actions visible. That depends on the other sessions 
committing actions.

See:

https://www.postgresql.org/docs/current/transaction-iso.html


> 
> Feeling quite dumb now. But then, there neither is data visible in the 
> install session.
> 
> insert data into TASK_DEPENDENCY⠒V
> INSERT 0 34

The above says the data was inserted.

> COMMIT
>   count
> -------
>       0


Where and when was the count query run?

> (1 row)
> 
> 
> Thanks for taking care.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Empty materialized view

From

Thiemo Kellner

Date:

25 March, 00:27:45

Am 24.03.2024 um 22:06 schrieb Adrian Klaver:
>> The view session is on auto commit. (It's sole purpose to query stuff 
>> and not to have explicitly terminate transactions do to syntax errors 
>> and so on.)
> 
> Autocommit will only affect actions in that session, it will not make 
> the other sessions actions visible. That depends on the other sessions 
> committing actions.
> 
> See:
> 
> https://www.postgresql.org/docs/current/transaction-iso.html

I am under the impression that (higher transaction number in my example 
means more recent transaction) if my autocommit session's last 
transaction is 1 (some select), then the install session installs the 
objects and data with a series of transactions (2 - 20), i.e. several 
commits, and I afterwards use the autocommit session to check on the 
content of the objects installed in transactions 2 to 20, I should see 
the data having a transaction in the autocommit > 20. It works like this 
at least for the tables. But not for this mv.

>>
>> Feeling quite dumb now. But then, there neither is data visible in the 
>> install session.
>>
>> insert data into TASK_DEPENDENCY⠒V
>> INSERT 0 34
> 
> The above says the data was inserted.

But not into the MV but into TASK_DEPENDENCY⠒V.

> Where and when was the count query run?

Excerpt of the install script

\echo ## tenth level ##
\ir views/QUERY_PER_TASK⠒MV.pg_sql


-- \echo # functions relying on other objects #


\echo # insert data #
\echo ## first level ##
\ir insert_data/CENTRICITY.pg_sql
\ir insert_data/DIRECTION.pg_sql
\ir insert_data/GOOD_CLASS.pg_sql
\ir insert_data/NODE_TYPE.pg_sql
\ir insert_data/REGION.pg_sql
\ir insert_data/TASK_TYPE.pg_sql

\echo ## second level ##
\ir insert_data/AREA.pg_sql
\ir insert_data/DISTANCE⠒V.pg_sql
\ir insert_data/GOOD⠒V.pg_sql
\ir insert_data/MAP⠒V.pg_sql

\echo ## third level ##
\ir insert_data/DIRECT_NEIGHBOUR.pg_sql
\echo ### Scandinavia ###
\ir insert_data/Scandinavia/NODE⠒V.pg_sql
\ir insert_data/Scandinavia/PRODUCTION⠒V.pg_sql
\ir insert_data/Scandinavia/TASK⠒V.pg_sql

\echo ## forth level ##
\echo Scandinavia
\ir insert_data/Scandinavia/DROP_OFF⠒V.pg_sql
\ir insert_data/Scandinavia/PICK_UP⠒V.pg_sql
\ir insert_data/Scandinavia/TASK_DEPENDENCY⠒V.pg_sql

-- \echo ## fifth level ##
     -- \ir insert_data/Scandinavia/NODE_GOOD⠒V.pg_sql

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

commit;



Excerpt of the according 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 #
## first level ##
insert data into CENTRICITY
INSERT 0 2
COMMIT
insert data into DIRECTION
INSERT 0 8
COMMIT
insert data into GOOD_CLASS
INSERT 0 15
COMMIT
insert data into NODE_TYPE
INSERT 0 3
COMMIT
insert data into REGION
INSERT 0 15
COMMIT
insert data into TASK_TYPE
INSERT 0 5
COMMIT
## second level ##
insert data into AREA
INSERT 0 16
COMMIT
insert data into DISTANCE⠒V
INSERT 0 3
COMMIT
insert data into GOOD⠒V
INSERT 0 164
COMMIT
insert data into MAP⠒V
INSERT 0 41
COMMIT
## third level ##
insert data into DIRECT_NEIGHBOUR
INSERT 0 8
INSERT 0 16
COMMIT
### Scandinavia ###
insert data into NODE⠒V
INSERT 0 112
COMMIT
insert data into PRODUCTION⠒V
INSERT 0 11
COMMIT
insert data into TASK⠒V
INSERT 0 56
COMMIT
## forth level ##
Scandinavia
insert data into DROP_OFF⠒V
INSERT 0 91
COMMIT
insert data into PICK_UP⠒V
INSERT 0 73
COMMIT
insert data into TASK_DEPENDENCY⠒V
INSERT 0 34
COMMIT
  count
-------
     66
(1 row)

  count
-------
      0
(1 row)

COMMIT

Re: Empty materialized view

From

Adrian Klaver

Date:

25 March, 03:24:25

On 3/24/24 14:27, Thiemo Kellner wrote:

>>> Feeling quite dumb now. But then, there neither is data visible in 
>>> the install session.
>>>
>>> insert data into TASK_DEPENDENCY⠒V
>>> INSERT 0 34
>>
>> The above says the data was inserted.
> 
> But not into the MV but into TASK_DEPENDENCY⠒V.
> 
>> Where and when was the count query run?
> 

> 
> Excerpt of the according 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

At above you have not entered the data into the tables the MV depends on 
so SELECT 0 is reasonable.

> # insert data #
> ## first level ##
> insert data into CENTRICITY
> INSERT 0 2
> COMMIT
> insert data into DIRECTION
> INSERT 0 8
> COMMIT
> insert data into GOOD_CLASS
> INSERT 0 15
> COMMIT
> insert data into NODE_TYPE
> INSERT 0 3
> COMMIT
> insert data into REGION
> INSERT 0 15
> COMMIT
> insert data into TASK_TYPE
> INSERT 0 5
> COMMIT
> ## second level ##
> insert data into AREA
> INSERT 0 16
> COMMIT
> insert data into DISTANCE⠒V
> INSERT 0 3
> COMMIT
> insert data into GOOD⠒V
> INSERT 0 164
> COMMIT
> insert data into MAP⠒V
> INSERT 0 41
> COMMIT
> ## third level ##
> insert data into DIRECT_NEIGHBOUR
> INSERT 0 8
> INSERT 0 16
> COMMIT
> ### Scandinavia ###
> insert data into NODE⠒V
> INSERT 0 112
> COMMIT
> insert data into PRODUCTION⠒V
> INSERT 0 11
> COMMIT
> insert data into TASK⠒V
> INSERT 0 56
> COMMIT
> ## forth level ##
> Scandinavia
> insert data into DROP_OFF⠒V
> INSERT 0 91
> COMMIT
> insert data into PICK_UP⠒V
> INSERT 0 73
> COMMIT
> insert data into TASK_DEPENDENCY⠒V
> INSERT 0 34
> COMMIT
>   count
> -------
>      66
> (1 row)
> 
>   count
> -------
>       0
> (1 row)

The 0 count above represents the below correct? :

select count(*) from SNOWRUNNER.QUERY_PER_TASK⠒MV;

If so, again that is reasonable as I don't see anywhere you refresh 
QUERY_PER_TASK⠒MV after the underlying tables have data entered. At this 
point it is still at the state you left it at here:

## 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

> 
> COMMIT
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Empty materialized view

From

Thiemo Kellner

Date:

25 March, 12:41:41

My bad as always. Having the refresh after the filling of the tables 
does the trick. Thanks for your help.