Zeile mit SQL einfügen wenn noch nicht vorhanden

[rotalever]

Ich habe ein Postgresql z.B. eine Tabelle "user" mit den Spalten "email" und "name" und "id" (PRIMARY KEY), wobei "email" auf ein UNIQUE INDEX drauf liegt und somit keine email doppelt vorkommen darf. Das ist ja auch logisch..
Fügt man nun eine neue Zeile ein, ist das kein Problem:

INSERT INTO user (email,name) VALUES ("a@b.c","hans wurst") RETURNING id;

Was passiert jetzt, wenn die Zeile schon da ist? Dann gibt es einen Error. Jetzt gibt es zwei möglichkeiten, entweder ich fange den Error mit PHP ab und erkenne das es sich um einen "Duplicated key"-Error handelt, oder ich versuche es auf andere Weise... Beide Möglichkeiten kämen in Frage, also wenn jemand weiß, wie man das korrekt mit PHP abfängt, dann reicht das schon.

Die zweite Möglichkeit wäre sowas zumachen wie dort:
http://archives.postgresql.org/pgsql-sql/2004-10/msg00096.php
Wie der Autor aber schon korrekt anmerkt, ist das nicht sehr sicher, wenn mehrere Requests parallel ausgeführt werden. Jetzt habe ich schon einiges über Transactions gelesen, sehe aber keinen Weg, damit einen möglichen Fehler auszuschließen. Der Fehler wäre, dass das SELECT die Zeile noch nicht sieht, dann wird sie parallel dazu aus einem anderen Script eingefügt und jetzt noch ein zweites Mal wodurch der Fehler enstehen würde.

Ich glaube die erste Lösung meines Problems wäre einfacher, ich weiß allerdings nicht, wie ich nur exakt den duplicated key Error abfangen kann, so dass andere Fehler trotzdem erkannt werden können.

[Achill]

Den Fall, dass ein User mit der E-Mail Adresse schon existiert, würde doch ein einfaches SELECT im Vorfeld identifizieren.

Ansonsten solltest du alle komplexeren DB-Operationen - besonders wenn ein Erfolg nicht immer gegeben ist - in Transaktionen ausführen.

Transaktionen kapseln alle DB-Operationen, die in dieser durchgeführt werden. Nur die DB-Operationen einer erfolgreiche Transaktionen werden in der DB dauerhaft gespeichert. Bei einem Abbruch einer Transaktion werden alle Operationen rückgängig gemacht. Ein Abbruch einer Transaktion kann durch eine Programmanweisung erzeugt werden oder durch einen Konflikt in der DB (Doppelter PK, Falscher FK, Unique Index, ...) entstehen.

Wird versucht, parallel zwei mal den gleichen Benutzer anzulegen (Die E-Mail-Adresse ist unbenutzt), dann wird es für einen Prozess erfolgreich sein und die Transaktion des anderen Prozesses wird zurück gerollt, weil ein User mit dieser E-Mail schon existiert.

Ein anderes gutes Beispiel wären Löschroutien, die nicht erfolgreich ablaufen. Dann wären die Daten in einem undefinierten Zustand - macht man dies in einer Transaktion, dann wird die Löschung rückgängig gemacht.

[rotalever]

Ein SELECT im Vorfeld bringt nichts, da ja in der kurzen Zeit zwischen SELECT und INSERT ein anderes Script die Zeile einfügen könnte.

Bei mir handelt es sich eigentlich immer um einzelne INSERT-Queries, da würde eine Transaction nicht viel bringen?

Wenn ich mein INSERT einfach in eine Transaction reinpacke und das jetzt fehlschlägt, weil die email-Adresse bereits existiert, wird dann einfach die Transaktion ohne weitere Rückmeldung abgebrochen, oder trotzdem noch ein Error zurückgeben, also genauso wie es wäre, hätte ich keine Transaction benutzt?

[Achill]

Ein SELECT im Vorfeld bringt nichts, da ja in der kurzen Zeit zwischen SELECT und INSERT ein anderes Script die Zeile einfügen könnte.


Ich dachte an den Fall, dass ein User schon angemeldet ist, dann ist ein Check im Vorfeld billiger als viele Inserst + späteren Fehler und ggf. Abbruch.


Bei mir handelt es sich eigentlich immer um einzelne INSERT-Queries, da würde eine Transaction nicht viel bringen?


Wenn die INSERT Operationen in einem Zusammenhang zueinander stehen und die Daten, wenn eine / ein paar Inserts fehlen nutzlos sind und ggf. wieder gelöscht werden dann wäre eine Transaktion sinnvoll, da diese das Problem löst.


Wenn ich mein INSERT einfach in eine Transaction reinpacke und das jetzt fehlschlägt, weil die email-Adresse bereits existiert, wird dann einfach die Transaktion ohne weitere Rückmeldung abgebrochen, oder trotzdem noch ein Error zurückgeben, also genauso wie es wäre, hätte ich keine Transaction benutzt?


Wahrscheinlich wir es genauso ein Fehler geben ... Wie sieht denn der Fehler unter PHP aus, den du abfangen willst?

[rotalever]

Wahrscheinlich wir es genauso ein Fehler geben ... Wie sieht denn der Fehler unter PHP aus, den du abfangen willst?
Der Labert dann was von:

Warning: pg_execute() [function.pg-execute]: Query failed: ERROR: duplicate key value violates unique
constraint "TABELLENNAME_pkey" in DATEINAME on line 24

Also ich habe das Statement prepared und rufe pg_execute('',$vars) auf, wobei $vars eben die zu übergebenden Variablen sind. Das ganze mache ich zur Zeit so:

$result = pg_execute('', $vars) or db_error();

Wobei db_error() eine eigene Funktion ist und nichts anderes macht, als das Script vernünftig zu beenden und dann exit() aufzurufen. Die oben genannte Fehlermeldung zeigt mir PHP selbständig an, noch bevor db_error() aufgerufen wird, darauf habe ich scheinbar keinen Einfluss.

[Berni]

Diese Meldung kannst du wohl mit error_reporting(0); abschalten (danach am Besten gleich wieder anschalten). Also in etwa so
$old_level = error_reporting();
error_reporting(0);
$result = pg_execute('', $vars) or db_error();
error_reporting($old_level);

Eventuell muss mans auch hiermit http://usphp.com/manual/en/function.pg-set-error-verbosity.php
abschalten.
Wenn $result false ist, gabs einen Fehler bei der Ausführung und somit wohl den duplicate key error. Ansonsten gibts auch Funktionen um die Fehlermeldung als Variable zu bekommen ( http://usphp.com/manual/en/function.pg-last-error.php )
Sind jetzt nur Ideen meinerseits, ich nutze kein PostgreSQL. Unter MySQL würde ichs halt mit REPLACE INTO bzw. INSERT INTO ... ON DUPLICATE KEY machen ;)

[rotalever]

[...]
Eventuell muss mans auch hiermit http://usphp.com/manual/en/function.pg-set-error-verbosity.php
abschalten.
Wenn $result false ist, gabs einen Fehler bei der Ausführung und somit wohl den duplicate key error. Ansonsten gibts auch Funktionen um die Fehlermeldung als Variable zu bekommen ( http://usphp.com/manual/en/function.pg-last-error.php )
Sind jetzt nur Ideen meinerseits, ich nutze kein PostgreSQL. Unter MySQL würde ichs halt mit REPLACE INTO bzw. INSERT INTO ... ON DUPLICATE KEY machen ;)
Ja das Abschalten des Errors wär wohl eine Möglichkeit. Aber das "ON DUPLICATE KEY" für MySQL hat mich wahrscheinlich auf die Lösung gebracht. Ich hab mal in Google "postgresql ON DUPLICATE KEY" eingegebn und bin auf folgendes gestoßen:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html
Ganz unten auf der Seite ist ein Beispiel, wie man die Exception abfangen kann und dann entsprechend darauf reagieren. Ich denke, ich kann dieses Beispiel so für meine Zwecke modifizieren, dass es passt :smile:

edit: Es funktioniert. Ich muss mir jetzt allerdings für jedes INSERT eine eigene Funktion basteln.

[daflow]

Einfacher wäre wohl eine Transaktion mit
-"select... for update" (zwecks Locking)
-"insert... " (oder kein insert falls vorhanden)

[rotalever]

Einfacher wäre wohl eine Transaktion mit
-"select... for update" (zwecks Locking)
-"insert... " (oder kein insert falls vorhanden)
Werd ich mir mal anschauen.

Ansonsten lautet eine meiner Funktionen jetzt:

CREATE OR REPLACE FUNCTION insert_user(e varchar(50), n varchar(50)) RETURNS int AS
$$
DECLARE
retid int;
BEGIN
BEGIN
INSERT INTO user (email,name) VALUES (e,n) RETURNING id INTO retid;
EXCEPTION WHEN unique_violation THEN SELECT id INTO retid FROM user WHERE lower(email)=lower(e) LIMIT 1;
END;
IF retid IS NULL THEN
RETURN -1;
ELSE
RETURN retid;
END IF;
END;
$$
LANGUAGE plpgsql;

Die -1 bedeutet dann, dass der INSERT fehlgeschlagen ist, weil die Zeile schon existiert und der darauffolgende SELECT kein Ergebnis liefert, da die Zeile mittlerweile gelöscht wurde...


EDIT: Das mit diesem Select for update: Der locked die row ja nur, wenn sie bereits vorhanden ist. Wenn sie aber noch nicht vorhanden ist, kann auch nichts gelocked werden. Trotzdem kann dann zwischen SELECT und INSERT ein paralleler Zugriff die row einfügen, sodass INSERT fehlschlagen würde. Oder sehe ich das falsch?

[daflow]

Ähm ja.. du willst ja inserten... ;)
das wär evtl. ne Option: http://www.postgresql.org/docs/7.4/interactive/sql-lock.html

[rotalever]

Ähm ja.. du willst ja inserten... ;)
das wär evtl. ne Option: http://www.postgresql.org/docs/7.4/interactive/sql-lock.html
Hmm.. Table-Locken? Das drückt sicher ganz schön heftig auf die Performance.