Problem - Konfiguration oder Anwendung?

Tja gebracht hat es leider nichts.

Jan  9 14:22:21 xenpsql001 kernel: [ 8288.582783] postgres invoked oom-killer: gfp_mask=0x100cca(GFP_HIGHUSER_MOVABLE), order=0, oom_score_adj=0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582785] CPU: 3 PID: 3306 Comm: postgres Not tainted 5.10.0-19-amd64 #1 Debian 5.10.149-2
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582786] Hardware name: QEMU Standard PC (Q35 + ICH9, 2009), BIOS 1.14.0-2 04/01/2014
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582787] Call Trace:
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582797]  dump_stack+0x6b/0x83
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582799]  dump_header+0x4a/0x1f4
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582800]  oom_kill_process.cold+0xb/0x10
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582802]  out_of_memory+0x1bd/0x4e0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582804]  __alloc_pages_slowpath.constprop.0+0xbcc/0xc90
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582805]  __alloc_pages_nodemask+0x2de/0x310
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582806]  pagecache_get_page+0x175/0x390
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582807]  filemap_fault+0x6a2/0x900
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582816]  ext4_filemap_fault+0x2d/0x50 [ext4]
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582819]  __do_fault+0x37/0x170
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582820]  handle_mm_fault+0x1208/0x1c10
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582822]  do_user_addr_fault+0x1b8/0x400
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582824]  ? switch_fpu_return+0x44/0xc0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582826]  exc_page_fault+0x78/0x160
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582827]  ? asm_exc_page_fault+0x8/0x30
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582828]  asm_exc_page_fault+0x1e/0x30
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582830] RIP: 0033:0x55b840b676e0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582833] Code: Unable to access opcode bytes at RIP 0x55b840b676b6.
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582833] RSP: 002b:00007fff6dc1a708 EFLAGS: 00010246
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582834] RAX: 0000000000000000 RBX: 000055b8414b43a8 RCX: 0000000020000000
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582835] RDX: 000055b840f78c50 RSI: 00007fff6dc1a76c RDI: 000055b84148fe08
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582835] RBP: 00007fff6dc1a7e0 R08: 000055b840f78c60 R09: 0000000000000000
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582836] R10: 0000000000000008 R11: 0000000000000038 R12: 0000000000000000
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582836] R13: 0000000043794f88 R14: 00007f3fb7923600 R15: 00007fff6dc1a770
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582837] Mem-Info:
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582839] active_anon:4985288 inactive_anon:6960647 isolated_anon:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582839]  active_file:12 inactive_file:0 isolated_file:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582839]  unevictable:0 dirty:3 writeback:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582839]  slab_reclaimable:59458 slab_unreclaimable:10543
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582839]  mapped:3203056 shmem:3203728 pagetables:234879 bounce:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582839]  free:70635 free_pcp:986 free_cma:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582841] Node 0 active_anon:19941152kB inactive_anon:27842588kB active_file:48kB inactive_file:0kB unevictable:0kB isolated(anon):0kB isolated(file):0kB mapped:12812224kB dirty:12kB writeback:0kB shmem:12814912kB shmem_thp: 0kB shmem_pmdmapped: 0kB anon_thp: 2217984kB writeback_tmp:0kB kernel_stack:3616kB all_unreclaimable? no
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582841] Node 0 DMA free:15908kB min:20kB low:32kB high:44kB reserved_highatomic:0KB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB writepending:0kB present:15992kB managed:15908kB mlocked:0kB pagetables:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582843] lowmem_reserve[]: 0 1940 48143 48143 48143
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582844] Node 0 DMA32 free:187484kB min:2720kB low:4704kB high:6688kB reserved_highatomic:0KB active_anon:746896kB inactive_anon:1045428kB active_file:16kB inactive_file:84kB unevictable:0kB writepending:0kB present:2080624kB managed:2015088kB mlocked:0kB pagetables:30652kB bounce:0kB free_pcp:32kB local_pcp:4kB free_cma:0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582846] lowmem_reserve[]: 0 0 46203 46203 46203
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582847] Node 0 Normal free:79148kB min:79172kB low:126484kB high:173796kB reserved_highatomic:0KB active_anon:19194256kB inactive_anon:26797160kB active_file:20kB inactive_file:164kB unevictable:0kB writepending:12kB present:48234496kB managed:47317716kB mlocked:0kB pagetables:908864kB bounce:0kB free_pcp:3912kB local_pcp:1160kB free_cma:0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582849] lowmem_reserve[]: 0 0 0 0 0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582850] Node 0 DMA: 1*4kB (U) 0*8kB 0*16kB 1*32kB (U) 2*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (M) 3*4096kB (M) = 15908kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582855] Node 0 DMA32: 335*4kB (UME) 1603*8kB (UME) 2618*16kB (UME) 1335*32kB (UME) 755*64kB (UME) 142*128kB (UME) 54*256kB (UME) 13*512kB (UME) 2*1024kB (UM) 0*2048kB 0*4096kB = 187796kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582859] Node 0 Normal: 2146*4kB (UME) 1269*8kB (UME) 2255*16kB (UME) 610*32kB (UME) 71*64kB (UME) 4*128kB (M) 1*256kB (M) 1*512kB (M) 0*1024kB 0*2048kB 0*4096kB = 80160kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582864] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582864] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582865] 3223230 total pagecache pages
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582867] 19128 pages in swap cache
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582868] Swap cache stats: add 6528023, delete 6508893, find 152536/201375
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582868] Free swap  = 0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582868] Total swap = 25701372kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582869] 12582778 pages RAM
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582869] 0 pages HighMem/MovableOnly
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582869] 245600 pages reserved
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582869] 0 pages hwpoisoned
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582870] Tasks state (memory values in pages):
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582870] [  pid  ]   uid  tgid total_vm      rss pgtables_bytes swapents oom_score_adj name
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582873] [    292]     0   292    20275      220   180224        0          -250 systemd-journal
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582874] [    312]     0   312     5431      302    69632        0         -1000 systemd-udevd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582876] [    445]   109   445     1976      111    49152        0             0 rpcbind
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582876] [    447]   105   447     2065      113    57344        0          -900 dbus-daemon
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582877] [    452]     0   452    55234      619    77824       76             0 rsyslogd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582878] [    460]     0   460     3461      200    65536        0             0 systemd-logind
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582879] [    464]     0   464     1687       67    53248        0             0 cron
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582880] [    472]     0   472    24972      331    77824        0             0 dhclient
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582881] [    621]   103   621     4651      254    69632        0             0 exim4
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582882] [    714]     0   714      958       51    49152        0             0 ntpd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582883] [    715]   110   715      994       33    45056        0             0 ntpd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582884] [    716]   110   716     1011       56    49152        0             0 ntpd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582884] [    722]     0   722     2412       92    49152        0             0 xinetd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582885] [    729]   107   729  3237047    55714   667648      307          -900 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582886] [    731]   107   731  3237366  3181462 25673728      320             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582887] [    732]   107   732  3237086  3054589 25640960      322             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582888] [    733]   107   733  3237047     4351   167936      330             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582888] [    734]   107   734  3237230      427   176128      380             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582889] [    735]   107   735    17356      237   122880      325             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582890] [    736]   107   736  3237187      242   147456      376             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582891] [    748]     0   748     1462       29    45056        0             0 agetty
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582891] [    805]     0   805     3801      438    69632        5             0 sshd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582892] [    808]     0   808     3800      298    69632        0             0 systemd
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582893] [    809]     0   809    41751      665    98304        0             0 (sd-pam)
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582894] [    827]     0   827     1791        2    49152      138             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582894] [    831]     0   831     1715       24    45056       36             0 screen
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582895] [    832]     0   832     2475      598    53248      249             0 screen
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582896] [    833]     0   833     1791        1    53248      141             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582897] [    834]     0   834     1791        0    57344      141             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582897] [    835]     0   835     1791        0    57344      123             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582898] [    836]     0   836     1791        0    45056      141             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582899] [    837]     0   837     1791        0    53248      124             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582899] [    838]     0   838     1845       37    49152      119             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582900] [    839]     0   839     1791        1    49152      141             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582901] [    840]     0   840     1791        1    49152      141             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582902] [    842]     0   842     2586      154    61440       62             0 top
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582903] [    843]     0   843     2528        0    65536      156             0 su
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582904] [    844]   107   844     1791        1    53248      138             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582904] [    846]   107   846     4456      701    69632      151             0 pg_top
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582905] [    858]     0   858     1350       13    49152        3             0 tail
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582906] [    864]     0   864     1350        0    49152       19             0 tail
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582907] [    888]     0   888     2528        0    57344      148             0 su
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582907] [    889]   107   889     1791      131    49152       16             0 bash
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582908] [    955]   107   955  4068452   443652  7303168   349615             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582909] [    966]   107   966  3287947  1387409 26136576    12511             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582910] [    969]   107   969  3287704  1382560 26144768    12533             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582910] [    970]   107   970  3287352  1368475 26107904    12308             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582911] [    971]   107   971  3287036  1367553 26140672    12134             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582912] [   1003]   107  1003     5258      316    81920        1             0 psql
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582913] [   1004]   107  1004  3237319     1854   286720      524         -1000 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582913] [   1886]   107  1886  3762860  2761009 30019584   251751             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582914] [   1889]   107  1889  3771134  2800479 30085120   228760             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582915] [   1890]   107  1890  3766402  2820402 30044160   192876             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582916] [   1891]   107  1891  3771794  2869432 30089216   159472             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582916] [   1892]   107  1892  3756320  2969372 29962240    34173             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582917] [   1893]   107  1893  3757624  2732399 29974528   254133             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582918] [   1894]   107  1894  3754931  2732990 29949952   257045             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582919] [   1895]   107  1895  3746930  2706524 29884416   255862             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582920] [   1896]   107  1896  3784616  2786267 30191616   273228             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582920] [   1897]   107  1897  3764114  2744696 30027776   264012             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582921] [   1898]   107  1898  3751897  2738774 29925376   250796             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582922] [   1902]   107  1902  3775795  2765508 30121984   268510             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582923] [   1903]   107  1903  3769751  2748089 30072832   267884             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582923] [   1904]   107  1904  3776874  2762775 30130176   272872             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582924] [   1905]   107  1905  3758495  2723482 29982720   268364             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582925] [   1908]   107  1908  3767875  2748604 30056448   267458             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582926] [   1911]   107  1911  3764442  2742786 30031872   265335             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582926] [   1942]   107  1942  4247330   932709 30208000   373553             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582927] [   1947]   107  1947  4252906   922226 30023680   383247             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582928] [   1948]   107  1948  4258618   928815 30109696   383406             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582928] [   1949]   107  1949  4241298   927514 29872128   368102             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582929] [   1950]   107  1950  4263287   939040 30400512   381476             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582930] [   1957]   107  1957  3287833  1379746 26177536    12592             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582931] [   2121]   107  2121  3464237   211473 10764288    83999             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582931] [   2126]   107  2126  3237840   301603 25698304      713             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582932] [   3306]   107  3306  3368439  1810722 25874432      277             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582933] [   3368]   107  3368  3368439   848773 25968640      277             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582934] [   3602]   107  3602  3238152    72709  5763072      262             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582934] [   3644]   107  3644  3237853    45701  3473408      262             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582935] [   3649]   107  3649  3368430   144880  5513216      276             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582936] [   3655]   107  3655  3237792    38731  3026944      262             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582937] [   3656]   107  3656  3237709    31915  2486272      262             0 postgres
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582937] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/system.slice/system-postgresql.slice/postgresql@14-main.service,task=postgres,pid=731,uid=107
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.582947] Out of memory: Killed process 731 (postgres) total-vm:12949464kB, anon-rss:1932kB, file-rss:0kB, shmem-rss:12723916kB, UID:107 pgtables:25072kB oom_score_adj:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599767] postgres invoked oom-killer: gfp_mask=0x100cca(GFP_HIGHUSER_MOVABLE), order=0, oom_score_adj=0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599769] CPU: 2 PID: 1903 Comm: postgres Not tainted 5.10.0-19-amd64 #1 Debian 5.10.149-2
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599769] Hardware name: QEMU Standard PC (Q35 + ICH9, 2009), BIOS 1.14.0-2 04/01/2014
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599770] Call Trace:
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599773]  dump_stack+0x6b/0x83
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599775]  dump_header+0x4a/0x1f4
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599776]  oom_kill_process.cold+0xb/0x10
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599778]  out_of_memory+0x1bd/0x4e0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599779]  __alloc_pages_slowpath.constprop.0+0xbcc/0xc90
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599781]  __alloc_pages_nodemask+0x2de/0x310
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599782]  pagecache_get_page+0x175/0x390
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599783]  filemap_fault+0x6a2/0x900
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599792]  ext4_filemap_fault+0x2d/0x50 [ext4]
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599794]  __do_fault+0x37/0x170
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599795]  handle_mm_fault+0x1208/0x1c10
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599797]  do_user_addr_fault+0x1b8/0x400
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599798]  exc_page_fault+0x78/0x160
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599800]  ? asm_exc_page_fault+0x8/0x30
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599800]  asm_exc_page_fault+0x1e/0x30
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599801] RIP: 0033:0x55b840cc23b8
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599803] Code: Unable to access opcode bytes at RIP 0x55b840cc238e.
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599804] RSP: 002b:00007fff6dc1bd70 EFLAGS: 00010293
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599805] RAX: 000055b840e53700 RBX: 00007fff6dc1be60 RCX: 0000000000000000
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599805] RDX: 000055b8413e32f0 RSI: 0000000000000065 RDI: 0000000000000065
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599806] RBP: 00007fff6dc1be40 R08: 000055b8c3249c00 R09: 0000000000000000
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599806] R10: 00007fff6dc1be60 R11: 0000000000000000 R12: 0000000000000065
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599806] R13: 000055b8c3249110 R14: 0000000000000000 R15: 0000000000000000
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599808] Mem-Info:
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599810] active_anon:5010222 inactive_anon:6935369 isolated_anon:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599810]  active_file:12 inactive_file:39 isolated_file:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599810]  unevictable:0 dirty:0 writeback:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599810]  slab_reclaimable:59458 slab_unreclaimable:10543
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599810]  mapped:3203091 shmem:3203728 pagetables:234879 bounce:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599810]  free:70574 free_pcp:1682 free_cma:0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599811] Node 0 active_anon:20040888kB inactive_anon:27741476kB active_file:48kB inactive_file:156kB unevictable:0kB isolated(anon):0kB isolated(file):0kB mapped:12812364kB dirty:0kB writeback:0kB shmem:12814912kB shmem_thp: 0kB shmem_pmdmapped: 0kB anon_thp: 2217984kB writeback_tmp:0kB kernel_stack:3616kB all_unreclaimable? no
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599811] Node 0 DMA free:15908kB min:20kB low:32kB high:44kB reserved_highatomic:0KB active_anon:0kB inactive_anon:0kB active_file:0kB inactive_file:0kB unevictable:0kB writepending:0kB present:15992kB managed:15908kB mlocked:0kB pagetables:0kB bounce:0kB free_pcp:0kB local_pcp:0kB free_cma:0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599813] lowmem_reserve[]: 0 1940 48143 48143 48143
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599815] Node 0 DMA32 free:187528kB min:2720kB low:4704kB high:6688kB reserved_highatomic:0KB active_anon:746896kB inactive_anon:1045428kB active_file:48kB inactive_file:8kB unevictable:0kB writepending:0kB present:2080624kB managed:2015088kB mlocked:0kB pagetables:30652kB bounce:0kB free_pcp:304kB local_pcp:0kB free_cma:0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599816] lowmem_reserve[]: 0 0 46203 46203 46203
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599817] Node 0 Normal free:78860kB min:79172kB low:126484kB high:173796kB reserved_highatomic:0KB active_anon:19293776kB inactive_anon:26696052kB active_file:0kB inactive_file:104kB unevictable:0kB writepending:0kB present:48234496kB managed:47317716kB mlocked:0kB pagetables:908864kB bounce:0kB free_pcp:6424kB local_pcp:1240kB free_cma:0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599819] lowmem_reserve[]: 0 0 0 0 0
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599820] Node 0 DMA: 1*4kB (U) 0*8kB 0*16kB 1*32kB (U) 2*64kB (U) 1*128kB (U) 1*256kB (U) 0*512kB 1*1024kB (U) 1*2048kB (M) 3*4096kB (M) = 15908kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599825] Node 0 DMA32: 321*4kB (UME) 1583*8kB (UME) 2618*16kB (UME) 1335*32kB (UME) 755*64kB (UME) 142*128kB (UME) 54*256kB (UME) 13*512kB (UME) 2*1024kB (UM) 0*2048kB 0*4096kB = 187580kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599829] Node 0 Normal: 1951*4kB (UE) 1181*8kB (UME) 2261*16kB (UME) 610*32kB (UME) 71*64kB (UME) 4*128kB (M) 1*256kB (M) 1*512kB (M) 0*1024kB 0*2048kB 0*4096kB = 78772kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599834] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=1048576kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599834] Node 0 hugepages_total=0 hugepages_free=0 hugepages_surp=0 hugepages_size=2048kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599835] 3223141 total pagecache pages
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599836] 19146 pages in swap cache
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599837] Swap cache stats: add 6528060, delete 6508912, find 152537/201377
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599837] Free swap  = 0kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599838] Total swap = 25701372kB
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599838] 12582778 pages RAM
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599838] 0 pages HighMem/MovableOnly
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599838] 245600 pages reserved
Jan  9 14:22:22 xenpsql001 kernel: [ 8288.599839] 0 pages hwpoisoned

Was mir bei dem Ganzen überhaupt nicht klar ist:
Mit den o.g Werten (work_mem, habe ich jetzt auf 16MB runtergesetzt) kann ich zwar mehr Datensätze verarbeiten ABER das Problem ist damit halt nur nach hinten verschoben.
Ich frage mich halt wer für Speicher aufräumen von Zeit zu Zeit zuständig ist - die Applikation oder der Server? Für mich sieht es halt so aus, dass die Applikation Speicher fordert weil sie ihn auch benutzen will und irgendwann ist halt dieser Alle. Es fehlt halt den Speicher den man nicht mehr nutzt freizugeben.
Im Normalfall, hätte ich gesagt, dafür muss die Applikation sorgen, weil doch nur die wirklich weiß ob sie den Speicher noch nutzt oder nicht… Aber ob das auch so bei einer DB Applikation richtig ist… weiß ich nicht.

Nein das läuft anders: die Applikation sagt nur ‘Select…’ oder ‘Update …’, nicht aber ‘allokieren 10GB’ und kann daher auch nicht etwas freigeben. Du hast eine aktuelle Version, daher schließe ich (bekannte) Bugs auch aus. Hast Du irgend welche fremden Extensions via shared_preload_libraries eingebunden?

Hmm, das habe ich aus dem Artikel bzgl. Out-Of Memory Killer etwas anders verstanden. Aber OK, da kenne ich mich viel zu wenig aus mit den Hintergründen wie eine SQL Datenbank arbeitet

Mit 100% Sicherheit nicht. Oder besser gesagt nicht, dass es mir bekannt wäre. Bei den Debian Systemen installiere ich immer nur das absolute Minimum damit gerade Abhängigkeiten aufgelöst werden.

Aber wenn die Anwendung selbst kein Speicher allokiert und somit auch keins Freigeben kann, dann bleibt bald nur der Server oder eben meine Konfig.
Ich packe meine komplette Konfig hier. Vielleicht ist da an anderer Stelle ein Mist was ich für unwichtig erachtet habe es aber doch wichtig ist. (Ich habe in den letzten Versuchen immer um 8MB wal_mem reduziert. Es hat aber keine oder nur sehr minimale Auswirkung. auf das was verarbeitet werden kann)

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The "=" is optional.)  Whitespace may be used.  Comments are introduced with
# "#" anywhere on a line.  The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, run "pg_ctl reload", or execute
# "SELECT pg_reload_conf()".  Some parameters, which are marked below,
# require a server shutdown and restart to take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on".  Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units:  kB = kilobytes        Time units:  ms  = milliseconds
#                MB = megabytes                     s   = seconds
#                GB = gigabytes                     min = minutes
#                TB = terabytes                     h   = hours
#                                                   d   = days


#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------

# The default values of these variables are driven from the -D command-line
# option or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/var/lib/postgresql/14/main'		# use data in another directory
					# (change requires restart)
hba_file = '/etc/postgresql/14/main/pg_hba.conf'	# host-based authentication file
					# (change requires restart)
ident_file = '/etc/postgresql/14/main/pg_ident.conf'	# ident configuration file
					# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
external_pid_file = '/var/run/postgresql/14-main.pid'			# write an extra PID file
					# (change requires restart)


#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = 'localhost,xenpsql001.rjap.de'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 150			# (change requires restart)
#superuser_reserved_connections = 3	# (change requires restart)
unix_socket_directories = '/var/run/postgresql'	# comma-separated list of directories
					# (change requires restart)
#unix_socket_group = ''			# (change requires restart)
#unix_socket_permissions = 0777		# begin with 0 to use octal notation
					# (change requires restart)
#bonjour = off				# advertise server via Bonjour
					# (change requires restart)
#bonjour_name = ''			# defaults to the computer name
					# (change requires restart)

# - Security and Authentication -

#authentication_timeout = 1min		# 1s-600s
ssl = on
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_dh_params_file = ''
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
#ssl_ca_file = ''
#ssl_crl_file = ''
#password_encryption = md5		# md5 or scram-sha-256
#db_user_namespace = off
#row_security = on

# GSSAPI using Kerberos
#krb_server_keyfile = ''
#krb_caseins_users = off

# - TCP Keepalives -
# see "man 7 tcp" for details

#tcp_keepalives_idle = 0		# TCP_KEEPIDLE, in seconds;
					# 0 selects the system default
#tcp_keepalives_interval = 0		# TCP_KEEPINTVL, in seconds;
					# 0 selects the system default
#tcp_keepalives_count = 0		# TCP_KEEPCNT;
					# 0 selects the system default


#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------

# - Memory -

shared_buffers = 12GB			# min 128kB
					# (change requires restart)
#huge_pages = try			# on, off, or try
					# (change requires restart)
#temp_buffers = 8MB			# min 800kB
###temp_buffers = 1GB			# min 800kB
#max_prepared_transactions = 0		# zero disables the feature
					# (change requires restart)
# Caution: it is not advisable to set max_prepared_transactions nonzero unless
# you actively intend to use prepared transactions.
work_mem = 16MB				# min 64kB
maintenance_work_mem = 512MB		# min 1MB
#replacement_sort_tuples = 150000	# limits use of replacement selection sort
#autovacuum_work_mem = -1		# min 1MB, or -1 to use maintenance_work_mem
#max_stack_depth = 2MB			# min 100kB
dynamic_shared_memory_type = posix	# the default is the first option
					# supported by the operating system:
					#   posix
					#   sysv
					#   windows
					#   mmap
					# use none to disable dynamic shared memory
					# (change requires restart)

# - Disk -

#temp_file_limit = -1			# limits per-process temp file space
					# in kB, or -1 for no limit

# - Kernel Resource Usage -

#max_files_per_process = 1000		# min 25
### max_files_per_process = 100		# min 25
					# (change requires restart)
#shared_preload_libraries = ''		# (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0			# 0-100 milliseconds
#vacuum_cost_page_hit = 1		# 0-10000 credits
#vacuum_cost_page_miss = 10		# 0-10000 credits
#vacuum_cost_page_dirty = 20		# 0-10000 credits
#vacuum_cost_limit = 200		# 1-10000 credits

# - Background Writer -

#bgwriter_delay = 200ms			# 10-10000ms between rounds
#bgwriter_lru_maxpages = 100		# 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0		# 0-10.0 multiplier on buffers scanned/round
#bgwriter_flush_after = 512kB		# measured in pages, 0 disables

# - Asynchronous Behavior -

#effective_io_concurrency = 1		# 1-1000; 0 disables prefetching
#max_worker_processes = 8		# (change requires restart)
#max_parallel_workers_per_gather = 2	# taken from max_parallel_workers
#max_parallel_workers = 8		# maximum number of max_worker_processes that
					# can be used in parallel queries
#old_snapshot_threshold = -1		# 1min-60d; -1 disables; 0 is immediate
					# (change requires restart)
#backend_flush_after = 0		# measured in pages, 0 disables


#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------

# - Settings -

#wal_level = replica			# minimal, replica, or logical
					# (change requires restart)
#fsync = on				# flush data to disk for crash safety
					# (turning this off can cause
					# unrecoverable data corruption)
#synchronous_commit = on		# synchronization level;
					# off, local, remote_write, remote_apply, or on
#wal_sync_method = fsync		# the default is the first option
					# supported by the operating system:
					#   open_datasync
					#   fdatasync (default on Linux)
					#   fsync
					#   fsync_writethrough
					#   open_sync
#full_page_writes = on			# recover from partial page writes
#wal_compression = off			# enable compression of full-page writes
#wal_log_hints = off			# also do full page writes of non-critical updates
					# (change requires restart)
#wal_buffers = -1			# min 32kB, -1 sets based on shared_buffers
					# (change requires restart)
#wal_writer_delay = 200ms		# 1-10000 milliseconds
#wal_writer_flush_after = 1MB		# measured in pages, 0 disables

#commit_delay = 0			# range 0-100000, in microseconds
#commit_siblings = 5			# range 1-1000

# - Checkpoints -

# checkpoint_timeout = 5min		# range 30s-1d
checkpoint_timeout = 30min		# range 30s-1d
max_wal_size = 10GB
min_wal_size = 3GB
#checkpoint_completion_target = 0.5	# checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB		# measured in pages, 0 disables
#checkpoint_warning = 30s		# 0 disables

# - Archiving -

#archive_mode = off		# enables archiving; off, on, or always
				# (change requires restart)
#archive_command = ''		# command to use to archive a logfile segment
				# placeholders: %p = path of file to archive
				#               %f = file name only
				# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
#archive_timeout = 0		# force a logfile segment switch after this
				# number of seconds; 0 disables


#------------------------------------------------------------------------------
# REPLICATION
#------------------------------------------------------------------------------

# - Sending Server(s) -

# Set these on the master and on any standby that will send replication data.

#max_wal_senders = 10		# max number of walsender processes
				# (change requires restart)
#wal_keep_segments = 0		# in logfile segments, 16MB each; 0 disables
#wal_sender_timeout = 60s	# in milliseconds; 0 disables

#max_replication_slots = 10	# max number of replication slots
				# (change requires restart)
#track_commit_timestamp = off	# collect timestamp of transaction commit
				# (change requires restart)

# - Master Server -

# These settings are ignored on a standby server.

#synchronous_standby_names = ''	# standby servers that provide sync rep
				# method to choose sync standbys, number of sync standbys,
				# and comma-separated list of application_name
				# from standby(s); '*' = all
#vacuum_defer_cleanup_age = 0	# number of xacts by which cleanup is delayed

# - Standby Servers -

# These settings are ignored on a master server.

#hot_standby = on			# "off" disallows queries during recovery
					# (change requires restart)
#max_standby_archive_delay = 30s	# max delay before canceling queries
					# when reading WAL from archive;
					# -1 allows indefinite delay
#max_standby_streaming_delay = 30s	# max delay before canceling queries
					# when reading streaming WAL;
					# -1 allows indefinite delay
#wal_receiver_status_interval = 10s	# send replies at least this often
					# 0 disables
#hot_standby_feedback = off		# send info from standby to prevent
					# query conflicts
#wal_receiver_timeout = 60s		# time that receiver waits for
					# communication from master
					# in milliseconds; 0 disables
#wal_retrieve_retry_interval = 5s	# time to wait before retrying to
					# retrieve WAL after a failed attempt

# - Subscribers -

# These settings are ignored on a publisher.

#max_logical_replication_workers = 4	# taken from max_worker_processes
					# (change requires restart)
#max_sync_workers_per_subscription = 2	# taken from max_logical_replication_workers


#------------------------------------------------------------------------------
# QUERY TUNING
#------------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
#enable_indexonlyscan = on
#enable_material = on
#enable_mergejoin = on
#enable_nestloop = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on

# - Planner Cost Constants -

#seq_page_cost = 1.0			# measured on an arbitrary scale
#random_page_cost = 4.0			# same scale as above
cpu_tuple_cost = 0.03			# same scale as above
#cpu_index_tuple_cost = 0.005		# same scale as above
#cpu_operator_cost = 0.0025		# same scale as above
#parallel_tuple_cost = 0.1		# same scale as above
#parallel_setup_cost = 1000.0	# same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
effective_cache_size = 24GB

# - Genetic Query Optimizer -

#geqo = on
#geqo_threshold = 12
#geqo_effort = 5			# range 1-10
#geqo_pool_size = 0			# selects default based on effort
#geqo_generations = 0			# selects default based on effort
#geqo_selection_bias = 2.0		# range 1.5-2.0
#geqo_seed = 0.0			# range 0.0-1.0

# - Other Planner Options -

#default_statistics_target = 100	# range 1-10000
#constraint_exclusion = partition	# on, off, or partition
#cursor_tuple_fraction = 0.1		# range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8		# 1 disables collapsing of explicit
					# JOIN clauses
#force_parallel_mode = off


#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

#log_destination = 'stderr'		# Valid values are combinations of
					# stderr, csvlog, syslog, and eventlog,
					# depending on platform.  csvlog
					# requires logging_collector to be on.

# This is used when logging to stderr:
#logging_collector = off		# Enable capturing of stderr and csvlog
					# into log files. Required to be on for
					# csvlogs.
					# (change requires restart)

# These are only used if logging_collector is on:
#log_directory = 'log'			# directory where log files are written,
					# can be absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'	# log file name pattern,
					# can include strftime() escapes
#log_file_mode = 0600			# creation mode for log files,
					# begin with 0 to use octal notation
#log_truncate_on_rotation = off		# If on, an existing log file with the
					# same name as the new log file will be
					# truncated rather than appended to.
					# But such truncation only occurs on
					# time-driven rotation, not on restarts
					# or size-driven rotation.  Default is
					# off, meaning append to existing files
					# in all cases.
#log_rotation_age = 1d			# Automatic rotation of logfiles will
					# happen after that time.  0 disables.
#log_rotation_size = 10MB		# Automatic rotation of logfiles will
					# happen after that much log output.
					# 0 disables.

# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#syslog_sequence_numbers = on
#syslog_split_messages = on

# This is only relevant when logging to eventlog (win32):
# (change requires restart)
#event_source = 'PostgreSQL'

# - When to Log -

#client_min_messages = notice		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   log
					#   notice
					#   warning
					#   error

#log_min_messages = warning		# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic

#log_min_error_statement = error	# values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic (effectively off)

#log_min_duration_statement = -1	# -1 is disabled, 0 logs all statements
					# and their durations, > 0 logs only
					# statements running at least this number
					# of milliseconds


# - What to Log -

#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default		# terse, default, or verbose messages
#log_hostname = off
log_line_prefix = '%m [%p] %q%u@%d '		# special values:
					#   %a = application name
					#   %u = user name
					#   %d = database name
					#   %r = remote host and port
					#   %h = remote host
					#   %p = process ID
					#   %t = timestamp without milliseconds
					#   %m = timestamp with milliseconds
					#   %n = timestamp with milliseconds (as a Unix epoch)
					#   %i = command tag
					#   %e = SQL state
					#   %c = session ID
					#   %l = session line number
					#   %s = session start timestamp
					#   %v = virtual transaction ID
					#   %x = transaction ID (0 if none)
					#   %q = stop here in non-session
					#        processes
					#   %% = '%'
					# e.g. '<%u%%%d> '
#log_lock_waits = off			# log lock waits >= deadlock_timeout
#log_statement = 'none'			# none, ddl, mod, all
#log_replication_commands = off
#log_temp_files = -1			# log temporary files equal or larger
					# than the specified size in kilobytes;
					# -1 disables, 0 logs all temp files
log_timezone = 'localtime'


# - Process Title -

cluster_name = '14/main'		# added to process titles if nonempty
					# (change requires restart)
#update_process_title = on


#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------

# - Query/Index Statistics Collector -

#track_activities = on
#track_counts = on
#track_io_timing = off
#track_functions = none			# none, pl, all
#track_activity_query_size = 1024	# (change requires restart)
stats_temp_directory = '/var/run/postgresql/14-main.pg_stat_tmp'


# - Statistics Monitoring -

#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off


#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = yes			# Enable autovacuum subprocess?  'on'
					# requires track_counts to also be on.
#log_autovacuum_min_duration = -1	# -1 disables, 0 logs all actions and
					# their durations, > 0 logs only
					# actions running at least this number
					# of milliseconds.
#autovacuum_max_workers = 3		# max number of autovacuum subprocesses
					# (change requires restart)
#autovacuum_naptime = 1min		# time between autovacuum runs
#autovacuum_vacuum_threshold = 50	# min number of row updates before
					# vacuum
#autovacuum_analyze_threshold = 50	# min number of row updates before
					# analyze
#autovacuum_vacuum_scale_factor = 0.2	# fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1	# fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000	# maximum XID age before forced vacuum
					# (change requires restart)
#autovacuum_multixact_freeze_max_age = 400000000	# maximum multixact age
					# before forced vacuum
					# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms	# default vacuum cost delay for
					# autovacuum, in milliseconds;
					# -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1	# default vacuum cost limit for
					# autovacuum, -1 means use
					# vacuum_cost_limit


#------------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#------------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '"$user", public'	# schema names
#default_tablespace = ''		# a tablespace name, '' uses the default
#temp_tablespaces = ''			# a list of tablespace names, '' uses
					# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#default_transaction_deferrable = off
#session_replication_role = 'origin'
#statement_timeout = 0			# in milliseconds, 0 is disabled
#lock_timeout = 0			# in milliseconds, 0 is disabled
#idle_in_transaction_session_timeout = 0	# in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#vacuum_multixact_freeze_min_age = 5000000
#vacuum_multixact_freeze_table_age = 150000000
#bytea_output = 'hex'			# hex, escape
#xmlbinary = 'base64'
#xmloption = 'content'
#gin_fuzzy_search_limit = 0
#gin_pending_list_limit = 4MB

# - Locale and Formatting -

datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
timezone = 'localtime'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
					# abbreviations.  Currently, there are
					#   Default
					#   Australia (historical usage)
					#   India
					# You can create your own file in
					# share/timezonesets/.
#extra_float_digits = 0			# min -15, max 3
#client_encoding = sql_ascii		# actually, defaults to database
					# encoding

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'de_DE.UTF-8'			# locale for system error message
					# strings
lc_monetary = 'de_DE.UTF-8'			# locale for monetary formatting
lc_numeric = 'de_DE.UTF-8'			# locale for number formatting
lc_time = 'de_DE.UTF-8'				# locale for time formatting

# default configuration for text search
default_text_search_config = 'pg_catalog.german'

# - Other Defaults -

#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#session_preload_libraries = ''


#------------------------------------------------------------------------------
# LOCK MANAGEMENT
#------------------------------------------------------------------------------

#deadlock_timeout = 1s
#max_locks_per_transaction = 64		# min 10
					# (change requires restart)
#max_pred_locks_per_transaction = 64	# min 10
					# (change requires restart)
#max_pred_locks_per_relation = -2	# negative values mean
					# (max_pred_locks_per_transaction
					#  / -max_pred_locks_per_relation) - 1
#max_pred_locks_per_page = 2            # min 0


#------------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#------------------------------------------------------------------------------

# - Previous PostgreSQL Versions -

#array_nulls = on
#backslash_quote = safe_encoding	# on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#lo_compat_privileges = off
#operator_precedence_warning = off
#quote_all_identifiers = off
#standard_conforming_strings = on
#synchronize_seqscans = on

# - Other Platforms and Clients -

#transform_null_equals = off


#------------------------------------------------------------------------------
# ERROR HANDLING
#------------------------------------------------------------------------------

#exit_on_error = off			# terminate session on any error?
#restart_after_crash = on		# reinitialize after backend crash?


#------------------------------------------------------------------------------
# CONFIG FILE INCLUDES
#------------------------------------------------------------------------------

# These options allow settings to be loaded from files other than the
# default postgresql.conf.

include_dir = 'conf.d'			# include files ending in '.conf' from
					# directory 'conf.d'
#include_if_exists = 'exists.conf'	# include file only if it exists
#include = 'special.conf'		# include file


#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------

# Add settings for extensions here

Eigentlich ist der Speicherverbrauch einer Transaktion relativ unabhängig von der Anzahl der Datensätze die davon betroffen sind.

Ich habe schon Millionen von Datensätzen importiert ohne mit dem Speicher ein Problem zu bekommen.

Soweit ich weiß, gibt es allerdings (mindestens) zwei Dinge, die bei größeren Transaktionen auch den Speicherverbrauch erhöhen: deferred Constraints und Statement Level Trigger die “transition” Tabellen verwenden. Wird das in der Datenbank verwendet?

Vielleicht gibt es aber auch “normale” Trigger die einfach alle geänderten Datensätze irgendwie “aufsammeln” und im Speicher halten wollen. Das solltest Du auch mal prüfen.

Das weiß ich natürlich nicht.

Das sind die Tabellen.

                        Liste der Relationen
 Schema |               Name               |   Typ   |  Eigentümer
--------+----------------------------------+---------+---------------
 public | idcache_filepath                 | Tabelle | idimager_main
 public | idcache_prop                     | Tabelle | idimager_main
 public | idcatalogitem                    | Tabelle | idimager_main
 public | idcatalogitemdefinition          | Tabelle | idimager_main
 public | idcollectioninfo                 | Tabelle | idimager_main
 public | idfilepath                       | Tabelle | idimager_main
 public | idhistory                        | Tabelle | idimager_main
 public | idimagearea                      | Tabelle | idimager_main
 public | idimagebox                       | Tabelle | idimager_main
 public | idimageboxusage                  | Tabelle | idimager_main
 public | idimagecard                      | Tabelle | idimager_main
 public | idimagecollection                | Tabelle | idimager_main
 public | idimagecollectionitem            | Tabelle | idimager_main
 public | idimagecomment                   | Tabelle | idimager_main
 public | idimagedata                      | Tabelle | idimager_main
 public | idimagegallery                   | Tabelle | idimager_main
 public | idimageiptc                      | Tabelle | idimager_main
 public | idimagemodel                     | Tabelle | idimager_main
 public | idimagescore                     | Tabelle | idimager_main
 public | idimagesubscription              | Tabelle | idimager_main
 public | idimageversion                   | Tabelle | idimager_main
 public | idjob                            | Tabelle | idimager_main
 public | idjobgroup                       | Tabelle | idimager_main
 public | idmediuminfo                     | Tabelle | idimager_main
 public | idmultiusernotification          | Tabelle | idimager_main
 public | idplaceholder                    | Tabelle | idimager_main
 public | idprop                           | Tabelle | idimager_main
 public | idpropcategory                   | Tabelle | idimager_main
 public | idpropgroup                      | Tabelle | idimager_main
 public | idpropgroupcontent               | Tabelle | idimager_main
 public | idpropgrouppathcontent           | Tabelle | idimager_main
 public | idpropgroupperiodcontent         | Tabelle | idimager_main
 public | idproprelation                   | Tabelle | idimager_main
 public | idsearchdata                     | Tabelle | idimager_main
 public | idsearchdatavalue                | Tabelle | idimager_main
 public | idsyncimageiptc                  | Tabelle | idimager_main
 public | idsyncthumb                      | Tabelle | idimager_main
 public | idsyncxmp                        | Tabelle | idimager_main
 public | idtempfilelist                   | Tabelle | idimager_main
 public | idtemplist                       | Tabelle | idimager_main
 public | iduser                           | Tabelle | idimager_main
 public | iduserfavorite                   | Tabelle | idimager_main
 public | idusergroup                      | Tabelle | idimager_main
 public | iduserprop                       | Tabelle | idimager_main
 public | iduserusage                      | Tabelle | idimager_main
 public | iduseruser                       | Tabelle | idimager_main
 public | idversioninfo                    | Tabelle | idimager_main
 public | idversionplaceholder             | Tabelle | idimager_main
 public | v_albumdisplayversionplaceholder | Sicht   | idimager_main
 public | v_albumversion                   | Sicht   | idimager_main
 public | v_catalogitem                    | Sicht   | idimager_main
 public | v_catalogitemperiods             | Sicht   | idimager_main
 public | v_catalogitemversion             | Sicht   | idimager_main
 public | v_catalogperiods                 | Sicht   | idimager_main
 public | v_catalogversionperiods          | Sicht   | idimager_main
 public | v_filepath                       | Sicht   | idimager_main
public | v_filetypes                      | Sicht   | idimager_main
 public | v_idimagecollectionitem          | Sicht   | idimager_main
 public | v_imageversion                   | Sicht   | idimager_main
 public | v_placeholderversion             | Sicht   | idimager_main
 public | v_prop                           | Sicht   | idimager_main
 public | v_propcategory                   | Sicht   | idimager_main
 public | v_versionplaceholder             | Sicht   | idimager_main

Gibt es eine Art “select” o.ä damit man auf ein Schlag ein “\d+” auf alle Tabellen machen kann, dann könnte ich vielleicht etwas dazu sagen.

Natürlich wieder nur eine schwache Idee. In der einer Datenbank werden die Vorschaubilder für jedes einzelne Bild gespeichert. bei ~200000 Bildern hat die Datenbank jetzt die Größe von

select pg_database_size('photosupreme_thumbs');
 pg_database_size
------------------
     115236520739

Wenn jetzt die Anwendung eben ~50000 Datensätze also auch Vorschaubilder neu schreiben will, und das irgendwie im Speicher bleibt, dann geht dieser doch irgendwann einfach aus (oder bin ich wider auf nem Holzweg?) Ich habe immer so verstanden, dass man in eine Datenbank nie Bilddateien schreiben soll, aber nun ja ich bin nicht der Entwickler der Anwendung, sondern nur ein User.Der Entwickler wird hoffe ich seine Gründe haben und es für OK befunden haben.

Trigger kannst Du grob auflisten via

select * from pg_trigger ;

Auch wenn die Vorschaubilder in der DB sind sollte das kein Problem sein. PG verwendet MVCC, was so realisiert ist, daß neue und alte Datensätze nicht irgendwo im RAM gehalten werden sondern in der DB. Jeder Record hat dann seine Merkmale, in welcher Transaktion er sichtbar ist (select xmin, xmax, * from table …, google mal danach wie das funktioniert…), daher hat @castorp Recht wenn er sagt, daß die Anzahl Records in einer Transaktion faktisch egal ist - auch deren physische Größe.

photosupreme=# select * from pg_trigger ;
   oid   | tgrelid | tgparentid |            tgname            | tgfoid  | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
---------+---------+------------+------------------------------+---------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+------------
   17867 |  242231 |          0 | RI_ConstraintTrigger_a_17867 |    1646 |      9 | O         | t            |        242254 |        281814 |        17866 | f            | f              |       0 |        | \x     |        |            |
   17868 |  242231 |          0 | RI_ConstraintTrigger_a_17868 |    1647 |     17 | O         | t            |        242254 |        281814 |        17866 | f            | f              |       0 |        | \x     |        |            |
   17869 |  242254 |          0 | RI_ConstraintTrigger_c_17869 |    1644 |      5 | O         | t            |        242231 |        281814 |        17866 | f            | f              |       0 |        | \x     |        |            |
   17870 |  242254 |          0 | RI_ConstraintTrigger_c_17870 |    1645 |     17 | O         | t            |        242231 |        281814 |        17866 | f            | f              |       0 |        | \x     |        |            |
   17872 |  242428 |          0 | RI_ConstraintTrigger_a_17872 |    1646 |      9 | O         | t            |        242254 |        281858 |        17871 | f            | f              |       0 |        | \x     |        |            |
   17873 |  242428 |          0 | RI_ConstraintTrigger_a_17873 |    1647 |     17 | O         | t            |        242254 |        281858 |        17871 | f            | f              |       0 |        | \x     |        |            |
   17874 |  242254 |          0 | RI_ConstraintTrigger_c_17874 |    1644 |      5 | O         | t            |        242428 |        281858 |        17871 | f            | f              |       0 |        | \x     |        |            |
   17875 |  242254 |          0 | RI_ConstraintTrigger_c_17875 |    1645 |     17 | O         | t            |        242428 |        281858 |        17871 | f            | f              |       0 |        | \x     |        |            |
   17877 |  242290 |          0 | RI_ConstraintTrigger_a_17877 |    1646 |      9 | O         | t            |        242296 |        281828 |        17876 | f            | f              |       0 |        | \x     |        |            |
   17878 |  242290 |          0 | RI_ConstraintTrigger_a_17878 |    1647 |     17 | O         | t            |        242296 |        281828 |        17876 | f            | f              |       0 |        | \x     |        |            |
   17879 |  242296 |          0 | RI_ConstraintTrigger_c_17879 |    1644 |      5 | O         | t            |        242290 |        281828 |        17876 | f            | f              |       0 |        | \x     |        |            |
   17880 |  242296 |          0 | RI_ConstraintTrigger_c_17880 |    1645 |     17 | O         | t            |        242290 |        281828 |        17876 | f            | f              |       0 |        | \x     |        |            |
   17882 |  242521 |          0 | RI_ConstraintTrigger_a_17882 |    1646 |      9 | O         | t            |        242296 |        281882 |        17881 | f            | f              |       0 |        | \x     |        |            |
   17883 |  242521 |          0 | RI_ConstraintTrigger_a_17883 |    1647 |     17 | O         | t            |        242296 |        281882 |        17881 | f            | f              |       0 |        | \x     |        |            |
   17884 |  242296 |          0 | RI_ConstraintTrigger_c_17884 |    1644 |      5 | O         | t            |        242521 |        281882 |        17881 | f            | f              |       0 |        | \x     |        |            |
   17885 |  242296 |          0 | RI_ConstraintTrigger_c_17885 |    1645 |     17 | O         | t            |        242521 |        281882 |        17881 | f            | f              |       0 |        | \x     |        |            |
   17887 |  242521 |          0 | RI_ConstraintTrigger_a_17887 |    1646 |      9 | O         | t            |        242231 |        281882 |        17886 | f            | f              |       0 |        | \x     |        |            |
   17888 |  242521 |          0 | RI_ConstraintTrigger_a_17888 |    1647 |     17 | O         | t            |        242231 |        281882 |        17886 | f            | f              |       0 |        | \x     |        |            |
   17889 |  242231 |          0 | RI_ConstraintTrigger_c_17889 |    1644 |      5 | O         | t            |        242521 |        281882 |        17886 | f            | f              |       0 |        | \x     |        |            |
   17890 |  242231 |          0 | RI_ConstraintTrigger_c_17890 |    1645 |     17 | O         | t            |        242521 |        281882 |        17886 | f            | f              |       0 |        | \x     |        |            |
   17892 |  242231 |          0 | RI_ConstraintTrigger_a_17892 |    1646 |      9 | O         | t            |        242329 |        281814 |        17891 | f            | f              |       0 |        | \x     |        |            |
   17893 |  242231 |          0 | RI_ConstraintTrigger_a_17893 |    1647 |     17 | O         | t            |        242329 |        281814 |        17891 | f            | f              |       0 |        | \x     |        |            |
   17894 |  242329 |          0 | RI_ConstraintTrigger_c_17894 |    1644 |      5 | O         | t            |        242231 |        281814 |        17891 | f            | f              |       0 |        | \x     |        |            |
   17895 |  242329 |          0 | RI_ConstraintTrigger_c_17895 |    1645 |     17 | O         | t            |        242231 |        281814 |        17891 | f            | f              |       0 |        | \x     |        |            |
   17897 |  242521 |          0 | RI_ConstraintTrigger_a_17897 |    1654 |      9 | O         | t            |        242329 |        281882 |        17896 | f            | f              |       0 |        | \x     |        |            |
   17898 |  242521 |          0 | RI_ConstraintTrigger_a_17898 |    1655 |     17 | O         | t            |        242329 |        281882 |        17896 | f            | f              |       0 |        | \x     |        |            |
   17899 |  242329 |          0 | RI_ConstraintTrigger_c_17899 |    1644 |      5 | O         | t            |        242521 |        281882 |        17896 | f            | f              |       0 |        | \x     |        |            |
   17900 |  242329 |          0 | RI_ConstraintTrigger_c_17900 |    1645 |     17 | O         | t            |        242521 |        281882 |        17896 | f            | f              |       0 |        | \x     |        |            |
   17902 |  242231 |          0 | RI_ConstraintTrigger_a_17902 |    1646 |      9 | O         | t            |        242544 |        281814 |        17901 | f            | f              |       0 |        | \x     |        |            |
   17903 |  242231 |          0 | RI_ConstraintTrigger_a_17903 |    1647 |     17 | O         | t            |        242544 |        281814 |        17901 | f            | f              |       0 |        | \x     |        |            |
   17904 |  242544 |          0 | RI_ConstraintTrigger_c_17904 |    1644 |      5 | O         | t            |        242231 |        281814 |        17901 | f            | f              |       0 |        | \x     |        |            |
   17905 |  242544 |          0 | RI_ConstraintTrigger_c_17905 |    1645 |     17 | O         | t            |        242231 |        281814 |        17901 | f            | f              |       0 |        | \x     |        |            |
   17907 |  242231 |          0 | RI_ConstraintTrigger_a_17907 |    1646 |      9 | O         | t            |        242299 |        281814 |        17906 | f            | f              |       0 |        | \x     |        |            |
   17908 |  242231 |          0 | RI_ConstraintTrigger_a_17908 |    1647 |     17 | O         | t            |        242299 |        281814 |        17906 | f            | f              |       0 |        | \x     |        |            |
   17909 |  242299 |          0 | RI_ConstraintTrigger_c_17909 |    1644 |      5 | O         | t            |        242231 |        281814 |        17906 | f            | f              |       0 |        | \x     |        |            |
   17910 |  242299 |          0 | RI_ConstraintTrigger_c_17910 |    1645 |     17 | O         | t            |        242231 |        281814 |        17906 | f            | f              |       0 |        | \x     |        |            |
   17912 |  242521 |          0 | RI_ConstraintTrigger_a_17912 |    1654 |      9 | O         | t            |        242299 |        281882 |        17911 | f            | f              |       0 |        | \x     |        |            |
   17913 |  242521 |          0 | RI_ConstraintTrigger_a_17913 |    1655 |     17 | O         | t            |        242299 |        281882 |        17911 | f            | f              |       0 |        | \x     |        |            |
   17914 |  242299 |          0 | RI_ConstraintTrigger_c_17914 |    1644 |      5 | O         | t            |        242521 |        281882 |        17911 | f            | f              |       0 |        | \x     |        |            |
   17915 |  242299 |          0 | RI_ConstraintTrigger_c_17915 |    1645 |     17 | O         | t            |        242521 |        281882 |        17911 | f            | f              |       0 |        | \x     |        |            |
   17917 |  242347 |          0 | RI_ConstraintTrigger_a_17917 |    1646 |      9 | O         | t            |        242308 |        281840 |        17916 | f            | f              |       0 |        | \x     |        |            |
   17918 |  242347 |          0 | RI_ConstraintTrigger_a_17918 |    1647 |     17 | O         | t            |        242308 |        281840 |        17916 | f            | f              |       0 |        | \x     |        |            |
   17919 |  242308 |          0 | RI_ConstraintTrigger_c_17919 |    1644 |      5 | O         | t            |        242347 |        281840 |        17916 | f            | f              |       0 |        | \x     |        |            |
   17920 |  242308 |          0 | RI_ConstraintTrigger_c_17920 |    1645 |     17 | O         | t            |        242347 |        281840 |        17916 | f            | f              |       0 |        | \x     |        |            |
   17922 |  242308 |          0 | RI_ConstraintTrigger_a_17922 |    1646 |      9 | O         | t            |        242323 |        281834 |        17921 | f            | f              |       0 |        | \x     |        |            |
   17923 |  242308 |          0 | RI_ConstraintTrigger_a_17923 |    1647 |     17 | O         | t            |        242323 |        281834 |        17921 | f            | f              |       0 |        | \x     |        |            |
   17924 |  242323 |          0 | RI_ConstraintTrigger_c_17924 |    1644 |      5 | O         | t            |        242308 |        281834 |        17921 | f            | f              |       0 |        | \x     |        |            |
   17925 |  242323 |          0 | RI_ConstraintTrigger_c_17925 |    1645 |     17 | O         | t            |        242308 |        281834 |        17921 | f            | f              |       0 |        | \x     |        |            |
   17927 |  242231 |          0 | RI_ConstraintTrigger_a_17927 |    1646 |      9 | O         | t            |        242323 |        281814 |        17926 | f            | f              |       0 |        | \x     |        |            |
   17928 |  242231 |          0 | RI_ConstraintTrigger_a_17928 |    1647 |     17 | O         | t            |        242323 |        281814 |        17926 | f            | f              |       0 |        | \x     |        |            |
   17929 |  242323 |          0 | RI_ConstraintTrigger_c_17929 |    1644 |      5 | O         | t            |        242231 |        281814 |        17926 | f            | f              |       0 |        | \x     |        |            |
   17930 |  242323 |          0 | RI_ConstraintTrigger_c_17930 |    1645 |     17 | O         | t            |        242231 |        281814 |        17926 | f            | f              |       0 |        | \x     |        |            |
   17932 |  242367 |          0 | RI_ConstraintTrigger_a_17932 |    1646 |      9 | O         | t            |        242347 |        281844 |        17931 | f            | f              |       0 |        | \x     |        |            |
   17933 |  242367 |          0 | RI_ConstraintTrigger_a_17933 |    1647 |     17 | O         | t            |        242347 |        281844 |        17931 | f            | f              |       0 |        | \x     |        |            |
   17934 |  242347 |          0 | RI_ConstraintTrigger_c_17934 |    1644 |      5 | O         | t            |        242367 |        281844 |        17931 | f            | f              |       0 |        | \x     |        |            |
   17935 |  242347 |          0 | RI_ConstraintTrigger_c_17935 |    1645 |     17 | O         | t            |        242367 |        281844 |        17931 | f            | f              |       0 |        | \x     |        |            |
   17937 |  242231 |          0 | RI_ConstraintTrigger_a_17937 |    1646 |      9 | O         | t            |        242357 |        281814 |        17936 | f            | f              |       0 |        | \x     |        |            |
17938 |  242231 |          0 | RI_ConstraintTrigger_a_17938 |    1647 |     17 | O         | t            |        242357 |        281814 |        17936 | f            | f              |       0 |        | \x     |        |            |
   17939 |  242357 |          0 | RI_ConstraintTrigger_c_17939 |    1644 |      5 | O         | t            |        242231 |        281814 |        17936 | f            | f              |       0 |        | \x     |        |            |
   17940 |  242357 |          0 | RI_ConstraintTrigger_c_17940 |    1645 |     17 | O         | t            |        242231 |        281814 |        17936 | f            | f              |       0 |        | \x     |        |            |
   17942 |  242263 |          0 | RI_ConstraintTrigger_a_17942 |    1646 |      9 | O         | t            |        242231 |        281822 |        17941 | f            | f              |       0 |        | \x     |        |            |
   17943 |  242263 |          0 | RI_ConstraintTrigger_a_17943 |    1647 |     17 | O         | t            |        242231 |        281822 |        17941 | f            | f              |       0 |        | \x     |        |            |
   17944 |  242231 |          0 | RI_ConstraintTrigger_c_17944 |    1644 |      5 | O         | t            |        242263 |        281822 |        17941 | f            | f              |       0 |        | \x     |        |            |
   17945 |  242231 |          0 | RI_ConstraintTrigger_c_17945 |    1645 |     17 | O         | t            |        242263 |        281822 |        17941 | f            | f              |       0 |        | \x     |        |            |
   17947 |  242521 |          0 | RI_ConstraintTrigger_a_17947 |    1654 |      9 | O         | t            |        242367 |        281882 |        17946 | f            | f              |       0 |        | \x     |        |            |
   17948 |  242521 |          0 | RI_ConstraintTrigger_a_17948 |    1655 |     17 | O         | t            |        242367 |        281882 |        17946 | f            | f              |       0 |        | \x     |        |            |
   17949 |  242367 |          0 | RI_ConstraintTrigger_c_17949 |    1644 |      5 | O         | t            |        242521 |        281882 |        17946 | f            | f              |       0 |        | \x     |        |            |
   17950 |  242367 |          0 | RI_ConstraintTrigger_c_17950 |    1645 |     17 | O         | t            |        242521 |        281882 |        17946 | f            | f              |       0 |        | \x     |        |            |
   17952 |  242231 |          0 | RI_ConstraintTrigger_a_17952 |    1646 |      9 | O         | t            |        242381 |        281814 |        17951 | f            | f              |       0 |        | \x     |        |            |
   17953 |  242231 |          0 | RI_ConstraintTrigger_a_17953 |    1647 |     17 | O         | t            |        242381 |        281814 |        17951 | f            | f              |       0 |        | \x     |        |            |
   17954 |  242381 |          0 | RI_ConstraintTrigger_c_17954 |    1644 |      5 | O         | t            |        242231 |        281814 |        17951 | f            | f              |       0 |        | \x     |        |            |
   17955 |  242381 |          0 | RI_ConstraintTrigger_c_17955 |    1645 |     17 | O         | t            |        242231 |        281814 |        17951 | f            | f              |       0 |        | \x     |        |            |
   17957 |  242521 |          0 | RI_ConstraintTrigger_a_17957 |    1654 |      9 | O         | t            |        242381 |        281882 |        17956 | f            | f              |       0 |        | \x     |        |            |
   17958 |  242521 |          0 | RI_ConstraintTrigger_a_17958 |    1655 |     17 | O         | t            |        242381 |        281882 |        17956 | f            | f              |       0 |        | \x     |        |            |
   17959 |  242381 |          0 | RI_ConstraintTrigger_c_17959 |    1644 |      5 | O         | t            |        242521 |        281882 |        17956 | f            | f              |       0 |        | \x     |        |            |
   17960 |  242381 |          0 | RI_ConstraintTrigger_c_17960 |    1645 |     17 | O         | t            |        242521 |        281882 |        17956 | f            | f              |       0 |        | \x     |        |            |
   17962 |  242231 |          0 | RI_ConstraintTrigger_a_17962 |    1646 |      9 | O         | t            |        242385 |        281814 |        17961 | f            | f              |       0 |        | \x     |        |            |
   17963 |  242231 |          0 | RI_ConstraintTrigger_a_17963 |    1647 |     17 | O         | t            |        242385 |        281814 |        17961 | f            | f              |       0 |        | \x     |        |            |
   17964 |  242385 |          0 | RI_ConstraintTrigger_c_17964 |    1644 |      5 | O         | t            |        242231 |        281814 |        17961 | f            | f              |       0 |        | \x     |        |            |
   17965 |  242385 |          0 | RI_ConstraintTrigger_c_17965 |    1645 |     17 | O         | t            |        242231 |        281814 |        17961 | f            | f              |       0 |        | \x     |        |            |
   17967 |  242263 |          0 | RI_ConstraintTrigger_a_17967 |    1654 |      9 | O         | t            |        242385 |        281822 |        17966 | f            | f              |       0 |        | \x     |        |            |
   17968 |  242263 |          0 | RI_ConstraintTrigger_a_17968 |    1655 |     17 | O         | t            |        242385 |        281822 |        17966 | f            | f              |       0 |        | \x     |        |            |
   17969 |  242385 |          0 | RI_ConstraintTrigger_c_17969 |    1644 |      5 | O         | t            |        242263 |        281822 |        17966 | f            | f              |       0 |        | \x     |        |            |
   17970 |  242385 |          0 | RI_ConstraintTrigger_c_17970 |    1645 |     17 | O         | t            |        242263 |        281822 |        17966 | f            | f              |       0 |        | \x     |        |            |
   17972 |  242419 |          0 | RI_ConstraintTrigger_a_17972 |    1646 |      9 | O         | t            |        242567 |        281856 |        17971 | f            | f              |       0 |        | \x     |        |            |
   17973 |  242419 |          0 | RI_ConstraintTrigger_a_17973 |    1647 |     17 | O         | t            |        242567 |        281856 |        17971 | f            | f              |       0 |        | \x     |        |            |
   17974 |  242567 |          0 | RI_ConstraintTrigger_c_17974 |    1644 |      5 | O         | t            |        242419 |        281856 |        17971 | f            | f              |       0 |        | \x     |        |            |
   17975 |  242567 |          0 | RI_ConstraintTrigger_c_17975 |    1645 |     17 | O         | t            |        242419 |        281856 |        17971 | f            | f              |       0 |        | \x     |        |            |
   17977 |  242385 |          0 | RI_ConstraintTrigger_a_17977 |    1646 |      9 | O         | t            |        242567 |        281850 |        17976 | f            | f              |       0 |        | \x     |        |            |
   17978 |  242385 |          0 | RI_ConstraintTrigger_a_17978 |    1647 |     17 | O         | t            |        242567 |        281850 |        17976 | f            | f              |       0 |        | \x     |        |            |
   17979 |  242567 |          0 | RI_ConstraintTrigger_c_17979 |    1644 |      5 | O         | t            |        242385 |        281850 |        17976 | f            | f              |       0 |        | \x     |        |            |
   17980 |  242567 |          0 | RI_ConstraintTrigger_c_17980 |    1645 |     17 | O         | t            |        242385 |        281850 |        17976 | f            | f              |       0 |        | \x     |        |            |
   17982 |  242521 |          0 | RI_ConstraintTrigger_a_17982 |    1654 |      9 | O         | t            |        242428 |        281882 |        17981 | f            | f              |       0 |        | \x     |        |            |
   17983 |  242521 |          0 | RI_ConstraintTrigger_a_17983 |    1655 |     17 | O         | t            |        242428 |        281882 |        17981 | f            | f              |       0 |        | \x     |        |            |
   17984 |  242428 |          0 | RI_ConstraintTrigger_c_17984 |    1644 |      5 | O         | t            |        242521 |        281882 |        17981 | f            | f              |       0 |        | \x     |        |            |
   17985 |  242428 |          0 | RI_ConstraintTrigger_c_17985 |    1645 |     17 | O         | t            |        242521 |        281882 |        17981 | f            | f              |       0 |        | \x     |        |            |
   17987 |  242450 |          0 | RI_ConstraintTrigger_a_17987 |    1646 |      9 | O         | t            |        242470 |        281862 |        17986 | f            | f              |       0 |        | \x     |        |            |
   17988 |  242450 |          0 | RI_ConstraintTrigger_a_17988 |    1647 |     17 | O         | t            |        242470 |        281862 |        17986 | f            | f              |       0 |        | \x     |        |            |
   17989 |  242470 |          0 | RI_ConstraintTrigger_c_17989 |    1644 |      5 | O         | t            |        242450 |        281862 |        17986 | f            | f              |       0 |        | \x     |        |            |
   17990 |  242470 |          0 | RI_ConstraintTrigger_c_17990 |    1645 |     17 | O         | t            |        242450 |        281862 |        17986 | f            | f              |       0 |        | \x     |        |            |
   17992 |  242428 |          0 | RI_ConstraintTrigger_a_17992 |    1646 |      9 | O         | t            |        242470 |        281858 |        17991 | f            | f              |       0 |        | \x     |        |            |
   17993 |  242428 |          0 | RI_ConstraintTrigger_a_17993 |    1647 |     17 | O         | t            |        242470 |        281858 |        17991 | f            | f              |       0 |        | \x     |        |            |
   17994 |  242470 |          0 | RI_ConstraintTrigger_c_17994 |    1644 |      5 | O         | t            |        242428 |        281858 |        17991 | f            | f              |       0 |        | \x     |        |            |
   17995 |  242470 |          0 | RI_ConstraintTrigger_c_17995 |    1645 |     17 | O         | t            |        242428 |        281858 |        17991 | f            | f              |       0 |        | \x     |        |            |
   17997 |  242450 |          0 | RI_ConstraintTrigger_a_17997 |    1646 |      9 | O         | t            |        242474 |        281862 |        17996 | f            | f              |       0 |        | \x     |        |            |
   17998 |  242450 |          0 | RI_ConstraintTrigger_a_17998 |    1647 |     17 | O         | t            |        242474 |        281862 |        17996 | f            | f              |       0 |        | \x     |        |            |
   17999 |  242474 |          0 | RI_ConstraintTrigger_c_17999 |    1644 |      5 | O         | t            |        242450 |        281862 |        17996 | f            | f              |       0 |        | \x     |        |            |
   18000 |  242474 |          0 | RI_ConstraintTrigger_c_18000 |    1645 |     17 | O         | t            |        242450 |        281862 |        17996 | f            | f              |       0 |        | \x     |        |            |
   18002 |  242450 |          0 | RI_ConstraintTrigger_a_18002 |    1646 |      9 | O         | t            |        242478 |        281862 |        18001 | f            | f              |       0 |        | \x     |        |            |
   18003 |  242450 |          0 | RI_ConstraintTrigger_a_18003 |    1647 |     17 | O         | t            |        242478 |        281862 |        18001 | f            | f              |       0 |        | \x     |        |            |
   18004 |  242478 |          0 | RI_ConstraintTrigger_c_18004 |    1644 |      5 | O         | t            |        242450 |        281862 |        18001 | f            | f              |       0 |        | \x     |        |            |
   18005 |  242478 |          0 | RI_ConstraintTrigger_c_18005 |    1645 |     17 | O         | t            |        242450 |        281862 |        18001 | f            | f              |       0 |        | \x     |        |            |
   18007 |  242521 |          0 | RI_ConstraintTrigger_a_18007 |    1646 |      9 | O         | t            |        242450 |        281882 |        18006 | f            | f              |       0 |        | \x     |        |            |
   18008 |  242521 |          0 | RI_ConstraintTrigger_a_18008 |    1647 |     17 | O         | t            |        242450 |        281882 |        18006 | f            | f              |       0 |        | \x     |        |            |
   18009 |  242450 |          0 | RI_ConstraintTrigger_c_18009 |    1644 |      5 | O         | t            |        242521 |        281882 |        18006 | f            | f              |       0 |        | \x     |        |            |
   18010 |  242450 |          0 | RI_ConstraintTrigger_c_18010 |    1645 |     17 | O         | t            |        242521 |        281882 |        18006 | f            | f              |       0 |        | \x     |        |            |
18012 |  242428 |          0 | RI_ConstraintTrigger_a_18012 |    1646 |      9 | O         | t            |        242483 |        281858 |        18011 | f            | f              |       0 |        | \x     |        |            |
   18013 |  242428 |          0 | RI_ConstraintTrigger_a_18013 |    1647 |     17 | O         | t            |        242483 |        281858 |        18011 | f            | f              |       0 |        | \x     |        |            |
   18014 |  242483 |          0 | RI_ConstraintTrigger_c_18014 |    1644 |      5 | O         | t            |        242428 |        281858 |        18011 | f            | f              |       0 |        | \x     |        |            |
   18015 |  242483 |          0 | RI_ConstraintTrigger_c_18015 |    1645 |     17 | O         | t            |        242428 |        281858 |        18011 | f            | f              |       0 |        | \x     |        |            |
   18017 |  242323 |          0 | RI_ConstraintTrigger_a_18017 |    1646 |      9 | O         | t            |        242376 |        281836 |        18016 | f            | f              |       0 |        | \x     |        |            |
   18018 |  242323 |          0 | RI_ConstraintTrigger_a_18018 |    1647 |     17 | O         | t            |        242376 |        281836 |        18016 | f            | f              |       0 |        | \x     |        |            |
   18019 |  242376 |          0 | RI_ConstraintTrigger_c_18019 |    1644 |      5 | O         | t            |        242323 |        281836 |        18016 | f            | f              |       0 |        | \x     |        |            |
   18020 |  242376 |          0 | RI_ConstraintTrigger_c_18020 |    1645 |     17 | O         | t            |        242323 |        281836 |        18016 | f            | f              |       0 |        | \x     |        |            |
   18022 |  242521 |          0 | RI_ConstraintTrigger_a_18022 |    1654 |      9 | O         | t            |                
242376 |        281882 |        18021 | f            | f              |       0 |        | \x     |        |            |
   18023 |  242521 |          0 | RI_ConstraintTrigger_a_18023 |    1655 |     17 | O         | t            |        242376 |        281882 |        18021 | f            | f              |       0 |        | \x     |        |            |
   18024 |  242376 |          0 | RI_ConstraintTrigger_c_18024 |    1644 |      5 | O         | t            |        242521 |        281882 |        18021 | f            | f              |       0 |        | \x     |        |            |
   18025 |  242376 |          0 | RI_ConstraintTrigger_c_18025 |    1645 |     17 | O         | t            |        242521 |        281882 |        18021 | f            | f              |       0 |        | \x     |        |            |
   18027 |  242521 |          0 | RI_ConstraintTrigger_a_18027 |    1654 |      9 | O         | t            |        242544 |        281882 |        18026 | f            | f              |       0 |        | \x     |        |            |
   18028 |  242521 |          0 | RI_ConstraintTrigger_a_18028 |    1655 |     17 | O         | t            |        242544 |        281882 |        18026 | f            | f              |       0 |        | \x     |        |            |
   18029 |  242544 |          0 | RI_ConstraintTrigger_c_18029 |    1644 |      5 | O         | t            |        242521 |        281882 |        18026 | f            | f              |       0 |        | \x     |        |            |
   18030 |  242544 |          0 | RI_ConstraintTrigger_c_18030 |    1645 |     17 | O         | t            |        242521 |        281882 |        18026 | f            | f              |       0 |        | \x     |        |            |
   18032 |  242521 |          0 | RI_ConstraintTrigger_a_18032 |    1646 |      9 | O         | t            |        242551 |        281882 |        18031 | f            | f              |       0 |        | \x     |        |            |
   18033 |  242521 |          0 | RI_ConstraintTrigger_a_18033 |    1647 |     17 | O         | t            |        242551 |        281882 |        18031 | f            | f              |       0 |        | \x     |        |            |
   18034 |  242551 |          0 | RI_ConstraintTrigger_c_18034 |    1644 |      5 | O         | t            |        242521 |        281882 |        18031 | f            | f              |       0 |        | \x     |        |            |
   18035 |  242551 |          0 | RI_ConstraintTrigger_c_18035 |    1645 |     17 | O         | t            |        242521 |        281882 |        18031 | f            | f              |       0 |        | \x     |        |            |
   18037 |  242521 |          0 | RI_ConstraintTrigger_a_18037 |    1646 |      9 | O         | t            |        242557 |        281882 |        18036 | f            | f              |       0 |        | \x     |        |            |
   18038 |  242521 |          0 | RI_ConstraintTrigger_a_18038 |    1647 |     17 | O         | t            |        242557 |        281882 |        18036 | f            | f              |       0 |        | \x     |        |            |
   18039 |  242557 |          0 | RI_ConstraintTrigger_c_18039 |    1644 |      5 | O         | t            |        242521 |        281882 |        18036 | f            | f              |       0 |        | \x     |        |            |
   18040 |  242557 |          0 | RI_ConstraintTrigger_c_18040 |    1645 |     17 | O         | t            |        242521 |        281882 |        18036 | f            | f              |       0 |        | \x     |        |            |
   18042 |  242521 |          0 | RI_ConstraintTrigger_a_18042 |    1654 |      9 | O         | t            |        242557 |        281882 |        18041 | f            | f              |       0 |        | \x     |        |            |
   18043 |  242521 |          0 | RI_ConstraintTrigger_a_18043 |    1655 |     17 | O         | t            |        242557 |        281882 |        18041 | f            | f              |       0 |        | \x     |        |            |
   18044 |  242557 |          0 | RI_ConstraintTrigger_c_18044 |    1644 |      5 | O         | t            |        242521 |        281882 |        18041 | f            | f              |       0 |        | \x     |        |            |
   18045 |  242557 |          0 | RI_ConstraintTrigger_c_18045 |    1645 |     17 | O         | t            |        242521 |        281882 |        18041 | f            | f              |       0 |        | \x     |        |            |
   18047 |  242428 |          0 | RI_ConstraintTrigger_a_18047 |    1646 |      9 | O         | t            |        242548 |        281858 |        18046 | f            | f              |       0 |        | \x     |        |            |
   18048 |  242428 |          0 | RI_ConstraintTrigger_a_18048 |    1647 |     17 | O         | t            |        242548 |        281858 |        18046 | f            | f              |       0 |        | \x     |        |            |
   18049 |  242548 |          0 | RI_ConstraintTrigger_c_18049 |    1644 |      5 | O         | t            |        242428 |        281858 |        18046 | f            | f              |       0 |        | \x     |        |            |
   18050 |  242548 |          0 | RI_ConstraintTrigger_c_18050 |    1645 |     17 | O         | t            |        242428 |        281858 |        18046 | f            | f              |       0 |        | \x     |        |            |
   18052 |  242521 |          0 | RI_ConstraintTrigger_a_18052 |    1646 |      9 | O         | t            |
18053 |  242521 |          0 | RI_ConstraintTrigger_a_18053 |    1647 |     17 | O         | t            |        242548 |        281882 |        18051 | f            | f              |       0 |        | \x     |        |            |
   18054 |  242548 |          0 | RI_ConstraintTrigger_c_18054 |    1644 |      5 | O         | t            |        242521 |        281882 |        18051 | f            | f              |       0 |        | \x     |        |            |
   18055 |  242548 |          0 | RI_ConstraintTrigger_c_18055 |    1645 |     17 | O         | t            |        242521 |        281882 |        18051 | f            | f              |       0 |        | \x     |        |            |
 5187213 |  242263 |          0 | idfilepath_update_trigger    | 5187212 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
 5187215 |  242263 |          0 | idfilepath_delete_trigger    | 5187214 |     11 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
 5187217 |  242263 |          0 | idfilepath_insert_trigger    | 5187216 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
 5187302 |  242428 |          0 | idprop_insert_trigger        | 5187301 |      5 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
 5187304 |  242428 |          0 | idprop_update_trigger        | 5187303 |     17 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
 5187306 |  242428 |          0 | idprop_delete_trigger        | 5187305 |     11 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |
 5995320 | 5978789 |          0 | idsearchdata_delete_trigger  | 5995319 |     11 | O         | f            |             0 |             0 |            0 | f            | f              |       0 |        | \x     |        |            |

offenbar also nur Constraint-Trigger bis auf idfilepath*, idsearchdate* und idprop* - und da gibt es gleichlautende Tabellen.

Ich hatte die letzte Abstürze mehr oder minder kurz davor in pg_top noch geguckt.
Dabei war ich irgendwie doch überrascht, dass es zum Absturz kam, denn

Memory free war auch so in etwa in dem Bereich wie jetzt, Swap ging plötzlich relativ schnell gegen ß. Aber der Wert “21G cached” war fast unverändert. Da wäre ich der Meinung so lange noch 20G RAM als Cache da sind, ist noch Speicher ohne Ende frei. Also irgendwie für mich kein wirklich klares Bild. Ja im Log hieß es Swap 0. So lange RAM da ist, ist doch Swap egal.

Es sind so aus, dass jetzt ohne Absturz 15449 Datensätze verarbeitet werden können.
Die Anwendung ist mit den Vorschaubildern fertig und Speicher RAM/ Swap wird immer mehr frei.

Warum sehe ich 2x den autovacuum worker bei der einer Datenbank?

 2631              13G  301M active  53:42  53:42   0.2     2 postgres: 14/main: autovacuum worker photosupreme_thumbs
   2398              13G   10G active  63:18  63:18   1.0     7 postgres: 14/main: autovacuum worker photosupreme
   2419              13G 8857M active  62:18  62:18   3.2     4 postgres: 14/main: autovacuum worker photosupreme

Hmm, die “xxx_delete_trigger”, “xxx_insert_trigger” und “xxx_update_trigger” wurden von der Anwendung angelegt. Was machen die?

keine Ahnung - wie gesagt ich bin nur der User. (Vermutung - wenn ich in der Anwendung die Verzeichnisse, Platte ändere wird überall die GUID verändert werden müssen. Vielleicht so etwas)
In der idFilePath Tabelle werden die Verzeichnisse verwaltet. Dabei geht der Entwickler hin und arbeitet mit GUID’s die aufeinander aufbauen um von der Hauptplatte bis zum letzten Verzeichnis durchzukommen

Vielleicht noch ein anderer - am Ende unbedeutender Hinweis. Die Anwendung habe ich in 2 Versionen. Eine eben Klient-Server mit PostgreSQL und eine Standalone die unter Windows SQLite Datenbank nutzt. Gut jetzt SQLite ist ein wenig anders, aber da habe ich diese Probleme nicht. Ich kann auf ein Schlag beliebig viele Datensätze verarbeiten lassen.

Mit pg_dump bekommst Du die Triggerdefinition und die den Sourcecode der dazu gehörigen Triggerfunktion.

1 „Gefällt mir“

Ich hoffe alle erwischt zu haben.

--
-- Name: idfilepath_delete_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idfilepath_delete_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
begin 
  delete  
    from idCache_FilePath t 
  where t.FilePathGUID in (select GUID from FilePathChildList(old.GUID)); 
 
  return old; 
end; 
$$;


ALTER FUNCTION public.idfilepath_delete_trigger_fnc() OWNER TO idimager_main;

--
-- Name: idfilepath_insert_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idfilepath_insert_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
declare 
  rRow record; 
begin 
  for rRow in 
	select *  
	from FilePathParentList(new.GUID)  
	order by idLevel  
	limit 1 
  loop 
    delete  
    from idCache_FilePath t 
    where t.FilePathGUID = rRow.FilePathGUID; 
 
    begin 
      insert 
	    into idCache_FilePath (FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) 
      values (rRow.FilePathGUID, rRow.idLevel, rRow.GUID, rRow.ParentGUID, rRow.FilePath, rRow.RootGUID); 
    exception when unique_violation then 
	  -- Ignore duplicate inserts. 
    end; 
  end loop; 
   
  return new; 
end; 
$$;


ALTER FUNCTION public.idfilepath_insert_trigger_fnc() OWNER TO idimager_main;

--
-- Name: idfilepath_update_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idfilepath_update_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
declare 
  rChildRow record; 
  rRow record; 
begin 
  if (old.ParentGUID <> new.ParentGUID) or 
     (old.FolderName <> new.FolderName) 
  then 
    if exists (select 1 from idCache_FilePath  where FilePathGUID = old.GUID) then 
    --update 
      for rChildRow in 
      select *  
      from FilePathChildList(new.GUID)  
      loop 
        for rRow in 
        select *  
        from FilePathParentList(rChildRow.GUID)  
        order by idLevel  
        limit 1 
        loop 
          update idCache_FilePath 
            set idLevel = rRow.idLevel,  
                GUID = rRow.GUID,  
                ParentGUID = rRow.ParentGUID,  
                FilePath = rRow.FilePath, 
                RootGUID = rRow.RootGUID 
          where 
            FilePathGUID = rRow.FilePathGUID; 
        end loop;	   
      end loop; 
  else 
    --insert 
      for rRow in 
      select *  
      from FilePathParentList(new.GUID)  
      order by idLevel  
      limit 1 
      loop 
        begin 
          insert 
          into idCache_FilePath (FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) 
          values (rRow.FilePathGUID, rRow.idLevel, rRow.GUID, rRow.ParentGUID, rRow.FilePath, rRow.RootGUID); 
        exception when unique_violation then 
          -- Ignore duplicate inserts. 
        end; 
      end loop; 
  end if; 
  end if; 
  
  return new; 
end; 
$$;


ALTER FUNCTION public.idfilepath_update_trigger_fnc() OWNER TO idimager_main;

--
-- Name: idprop_delete_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idprop_delete_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
begin 
  delete  
    from idCache_Prop t 
  where t.PropGUID = old.GUID; 
 
  return old; 
end; 
$$;


ALTER FUNCTION public.idprop_delete_trigger_fnc() OWNER TO idimager_main;

--
-- Name: idprop_insert_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idprop_insert_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
begin 
  delete 
  from idCache_Prop t 
  where t.PropGUID = new.GUID; 
  
  begin 
    insert 
      into idCache_Prop (PropGUID, CategoryGUID) 
    values ( 
             new.GUID,  
             (  
               WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS  
               ( 
                 SELECT 0 AS idlevel, 
                   px.guid, 
                   px.parentguid 
                 FROM idprop px 
                 WHERE px.guid = new.GUID 
                 UNION ALL 
                 SELECT hp_1.idlevel - 1 AS idlevel, 
                   hp_1.guid, 
                   px.parentguid 
                 FROM idprop px, 
                      higherparent hp_1 
                 WHERE px.guid = hp_1.parentguid 
                ) 
                SELECT hp.parentguid 
                FROM higherparent hp 
                WHERE hp.guid = new.GUID 
                ORDER BY hp.idlevel 
                LIMIT 1 
             ) 
           ); 
  exception when unique_violation then 
    -- Ignore duplicate inserts. 
  end; 
  return new; 
end; 
$$;


ALTER FUNCTION public.idprop_insert_trigger_fnc() OWNER TO idimager_main;

--
-- Name: idprop_update_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idprop_update_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
begin 
  if (old.ParentGUID <> new.ParentGUID)  
  then 
    if exists (select 1 from idCache_Prop where PropGUID = old.GUID) then 
      --update 
      update idCache_Prop 
        set PropGUID = new.GUID,  
            CategoryGUID =              (  
               WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS  
               ( 
                 SELECT 0 AS idlevel, 
                   px.guid, 
                   px.parentguid 
                 FROM idprop px 
                 WHERE px.guid = new.GUID 
                 UNION ALL 
                 SELECT hp_1.idlevel - 1 AS idlevel, 
                   hp_1.guid, 
                   px.parentguid 
                 FROM idprop px, 
                      higherparent hp_1 
                 WHERE px.guid = hp_1.parentguid 
                ) 
                SELECT hp.parentguid 
                FROM higherparent hp 
                WHERE hp.guid = new.GUID 
                ORDER BY hp.idlevel 
                LIMIT 1 
             ) 
 
      where 
        PropGUID = old.GUID; 
  else 
      --insert 
      begin 
        insert 
          into idCache_Prop (PropGUID, CategoryGUID) 
        values ( 
                 new.GUID,  
                 (  
                   WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS  
                   ( 
                     SELECT 0 AS idlevel, 
                       px.guid, 
                       px.parentguid 
                     FROM idprop px 
                     WHERE px.guid = new.GUID 
                     UNION ALL 
                     SELECT hp_1.idlevel - 1 AS idlevel, 
                       hp_1.guid, 
                       px.parentguid 
                     FROM idprop px, 
                          higherparent hp_1 
                     WHERE px.guid = hp_1.parentguid 
                    ) 
                    SELECT hp.parentguid 
                    FROM higherparent hp 
                    WHERE hp.guid = new.GUID 
                    ORDER BY hp.idlevel 
                    LIMIT 1 
                 ) 
               ); 
      exception when unique_violation then 
        -- Ignore duplicate inserts. 
      end; 
  end if; 
  end if; 
  
  return new; 
end; 
$$;


ALTER FUNCTION public.idprop_update_trigger_fnc() OWNER TO idimager_main;

--
-- Name: idsearchdata_delete_trigger_fnc(); Type: FUNCTION; Schema: public; Owner: idimager_main
--

CREATE FUNCTION public.idsearchdata_delete_trigger_fnc() RETURNS trigger
    LANGUAGE plpgsql
    AS $$ 
begin 
  delete  
    from idSearchDataValue 
  where GUID = old.GUID; 
 
  return old; 
end; 
$$;


ALTER FUNCTION public.idsearchdata_delete_trigger_fnc() OWNER TO idimager_main;

SET default_tablespace = '';

SET default_table_access_method = heap;


--
-- Name: idfilepath idfilepath_delete_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idfilepath_delete_trigger BEFORE DELETE ON public.idfilepath FOR EACH ROW EXECUTE FUNCTION public.idfilepath_delete_trigger_fnc();


--
-- Name: idfilepath idfilepath_insert_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idfilepath_insert_trigger AFTER INSERT ON public.idfilepath FOR EACH ROW EXECUTE FUNCTION public.idfilepath_insert_trigger_fnc();


--
-- Name: idfilepath idfilepath_update_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idfilepath_update_trigger AFTER UPDATE ON public.idfilepath FOR EACH ROW EXECUTE FUNCTION public.idfilepath_update_trigger_fnc();


--
-- Name: idprop idprop_delete_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idprop_delete_trigger BEFORE DELETE ON public.idprop FOR EACH ROW EXECUTE FUNCTION public.idprop_delete_trigger_fnc();


--
-- Name: idprop idprop_insert_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idprop_insert_trigger AFTER INSERT ON public.idprop FOR EACH ROW EXECUTE FUNCTION public.idprop_insert_trigger_fnc();


--
-- Name: idprop idprop_update_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idprop_update_trigger AFTER UPDATE ON public.idprop FOR EACH ROW EXECUTE FUNCTION public.idprop_update_trigger_fnc();


--
-- Name: idsearchdata idsearchdata_delete_trigger; Type: TRIGGER; Schema: public; Owner: idimager_main
--

CREATE TRIGGER idsearchdata_delete_trigger BEFORE DELETE ON public.idsearchdata FOR EACH ROW EXECUTE FUNCTION public.idsearchdata_delete_trigger_fnc();

Ich vermute mal das Speicherproblem wird durch das Aufsammeln von Werten in FilePathParentList und FilePathChildList sowie den diversen rekursiven Abfragen verursacht (ich vermute die beiden Funktionen nutzen auch rekursive Abfragen). Die unsäglichen FOR loops zum Einfügen oder UPDATE von einzelnen Datensätze machen das Ganze auch nicht besser. Ich vermute das gibt es alles in der SQLite DB so nicht (zumindest gibt es keine Set returning functions) und deswegen entsteht dort auch kein Speicherproblem.

1 „Gefällt mir“

Wie tiefgehend ist diese Vermutung? Ich kann den Entwickler anschreiben, da er sagen wir bei solchen Sachen nicht gleich “super toll, mache ich mich an die Arbeit” schreit … möchte ich vermeiden, dass die Diskussion mit ihn sich noch extra schwerer als sonst gestaltet :slight_smile:

So sehen die Trigger in der SQLite Version aus:

CREATE TRIGGER t_bd_idUser
before delete on idUser
begin
  delete from idCatalogItem where 
UserGUID = old.GUID
;
  delete from idProp where 
UserGUID = old.GUID
;
  delete from idPropGroup where 
UserGUID = old.GUID
;
  delete from idUserProp where 
UserGUID = old.GUID
;
  delete from idImageCard where 
UserGUID = old.GUID
;
  delete from idImageSubscription where 
UserGUID = old.GUID
;
  delete from idUserFavorite where 
UserGUID = old.GUID
;
  delete from idUserUsage where 
UserGUID = old.GUID
;
  delete from idUserUser where 
UserGUID = old.GUID
;
  delete from idUserUser where 
UserGUID2 = old.GUID
;
  delete from idImageBoxUsage where 
UserGUID = old.GUID
;
  delete from idImageModel where 
UserGUID = old.GUID
;
  delete from idImageScore where 
UserGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idPropGroup
before insert on idPropGroup
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idPropGroup
before update of GUID,UserGUID on idPropGroup
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bd_idPropGroup
before delete on idPropGroup
begin
  delete from idPropGroupPathContent where 
GUID = old.GUID
;
  delete from idPropGroupPeriodContent where 
GUID = old.GUID
;
  delete from idPropGroupContent where 
GUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idPropGroupPathContent
before insert on idPropGroupPathContent
begin
  select case when
    ((select 1 from idPropGroup where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPropGroup'
                )
  end;
end;
CREATE TRIGGER t_bu_idPropGroupPathContent
before update of FilePath,GUID,PropType on idPropGroupPathContent
begin
  select case when
    ((select 1 from idPropGroup where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPropGroup'
                )
  end;
end;
CREATE TRIGGER t_bi_idPropGroupPeriodContent
before insert on idPropGroupPeriodContent
begin
  select case when
    ((select 1 from idPropGroup where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPropGroup'
                )
  end;
end;
CREATE TRIGGER t_bu_idPropGroupPeriodContent
before update of GUID,Period,PropType on idPropGroupPeriodContent
begin
  select case when
    ((select 1 from idPropGroup where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPropGroup'
                )
  end;
end;
CREATE TRIGGER t_bi_idPropGroupContent
before insert on idPropGroupContent
begin
  select case when
    ((select 1 from idPropGroup where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPropGroup'
                )
  end;
  select case when
    ((select 1 from idProp where 
GUID = new.PropGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
end;
CREATE TRIGGER t_bu_idPropGroupContent
before update of GUID,PropGUID,PropType on idPropGroupContent
begin
  select case when
    ((select 1 from idPropGroup where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPropGroup'
                )
  end;
  select case when
    ((select 1 from idProp where 
GUID = new.PropGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
end;
CREATE TRIGGER t_bi_idCatalogItemDefinition
before insert on idCatalogItemDefinition
begin
  select case when
    ((select 1 from idProp where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.CatalogItemGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bu_idCatalogItemDefinition
before update of CatalogItemGUID,GUID on idCatalogItemDefinition
begin
  select case when
    ((select 1 from idProp where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.CatalogItemGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bi_idUserProp
before insert on idUserProp
begin
  select case when
    ((select 1 from idProp where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idUserProp
before update of GUID,UserGUID on idUserProp
begin
  select case when
    ((select 1 from idProp where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bi_idImageCard
before insert on idImageCard
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageCard
before update of GUID,ImageGUID,UserGUID on idImageCard
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bi_idImageSubscription
before insert on idImageSubscription
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageSubscription
before update of GUID,ImageGUID,UserGUID on idImageSubscription
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bi_idImageIPTC
before insert on idImageIPTC
begin
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageIPTC
before update of GUID,ImageGUID on idImageIPTC
begin
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bd_idPlaceHolder
before delete on idPlaceHolder
begin
  delete from idVersionPlaceHolder where 
PlaceHolderGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idVersionPlaceHolder
before insert on idVersionPlaceHolder
begin
  select case when
    ((select 1 from idPlaceHolder where 
GUID = new.PlaceHolderGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPlaceHolder'
                )
  end;
  select case when
    ((select 1 from idImageVersion where 
GUID = new.ItemGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageVersion'
                )
  end;
end;
CREATE TRIGGER t_bu_idVersionPlaceHolder
before update of ItemGUID,PlaceHolderGUID on idVersionPlaceHolder
begin
  select case when
    ((select 1 from idPlaceHolder where 
GUID = new.PlaceHolderGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idPlaceHolder'
                )
  end;
  select case when
    ((select 1 from idImageVersion where 
GUID = new.ItemGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageVersion'
                )
  end;
end;
CREATE TRIGGER t_bi_idImageComment
before insert on idImageComment
begin
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageComment
before update of GUID,ImageGUID on idImageComment
begin
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bi_idUserFavorite
before insert on idUserFavorite
begin
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idUserFavorite
before update of GUID,ImageGUID,UserGUID on idUserFavorite
begin
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bi_idUserUsage
before insert on idUserUsage
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idUserUsage
before update of GUID,UserGUID on idUserUsage
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bi_idUserUser
before insert on idUserUser
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID2
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idUserUser
before update of GUID,UserGUID,UserGUID2 on idUserUser
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID2
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bd_idImageBox
before delete on idImageBox
begin
  delete from idImageBoxUsage where 
BoxGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageBoxUsage
before insert on idImageBoxUsage
begin
  select case when
    ((select 1 from idImageBox where 
GUID = new.BoxGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageBox'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageBoxUsage
before update of BoxGUID,UserGUID on idImageBoxUsage
begin
  select case when
    ((select 1 from idImageBox where 
GUID = new.BoxGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageBox'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bi_idImageModel
before insert on idImageModel
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageModel
before update of GUID,UserGUID on idImageModel
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bd_idImageModel
before delete on idImageModel
begin
  delete from idImageGallery where 
ModelGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageGallery
before insert on idImageGallery
begin
  select case when
    ((select 1 from idImageModel where 
GUID = new.ModelGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageModel'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageGallery
before update of GUID,ModelGUID on idImageGallery
begin
  select case when
    ((select 1 from idImageModel where 
GUID = new.ModelGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageModel'
                )
  end;
end;
CREATE TRIGGER t_bd_idImageGallery
before delete on idImageGallery
begin
  delete from idImageCollection where 
GalleryGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageCollection
before insert on idImageCollection
begin
  select case when
    ((select 1 from idImageGallery where 
GUID = new.GalleryGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageGallery'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageCollection
before update of GalleryGUID,GUID on idImageCollection
begin
  select case when
    ((select 1 from idImageGallery where 
GUID = new.GalleryGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageGallery'
                )
  end;
end;
CREATE TRIGGER t_bd_idImageCollection
before delete on idImageCollection
begin
  delete from idImageCollectionItem where 
GUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageCollectionItem
before insert on idImageCollectionItem
begin
  select case when
    ((select 1 from idImageCollection where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageCollection'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ItemGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageCollectionItem
before update of GUID,ItemGUID on idImageCollectionItem
begin
  select case when
    ((select 1 from idImageCollection where 
GUID = new.GUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageCollection'
                )
  end;
  select case when
    ((select 1 from idCatalogItem where 
GUID = new.ItemGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idCatalogItem'
                )
  end;
end;
CREATE TRIGGER t_bd_idImageCollectionItem
before delete on idImageCollectionItem
begin
  delete from idImageScore where 
ContestGUID = old.GUID
and ImageGUID = old.ItemGUID
;
end;
CREATE TRIGGER t_bi_idImageScore
before insert on idImageScore
begin
  select case when
    ((select 1 from idImageCollectionItem where 
GUID = new.ContestGUID
and ItemGUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageCollectionItem'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idImageScore
before update of ContestGUID,GUID,ImageGUID,UserGUID on idImageScore
begin
  select case when
    ((select 1 from idImageCollectionItem where 
GUID = new.ContestGUID
and ItemGUID = new.ImageGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idImageCollectionItem'
                )
  end;
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bi_idPropRelation
before insert on idPropRelation
begin
  select case when
    ((select 1 from idProp where 
GUID = new.ParentGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
  select case when
    ((select 1 from idProp where 
GUID = new.ChildGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
end;
CREATE TRIGGER t_bu_idPropRelation
before update of ChildGUID,GUID,ParentGUID on idPropRelation
begin
  select case when
    ((select 1 from idProp where 
GUID = new.ParentGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
  select case when
    ((select 1 from idProp where 
GUID = new.ChildGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idProp'
                )
  end;
end;
CREATE TRIGGER t_bi_idProp
before insert on idProp
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bu_idProp
before update of GUID,ParentGUID,UserGUID on idProp
begin
  select case when
    ((select 1 from idUser where 
GUID = new.UserGUID
) is null)
    then raise (abort,
                   'Foreign key violation: no record in idUser'
                )
  end;
end;
CREATE TRIGGER t_bi_idImageArea

before insert on idImageArea

begin

  select case when

    ((select 1 from idProp where 

GUID = new.PropGUID

) is null)

 and  (IfNull(new.PropGUID, '') <> '')

    then raise (abort,

                   'Foreign key violation: no record in idProp'

                )

  end;

end;
CREATE TRIGGER t_bu_idImageArea

before update of GUID,ImageGUID,PropGUID on idImageArea

begin

  select case when

    ((select 1 from idProp where 

GUID = new.PropGUID

) is null)

 and  (IfNull(new.PropGUID, '') <> '')

    then raise (abort,

                   'Foreign key violation: no record in idProp'

                )

  end;

end;
CREATE TRIGGER [t_bu_idCatalogItem] BEFORE UPDATE OF [GUID], [PathGUID], [UserGUID] ON [idCatalogItem] 
BEGIN 
  SELECT CASE WHEN ((SELECT 1 
  FROM   [idUser] 
  WHERE  [GUID] = [new].[UserGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idUser') END; 
  SELECT CASE WHEN ((SELECT 1 
  FROM   [idFilePath] 
  WHERE  [GUID] = [new].[PathGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idFilePath') END; 
END;
CREATE TRIGGER [t_bi_idCatalogItem] BEFORE INSERT ON [idCatalogItem] 
BEGIN 
  SELECT CASE WHEN ((SELECT 1 
  FROM   [idUser] 
  WHERE  [GUID] = [new].[UserGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idUser') END; 
  SELECT CASE WHEN ((SELECT 1 
  FROM   [idFilePath] 
  WHERE  [GUID] = [new].[PathGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idFilePath') END; 
END;
CREATE TRIGGER [t_bd_idCatalogItem] BEFORE DELETE ON [idCatalogItem] 
BEGIN 
  DELETE FROM 
    [idCatalogItemDefinition] 
  WHERE 
    [CatalogItemGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageCard] 
  WHERE 
    [ImageGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageSubscription] 
  WHERE 
    [ImageGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageIPTC] 
  WHERE 
    [ImageGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageVersion] 
  WHERE 
    [MainImageGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageComment] 
  WHERE 
    [ImageGUID] = [old].[GUID]; 
  DELETE FROM 
    [idUserFavorite] 
  WHERE 
    [ImageGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageCollectionItem] 
  WHERE 
    [ItemGUID] = [old].[GUID]; 
  DELETE FROM 
    [idImageArea] 
  WHERE 
    [ImageGUID] = [old].[GUID]; 
END;
CREATE TRIGGER t_bd_idImageVersion 
before delete on idImageVersion 
begin 
  delete from idVersionPlaceHolder where  
ItemGUID = old.GUID 
; 
  delete from idImageArea where  
ImageGUID = old.GUID 
; 
end;
CREATE TRIGGER t_bi_idImageVersion 
before insert on idImageVersion 
begin 
  select case when 
    ((select 1 from idCatalogItem where  
GUID = new.MainImageGUID 
) is null) 
    then raise (abort, 
                   'Foreign key violation: no record in idCatalogItem' 
                ) 
  end; 
  select case when 
    ((select 1 from idFilePath where  
GUID = new.PathGUID 
) is null) 
    then raise (abort, 
                   'Foreign key violation: no record in idFilePath' 
                ) 
  end; 
end;
CREATE TRIGGER t_bu_idImageVersion 
before update of GUID,MainImageGUID,PathGUID on idImageVersion 
begin 
  select case when 
    ((select 1 from idCatalogItem where  
GUID = new.MainImageGUID 
) is null) 
    then raise (abort, 
                   'Foreign key violation: no record in idCatalogItem' 
                ) 
  end; 
  select case when 
    ((select 1 from idFilePath where  
GUID = new.PathGUID 
) is null) 
    then raise (abort, 
                   'Foreign key violation: no record in idFilePath' 
                ) 
  end; 
end;
CREATE TRIGGER t_bd_idPropCategory
before delete on idPropCategory
begin
  delete from idProp where ParentGUID = old.GUID;
end;
CREATE TRIGGER t_au_idFilePath  
after update on idFilePath 
for each row 
when ( 
       (old.ParentGUID <> new.ParentGUID) or 
       (old.FolderName <> new.FolderName) 
     ) and ( 
       exists (select 1 from idCache_FilePath  where FilePathGUID = new.GUID) 
     ) 
begin 
  -- update 
  update idCache_FilePath 
    set (idLevel, GUID, ParentGUID, FilePath, RootGUID) =  
        ( 
          select idLevel, GUID, ParentGUID, FilePath, RootGUID  
          from ( 
                 with recursive higherparent(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) as      
                 (     
                   select fp.GUID, 0, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator as FilePath, fp.ParentGUID    
                   from   idFilePath fp   
                   inner join idMediumInfo m on m.GUID = fp.MediumGUID   
                   where  fp.GUID = idCache_FilePath.FilePathGUID  
                   union all  
                   select hp.FilePathGUID, hp.idLevel-1, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator || hp.FilePath as FilePath, hp.ParentGUID    
                   from   higherparent hp  
                   inner join idFilePath fp on fp.GUID = hp.ParentGUID  
                   inner join idMediumInfo m on m.GUID = fp.MediumGUID    
                 )     
                 select *     
                 from higherparent  
                 order by idLevel  
                 limit 1 
               ) 
        ) 
    where  
      FilePathGUID in ( 
                        select GUID 
                        from (  
                          with recursive lowerparent(FilePathGUID, idLevel, GUID, ParentGUID) as   
                          (   
                            select fp.GUID, 0, fp.GUID, fp.ParentGUID   
                            from   idFilePath fp   
                            where  fp.GUID = new.GUID  
                            union all   
                            select lp.FilePathGUID, lp.idLevel+1, fp.GUID, fp.ParentGUID   
                            from   idFilePath fp, lowerparent lp   
                            where  fp.ParentGUID = lp.GUID   
                          )   
                          select * 
                          from lowerparent 
                        )        
                      ); 
end
;
CREATE TRIGGER t_au2_idFilePath  
after update on idFilePath 
for each row 
when not exists (select 1 from idCache_FilePath where FilePathGUID = new.GUID) 
begin 
  -- insert 
  insert into idCache_FilePath 
      (FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID)  
      select FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID  
      from ( 
             with recursive higherparent(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) as      
             (     
               select fp.GUID, 0, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator as FilePath, fp.ParentGUID    
               from   idFilePath fp   
               inner join idMediumInfo m on m.GUID = fp.MediumGUID   
               where  fp.GUID = new.GUID  
               union all  
               select hp.FilePathGUID, hp.idLevel-1, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator || hp.FilePath as FilePath, hp.ParentGUID    
               from   higherparent hp  
               inner join idFilePath fp on fp.GUID = hp.ParentGUID  
               inner join idMediumInfo m on m.GUID = fp.MediumGUID    
             )     
             select *     
             from higherparent  
             order by idLevel  
             limit 1 
           ); 
end
;
CREATE TRIGGER t_ai_idFilePath  
after insert on idFilePath 
for each row 
when not exists (select 1 from idCache_FilePath where FilePathGUID = new.GUID) 
begin 
  -- insert 
  insert into idCache_FilePath 
      (FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID)  
      select FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID  
      from ( 
             with recursive higherparent(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) as      
             (     
               select fp.GUID, 0, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator as FilePath, fp.ParentGUID    
               from   idFilePath fp   
               inner join idMediumInfo m on m.GUID = fp.MediumGUID   
               where  fp.GUID = new.GUID  
               union all  
               select hp.FilePathGUID, hp.idLevel-1, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator || hp.FilePath as FilePath, hp.ParentGUID    
               from   higherparent hp  
               inner join idFilePath fp on fp.GUID = hp.ParentGUID  
               inner join idMediumInfo m on m.GUID = fp.MediumGUID    
             )     
             select *     
             from higherparent  
             order by idLevel  
             limit 1 
           ); 
end
;
CREATE TRIGGER t_bd_idFilePath  
before delete on idFilePath  
for each row 
begin  
  delete from idCatalogItem  
  where PathGUID = old.GUID; 
    
  delete from idImageVersion  
  where PathGUID = old.GUID; 
 
  delete from idCache_FilePath 
  where FilePathGUID in ( 
    select GUID  
    from ( 
  	  with recursive lowerparent(FilePathGUID, idLevel, GUID, ParentGUID) as   
        (   
          select fp.GUID, 0, fp.GUID, fp.ParentGUID   
          from   idFilePath fp   
          where  fp.GUID = old.GUID  
          union all   
          select lp.FilePathGUID, lp.idLevel+1, fp.GUID, fp.ParentGUID   
          from   idFilePath fp, lowerparent lp   
          where  fp.ParentGUID = lp.GUID   
        ) 
  	  select *   
  	  from lowerparent      
    ) 
  ); 
end;
CREATE TRIGGER t_ai_idProp   
after insert on idProp  
for each row  
when not exists (select 1 from idCache_Prop where PropGUID = new.GUID)  
begin  
  -- insert  
  insert 
    into idCache_Prop (PropGUID, CategoryGUID) 
  values ( 
           new.GUID,  
           (  
             WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS  
             ( 
               SELECT 0 AS idlevel, 
                 px.guid, 
                 px.parentguid 
               FROM idprop px 
               WHERE px.guid = new.GUID 
               UNION ALL 
               SELECT hp_1.idlevel - 1 AS idlevel, 
                 hp_1.guid, 
                 px.parentguid 
               FROM idprop px, 
                    higherparent hp_1 
               WHERE px.guid = hp_1.parentguid 
              ) 
              SELECT hp.parentguid 
              FROM higherparent hp 
              WHERE hp.guid = new.GUID 
              ORDER BY hp.idlevel 
              LIMIT 1 
           ) 
         ); 
end
;
CREATE TRIGGER t_au_idProp   
after update on idProp  
for each row  
when (  
       (old.ParentGUID <> new.ParentGUID)  
     ) and (  
       exists (select 1 from idCache_Prop  where PropGUID = new.GUID)  
     )  
begin  
  -- update  
  update idCache_Prop 
    set PropGUID = new.GUID,  
        CategoryGUID =              (  
           WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS  
           ( 
             SELECT 0 AS idlevel, 
               px.guid, 
               px.parentguid 
             FROM idprop px 
             WHERE px.guid = new.GUID 
             UNION ALL 
             SELECT hp_1.idlevel - 1 AS idlevel, 
               hp_1.guid, 
               px.parentguid 
             FROM idprop px, 
                  higherparent hp_1 
             WHERE px.guid = hp_1.parentguid 
            ) 
            SELECT hp.parentguid 
            FROM higherparent hp 
            WHERE hp.guid = new.GUID 
            ORDER BY hp.idlevel 
            LIMIT 1 
         ) 
  where 
    PropGUID = old.GUID; 
end
;
CREATE TRIGGER t_bd_idProp 
before delete on idProp 
begin 
  delete from idPropGroupContent where PropGUID = old.GUID; 
  delete from idCatalogItemDefinition where GUID = old.GUID; 
  delete from idUserProp where GUID = old.GUID; 
  delete from idPropRelation where ParentGUID = old.GUID; 
  delete from idPropRelation where ChildGUID = old.GUID; 
  delete from idImageArea where PropGUID = old.GUID; 
   
  delete  
    from idCache_Prop 
  where PropGUID = old.GUID; 
end;

Was mir noch einfiel in dem Zusammenhang bzgl der Trigger - kann der Entwickler der Anwendung in irgendeine Weise Freigabe des Speichers veranlassen?
Also wenn er - aus welchen Grund auch immer die Trigger nicht ändern kann - kann er etwas einbauen in Form - alle 10000 Datensätze benutzten RAM freigeben. ?