# Problem - Konfiguration oder Anwendung? **URL:** \ **Category:** Konfiguration & Verwaltung\ **Created:** [7. Januar 2023 um 21:37 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657 "2023-01-07T21:37:49Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 13:34 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/21 "2023-01-09T13:34:25Z")
Tja gebracht hat es leider nichts. ```auto 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 ``` ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 14:20 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/22 "2023-01-09T14:20:12Z")
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. ---
**Author:** ![akretschmer](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/a/a6a055/32.png) [@akretschmer](https://www.pg-forum.de/u/akretschmer)\ **Post date:** [9. Januar 2023 um 15:41 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/23 "2023-01-09T15:41:03Z")
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? ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 16:41 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/24 "2023-01-09T16:41:50Z")
> [@akretschmer](#): > > nicht aber ‘allokieren 10GB’ 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 > [@akretschmer](#): > > Hast Du irgend welche fremden Extensions via shared\_preload\_libraries eingebunden? 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) ```auto # ----------------------------- # 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 ``` ---
**Author:** ![castorp](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/c/e99b99/32.png) [@castorp](https://www.pg-forum.de/u/castorp)\ **Post date:** [9. Januar 2023 um 17:05 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/25 "2023-01-09T17:05:30Z")
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. ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 17:22 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/26 "2023-01-09T17:22:47Z")
> [@castorp](#): > > Wird das in der Datenbank verwendet? Das weiß ich natürlich nicht. Das sind die Tabellen. ```auto 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. > [@castorp](#): > > Eigentlich ist der Speicherverbrauch einer Transaktion relativ unabhängig von der Anzahl der Datensätze die davon betroffen sind. 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 ```auto 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. ---
**Author:** ![akretschmer](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/a/a6a055/32.png) [@akretschmer](https://www.pg-forum.de/u/akretschmer)\ **Post date:** [9. Januar 2023 um 17:26 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/27 "2023-01-09T17:26:59Z")
Trigger kannst Du grob auflisten via ```auto 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. ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 18:01 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/28 "2023-01-09T18:01:19Z")
> [@akretschmer](#): > > `select * from pg_trigger ;` ```auto 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 | ``` ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 18:01 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/29 "2023-01-09T18:01:50Z")
```auto 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 | | | ``` ---
**Author:** ![akretschmer](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/a/a6a055/32.png) [@akretschmer](https://www.pg-forum.de/u/akretschmer)\ **Post date:** [9. Januar 2023 um 18:13 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/30 "2023-01-09T18:13:50Z")
offenbar also nur Constraint-Trigger bis auf idfilepath\*, idsearchdate\* und idprop\* - und da gibt es gleichlautende Tabellen. ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 18:32 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/31 "2023-01-09T18:32:00Z")
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 ![09-01-_2023_19-26-41](https://www.pg-forum.de/uploads/default/original/1X/a2a542fa287fa5da6539ccac682c6a10dac45ab6.jpeg) 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. ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 18:37 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/32 "2023-01-09T18:37:00Z")
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? ```auto 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 ``` ---
**Author:** ![castorp](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/c/e99b99/32.png) [@castorp](https://www.pg-forum.de/u/castorp)\ **Post date:** [9. Januar 2023 um 18:53 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/33 "2023-01-09T18:53:20Z")
Hmm, die “xxx\_delete\_trigger”, “xxx\_insert\_trigger” und “xxx\_update\_trigger” wurden von der Anwendung angelegt. Was machen die? ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [9. Januar 2023 um 19:13 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/34 "2023-01-09T19:13:47Z")
> [@castorp](#): > > 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. ---
**Author:** ![castorp](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/c/e99b99/32.png) [@castorp](https://www.pg-forum.de/u/castorp)\ **Post date:** [9. Januar 2023 um 22:27 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/35 "2023-01-09T22:27:12Z")
> [@Robbi](#): > > keine Ahnung - wie gesagt ich bin nur der User. Mit pg\_dump bekommst Du die Triggerdefinition und die den Sourcecode der dazu gehörigen Triggerfunktion. ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [10. Januar 2023 um 06:19 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/36 "2023-01-10T06:19:56Z")
Ich hoffe alle erwischt zu haben. ```auto -- -- 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(); ``` ---
**Author:** ![castorp](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/c/e99b99/32.png) [@castorp](https://www.pg-forum.de/u/castorp)\ **Post date:** [10. Januar 2023 um 07:08 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/37 "2023-01-10T07:08:13Z")
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. ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [10. Januar 2023 um 07:45 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/38 "2023-01-10T07:45:28Z")
> [@castorp](#): > > Ich vermute mal das Speicherproblem wird durch das Aufsammeln von Werten 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 🙂 ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [10. Januar 2023 um 07:52 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/39 "2023-01-10T07:52:10Z")
So sehen die Trigger in der SQLite Version aus: ```auto 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; ``` ---
**Author:** ![Robbi](https://www.pg-forum.de/letter_avatar_proxy/v4/letter/r/eada6e/32.png) [@Robbi](https://www.pg-forum.de/u/Robbi)\ **Post date:** [10. Januar 2023 um 16:04 UTC](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657/40 "2023-01-10T16:04:03Z")
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. ? [Vorherige Seite](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657.md?page=1) [Nächste Seite](https://www.pg-forum.de/t/problem-konfiguration-oder-anwendung/12657.md?page=3)