postgresql : conflict with simultaneous "create materialized view"

All we need is an easy explanation of the problem, so here it is.

This is a continuation on my old question Multiple concurrent "REFRESH MATERIALIZED VIEW": how to manage?

Now I’m unfortunately struggling with the real thing after having been more theoretical initially 🙂

I have a program which sends many requests, and finally refresh a materialized view. Unfortunately at times, two concurrent jobs request overlapping CREATE MATERIALIZED VIEW milestones_files (the view is pretty big and takes several minutes to be refreshed), so the latest one fails (obviously), which cancels the whole transaction (which is bad, because some database updates are dropped too).

Here is the log when the error occurs (without all of the SQL lines for brevity):

2022-03-22 11:17:37.685 UTC [29004] [email protected] ERREUR:  Bloquage mortel dĂ©tectĂ©
2022-03-22 11:17:37.685 UTC [29004] [email protected] DÉTAIL:  Le processus 29004 attend AccessExclusiveLock sur relation 181019 de la base de donnĂ©es 19044 ; bloquĂ© par le processus 29174.
2022-03-22 11:17:37.685 UTC [29004] [email protected] ASTUCE :  Voir les journaux applicatifs du serveur pour les dĂ©tails sur la requĂŞte.
2022-03-22 11:17:37.685 UTC [29004] [email protected] INSTRUCTION :  CREATE MATERIALIZED VIEW milestones_files_29000 AS SELECT sa.id AS archive,
2022-03-22 11:17:37.691 UTC [29243] [email protected] ERREUR:  could not open relation with OID 181019
2022-03-22 11:17:37.691 UTC [29243] [email protected] INSTRUCTION :  SELECT REGEXP_REPLACE(pg_catalog.pg_get_ruledef(r.oid, TRUE), '^.*DO INSTEAD  (SELECT.*);$', '\1') FROM pg_catalog.pg_rewrite r WHERE r.ev_class = ( SELECT oid FROM pg_class WHERE relname = 'milestones_files')
2022-03-22 11:17:37.693 UTC [29243] [email protected] ERREUR:  la transaction est annulĂ©e, les commandes sont ignorĂ©es jusqu'Ă  la fin du bloc
2022-03-22 11:17:37.693 UTC [29243] [email protected] INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:37.696 UTC [29243] [email protected] ERREUR:  l'instruction prĂ©parĂ©e « update_archivemirrors » n'existe pas
2022-03-22 11:17:39.510 UTC [29004] [email protected] ERREUR:  la transaction est annulĂ©e, les commandes sont ignorĂ©es jusqu'Ă  la fin du bloc
2022-03-22 11:17:39.510 UTC [29004] [email protected] INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:39.510 UTC [29004] [email protected] ERREUR:  l'instruction prĂ©parĂ©e « update_archivemirrors » n'existe pas

Of course I could simply put the CREATE MATERIALIZED VIEW command outside the main transaction, however the latest refresh would still fail, therefore the view wouldn’t reflect the latest changes in the database, which is still a (less serious) problem.

Ideally I’d want the latest refresh to take precedence, but without failing the transaction already running in the other process (which would be the case if I simply kill the other process after checking for locks, as per Kill concurrent materialized view refresh PID ).

From the documentation, it doesn’t seem that adding the CONCURRENTLY option would solve my problem either (which isn’t about concurrent SELECTs).

Any suggestion welcome. Of course I could try to implement the solution proposed in my old question, though it would be particularly complex given the cascading queries and transactions and the huge database…

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You could write a procedure for that:

CREATE PROCEDURE some_name(...)
   LANGUAGE plpgsql AS
$$BEGIN
   /* do all the inserts */
   COMMIT;
   REFRESH MATERIALIZED VIEW ...;
END;$$;

The same can of course also be written using client code. This may lock, but never deadlock, and you can be certain that the materialized view will reflect the inserts when the procedure has completed.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply