Re: Empty materialized view - Mailing list pgsql-general

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