The following bug has been logged online:
Bug reference: 2217
Logged by: ATTILA GATI
Email address: elkgati@gold.uni-miskolc.hu
PostgreSQL version: >7.4.7
Operating system: Linux (debian, sarge)
Description: serial in rule and trigger
Details:
create table test(id serial, txt varchar);
create table mon(n int);
create rule monitor as on insert to test do
insert into mon values (NEW.id);
insert into test (txt) values ('xxx');
What I expect is to get the latest id written in
table mon whenever I insert a data into table test.
However test.id will be incremented by 2!
Ids in the test table will be 1,3,5, etc.
and in the mon table 2,4,6, etc.
So the simple usage of the NEW.id value will
increment the serial data for an unknown reason.
If I duplicate the insert into in the rule:
.. do
(insert into mon values (NEW.id);
insert into mon values (NEW.id);)
test.id will be incremented by 3!
This is a silly test, I describe the original problem:
I have to keep a part of a database synchronized on 2 different machines.
I tried to write a trigger using dblink, so whevever a
data is inserted into a table on any of the machines, it was supposed to be
inserted on the "mirror" machine as well.
As there might be holes in a serial - as described in the documentation - I
tried to check, if the 2 ids are identical, but experienced unexpected
double jumps, so I created the above enclosed test.
So I experienced the same behaviour either using a trigger or a rule.
Also I tried version 7.4.7 and 8.1.
However - although the relevant part of the documentation is identical for
both versions - in case
of version 8.1 I found now holes when the transaction was aborted for some
reason (not in the above example,
just without a trigger or rule).
So there must be a difference between the 2 versions, but the documentation
hasn't been modified.
I used the default settings, no modifications in the config file.
