0043527: Dbsourcemanager MaterializedView support: if MV depends on other MV order of execution can be wrong

Description

I have a MV which definition has a select statement on another MV (see [1]). Postgresql requires that the referenced MV already exists and will throw an error [2].

[1]
SELECT r_1.obrtmd_org_id,
            r_1.year,
            r_1.month,
            r_1.day,
            r_1.dateordered,
            sum(r_1.ticket_cnt) AS ticket_cnt,
            sum(r_1.line_gross_amount) AS line_gross_amount,
            sum(r_1.qtyordered) AS qty
           FROM obrts_order_stats_pr_dt_mv r_1

[2]
[createreportingdatabase] 4965 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: relation "obrts_order_stats_pr_dt_mv" does not exist
[createreportingdatabase] Position: 1194

Steps To Reproduce

Create a MV which depends another MV and then dbsourcemanager

Proposed Solution

Use module dependencies to find the order of MV creation. This is however not always sufficient. Other idea (given by AL) to ignore exceptions and just repeat failed MV creation in a loop until all MV are created or no remaining MV succeeds.

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

related to

feature request

0042042

3.0PR20Q1

closed

AugustoMauch

Add support of materialized views

Notes
(0118861) alostale (manager) 2020-03-30 08:38	MR: https://gitlab.com/openbravo/product/dbsm/-/merge_requests/8 [^]

(0119112) hgbot (developer) 2020-04-09 10:25	Repository: erp/devel/pi Changeset: 5e3802cd030a0a27b3d2bfb6c050bcc8bf375bea Author: Asier Lostalé <asier.lostale <at> openbravo.com> Date: Thu Apr 09 10:24:45 2020 +0200 URL: http://code.openbravo.com/erp/devel/pi/rev/5e3802cd030a0a27b3d2bfb6c050bcc8bf375bea [^] fixed BUG-43527: creation of materialized view depedending on another MV fails Creation of materialized views depending on another materialized view failed in case the dependency was not previously created. Treating materialized views in the same way regular views are treated adding FORCE = TRUE option so that it retries failed executions. --- M src-db/database/lib/dbsourcemanager.jar ---

(0119214) caristu (developer) 2020-04-16 11:26	Reviewed + tested OK

Issue History
Date Modified	Username	Field	Change
2020-03-19 21:07	mtaal	New Issue
2020-03-19 21:07	mtaal	Assigned To	=> alostale
2020-03-19 21:07	mtaal	Modules	=> Core
2020-03-19 21:07	mtaal	Triggers an Emergency Pack	=> No
2020-03-19 21:07	mtaal	Issue Monitored: mtaal
2020-03-30 08:38	alostale	Note Added: 0118861
2020-04-09 10:25	hgbot	Checkin
2020-04-09 10:25	hgbot	Note Added: 0119112
2020-04-09 10:25	hgbot	Status	new => resolved
2020-04-09 10:25	hgbot	Resolution	open => fixed
2020-04-09 10:25	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/5e3802cd030a0a27b3d2bfb6c050bcc8bf375bea [^]
2020-04-10 10:12	alostale	Review Assigned To	=> caristu
2020-04-16 11:26	caristu	Note Added: 0119214
2020-04-16 11:26	caristu	Status	resolved => closed
2020-04-16 11:26	caristu	Fixed in Version	=> 3.0PR20Q3
2021-09-21 14:35	alostale	Relationship added	related to 0042042