currval() und nextval in einer Abfrage

Hallo Zusammen

ich habe ein Problem beim insert mit curval.
Kurz das Szenario: postgres 8.4
Eine DB mit einer Sequenz (MAIN_SEQUENCE) die für alle Tabellen genutzt wird um eindeutige IDs über die ganze DB zu haben. Daran kann ich ohne weiteres nichts ändern, da es sich um eine bestehende DB handelt.
Ich habe nun 2 Tabellen:

CREATE TABLE tb_1 (
id bigint NOT NULL,
col character varying,
CONSTRAINT tb_1_pk PRIMARY KEY (id));
CREATE TABLE tb_2 (
id bigint NOT NULL,
tb_1_id bigint NOT NULL,
col character varying,
CONSTRAINT tb_2_id PRIMARY KEY (id),
CONSTRAINT tb_2_fk_tb_1 FOREIGN KEY (tb_1_id)
REFERENCES tb_1 (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION);

und würde gerne ein insert aller

begin;
insert into tb_1 (id, col) values (nextval(’“MAIN_SEQUENCE”’), ‘value in tb1’);
insert into tb_2 (tb_1_id, id, col) values (currval(’“MAIN_SEQUENCE”’), nextval(’“MAIN_SEQUENCE”’), ‘value in tb2’);
commit;

machen, was aber nicht funktioniert, wenn ich für die Tabellen unterschiedliche Sequenzen habe funktioniert natürlich alles wie gewohnt.

Ich würde ja fast sagen, das ist ein Bug. Aber davon mal abgesehen kann mir jemand sagen ob er auch schon so ein Problem hatte und wie er es gelöst hat?

Danke+Gruß

Das ist kein Bug, sondern PEBKEC. Du solltest mal die Fehlermeldung lesen.

test=*# insert into tb_2 (tb_1_id, id, col) values (currval('"MAIN_SEQUENCE"'), nextval('"MAIN_SEQUENCE"'), 'value in tb2');
ERROR:  insert or update on table "tb_2" violates foreign key constraint "tb_2_fk_tb_1"
DETAIL:  Key (tb_1_id)=(2) is not present in table "tb_1".

Du versuchst, 2 einzutragen (2 mal nextval), aber in der ersten tabelle steht nur ein record, mit 1. Peng!

Andreas

Hallo Andreas,
danke für die schnelle Antwort.

Aber genau das meine ich ja:

das currval hat, obwohl es vor dem Aufruf des 2. nextval steht, schon den wert vom 2ten nextval.
Nach meinem Verständnis müsste es zu diesem Zeitpunkt eigentlich noch den vom ersten Aufruf und damit 1 haben, oder?
Natürlich war das mit dem BUG eine provokante Aussage, aber wie löse ich denn mein Problem, wenn currval offensichtlich nicht geht, wenn nextval nochmal im gleichen Statement steht?

Nein.

Das erste Feld bekommt currval, was die ID des Inserts in der ersten Tabelle ist. Das zweite Feld den Wert von nextval(). Dieser Wert ist nicht in der ersten Tabelle.

Natürlich war das mit dem BUG eine provokante Aussage, aber wie löse ich denn mein Problem, wenn currval offensichtlich nicht geht, wenn nextval nochmal im gleichen Statement steht?

Was genau geht denn nicht? Was willst Du da einfügen? Ich seh Dein Problem nicht …

Andreas

Das Verhalten, was du beschreibst, wäre genau das, was ich haben will, denn ich hab in dem 2ten instert deswegen extra die Reihenfolge der Spalten vertauscht.

insert into tb_2 (tb_1_id, id, col) values (currval(’“MAIN_SEQUENCE”’), nextval(’“MAIN_SEQUENCE”’), ‘value in tb2’);

erst die FK-Spalte tb_1_id, die mit currval gefüllt wird und dann die PK-Spalte id, die mit nextval gefüllt wird.

Aber die FK-Spalte bekommt trotzdem den Wert 2 und dann fliegt natürlich der Fehler.

Mein Problem ist einfach, dass ich zwei Tabellen die wie im Beispiel Verknüpft sind und deren PKs beide aus der gleichen Sequenz gefüllt werden, einfügen möchte.

Ahh ja, okay.

Nun, zuerst wird geschaut, was in values(…) steht und das dortige nextval() zündet. Dann erfolgt der insert in tb_2. Zu diesem Zeitpunkt ist currval(() aber schon berechnet, durch das nextval() vorhin.

Du kannst es so machen:

test=# select nextval('"MAIN_SEQUENCE"');                                                              
 nextval                                                                                               
---------                                                                                              
      12                                                                                               
(1 Zeile)                                                                                              

Zeit: 0,144 ms
test=*# insert into tb_1 (id, col) values (currval('"MAIN_SEQUENCE"'), 'value in tb1');
INSERT 0 1                                                                             
Zeit: 0,256 ms                                                                         
test=*# insert into tb_2 (tb_1_id, id, col) values (currval('"MAIN_SEQUENCE"'), currval('"MAIN_SEQUENCE"'), 'value in tb2');
INSERT 0 1
Zeit: 0,320 ms
test=*# select * from tb_1;
 id |     col
----+--------------
 12 | value in tb1
(1 Zeile)

Zeit: 0,258 ms
test=*# select * from tb_2;
 id | tb_1_id |     col
----+---------+--------------
 12 |      12 | value in tb2
(1 Zeile)

In 9.1 wird dies wohl vielleicht schöner gehen, mit WITH …

Andreas

Danke Andres für deine Hilfe.

Leider hilft mir diese Lösung nicht wirklich weiter, da es von oben - frag mich nicht warum - die Vorgabe gibt, dass wirklich jede Id in gewissen Tabellen eindeutig ist.
Womit ich ein Ergebnis wie folgendes brauche:

test=*# select * from tb_1;
 id |     col
----+--------------
 12 | value in tb1
(1 Zeile)

test=*# select * from tb_2;
 id | tb_1_id |     col
----+---------+--------------
 13 |      12 | value in tb2
(1 Zeile)

Ich bin halt davon ausgegangen, dass auch das currval bei der Berechnung der values “zündet” und nicht nur das nextval und das currval nicht erst beim eigentlichen insert. Deswegen auch die provokante Bug-Aussage :wink:

Dann muss ich halt recht durty über ein sub-select die id von tb_1 aller

begin;
insert into tb_1 (id, col) values (nextval('"MAIN_SEQUENCE"'), 'value in tb1');
insert into tb_2 (tb_1_id, id, col) values ((select id from tb_1 where col = 'value in tb1'), nextval('"MAIN_SEQUENCE"'), 'value in tb2');
commit;

holen. Das geht, da ich mehere Spalten hab und die Einträge wieder eindeutig identifizieren kann.
Da ich gut 35.000 inserts dieser Art machen muss auf zwei sehr großen Tabellen wobei die anderen Spalten, die ich zum wieder eindeutigen identifizieren brauche nicht indiziert sind (was das sub-select sehr teuer macht), wird das Ganze recht lange dauern und Last verursachen, was ich gerne vermieden hätte.

Hauptsache ich weiß, dass in meiner Abfrage kein Fehler ist und es so offensichtlich nicht geht.
Also nochmal danke!

Das mit dem Subselect würde ich nicht machen. Auch wenn ich noch nicht wirklich Dein Problem wohl verstanden habe. Aber vielleicht ist eine kleine plpgsql-procedure für Dich eine Lösung?
Da halt die nächste Sequence holen, in einer Variablen speichern, und dann Deine Inserts machen.

Andreas