Follow

Simultaneous 'CREATE OR REPLACE MATERIALIZED VIEW" commands on same view cause both commands to return errors

Incident Synopsis:  

 

Simultaneous 'CREATE OR REPLACE MATERIALIZED VIEW" commands on same view cause both commands to return errors

 

Problem/Question:

 

Execute query below (twice in separate sessions) at the same time:

 

CREATE OR REPLACE MATERIALIZED VIEW <mv_name> as SELECT * FROM <table_name>

 

CREATE OR REPLACE MATERIALIZED VIEW <mv_name> as SELECT * FROM <table_name>

 

 

Problem Detail:

 

When executing the identical MV create statements (above) twice GPUdb throws the following error and both MV’s fail to create.

  ---------------------------

[GPUdb]executeSql: Error: 'All requests threw errors,  first error: Received error: Missing table:  'Exchange_5_a345f236-4143-4e0f-8dba-7d95072a8a7f' (TM/CSGMc:125)  (S/SDc:1175); code:1 'Error' in Job process'

 

[GPUdb]executeSql:  Error: 'All requests threw errors, first error: Received error: Missing  table: 'Exchange_5_9f7d1f8a-1c47-4084-b75e-4142ccebb229' (TM/CSGMc:125)  (S/SDc:1175); code:1 'Error' in Job process'

 

Environment :

 

Kinetica on-prem 7.0.17.0

 

Cause :

 

GPUdb query planning logic did not allow identical MV’s to be created simultaneously.

 

Solution/Answer :

 

Re-architect your database design as it is inefficient to create two identical MV’s at the same time.

 

Post 7.0.17.3, instead of failing both MV creations we serialize the creation of both views.

 

Special Considerations :

 

Dual identical MV’s seems like a waste and can be optimized depending on the need in diff ways.

 

https://www.kinetica.com/docs/concepts/materialized_views.html

  

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.