postgresql : conflict with simultaneous “create materialized view”

by

The 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] [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…

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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 Comment