Обсуждение: Empty materialized view
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
Вложения
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
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
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.
Вложения
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
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;
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
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.
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
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
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
My bad as always. Having the refresh after the filling of the tables does the trick. Thanks for your help.