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.
\echo Set materialised view QUERY_PER_TASK⠒MV up
drop materialized view if exists QUERY_PER_TASK⠒MV;
create materialized view QUERY_PER_TASK⠒MV as
select TDP.TOP_LEVEL_TASK_TYPE⠒NAME,
TDP.TOP_LEVEL_TASK⠒NAME,
TDP.TASK_TREE⠒HIGHEST_PRIORITY,
TDP.TASK⠒NAME,
TDP.TASK_TYPE⠒NAME,
TDP.TASK⠒PRIORITY,
TDP.TASK⠒DESCRIPTION,
TDP.GOOD⠒NAME,
TDP.GOOD_4_THIS_TASK,
TDP.QUANTITY⠒DROP_OFF,
TDP.QUANTITY⠒NEEDED_4_THIS_REGION,
TDP.QUANTITY⠒NEEDED_4_THIS_TASK_TREE,
TDP.QUANTITY⠒PICK_UP,
TDP.QUANTITY⠒READY_4_THIS_REGION,
TDP.GOOD⠒SIZE,
TDP.GOOD⠒SIZE * TDP.QUANTITY⠒DROP_OFF
as NEEDED_TRANSPORT_CAPACITY_4_THIS_TASK,
TDP.NEEDED_TRANSPORT_CAPACITY_4_THIS_REGION,
TDP.NEEDED_TRANSPORT_CAPACITY_4_THIS_TASK_TREE,
case TDP.NODE_TYPE⠒NAME⠒PICK_UP
when 'Crafting zone' then R.GOOD⠒RESOURCE⠒NAME
else null
end as GOOD⠒RESOURCE⠒NAME,
P.QUANTITY⠒READY_4_THIS_REGION
as RESOURCE_QUANTITY⠒READY_4_THIS_REGION,
TDP.NODE⠒MAP⠒NAME⠒DROP_OFF,
TDP.NODE⠒NAME⠒DROP_OFF,
TDP.NODE⠒MAP⠒NAME⠒PICK_UP,
TDP.NODE⠒NAME⠒PICK_UP,
P.NODE⠒MAP⠒NAME as NODE⠒MAP⠒NAME⠒PICK_UP_RESOURCE,
P.NODE⠒NAME as NODE⠒NAME⠒PICK_UP_RESOURCE,
TDP.DIRECTION⠒CODE⠒DROP_OFF,
TDP.CENTRICITY⠒NAME⠒DROP_OFF,
TDP.DIRECTION⠒CODE⠒PICK_UP,
TDP.CENTRICITY⠒NAME⠒PICK_UP,
TDP.NODE⠒DESCRIPTION⠒DROP_OFF,
TDP.PICK_UP⠒DESCRIPTION,
TDP.NODE⠒DESCRIPTION⠒PICK_UP,
TDP.DIRECTION⠒DESCRIPTION⠒DROP_OFF,
TDP.CENTRICITY⠒DESCRIPTION⠒DROP_OFF,
TDP.DIRECTION⠒DESCRIPTION⠒PICK_UP,
TDP.CENTRICITY⠒DESCRIPTION⠒PICK_UP,
P.DIRECTION⠒DESCRIPTION
as DIRECTION⠒DESCRIPTION⠒PICK_UP_RESOURCE,
P.CENTRICITY⠒DESCRIPTION
as
CENTRICITY⠒DESCRIPTION⠒PICK_UP_RESOURCE,
TDP.GOOD⠒DESCRIPTION,
TDP.GOOD⠒WEIGHT,
TDP.PATH,
TDP.REGION⠒NAME,
TDP.TOP_LEVEL_TASK⠒PRIORITY,
TDP.REGION⠒ID,
TDP.TASK_PREDECESSOR⠒ID,
TDP.TASK⠒ID,
TDP.LEVEL,
TDP.GOOD⠒ID
from SNOWRUNNER.TASK_HIERARCHY_DROP_OFF_PICK_UP_AVAIL⠒V TDP
left outer join SNOWRUNNER.PRODUCTION⠒V R
on 1 = 1
and TDP.GOOD⠒ID = R.GOOD⠒PRODUCT⠒ID
and TDP.REGION⠒ID = R.REGION⠒ID
left outer join SNOWRUNNER.PICK_UP⠒V P
on 1 = 1
and R.GOOD⠒RESOURCE⠒ID = P.GOOD⠒ID
and R.REGION⠒ID = P.REGION⠒ID
where 1 = 1
order by TDP.REGION⠒NAME asc,
TASK_TREE⠒HIGHEST_PRIORITY asc,
NODE⠒MAP⠒NAME⠒DROP_OFF asc,
NODE⠒MAP⠒NAME⠒PICK_UP asc,
NODE⠒MAP⠒NAME⠒PICK_UP asc,
NODE⠒MAP⠒NAME⠒PICK_UP_RESOURCE asc,
NODE⠒NAME⠒PICK_UP_RESOURCE asc,
GOOD⠒NAME asc,
NODE⠒MAP⠒NAME⠒DROP_OFF asc,
PATH asc
with data;
refresh materialized view QUERY_PER_TASK⠒MV
with data;
comment on materialized view QUERY_PER_TASK⠒MV is
'
$Header$';
commit;
