Ich implementiere gerade ein Kundenverwaltungssystem auf PostgreSQL aufbauend. Also habe ich eine Tabelle ‘customers’ mit ‘id’ (Kundennummer) als Primary Key. Default Value für die Spalte ‘id’ ist ‘nextval(‘customers_id_seq’)’. Die Sequenz zählt stets um eins rauf. Soweit so gut…
Jetzt wurden jedoch in der Vergangenheit die Kundennummern nach einem anderen - alphabetischen System vergeben. D.h. es existieren bereits Kunden mit der ‘id’ 20864 oder 30456 etc. Mit Implementierung des Systems können Kundennummern nun auch aufeinanderfolgend (1, 2, 3, …) vergeben werden.
Mache ich jetzt ein paar Inserts - die alle einwandfrei funktionieren - und komme irgendwann an die Stelle, an der die durch ‘nextval(‘customers_id_seq’)’ erzeugte ‘id’ schon existiert, schlägt der Insert fehl und muss noch einmal (od. event. sogar mehrmals) ausgeführt werden, bis ‘nextval()’ einen Wert zurückgibt, der noch nicht vergeben ist.
Gibt es eine Möglichkeit dieses Problem (die fehlgeschlagenen Inserts) zu vermeiden und womöglich über einen Trigger od. eine Rule den Insert-Befehl oder ‘nextval()’ so oft auszuführen, bis ein noch nicht existierender Wert zurückgeliefert wird und der Insert - vorausgesetzt es gibt sonst keinen Fehler - erfolgreich durchgeführt wird?
Ich muss gestehen, dass ich nur rudimentäre Kentnisse im Umgang mit PostgreSQL habe und noch nie mit Triggern/Rules gearbeitet habe. Möchte allerdings dieses Problem nur ungern auf Applikationsebene lösen.
Jemand eine Idee? Freue mich auf jeden Vor-/Ratschlag!
Ja, dachte ich mir dann auch schon. Setz doch einfach die Sequence auf einen Wert höher als der max. Wert, der schon da ist. Ich denke, das wäre das einfachste.
Entweder das von akretschmer vorgeschlagene Verfahren oder du darfst nicht einfach die nächste ID der Sequence verwenden sondern musst einfach die nächste freie mittels Sequenz plus Funktion ermitteln. Die Funktion schaut dann halt nach, ob es diese Nummer schon in deiner Tabelle gibt und generiert zur Not die nächste, bis alles passt.
Vielen Dank für eure Vorschläge! Ich habe das ganze nach langem Zähneknirschen endlich zu meiner Zufriedenheit wie folgt gelöst:
--
-- how to skip exisiting primary key values with a sequence
--
CREATE SEQUENCE seq_foo;
CREATE TABLE foo (id INT DEFAULT nextval('seq_foo'));
-- create a function, for a before-trigger
CREATE FUNCTION _foo_seq() RETURNS trigger AS $foo_stamp$
DECLARE
_c INT;
BEGIN
_c := 0;
SELECT INTO _c count(*) FROM foo WHERE id = NEW.id;
WHILE _c > 0 LOOP
NEW.id := nextval('seq_foo');
SELECT INTO _c count(*) FROM foo WHERE id = NEW.id;
END LOOP;
RETURN NEW;
END;
$foo_stamp$ LANGUAGE plpgsql;
-- now create a before-trigger on insert
CREATE TRIGGER foo_trigger BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE _foo_seq();
-- insert values in table foo as follows
INSERT INTO foo VALUES (nextval('seq_foo'));