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
