Mit den Attachments die zugehörigen BLOBs löschen

Zusammenfassung

In #942 wurde untersucht, warum die Backup-Downtime des Produktivsystems immer länger wird. Dabei wurde festgestellt, dass beim Löschen von Attachments die dazugehörigen BLOBs aus der Tabelle pg_catalog.pg_largeobject nicht mehr gelöscht werden. Hintergrund war eine Änderung beim Löschen durch den Bugfix #868 (closed).

Schritte zum Reproduzieren

Mit diesem SQL-Befehl können Objekte in pg_largeobject aufgelistet werden, die keinen Verweis in attachments mehr haben.

SELECT l.oid
FROM   pg_largeobject_metadata l
WHERE (NOT EXISTS (SELECT 1 FROM attachment a WHERE a.content = l.oid));

Diese Liste sollte leer sein.

Wie verhält sich der aktuelle Fehler?

Die Attachments werden in den beiden Tabellen attachment und pg_largeobject gespeichert. Dabei referenziert attachment.content die Spalte pg_largeobject.loid. Dafür gibt es jedoch keinen FOREIGN KEY.

zustelldienst=> \d attachment
                Table "public.attachment"
    Column     |  Type  | Collation | Nullable | Default 
---------------+--------+-----------+----------+---------
 id            | bigint |           | not null | 
 attachment_id | uuid   |           | not null | 
 content       | oid    |           | not null | 
 fk_submission | bigint |           | not null | 
 content_size  | bigint |           | not null | 
Indexes:
    "attachment_pkey" PRIMARY KEY, btree (id)
    "attachment_attachment_id_idx" UNIQUE, btree (attachment_id)
Foreign-key constraints:
    "attachment_fk_submission_fkey" FOREIGN KEY (fk_submission) REFERENCES submission(id)

zustelldienst=> \d pg_largeobject
         Table "pg_catalog.pg_largeobject"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 loid   | oid     |           | not null | 
 pageno | integer |           | not null | 
 data   | bytea   |           | not null | 
Indexes:
    "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno)

Im Rahmen des Bugfixes #868 (closed) wurde das Löschen von Attachments auf folgendes SQL-Statement umgestellt.

SubmissionRepository.java, 43:

    @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Query(value = "WITH saa AS ( " +
        "  DELETE FROM submission_announced_attachments " +
        "    WHERE submission_id = :id), " +
        "     a AS ( " +
        "       DELETE " +
        "         FROM attachment " +
        "           WHERE fk_submission = :id) " +
        "UPDATE submission " +
        "SET encrypted_metadata = null, " +
        "    encrypted_data     = null, " +
        "    current_status     = 'DELETED', " +
        "    state_changed_at   = :stateChangedAt " +
        "WHERE id = :id ",
        nativeQuery = true)
    void clearSubmissionOfData(Long id, OffsetDateTime stateChangedAt);

Dieses löscht die Einträge aus submission_announced_attachments und attachment, nicht jedoch aus pg_largeobject.

Was ist das erwartete richtige Verhalten?

Siehe "Schritte zum Reproduzieren"

Relevante Protokolle/Screenshots/Anfragen

Akzeptanzkriterien zur Fehlerbehebung

1. Einträge aus pg_largeobject löschen, bevor die Referenzen aus attachment gelöscht werden
2. [ ] Herstellen einer FOREIGN KEY Beziehung (evtl. mit DELETE CASCADE?) - da lo_unlink benutzt wird, ist AK2 nicht notwendig.
3. Hinzufügen eines Tests, der eine Submission mit Attachment in die Datenbank einfügt, löscht und prüft, dass pg_largeobject leer ist