Ergebnis 1 bis 8 von 8

Thema: Umsortierung von Datensätzen mit nur einem SQL-Aufruf

  1. #1
    MatthiasR ist offline Neuer Benutzer
    Registriert seit
    29.07.2009
    Beiträge
    12

    Standard Umsortierung von Datensätzen mit nur einem SQL-Aufruf

    Ich habe folgendes Problem: es geht um eine Tabelle "ansprechpartner", in der zu verschiedenen Personen beliebig viele Ansprechpartner in einer bestimmten Reihenfolge gespeichert werden können. Die besagte Tabelle hat also folgende Spalten:

    id: Primärschlüssel der Ansprechpartner in Form eines Serials.
    person_id: Fremdschlüssel der Person, der ein Ansprechpartner zugewiesen wird.
    reihenfolge: laufende Nummer von 1 aufsteigend, der angibt, in welcher Reihenfolge die verschiedenen Ansprechpartner kontaktiert werden sollen.
    name: Name des Ansprechpartners.

    Außerdem besitzt die Tabelle einen Unique-Constraint auf die Spalten person_id und reihenfolge, um sicherzustellen, dass jede Reihenfolgennummer pro Person nur einmalig vergeben wird.

    Dieser Unique-Constraint macht mir aber das Leben beim Umsortieren der Ansprechpartner schwer, das ich wie folgt implementiert habe (beispielhaft soll Stelle 5 auf Stelle 2 verschoben werden):

    Code:
    -- alles innerhalb einer Transaktion logischerweise
    BEGIN;
    
    -- einen bestimmten Ansprechpartner von Person 4711, dessen id ich kenne, temporär ans Ende der Liste setzen
    UPDATE
        ansprechpartner
    SET
        reihenfolge = (SELECT max(reihenfolge) + 2 FROM ansprechpartner WHERE person_id = 4711)
    WHERE    
        id = 1337;
    
    
    -- alle Positionen zwischen der alten Stelle (5) und der neue (2) um 1 nach hinten versetzen
    UPDATE
        ansprechpartner
    SET
        reihenfolge = reihenfolge + 1
    WHERE
        person_id = 4711 AND
        reihenfolge >= 2 AND
        reihenfolge <= 5; -- FEHLER
    
    -- temporären Wert auf den richtigen (2) setzen
    UPDATE
        ansprechpartner
    SET
        reihenfolge = 2
    WHERE
        id = 1337;
    
    -- Transaktion abschließen
    COMMIT;
    Beim dritten SQL-Befehl bekomme ich jedoch eine Verletzung des Unique-Constraints gemeldet, obwohl der Zustand, der sich aus dem Befehl heraus ergibt, auch wieder konsistent wäre, da ich ja zuvor den zu verschiebenden Datensatz erstmal temporär ganz nach hinten verschoben habe.

    Frage: sieht PostgreSQL das zweite Update-Kommando nicht als einen einheitlichen Befehl an und macht intern doch drei einzelne Updates draus? Denn da wäre ja eine verletzung des Unique-Constraints vorprogrammiert und verständlich.
    Was mich auch wundert: wenn ich die Position 2 nach 5 verschiebe, also genau umgekehrt, muss ich ja alle Positionen dazwischen um 1 dekrementieren. Der entsprechende Befehl lautet:
    Code:
    UPDATE
        ansprechpartner
    SET
        reihenfolge = reihenfolge - 1
    WHERE
        person_id = 4711 AND
        reihenfolge > 2 AND
        reihenfolge <= 5;
    Das funktioniert komischerweise, was meine These untermauern dürfte, dass Postgres mit der kleinsten Position (3) anfängt, sie zu dekrementieren und sich dann nach oben arbeitet. Dabei entstehen ja immer die passenden "Lücken", die dann der nächste Wert ausfüllen kann.

    Sehe ich das richtig und wie kann ich das Verschieben trotzdem in einem einzigen SQL-Aufruf implementieren?

  2. #2
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von MatthiasR Beitrag anzeigen
    Ich habe folgendes Problem: es geht um eine Tabelle "ansprechpartner", in der zu verschiedenen Personen beliebig viele Ansprechpartner in einer bestimmten Reihenfolge gespeichert werden können. Die besagte Tabelle hat also folgende Spalten:

    id: Primärschlüssel der Ansprechpartner in Form eines Serials.
    person_id: Fremdschlüssel der Person, der ein Ansprechpartner zugewiesen wird.
    reihenfolge: laufende Nummer von 1 aufsteigend, der angibt, in welcher Reihenfolge die verschiedenen Ansprechpartner kontaktiert werden sollen.
    name: Name des Ansprechpartners.

    Außerdem besitzt die Tabelle einen Unique-Constraint auf die Spalten person_id und reihenfolge, um sicherzustellen, dass jede Reihenfolgennummer pro Person nur einmalig vergeben wird.

    Dieser Unique-Constraint macht mir aber das Leben beim Umsortieren der Ansprechpartner schwer, das ich wie folgt implementiert habe (beispielhaft soll Stelle 5 auf Stelle 2 verschoben werden):

    Code:
    -- alles innerhalb einer Transaktion logischerweise
    BEGIN;
    
    -- einen bestimmten Ansprechpartner von Person 4711, dessen id ich kenne, temporär ans Ende der Liste setzen
    UPDATE
        ansprechpartner
    SET
        reihenfolge = (SELECT max(reihenfolge) + 2 FROM ansprechpartner WHERE person_id = 4711)
    WHERE    
        id = 1337;
    
    
    -- alle Positionen zwischen der alten Stelle (5) und der neue (2) um 1 nach hinten versetzen
    UPDATE
        ansprechpartner
    SET
        reihenfolge = reihenfolge + 1
    WHERE
        person_id = 4711 AND
        reihenfolge >= 2 AND
        reihenfolge <= 5; -- FEHLER
    
    -- temporären Wert auf den richtigen (2) setzen
    UPDATE
        ansprechpartner
    SET
        reihenfolge = 2
    WHERE
        id = 1337;
    
    -- Transaktion abschließen
    COMMIT;
    Beim dritten SQL-Befehl bekomme ich jedoch eine Verletzung des Unique-Constraints gemeldet, obwohl der Zustand, der sich aus dem Befehl heraus ergibt, auch wieder konsistent wäre, da ich ja zuvor den zu verschiebenden Datensatz erstmal temporär ganz nach hinten verschoben habe.

    Frage: sieht PostgreSQL das zweite Update-Kommando nicht als einen einheitlichen Befehl an und macht intern doch drei einzelne Updates draus? Denn da wäre ja eine verletzung des Unique-Constraints vorprogrammiert und verständlich.
    Was mich auch wundert: wenn ich die Position 2 nach 5 verschiebe, also genau umgekehrt, muss ich ja alle Positionen dazwischen um 1 dekrementieren. Der entsprechende Befehl lautet:
    Code:
    UPDATE
        ansprechpartner
    SET
        reihenfolge = reihenfolge - 1
    WHERE
        person_id = 4711 AND
        reihenfolge > 2 AND
        reihenfolge <= 5;
    Das funktioniert komischerweise, was meine These untermauern dürfte, dass Postgres mit der kleinsten Position (3) anfängt, sie zu dekrementieren und sich dann nach oben arbeitet. Dabei entstehen ja immer die passenden "Lücken", die dann der nächste Wert ausfüllen kann.

    Sehe ich das richtig und wie kann ich das Verschieben trotzdem in einem einzigen SQL-Aufruf implementieren?
    In welcher Reihenfolge PG die Tabelle durchgeht kannst Du nicht wissen und nicht definieren. Also auch nicht darauf verlassen.

    Hinreichend neue Versionen von PG können Constrants aber DEFERRED setzen:

    Code:
    test=# create table d (i int unique deferrable);
    NOTICE:  CREATE TABLE / UNIQUE will create implicit index "d_i_key" for table "d"
    CREATE TABLE
    test=# begin;
    BEGIN
    test=# set constraints all deferred;
    SET CONSTRAINTS
    test=# insert into d values (1);
    INSERT 0 1
    test=# insert into d values (1);
    INSERT 0 1
    test=# insert into d values (1);
    INSERT 0 1
    test=# commit;
    ERROR:  duplicate key value violates unique constraint "d_i_key"
    DETAIL:  Key (i)=(1) already exists.
    Du mußt halt nur dafür sorgen, daß zum COMMIT alles paßt und stimmt.


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #3
    MatthiasR ist offline Neuer Benutzer
    Registriert seit
    29.07.2009
    Beiträge
    12

    Standard

    Erstmal Danke für die Antwort! Leider konnte ich das in meinem Fall nicht entsprechend umsetzen. Zum einen geht es bei mir um einen Unique-Index über zwei Spalten, in deinem Beispiel definierst du eine einzelne Spalte (i int) als unique deferrable. Ich habe das Kommando "SET CONSTRAINTS ALL DEFERRED;" trotzdem mal ausprobiert und danach erneut versucht, meinen zweiten Update-Befehl abzusetzen, jedoch mit dem gleichen Ergebnis.

    Ich werde es jetzt aus dem aufrufenden Programm heraus steuern, dass die einzelnen Tabellenzeilen in der richtigen Reihenfolge einzeln umplatziert werden. Das ist keine besonders schöne Möglichkeit, aber so sollte es wenigstens funktionieren.

    --> Sehe ich das also richtig: unter Postgres ist ein solcher Update-Befehl, der gleichzeitig mehrere Tabellenzeilen ändert, nicht als eine Einheit zu verstehen, sondern jede zeilenänderung quasi für sich, sodass solche Unique-Constraints eben auch dort greifen?

  4. #4
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    SET CONSTRAINTS ALL changes the mode of all deferrable constraints


    Wenn du den Constraint nicht initial als deferrable definiert hast, dann kannst du ihn auch nicht nachträglich so setzen.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  5. #5
    MatthiasR ist offline Neuer Benutzer
    Registriert seit
    29.07.2009
    Beiträge
    12

    Standard

    Only foreign key constraints currently accept this clause. All other constraint types are not deferrable.
    Sagt die PostgreSQL 8.4 Doku. Hätte ich wohl dazuschreiben sollen, dass wir noch 8.4 einsetzen
    Geändert von MatthiasR (28.07.2011 um 16:09 Uhr)

  6. #6
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.056

    Standard

    Darum schrieb akretschmer auch von einigermaßen aktuellen Versionen ;-)
    In 8.4 bekommst du das nicht über diesen Weg hin, das geht ab 9.0.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  7. #7
    MatthiasR ist offline Neuer Benutzer
    Registriert seit
    29.07.2009
    Beiträge
    12

    Standard

    OK, schonmal der erste kleine Anreiz, einen Wechsel durchzuführen

  8. #8
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Dresden
    Beiträge
    5.472

    Standard

    Zitat Zitat von MatthiasR Beitrag anzeigen
    OK, schonmal der erste kleine Anreiz, einen Wechsel durchzuführen
    DO - Statements, exclusion constraints, Streaming Replication, per column trigger, besseres VACUUM FULL, additional windowing options (PRECEDING and FOLLOWING), ...


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

Ähnliche Themen

  1. Zusammenfassen von Datensätzen bei Abfrage
    Von Freakster235 im Forum Andere Datenbanken
    Antworten: 4
    Letzter Beitrag: 04.04.2011, 09:49
  2. Frage Zusammenfassen von Datensätzen bei Abfrage
    Von Freakster235 im Forum SQL
    Antworten: 3
    Letzter Beitrag: 03.04.2011, 19:43
  3. Versionsveraltung von Datensätzen in Post
    Von araxes im Forum Allgemeines
    Antworten: 3
    Letzter Beitrag: 26.04.2010, 10:45
  4. Problem Daten aus 2 Datensätzen in einem ausgeben
    Von Jupp Hintz im Forum SQL
    Antworten: 1
    Letzter Beitrag: 27.06.2008, 16:06
  5. Aufruf von Update vor einem Update auf gleiche Tabelle
    Von Heisp im Forum Trigger und Funktionen
    Antworten: 3
    Letzter Beitrag: 26.06.2007, 14:21

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum