0

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

2
  • 1
    If you refresh the materialized view whenever there is a data modification, you are doing something wrong. How about a regular table that is updated by a trigger? Mar 23, 2022 at 16:05
  • @LaurenzAlbe the materialized view is updated after a long series of INSERTs, but sometimes there are collisions between different jobs. Jobs typically last a few hours, but the refresh takes up to 15 minutes (several million rows in the view, typically up to 100000 added new lines for each job). I'm somewhat afraid of the number of code changes across several applications and languages that getting rid of the view implies :)
    – wazoox
    Mar 23, 2022 at 16:53

1 Answer 1

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.

1
  • This sounds promising actually, and not completely out of my league :)
    – wazoox
    Mar 24, 2022 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.