Ergebnis 1 bis 9 von 9

Thema: pg_affected_rows != mysql_affected_rows !?

  1. #1
    Swift42 ist offline Neuer Benutzer
    Registriert seit
    25.11.2008
    Beiträge
    7

    Standard pg_affected_rows != mysql_affected_rows !?

    Hi there!

    Ich portiere aktuell ein Projekt von MySQL auf PostgreSQL. Allerdings verhalten sich o.g. Funktionen unterschiedlich, was bei dem Projekt zu Problemen führt.
    Während MySQL bei einem Update nur Zeilen schreibt, die neue Daten enthalten, schreibt PostgreSQL die Zeile anscheinend immer, egal ob die Daten sich unterscheiden oder nicht.

    MySQL:
    update users set name='Test' where id=1
    Affected rows: 1
    update users set name='Test' where id=1
    Affected rows: 0

    PostgreSQL:
    update users set name='Test' where id=1
    Affected rows: 1
    update users set name='Test' where id=1
    Affected rows: 1

    Wie kann ich unter Postgres das Mysql-Verhalten emulieren?
    Danke im Voraus.

    Swift42

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

    Standard

    Code:
    test=# create table affected_test (id serial, data text);
    NOTICE:  CREATE TABLE will create implicit sequence "affected_test_id_seq" for serial column "affected_test.id"
    CREATE TABLE
    test=# insert into affected_test (data) values ('irgendein Inhalt');
    INSERT 0 1
    test=# update affected_test set data='neuer Inhalt' where id=2;
    UPDATE 0
    test=# update affected_test set data='neuer Inhalt' where id=1;
    UPDATE 1
    Ich weiß, was du sagen möchtest, aber: die Aussage der PostgreSQL Funktion ist richtig. Die Zeilen werden tatsächlich aktualisiert - unabhängig davon was dort vorher stand.


    Gut, du willst das also umgehen: du musst vorher prüfen ob sich die Daten ändern. Das wird erstens etwas umfangreicher und zweitens kostet das Performance:

    Code:
    CREATE TABLE affected_test (id serial, data text);
    
    INSERT INTO affected_test (data) VALUES ('irgendein Inhalt');
    
    
    CREATE OR REPLACE FUNCTION update_test ()
              RETURNS TRIGGER
    AS $$
    DECLARE
    
    BEGIN
    
      IF TG_OP = 'UPDATE' THEN
          IF NEW.id = OLD.id AND NEW.data = OLD.data THEN
              -- keine Datenaenderung
              RETURN NULL;
          END IF;
          RETURN NEW;
      END IF;
    
      IF TG_OP = 'INSERT' THEN
          RETURN NEW;
      END IF;
    
      IF TG_OP = 'DELETE' THEN
          RETURN OLD;
      END IF;
    
    
      -- wird nicht erreicht
      RETURN NEW;
    END;
    $$
    LANGUAGE 'plpgsql';
    
    
    CREATE TRIGGER update_test_trigger BEFORE INSERT OR UPDATE OR DELETE ON affected_test FOR EACH ROW
                   EXECUTE PROCEDURE update_test();
    Gut, ausprobieren:

    Code:
    test=# update affected_test set data='neuer Inhalt' where id=1;
    UPDATE 1
    test=# update affected_test set data='neuer Inhalt' where id=1;
    UPDATE 0
    Passt.
    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

  3. #3
    Swift42 ist offline Neuer Benutzer
    Registriert seit
    25.11.2008
    Beiträge
    7

    Standard

    Vielen Dank für die schnelle und ausführliche Antwort.

    Sehr interessante Lösung. Ich hatte zwar gehofft, dass es eine native Postgres-Funktion dafür gibt, aber die Lösung gefällt mir trotzdem gut.

    Eine Frage hab ich aber noch: Der Trigger wird ja immer aktiv, sobald ich was in die Tabelle einfüge, lösche oder update.
    Kann ich es irgendwie erreichen, dass die Funktion nur aktiv wird, wenn ich das wünsche? Also egal bei welcher Tabelle?

    Ich möchte also sowas machen wie (vereinfacht dargestellt):
    check_same_rows_on_update=on;
    update irgendeinetabelle set ....
    check_same_rows_on_update=off;

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

    Standard

    Nein. Und du musst den Trigger auf jede Tabelle legen. Wie von mir gesagt: das Verhalten zu emulieren kostet Performance. Bleibt zu überlegen ob man das Problem nicht anders beseitigen kann indem man die Anwendung "heile" macht.

    Du möchtest etwas emulieren was so eigentlich falsch ist. Wenn man "affected" übersetzt heißt es "betroffene" oder "bewegte" Daten und bei einem Update sind die Daten immer betroffen. Daher ist logisch gesehen die Ausgabe von MySQL falsch.
    Ich würde mit mir reden lassen wenn das updated_rows() heißen würde ... dann könnte man die effektiv geänderten zählen ...


    Noch etwas:
    Du kannst den Trigger auch nur für UPDATE definieren. Alle anderen Fälle fange ich im trigger sowieso ab und da passiert nichts. Wichtig in deinem Fall sind ja die unterschiedlichen Zahlen bei Updates, alles andere passt.
    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
    Swift42 ist offline Neuer Benutzer
    Registriert seit
    25.11.2008
    Beiträge
    7

    Standard

    Ich geb einfach mal ein konkretes Beispiel:
    Das Projekt nutzt die Eigenheit von MySQL z.B. für einen einfachen Lock:

    update staticvalues set staticvalue=1 where id='lock_xy';

    Wenn nun affected rows = 0, weiß die Anwendung, dass irgendein anderer Thread den Lock bekommen hat (da staticvalue schon "1" war).
    Bei affected rows = 1 weiß die Anwendung dagegen, dass das eigene UPDATE erfolgreich war und damit der Lock für sich selbst aktiv ist.
    Bei matched rows = 0 weiß die Anwendung, dass der Lock-Wert nicht existiert und ein INSERT nötig ist.
    Und das alles nur mit einem einzigen UPDATE.

    Kann ich das genauso simpel oder annähernd so simpel in PG realisieren? Ohne einen Trigger, wie du ihn gerade beschrieben hast, zu verwenden?

    Danke nochmals :-)

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

    Standard

    Oh, das geht sogar noch besser ;-)

    Für mein Buch habe ich vor ein paar Tagen ein Kapitel zu diesem Thema geschrieben. Schau dir mal folgendes hier an:

    Locking in PostgreSQL

    Und dort empfehle ich dir den Abschnitt zu "Advisory Locking". Das dürfte exakt das sein, was du brauchst.
    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
    Swift42 ist offline Neuer Benutzer
    Registriert seit
    25.11.2008
    Beiträge
    7

    Standard

    Für's Protokoll ... ich habs nun so gelöst:

    begin;
    select staticvalue from staticvalues where id='lock_xy' for update;

    Werden 0 Ergebnisse geliefert, muss ein INSERT durchgeführt werden.
    Wird ein Ergebnis geliefert, wird es eingelesen.
    Ist staticvalue gleich dem neuen Wert (1==1), kann der Lock nicht durchgeführt werden - commit und entsprechendes Ergebnis zurückliefern.
    Ist staticvalue ungleich dem neuen Wert (0==1), kann der Lock geschrieben werden - UPDATE, commit und Ergebnis zurückliefern.

    Da durch das "for update" nichts dazwischenfunken kann, hat man einen allgemeinen Locking-Mechanismus, den man für alle möglichen Sachen verwenden kann.

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

    Standard

    Außer die Race Condition, die sich hier auftut.

    Aber das hast du ja schon selbst in einem anderen Thread gemerkt.
    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

  9. #9
    Swift42 ist offline Neuer Benutzer
    Registriert seit
    25.11.2008
    Beiträge
    7

    Standard

    Ja, korrekt, die einzige Schwachstelle ist derzeit, dass es konkurrierende Inserts geben kann. Das stört mich bei dem Projekt aber zum Glück weniger, da beim ersten Start des Projekts alle Lock-Werte in die DB geschrieben werden und danach dann nur noch das UPDATE greift, welches keiner Race-Condition unterliegt.

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