Problem - Konfiguration oder Anwendung?

Das weiß ich natürlich nicht.

Das sind die Tabellen.

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

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

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

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

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

Trigger kannst Du grob auflisten via

select * from pg_trigger ;

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

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

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

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

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

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

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

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

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

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

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

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

1 „Gefällt mir“

Ich hoffe alle erwischt zu haben.

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

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


ALTER FUNCTION public.idfilepath_delete_trigger_fnc() OWNER TO idimager_main;

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

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


ALTER FUNCTION public.idfilepath_insert_trigger_fnc() OWNER TO idimager_main;

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

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


ALTER FUNCTION public.idfilepath_update_trigger_fnc() OWNER TO idimager_main;

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

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


ALTER FUNCTION public.idprop_delete_trigger_fnc() OWNER TO idimager_main;

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

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


ALTER FUNCTION public.idprop_insert_trigger_fnc() OWNER TO idimager_main;

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

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


ALTER FUNCTION public.idprop_update_trigger_fnc() OWNER TO idimager_main;

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

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


ALTER FUNCTION public.idsearchdata_delete_trigger_fnc() OWNER TO idimager_main;

SET default_tablespace = '';

SET default_table_access_method = heap;


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

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


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

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


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

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


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

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


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

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


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

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


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

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

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

1 „Gefällt mir“

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

So sehen die Trigger in der SQLite Version aus:

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

before insert on idImageArea

begin

  select case when

    ((select 1 from idProp where 

GUID = new.PropGUID

) is null)

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

    then raise (abort,

                   'Foreign key violation: no record in idProp'

                )

  end;

end;
CREATE TRIGGER t_bu_idImageArea

before update of GUID,ImageGUID,PropGUID on idImageArea

begin

  select case when

    ((select 1 from idProp where 

GUID = new.PropGUID

) is null)

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

    then raise (abort,

                   'Foreign key violation: no record in idProp'

                )

  end;

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

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

Ich bin mir relativ sicher, dass es an den Triggern liegt.

In wie weit man das aber wirklich verbessern kann, kann ich so auf die Schnelle nicht sagen, dazu müsste man sich die Trigger mal genauer ansehen. Die vielen FOR loops sind auf jeden Fall schon mal eher zweifelhaft. Was die Funktionen genau machen, und ob man das optimieren kann, ist natürlich ohne den Code gar nicht zu sagen. Auffällig ist jedenfalls, dass die Trigger in SQLite deutlich weniger machen (und die Funktionen dort ja gar nicht existieren)

Nicht wirklich. Das Resultat der Funktion(en) muss so lange im Speicher gehalten werden wie die Trigger läuft. Danach wird der Speicher automatisch freigegeben.

Danke für Deine Hilfe.
Mal gucken, ich habe jetzt den Entwickler angeschrieben. Vermutlich kommt zwar “ja nee es ist alles super”, aber vielleicht wird es sich das konkrete Szenario doch anschauen.

Moin,
ich habe den Entwickler angeschrieben und auch eine Antwort bekommen. Er sieht keine Probleme mit seinen Triggern und For loops. In dem Zusammenhang meint er die Anwendung wurde etwas anders arbeiten als man es mit reinem auslesen der Datenbank vermuten lässt.

Weiter meinte er, es ist eher ein Problem von dem bei mir darunterliegendem Linux als mit der Datenbank und hat mir auch einen Link geschickt. Es geht dabei um das Out-Of-Memory wie schon Ihr beide es gemacht habt.

Was mir dabei überhaupt nicht klar ist, sind die Werte/ befehle mit “sysctl”, besonders die ersten 2.
Mein System liefert

cat /proc/sys/kernel/shmmax
18446744073692774399
cat /proc/sys/kernel/shmall
18446744073692774399

also doch viele größere Werte als es mit den Befehlen gesetzt werden. Erlaubt also Linux zu viel, weil es nun mal deutlich mehr als die 19GB bzw. 4MB die gesetzt werden?
(Zumal in der PostgreSQL Dokumentation zu Version 14 lese ich, dass die Standardwerte die neueres Linux Kernel liefert völlig OK sind , zumindest wenn man wie bei mir “dynamic_shared_memory_type = posix” stehen hat.

Nach seiner Aussage unter Windows hätte ich keine Probleme mit meiner Konfiguration und seiner Anwendung. Leider testen kann ich es nicht, Es wäre zumindest mit sehr großen Aufwand verbunden.

Es scheint ein unlösbares Problem zu sein.
Stelle ich bei der Linux VM den Parameter ein: “vm.overcommit_memory=2” wird nicht mehr der ganze Server wegen OOM abgeschossen , sondern einige Verbindungen der Anwendung.
Manchmal macht sich die Anwendung nichts draus. Manchmal zeigt die es schon als Fehler.

Ein versucht PostgreSQL auf einer Windows Server 2019 VM zu installieren brachte für mich auch auch keine Neue Erkenntnisse. SO bald eine Verbindung zwischen der Anwendung und Server steht und Daten fließen sollen, geht die CPU Last auf allen 8 Kernen auf dem Server auf 100% und alles geht unbrauchbar langsam.
Woran das liegt/ liegen kann habe ich auch nicht herausgefunden.
Irgendwo im Netz allerdings in Bezug auf PostgreSQL 9.x habe ich was von Problemen gelesen, wenn die Kodierung auf UTF8 steht. Der Server selbst hat zwar die Einstellung Windows1252 (oder so ähnlich) aber die Datenbank der Anwendung ist UTF8. Ob das das Problem sein kann… K.A

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.