pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (12+ messages)  latest in thread

Message-ID: <be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com>
Permalink:  ../be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com/
Also on:    postgresql.org/message-id/be71e324-69bd-4c17-945c-74b9319f4fa0@aklaver.com

 · 

reply

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