From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Thiemo Kellner <thiemo(at)gelassene-pferde(dot)biz>, "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Empty materialized view |
Date: | 2024-03-25 00:24:25 |
Message-ID: | be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2024-03-25 06:52:51 | Re: Not able to purge partition |
Previous Message | Thiemo Kellner | 2024-03-24 21:27:45 | Re: Empty materialized view |