postgresql : conflict with simultaneous "create materialized view"

Question

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] storiq@storiqone ERREUR:  Bloquage mortel détecté
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone 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] storiq@storiqone ASTUCE :  Voir les journaux applicatifs du serveur pour les détails sur la requête.
2022-03-22 11:17:37.685 UTC [29004] storiq@storiqone INSTRUCTION :  CREATE MATERIALIZED VIEW milestones_files_29000 AS SELECT sa.id AS archive,
2022-03-22 11:17:37.691 UTC [29243] storiq@storiqone ERREUR:  could not open relation with OID 181019
2022-03-22 11:17:37.691 UTC [29243] storiq@storiqone 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] storiq@storiqone 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] storiq@storiqone INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:37.696 UTC [29243] storiq@storiqone ERREUR:  l'instruction préparée « update_archivemirrors » n'existe pas
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone 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] storiq@storiqone INSTRUCTION :  UPDATE archivetoarchivemirror SET lastupdate = NOW(), jobrun = $1 WHERE archive = $2 OR jobrun = $1
2022-03-22 11:17:39.510 UTC [29004] storiq@storiqone 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...

Answer 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.