From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Thiemo Kellner <thiemo@gelassene-pferde.biz>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Empty materialized view
Date: Sun, 24 Mar 2024 17:24:25 -0700
Message-ID: <be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com> (raw)
In-Reply-To: <a5a1465b-180e-4d30-825d-71a46b8df14d@gelassene-pferde.biz>
References: <33aa6b8e-86fc-4f10-9242-e0c9ad39c007@gelassene-pferde.biz>
<js3tqo6rnk7wttg2kinjvwto5a3gwi4watqokqx6em633thq5b@xe4hvo5qapsl>
<2ec1a2bd-7791-4cc5-90ed-1b7b606aa420@gelassene-pferde.biz>
<61e2c2b7-1098-4179-91a2-24e06e0683a9@aklaver.com>
<ef0db0e6-4abe-44de-aabb-5255c4624bbf@gelassene-pferde.biz>
<d77ae41c-0627-4999-b120-8b611af3d73f@aklaver.com>
<983c4ea7-4861-436a-a23e-f40780c9f0d6@gelassene-pferde.biz>
<ed2bdabc-8fb4-4c70-b270-f169fee343ce@aklaver.com>
<a5a1465b-180e-4d30-825d-71a46b8df14d@gelassene-pferde.biz>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, thiemo@gelassene-pferde.biz
Subject: Re: Empty materialized view
In-Reply-To: <be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com>
References: <33aa6b8e-86fc-4f10-9242-e0c9ad39c007@gelassene-pferde.biz> <js3tqo6rnk7wttg2kinjvwto5a3gwi4watqokqx6em633thq5b@xe4hvo5qapsl> <2ec1a2bd-7791-4cc5-90ed-1b7b606aa420@gelassene-pferde.biz> <61e2c2b7-1098-4179-91a2-24e06e0683a9@aklaver.com> <ef0db0e6-4abe-44de-aabb-5255c4624bbf@gelassene-pferde.biz> <d77ae41c-0627-4999-b120-8b611af3d73f@aklaver.com> <983c4ea7-4861-436a-a23e-f40780c9f0d6@gelassene-pferde.biz> <ed2bdabc-8fb4-4c70-b270-f169fee343ce@aklaver.com> <a5a1465b-180e-4d30-825d-71a46b8df14d@gelassene-pferde.biz> <be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox