Re: Empty materialized view - Mailing list pgsql-general

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