Postgres, Serials und Rules: Achtung!

Suche


12.12.2011

Postgres, Serials und Rules: Achtung!

Peter Reiß, 17:50 Uhr in PPSA; Tags:

Achtung bei folgender Konstellation:

CREATE TABLE source
(
id serial NOT NULL,
"text" character varying(255),
CONSTRAINT id_pkeyPRIMARY KEY (id)
)

mit Regel:

CREATE RULE "insert_rule" AS
ON INSERT TO source DO  INSERT INTO dest (sourceid)
VALUES (NEW.id);

Serial und Bigserial sind in Postgres intern so konzipiert, dass der Wert für das Feld durch einen Aufruf von

nextval('source_id_seq')

erzeugt wird. Entgegen der Erwartung wird nach Ausführen der Regel aber nicht source.id in die Tabelle dest eingetragen, sondern source.id +1. Bug oder einfach so gewolltes Verhalten von Postgres? In der Postgres-Dokumentation zu Regeln findet sich zumindest ein entsprechender Kommentar:
“Beware that CREATE RULE insert_foo AS ON INSERT TO foos DO ALSO INSERT INTO bar (foo_id) VALUES (NEW.id); won’t work if foo.id is a serial (and foo_id REFERENCES foos ofcourse) because nextval() is evaluated twice.”

Sauberste Lösung: Hier keine Regel, sondern einen Trigger verwenden.

Postgres, Serials and Rules: Caution!

Caution with the following constellations:

CREATE TABLE source
(
id serial NOT NULL,
"text" character varying(255),
CONSTRAINT id_pkeyPRIMARY KEY (id)
)

with rule:

CREATE RULE "insert_rule" AS
ON INSERT TO source DO  INSERT INTO dest (sourceid)
VALUES (NEW.id);

Serial and Bigserial are so in Postgres internally designed that the data for the field can be generated with the request of

nextval('source_id_seq')

Against all expectations after carrying out the rule it is not source.id which is filled into the table dest but source.id +1. Bug or simply unwanted behavior of Postgres? In the Postgres-Documentation the rules can be found at least in on depending commentary:
“Beware that CREATE RULE insert_foo AS ON INSERT TO foos DO ALSO INSERT INTO bar (foo_id) VALUES (NEW.id); won’t work if foo.id is a serial (and foo_id REFERENCES foos ofcourse) because nextval() is evaluated twice.”

Cleanest solution: Do not use a rule here but a trigger.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Nach oben