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

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:

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:

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.

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;

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.

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 :slight_smile:

Oh, das geht sogar noch besser :wink:

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.

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.

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

Aber das hast du ja schon selbst in einem anderen Thread gemerkt.

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.