2010/9/16 Gissur Þórhallsson <gissur@loftmyndir.is>:
>> Yes. You're using RULEs where TRIGGERs would do. Change to TRIGGERs.
>
> While this could possibly solve my problem in particular; it doesn't explain
> why this is happening.
> Is this somehow expected behavior on an INSERT rule?
>
Rules can be pretty tricky things to work with, and this is one of the
well-known gotchas (to those who know it well!).
Consider the following simplified version of your example:
CREATE TABLE foo(a serial, b text);
CREATE TABLE bar(a int, b text);
CREATE RULE ins_rule AS ON INSERT TO foo
DO ALSO INSERT INTO bar VALUES(new.a, new.b);
You might think that the rule would guarantee that any insert into foo
would be mirrored with an identical insert on bar. However, this is
not the case. Consider, for example, this insert:
INSERT INTO foo(b) VALUES ('Row 1'), ('Row 2'), ('Row 3');
What the rule will actually do is cause 2 separate INSERT commands to
be executed. The first will add 3 rows to foo, choosing 3 successive
values for 'a' from the sequence. The second command is an insert into
bar, and since 'a' isn't specified, it will use the default for 'a'
from foo, causing another 3 values to be pulled from the sequence. So
the end result is:
SELECT * FROM foo;
a | b
---+-------
1 | Row 1
2 | Row 2
3 | Row 3
(3 rows)
SELECT * FROM bar;
a | b
---+-------
4 | Row 1
5 | Row 2
6 | Row 3
(3 rows)
which is probably not what you might expect.
It's this sort of thing that makes many people prefer triggers to rules.
Regards,
Dean