how to prevent out of shared memory error when refreshing materialized views

Question

Postgresql14, Windows server 2012

I get an out of shared memory related to a insufficient max_locks_per_transaction.

I have two tables:

tbl1 has insert/update every night.
These actions trigger a fonction that updates tbl2 (28 000 rows)
These updates on tbl2 trigger a function that refresh 8 materialized views.

It seems that there are too many locks to complete the refreshs because at a moment I read in the log : Then the updates are aborted UPDATE "...ERROR: 25P02: current transaction is aborted, commands ignored until end of transaction block.

It seems that I have to increase the number of max_lock_per_transition. The number could be 28 000x8 (did i understand the doc?).
My max_connection is 80 and max_locks_per_transaction 64 (default) so 5120 (80*64) locks.

Should I set max_lock_per_transition to 2800? Is it reasonable? or there are others solutions?

EDIT : as suggested by Laurenz Albe, I add some informations.

I update manually one row in tbl1 at 9:58
copy and paste log between 9:58 and 10:03 supposed time of the end of the supposed mat views refreshs.
during these 2 minutes, I took pictures of pg_locks table

log :

2023-02-16 09:58:09.413 CET [4824] 57 db=activite,user=POSTGIS LOG:  00000: temporary file: path "base/pgsql_tmp/pgsql_tmp4824.5.sharedfileset/0.0", size 8192
2023-02-16 09:58:09.413 CET [4824] 58 db=activite,user=POSTGIS CONTEXT:  SQL statement "refresh materialized view ge.prescription"
    PL/pgSQL function activite.maj_vm_prescription() line 13 at EXECUTE
2023-02-16 09:58:09.413 CET [4824] 59 db=activite,user=POSTGIS LOCATION:  ReportTemporaryFileUsage, fd.c:1448
2023-02-16 09:58:09.413 CET [4824] 60 db=activite,user=POSTGIS STATEMENT:  UPDATE activite.prescription SET
    numoa = '558'::character varying WHERE
    gid = 77833;
2023-02-16 09:58:09.420 CET [4824] 61 db=activite,user=POSTGIS LOG:  00000: temporary file: path "base/pgsql_tmp/pgsql_tmp4824.5.sharedfileset/1.0", size 8192
2023-02-16 09:58:09.420 CET [4824] 62 db=activite,user=POSTGIS CONTEXT:  SQL statement "refresh materialized view ge.prescription"
    PL/pgSQL function activite.maj_vm_prescription() line 13 at EXECUTE
2023-02-16 09:58:09.420 CET [4824] 63 db=activite,user=POSTGIS LOCATION:  ReportTemporaryFileUsage, fd.c:1448
2023-02-16 09:58:09.420 CET [4824] 64 db=activite,user=POSTGIS STATEMENT:  UPDATE activite.prescription SET
    numoa = '558'::character varying WHERE
    gid = 77833;
2023-02-16 09:58:09.431 CET [4824] 65 db=activite,user=POSTGIS LOG:  00000: temporary file: path "base/pgsql_tmp/pgsql_tmp4824.4.sharedfileset/0.0", size 139264
2023-02-16 09:58:09.431 CET [4824] 66 db=activite,user=POSTGIS CONTEXT:  SQL statement "refresh materialized view ge.prescription"
    PL/pgSQL function activite.maj_vm_prescription() line 13 at EXECUTE
2023-02-16 09:58:09.431 CET [4824] 67 db=activite,user=POSTGIS LOCATION:  ReportTemporaryFileUsage, fd.c:1448
2023-02-16 09:58:09.431 CET [4824] 68 db=activite,user=POSTGIS STATEMENT:  UPDATE activite.prescription SET
    numoa = '558'::character varying WHERE
    gid = 77833;
2023-02-16 09:58:09.438 CET [4824] 69 db=activite,user=POSTGIS LOG:  00000: temporary file: path "base/pgsql_tmp/pgsql_tmp4824.4.sharedfileset/1.0", size 8192
2023-02-16 09:58:09.438 CET [4824] 70 db=activite,user=POSTGIS CONTEXT:  SQL statement "refresh materialized view ge.prescription"
    PL/pgSQL function activite.maj_vm_prescription() line 13 at EXECUTE
2023-02-16 09:58:09.438 CET [4824] 71 db=activite,user=POSTGIS LOCATION:  ReportTemporaryFileUsage, fd.c:1448
2023-02-16 09:58:09.438 CET [4824] 72 db=activite,user=POSTGIS STATEMENT:  UPDATE activite.prescription SET
    numoa = '558'::character varying WHERE
    gid = 77833;
2023-02-16 09:58:10.816 CET [4824] 73 db=activite,user=POSTGIS LOG:  00000: duration: 3310.880 ms  statement: UPDATE activite.prescription SET
    numoa = '558'::character varying WHERE
    gid = 77833;
2023-02-16 09:58:10.816 CET [4824] 74 db=activite,user=POSTGIS LOCATION:  exec_simple_query, postgres.c:1306
2023-02-16 09:58:26.166 CET [4148] 1 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_catalog.pg_attribute": index scans: 1
    pages: 0 removed, 230 remain, 0 skipped due to pins, 53 skipped frozen
    tuples: 935 removed, 8707 remain, 0 are dead but not yet removable, oldest xmin: 199363
    index scan needed: 88 pages from table (38.26% of total) had 2111 dead item identifiers removed
    index "pg_attribute_relid_attnam_index": pages: 95 in total, 6 newly deleted, 30 currently deleted, 24 reusable
    index "pg_attribute_relid_attnum_index": pages: 65 in total, 3 newly deleted, 19 currently deleted, 16 reusable
    avg read rate: 8.772 MB/s, avg write rate: 3.869 MB/s
    buffer usage: 429 hits, 297 misses, 131 dirtied
    WAL usage: 304 records, 103 full page images, 451652 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.26 s
2023-02-16 09:58:26.166 CET [4148] 2 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:26.253 CET [4148] 3 db=,user= LOG:  00000: automatic analyze of table "activite.pg_catalog.pg_attribute"
    avg read rate: 14.907 MB/s, avg write rate: 1.257 MB/s
    buffer usage: 1020 hits, 166 misses, 14 dirtied
    system usage: CPU: user: 0.03 s, system: 0.00 s, elapsed: 0.08 s
2023-02-16 09:58:26.253 CET [4148] 4 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:26.322 CET [4148] 5 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_catalog.pg_class": index scans: 1
    pages: 0 removed, 33 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 59 removed, 1044 remain, 0 are dead but not yet removable, oldest xmin: 199364
    index scan needed: 26 pages from table (78.79% of total) had 252 dead item identifiers removed
    index "pg_class_oid_index": pages: 8 in total, 0 newly deleted, 0 currently deleted, 0 reusable
    index "pg_class_relname_nsp_index": pages: 17 in total, 0 newly deleted, 0 currently deleted, 0 reusable
    index "pg_class_tblspc_relfilenode_index": pages: 13 in total, 0 newly deleted, 3 currently deleted, 3 reusable
    avg read rate: 3.586 MB/s, avg write rate: 4.611 MB/s
    buffer usage: 153 hits, 21 misses, 27 dirtied
    WAL usage: 87 records, 21 full page images, 118807 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.04 s
2023-02-16 09:58:26.322 CET [4148] 6 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:26.349 CET [4148] 7 db=,user= LOG:  00000: automatic analyze of table "activite.pg_catalog.pg_class"
    avg read rate: 1.447 MB/s, avg write rate: 2.604 MB/s
    buffer usage: 505 hits, 5 misses, 9 dirtied
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
2023-02-16 09:58:26.349 CET [4148] 8 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:27.744 CET [4148] 9 db=,user= LOG:  00000: automatic vacuum of table "activite.ara.prescription": index scans: 0
    pages: 0 removed, 797 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 4898 remain, 0 are dead but not yet removable, oldest xmin: 199365
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 4.554 MB/s, avg write rate: 4.554 MB/s
    buffer usage: 860 hits, 801 misses, 801 dirtied
    WAL usage: 798 records, 1 full page images, 55465 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 1.37 s
2023-02-16 09:58:27.744 CET [4148] 10 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:28.745 CET [4148] 11 db=,user= LOG:  00000: automatic analyze of table "activite.ara.prescription"
    avg read rate: 6.352 MB/s, avg write rate: 0.273 MB/s
    buffer usage: 9109 hits, 813 misses, 35 dirtied
    system usage: CPU: user: 0.20 s, system: 0.01 s, elapsed: 0.99 s
2023-02-16 09:58:28.745 CET [4148] 12 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:30.166 CET [4148] 13 db=,user= LOG:  00000: automatic vacuum of table "activite.cif.prescription": index scans: 0
    pages: 0 removed, 805 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 4385 remain, 0 are dead but not yet removable, oldest xmin: 199366
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 2.641 MB/s, avg write rate: 4.768 MB/s
    buffer usage: 1196 hits, 448 misses, 809 dirtied
    WAL usage: 806 records, 1 full page images, 55937 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 1.32 s
2023-02-16 09:58:30.166 CET [4148] 14 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:31.163 CET [4148] 15 db=,user= LOG:  00000: automatic analyze of table "activite.cif.prescription"
    avg read rate: 3.498 MB/s, avg write rate: 0.196 MB/s
    buffer usage: 9905 hits, 446 misses, 25 dirtied
    system usage: CPU: user: 0.17 s, system: 0.00 s, elapsed: 0.99 s
2023-02-16 09:58:31.163 CET [4148] 16 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:32.889 CET [4148] 17 db=,user= LOG:  00000: automatic vacuum of table "activite.ge.prescription": index scans: 0
    pages: 0 removed, 1101 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 6563 remain, 0 are dead but not yet removable, oldest xmin: 199367
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 0.018 MB/s, avg write rate: 5.063 MB/s
    buffer usage: 2234 hits, 4 misses, 1105 dirtied
    WAL usage: 1102 records, 1 full page images, 73401 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 1.70 s
2023-02-16 09:58:32.889 CET [4148] 18 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:33.972 CET [4148] 19 db=,user= LOG:  00000: automatic analyze of table "activite.ge.prescription"
    avg read rate: 0.029 MB/s, avg write rate: 0.210 MB/s
    buffer usage: 10688 hits, 4 misses, 29 dirtied
    system usage: CPU: user: 0.28 s, system: 0.00 s, elapsed: 1.08 s
2023-02-16 09:58:33.972 CET [4148] 20 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:34.202 CET [4148] 21 db=,user= LOG:  00000: automatic vacuum of table "activite.go.prescription": index scans: 0
    pages: 0 removed, 404 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 2287 remain, 0 are dead but not yet removable, oldest xmin: 199370
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 0.154 MB/s, avg write rate: 4.042 MB/s
    buffer usage: 844 hits, 4 misses, 105 dirtied
    WAL usage: 405 records, 1 full page images, 32278 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.20 s
2023-02-16 09:58:34.202 CET [4148] 22 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:34.581 CET [4148] 23 db=,user= LOG:  00000: automatic analyze of table "activite.go.prescription"
    avg read rate: 0.021 MB/s, avg write rate: 0.373 MB/s
    buffer usage: 3970 hits, 1 misses, 18 dirtied
    system usage: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.37 s
2023-02-16 09:58:34.581 CET [4148] 24 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:35.201 CET [4148] 25 db=,user= LOG:  00000: automatic vacuum of table "activite.bfc.prescription": index scans: 0
    pages: 0 removed, 347 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 2118 remain, 0 are dead but not yet removable, oldest xmin: 199372
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 4.692 MB/s, avg write rate: 4.692 MB/s
    buffer usage: 379 hits, 351 misses, 351 dirtied
    WAL usage: 348 records, 1 full page images, 28915 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.58 s
2023-02-16 09:58:35.201 CET [4148] 26 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:35.603 CET [4148] 27 db=,user= LOG:  00000: automatic analyze of table "activite.bfc.prescription"
    avg read rate: 6.797 MB/s, avg write rate: 0.293 MB/s
    buffer usage: 3664 hits, 348 misses, 15 dirtied
    system usage: CPU: user: 0.07 s, system: 0.00 s, elapsed: 0.39 s
2023-02-16 09:58:35.603 CET [4148] 28 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:35.686 CET [4148] 29 db=,user= LOG:  00000: automatic vacuum of table "activite.hdf.prescription": index scans: 0
    pages: 0 removed, 381 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 1903 remain, 0 are dead but not yet removable, oldest xmin: 199375
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 0.509 MB/s, avg write rate: 0.509 MB/s
    buffer usage: 794 hits, 4 misses, 4 dirtied
    WAL usage: 382 records, 1 full page images, 30921 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.06 s
2023-02-16 09:58:35.686 CET [4148] 30 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:36.220 CET [4148] 31 db=,user= LOG:  00000: automatic analyze of table "activite.hdf.prescription"
    avg read rate: 0.029 MB/s, avg write rate: 0.322 MB/s
    buffer usage: 6119 hits, 2 misses, 22 dirtied
    system usage: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.53 s
2023-02-16 09:58:36.220 CET [4148] 32 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:36.343 CET [4148] 33 db=,user= LOG:  00000: automatic vacuum of table "activite.mm.prescription": index scans: 0
    pages: 0 removed, 623 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 3820 remain, 0 are dead but not yet removable, oldest xmin: 199376
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 0.338 MB/s, avg write rate: 0.338 MB/s
    buffer usage: 1279 hits, 4 misses, 4 dirtied
    WAL usage: 624 records, 1 full page images, 45199 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.09 s
2023-02-16 09:58:36.343 CET [4148] 34 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:36.987 CET [4148] 35 db=,user= LOG:  00000: automatic analyze of table "activite.mm.prescription"
    avg read rate: 0.037 MB/s, avg write rate: 0.243 MB/s
    buffer usage: 6424 hits, 3 misses, 20 dirtied
    system usage: CPU: user: 0.15 s, system: 0.01 s, elapsed: 0.64 s
2023-02-16 09:58:36.987 CET [4148] 36 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:37.248 CET [4148] 37 db=,user= LOG:  00000: automatic analyze of table "activite.naom.prescription"
    avg read rate: 0.101 MB/s, avg write rate: 0.572 MB/s
    buffer usage: 2800 hits, 3 misses, 17 dirtied
    system usage: CPU: user: 0.04 s, system: 0.01 s, elapsed: 0.23 s
2023-02-16 09:58:37.248 CET [4148] 38 db=,user= LOCATION:  do_analyze_rel, analyze.c:826
2023-02-16 09:58:37.295 CET [4148] 39 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5935083": index scans: 0
    pages: 0 removed, 223 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 3348 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 0.757 MB/s, avg write rate: 1.262 MB/s
    buffer usage: 450 hits, 3 misses, 5 dirtied
    WAL usage: 224 records, 1 full page images, 21542 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.03 s
2023-02-16 09:58:37.295 CET [4148] 40 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:37.326 CET [4148] 41 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5937734": index scans: 0
    pages: 0 removed, 80 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 1259 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 1.578 MB/s, avg write rate: 2.105 MB/s
    buffer usage: 164 hits, 3 misses, 4 dirtied
    WAL usage: 81 records, 1 full page images, 13105 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2023-02-16 09:58:37.326 CET [4148] 42 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:37.383 CET [4148] 43 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5944587": index scans: 0
    pages: 0 removed, 131 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 3231 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 1.555 MB/s, avg write rate: 2.074 MB/s
    buffer usage: 266 hits, 3 misses, 4 dirtied
    WAL usage: 132 records, 1 full page images, 16114 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2023-02-16 09:58:37.383 CET [4148] 44 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:37.429 CET [4148] 45 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5947500": index scans: 0
    pages: 0 removed, 210 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 3513 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 0.786 MB/s, avg write rate: 1.048 MB/s
    buffer usage: 424 hits, 3 misses, 4 dirtied
    WAL usage: 211 records, 1 full page images, 20775 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.02 s
2023-02-16 09:58:37.429 CET [4148] 46 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:37.448 CET [4148] 47 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5950402": index scans: 0
    pages: 0 removed, 26 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 1012 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 13.014 MB/s, avg write rate: 17.351 MB/s
    buffer usage: 56 hits, 3 misses, 4 dirtied
    WAL usage: 27 records, 1 full page images, 9919 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-02-16 09:58:37.448 CET [4148] 48 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:37.462 CET [4148] 49 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5951379": index scans: 0
    pages: 0 removed, 28 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 1714 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 19.306 MB/s, avg write rate: 25.741 MB/s
    buffer usage: 60 hits, 3 misses, 4 dirtied
    WAL usage: 29 records, 1 full page images, 10037 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-02-16 09:58:37.462 CET [4148] 50 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:37.479 CET [4148] 51 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_5953084": index scans: 0
    pages: 0 removed, 69 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 0 removed, 1881 remain, 0 are dead but not yet removable, oldest xmin: 199378
    index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
    avg read rate: 10.435 MB/s, avg write rate: 13.914 MB/s
    buffer usage: 142 hits, 3 misses, 4 dirtied
    WAL usage: 70 records, 1 full page images, 12456 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2023-02-16 09:58:37.479 CET [4148] 52 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:58:58.912 CET [1260] 77 db=,user= LOG:  00000: checkpoint starting: time
2023-02-16 09:58:58.912 CET [1260] 78 db=,user= LOCATION:  LogCheckpointStart, xlog.c:8782
2023-02-16 09:59:26.107 CET [2388] 1 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_catalog.pg_statistic": index scans: 1
    pages: 0 removed, 193 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 264 removed, 1419 remain, 0 are dead but not yet removable, oldest xmin: 199383
    index scan needed: 64 pages from table (33.16% of total) had 134 dead item identifiers removed
    index "pg_statistic_relid_att_inh_index": pages: 13 in total, 0 newly deleted, 0 currently deleted, 0 reusable
    avg read rate: 3.215 MB/s, avg write rate: 4.649 MB/s
    buffer usage: 408 hits, 83 misses, 120 dirtied
    WAL usage: 286 records, 113 full page images, 478661 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.20 s
2023-02-16 09:59:26.107 CET [2388] 2 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 09:59:26.451 CET [2388] 3 db=,user= LOG:  00000: automatic vacuum of table "activite.pg_toast.pg_toast_2619": index scans: 1
    pages: 0 removed, 395 remain, 0 skipped due to pins, 0 skipped frozen
    tuples: 326 removed, 1278 remain, 0 are dead but not yet removable, oldest xmin: 199383
    index scan needed: 93 pages from table (23.54% of total) had 331 dead item identifiers removed
    index "pg_toast_2619_index": pages: 14 in total, 2 newly deleted, 5 currently deleted, 3 reusable
    avg read rate: 3.575 MB/s, avg write rate: 4.267 MB/s
    buffer usage: 636 hits, 150 misses, 179 dirtied
    WAL usage: 365 records, 100 full page images, 154020 bytes
    system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.32 s
2023-02-16 09:59:26.451 CET [2388] 4 db=,user= LOCATION:  heap_vacuum_rel, vacuumlazy.c:864
2023-02-16 10:03:28.196 CET [1260] 79 db=,user= LOG:  00000: checkpoint complete: wrote 4483 buffers (27.4%); 0 WAL file(s) added, 3 removed, 0 recycled; write=269.162 s, sync=0.088 s, total=269.284 s; sync files=144, longest=0.007 s, average=0.001 s; distance=46917 kB, estimate=46917 kB
2023-02-16 10:03:28.196 CET [1260] 80 db=,user= LOCATION:  LogCheckpointEnd, xlog.c:8871

I was expected a refresh of the 8 mat views but only see 4 refreshs of one mat view (refresh materialized view ge.prescription)...weird.

I see the others mat views but only for vaccum.

Here are some pg_locks pictures I hope are relevant :

At no time did I see a number of relation field equals to a mat view.

Sahap Asci · Accepted Answer · 2023-02-16 09:50:36Z

0

Row locks are not (permanently) stored in the lock table, so that is not a problem. When it comes to the tables or materialized views, you don't need more that one lock table entry per table affected.

I can imagine two causes for this:

You have a partitioned table with many partitions, all of which end up in the lock table. Then increasing max_locks_per_transaction is really what you have to do. But you certainly don't have to use large numbers; certainly nothing that exceeds the number of tables and partitions.
You are running out of shared memory because you are using parallel query, and the segments created for data exchange are too big. That should not happen with DML statements (they don't use parallel query), but a simple remedy would be to disable parallel query by setting max_parallel_workers_per_gather = 0.

edited Feb 16, 2023 at 9:50

Sahap Asci

3,0441 gold badge15 silver badges28 bronze badges

answered Feb 15, 2023 at 15:29

Laurenz Albe

54.9k4 gold badges46 silver badges79 bronze badges

There is no partitioned tables in the database. I'll try your point 2 and come back.
– Leehan
Commented Feb 15, 2023 at 17:56
This morning, same error in the log after max_parallel_workers_per_gather = 0 and service Postgresql reboot. I am not in charge of it but I guess that the tbl1 (90k rows) is cleared and repopulate each night. And for each insert there is a refresh of the 8 materialized views. Maybe that's why I only see in the log the refresh of one materialized view, the start on another one then the error? All these refreshs would be too heavy to be done?
– Leehan
Commented Feb 16, 2023 at 8:02
It would be good to get the contents of pg_locks right when the problem happens. Please add that to the question.
– Laurenz Albe
Commented Feb 16, 2023 at 8:24
Edited and completed the first message as best I can.
– Leehan
Commented Feb 16, 2023 at 9:43
Well, those images only show a handful of locks, so that cannot be the problem. Are you using an older version of PostgreSQL?
– Laurenz Albe
Commented Feb 16, 2023 at 10:37

| Show 5 more comments

Stack Exchange Network

how to prevent out of shared memory error when refreshing materialized views

1 Answer 1

Your Answer

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

Hot Network Questions

how to prevent out of shared memory error when refreshing materialized views

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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