Für eine Datenbank existiert eine Tabelle in der sich bereits ca. 300.000 Primärschlüssel befinden. Die Bearbeiter der Datenbank haben in einer GUI die Möglichkeit in bestimmten Grenzen Primärschlüssel anzeigen zu lassen und entsprechend den restlichen Datensatz auszufüllen oder einen komplett neuen Datensatz (inkl. neuem Primärschlüssel) anzulegen.
Dabei kommt es manchmal vor das ein neuer Primärschlüssel angelegt werden soll, der schon existiert und dann entsprechend mit
duplicate key violates unique constraint abgewiesen wird. Problematisch ist dabei, daß die angegebenen Werte zum Datensatz verfallen und neu eingetragen werden sollen.
Damit dies nicht passiert, möchte ich nun die INSERT-Anfrage in ein UPDATE umbiegen.
Und zwar so:
test=# create table bar (id int, text text) with oids;
CREATE TABLE
test=# INSERT INTO bar VALUES (1,'a');
INSERT 16473 1
test=# INSERT INTO bar VALUES (2,'b');
INSERT 16474 1
test=# INSERT INTO bar VALUES (3,'c');
INSERT 16475 1
test=# SELECT * from bar ;
id | text
----+------
1 | a
2 | b
3 | c
(3 rows)
test=# INSERT INTO bar VALUES (1,'!');
ERROR: duplicate key violates unique constraint "bar_pkey"
Statt der letzten Fehlermeldung soll dann ein UPDATE ausgeführt werden, so das in 1.text = "a !" steht.
Nichts leichter als das, dachte ich mir, es gibt ja RULES.
Also, Handbuch gegriffen, RULES studiert und loskonstruiert, heraus kam:
test=# CREATE RULE "insup"
AS ON INSERT TO bar
WHERE new.id IN (select id from bar)
DO INSTEAD (update bar set text=text||' '||new.text where id=new.id);
CREATE RULE
test=# INSERT INTO bar VALUES (3,'X');
INSERT 0 0
test=# INSERT INTO bar VALUES (3,'Y');
INSERT 0 0
test=# SELECT * from bar ;
id | text
----+-------
1 | a
2 | b
3 | c X Y
(3 rows)
Funktioniert ja wunderbar mit dem UPDATE. Nur leider gibt es ein Problem:
test=# INSERT INTO bar VALUES (99,'TEST');
INSERT 16477 1
test=# SELECT * from bar ;
id | text
----+-----------
1 | a
2 | b
3 | c X X
99 | TEST TEST
(4 rows)
test=# INSERT INTO bar VALUES (99,'TEST2');
INSERT 0 0
test=# SELECT * from bar ;
id | text
----+-----------------
1 | a
2 | b
3 | c X X
99 | TEST TEST TEST2
(4 rows)
So'n Mist, wenn der Schlüssel noch nicht existiert, wird "text" zweimal eingefügt. Das darf natürlich nicht sein.
Nach ein paar Fragen im IRC bekam ich den Hinweis das RULES mit UPDATE teilweise unter RACE CONDITIONS leiden, so das man damit vorsichtig umgehen sollte. Stattdessen sollte ich lieber eine FUNCTION verwenden.
Als Pointer bekam ich http://developer.postgresql.org/docs/postgres/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING gereicht.
Nach ausgiebigem Studium des Werkes erschuf ich eine FUNCTION die tut was ich will :-)
Und es war sogar schon das passende Bsp. im Handbuch angegeben *g
CREATE FUNCTION ins_or_up (name int, data text) RETURNS VOID AS
$$
BEGIN LOOP
update bar set text = text || data where id=name;
if found then
return;
end if;
BEGIN
INSERT INTO bar (id,text) VALUES (name,data);
return;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
Das tut was ich will:
test=# INSERT INTO bar VALUES (1,'A');
INSERT 16501 1
test=# INSERT INTO bar VALUES (2,'B');
INSERT 16502 1
test=# INSERT INTO bar VALUES (3,'C');
INSERT 16503 1
test=# INSERT INTO bar VALUES (1,'X');
ERROR: duplicate key violates unique constraint "bar_pkey"
test=# SELECT * from bar ;
id | text
----+------
1 | A
2 | B
3 | C
(3 rows)
test=# SELECT ins_or_up(1,' X');
ins_or_up
-----------
(1 row)
test=# SELECT ins_or_up(2,' XX'); SELECT * from bar ;
ins_or_up
-----------
(1 row)
id | text
----+------
3 | C
1 | A X
2 | B XX
(3 rows)
test=#
Dann will ich mal die FUNCTION zerlegen:
CREATE FUNCTION ins_or_up (name int, data text) RETURNS VOID AS Erzeuge die FUNCTION ins_or_up, übergebe name als Integer und data als Text, Rückgabewert gibt es nicht.
$$
BEGIN LOOP Ein kleines Schleifchen
update bar set text = text || data where id=name; Aktualisiere die Tabelle bar, hänge den Text an den Datensatz mit der entsprechenden id an
if found then return;
end if; Wenn der Datensatz schon existiert, id also gefunden wird. Beende dann die FUNCTION und den IF-Zweig
BEGIN INSERT INTO bar (id,text) VALUES (name,data);
return; Anderenfalls soll der Satz normal eingefügt werden.
EXCEPTION WHEN unique_violation THEN
-- do nothing
END; Sollte der Datensatz schon existieren, tue nichst und beende den Zweig
END LOOP;
END;
$$ beende die Schleife und die FUNCTION
LANGUAGE plpgsql; Das ganze in PL/pgSQL.
