RRZE – Projekte & Prozesse (P&P)

Das Blog der RRZE Stabsstelle "Projekte & Prozesse"

Content

Postgres, Serials und Rules: Achtung!

Achtung bei folgender Konstellation:
[sql]
CREATE TABLE source
(
id serial NOT NULL,
“text” character varying(255),
CONSTRAINT id_pkeyPRIMARY KEY (id)
)
[/sql]

mit Regel:
[sql]
CREATE RULE “insert_rule” AS
ON INSERT TO source DO  INSERT INTO dest (sourceid)
VALUES (NEW.id);
[/sql]

Serial und Bigserial sind in Postgres intern so konzipiert, dass der Wert für das Feld durch einen Aufruf von
[sql]
nextval(‘source_id_seq’)
[/sql]
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:
[sql]
CREATE TABLE source
(
id serial NOT NULL,
“text” character varying(255),
CONSTRAINT id_pkeyPRIMARY KEY (id)
)
[/sql]

with rule:
[sql]
CREATE RULE “insert_rule” AS
ON INSERT TO source DO  INSERT INTO dest (sourceid)
VALUES (NEW.id);
[/sql]

Serial and Bigserial are so in Postgres internally designed that the data for the field can be generated with the request of
[sql]
nextval(‘source_id_seq’)
[/sql]
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 comment

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

You can use the following HTML tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>