Einzigartigkeit von Priorisierungen von Einträgen in Tabellen sicherstellen

Ich antworte später noch ausführlich auf deinen Beitrag.
Vorneweg:

Mir kommt es so vor, als brichst du hier die Normalisierung auf.
Mit “Suchindex” meinst du da den Gin Index in der Datenbank oder ist damit etwas anderes gemeint?
Wer fügt “in den Datensatz”/“in die Abfrage”/“in das Ergebnis” jeweils den richtigen Wert für “autorenid_815” ein?

Hier schon kurz die Antworten:

Ja, Suchindex steht synonym für eine extra Tabelle mit zwei Spalten (ID der Buchausgabe, tsvector) und einem Index auf dem tsvector der die normalisierten Suchbegriffe enthält. Ich hätte den tsvector auch in die Tabelle der Ausgaben geben können - Updates bei Änderung des Ausgabentitels schienen mir so aber einfacher.

Wer fügt “in den Datensatz”/“in die Abfrage”/“in das Ergebnis” jeweils den richtigen Wert für “autorenid_815” ein?

‘query rewriting’

SELECT DISTINCT ON (bookid) editionid
        FROM suchtabelle
            INNER JOIN ausgaben USING (editionid)
        WHERE textsearchable_index_gin @@ ts_rewrite(to_tsquery('simple','suchstring'), 'SELECT authorid, autorenname from authornames');

Problem dabei ist noch wie ich 815 zu authorenid_815 umwandeln kann. Geht das? Ansonten könnte ich notfalls eine Spalte anlegen die jeweils autorenid_ gefolgt von der eigentlichen ID enhält. Das wäre aber wohl gefrickel…

Mir ist gerade ein Fall in den Sinn gekommen, wo das so nicht mehr funktioniert - wenn ein Buchtitel den Namen eines Autoren enthält, zB ‘Und Nieztsche weinte’ müsste der Autorenname zumindest nicht gegen die ID ausgetausche, sondern lediglich um die ID erweitert werden, da sonst das Buch nicht gefunden würde.
Aber, würde nur der Nachname oder Vornamen eingegeben, findet ts_rewrite den Namen nicht mehr - außer ich setze auch einen Gin-Index auf die Autorennamen.
Blöde Sache das…

Merkst du was? :wink:

Ok, zurück zum Anfang: Was spricht gegen eine Tabelle mit Buchtiteln und eine mit Autoren, die jeweils extra Spalten für ggf. weitere Namen enthalten. Ein Trigger aktualisiert daraus eine weitere Spalte für die Volltextsuche.

Ob du das nun in eine extra Tabelle als “Suchindex” auslagerst oder in der gleichen Tabelle hälst, sei einmal dahin gestellt. Aber das Query Rewriting, was du da betreibst, bereitet dir im Endeffekt arge Probleme.

Das Problem, dass im schlimmsten Fall 25.000 bis 30.000 Einträge im für die Volltextsuche geändert werden müssten sobald ein Autorenname geändert wird bleibt jedoch bestehen. Shakespear wurde verdammt oft veröffentlicht…

Nun ja, die Änderung passiert aber in einer Transaktion, und danach greift Vacuum.
Hast du gemessen, dass so eine Änderung wirklich diese Beeinträchtigungen hat? Und wie viele Änderungen gibt es davon in der Stunde?

Zur Normalisierung gehört außerdem, das alle diese Veröffentlichungen einen eigenen Eintrag haben - dann änderst du ggf. nur diese Zeile, oder fügst eine neue hinzu.
Ich glaube immer noch, das dein Aufbrechen der Normalisierung dir mehr Probleme bereitet als das du damit löst.

Ach so, wollen wir das nächste Woche auf der Fachkonferenz mal durchdiskutieren? :wink:
Deutschsprachige PostgreSQL Konferenz

Inwiefern würde die Normalisierung aufgebrochen - mE wäre das Gegenteil der Fall?
Die Verzögerung wird ja erst richtig wirksam, da alle Begriffe nochmal normalisiert werden müssen für die Suche per Gin. Änderungen pro Stunde - sicher, wenige. Sollten aber mehrere Nutzer dann unter dem lag der db leiden wäre das unschön.

So eine Konferenz ist sicher was feines, nur ein wenig weit weg…

Um wenigstens nur einzigartige Autorennamen in den Index zu schreiben habe ich folgendes probiert:

UPDATE search_book sb                                                                                                                                                                                                                                   
SET textsearchable_index_gin = setweight(to_tsvector(e.title_lang, coalesce(e.title,'')), 'A') ||
setweight(to_tsvector('simple', coalesce(an.authornames,'')), 'B') ||
setweight(to_tsvector('simple', coalesce(e.isbn10,'')), 'C') ||
setweight(to_tsvector('simple', coalesce(e.isbn13,'')), 'D') 
FROM editions e inner join book_author ba using (bookid) inner join (
                                                                    select array_to_string(array_unique(array_agg(name)),' ') as authornames, authorid 
                                                                    from authornames 
                                                                    where authorid = new.authorid 
                                                                    group by authorid
                                                                    ) as an using (authorid)
WHERE an.authorid = new.authorid and sb.editionid = e.editionid;

bekomme aber folgende Meldung:

FEHLER:  NEW in einer Anfrage verwendet, die nicht Teil einer Regel ist
ZEILE 9: where authorid = new.authorid 
                          ^

ps. array_unique funktioniert so wie in php

Was ist der eigentliche Query, der diesen Trigger auslöst?

So weit komme ich gar nicht, bereits beim erstellen des Triggers erhalte ich die Meldung. Query soll ein Update oder Insert auf authornames sein.

Gibt es fragliche Spalte in “authornames”?

ja, ein

UPDATE search_book sb                                                                                                                                                                                                                                   
SET textsearchable_index_gin = setweight(to_tsvector(e.title_lang, coalesce(e.title,'')), 'A') ||
setweight(to_tsvector('simple', coalesce(an.authornames,'')), 'B') ||
setweight(to_tsvector('simple', coalesce(e.isbn10,'')), 'C') ||
setweight(to_tsvector('simple', coalesce(e.isbn13,'')), 'D')
FROM editions e inner join book_author ba using (bookid) inner join (
                                                                    select array_to_string(array_unique(array_agg(name)),' ') as authornames, authorid 
                                                                    from authornames 
                                                                    where authorid = 1 
                                                                    group by authorid
                                                                    ) as an using (authorid)
WHERE an.authorid = 1 and sb.editionid = e.editionid;

funktioniert.

Ok, wie sieht die komplette Funktionsdefinition aus?
Und ich weiss gerade nicht auswendig, ob das “new” auch klein geschrieben werden darf.

Es ist definitiv Zeit dass ich vom Rechner wegkomme - ich hatte den Rest der Triggerfunktion nicht eingegeben, sondern nur dass was ich gepostet habe. Mit “create or replace function search_authornames_trigger() returns trigger as $$” usw. bekomme ich die Fehlermeldung natürlich nicht mehr.
Danke für den Hinweis - ich geh jetzt vor die Tür. Es ist Wochenende, ich sollte mich mehr daran halten…