Thread: new.id has wrong value in INSERT RULE

Hi,

To explain the problem as clearly as I can I've included the schema, rule,
inserts, output and explanation below.

CREATE TABLE table_a (
    id                         serial     PRIMARY KEY,
    name                         text,
    active                     boolean     DEFAULT 't',
    date_created                 timestamp     DEFAULT
CURRENT_TIMESTAMP
);


CREATE TABLE table_b (
    id                         serial
PRIMARY KEY,
    table_a_id                    int
REFERENCES table_a ON DELETE CASCADE,
    yield                        int
NOT NULL,
    active                     boolean
DEFAULT 't',
    date_created                 timestamp
DEFAULT CURRENT_TIMESTAMP
);

-- to debug I've set 'yield' to be the value of new.id rather than the value
supplied in the insert statement (this is just to show what is happening
with new.id)
CREATE RULE table_b_insert_rule  AS ON INSERT TO table_b
    DO (UPDATE table_b SET active = 'f', yield = new.id WHERE table_a_id
= new.table_a_id AND id != new.id;);


INSERT INTO table_a (id, name) VALUES (1, 'test1');

SELECT * FROM table_a;

 id | name  | active |        date_created
----+-------+--------+----------------------------
  1 | test1 | t      | 2005-09-16 13:23:03.620813
(1 row)

INSERT INTO table_b (table_a_id, yield) VALUES (1, '100');

SELECT * FROM table_b;
 id | table_a_id | yield | active |        date_created
----+------------+-------+--------+----------------------------
  1 |          1 |     3 | f      | 2005-09-16 13:23:46.156202
(1 row)

The yield value is set to 3 rather than 1 as expected because the value of
new.id was 3.
This is totally unexpected behaviour, any help on getting to the bottom of
this is much appreciated.

It seems like table_b_id_seq is being incremented twice more that expected,
the first time you get 1 which is used as the id,
then it seems like it is being incremented it twice more and that's where
the value of 3 is coming from.
I've checked the increment value of the sequence as shown below and it is 1
as expected.

SELECT * FROM table_b_id_seq

 sequence_name  | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called
----------------+------------+--------------+---------------------+---------
--+-------------+---------+-----------+-----------
 table_b_id_seq |          3 |            1 | 9223372036854775807 |
1 |           1 |      30 | f         | t
(1 row)


I tried changing the value of the sequence increment to 3 and retested, see
output below.

ALTER SEQUENCE table_b_id_seq INCREMENT 3;

SELECT * FROM table_b_id_seq ;
 sequence_name  | last_value | increment_by |      max_value      |
min_value | cache_value | log_cnt | is_cycled | is_called
----------------+------------+--------------+---------------------+---------
--+-------------+---------+-----------+-----------
 table_b_id_seq |          3 |            3 | 9223372036854775807 |
1 |           1 |      30 | f         | t
(1 row)

INSERT INTO table_a (id, name) VALUES (2, 'test2');

SELECT * FROM table_a;
 id | name  | active |        date_created
----+-------+--------+----------------------------
  1 | test1 | t      | 2005-09-16 13:23:03.620813
  2 | test2 | t      | 2005-09-16 13:35:06.244128
(2 rows)

INSERT INTO table_b (table_a_id, yield) VALUES (2, '100');

SELECT * FROM table_b;
 id | table_a_id | yield | active |        date_created
----+------------+-------+--------+----------------------------
  1 |          1 |     3 | f      | 2005-09-16 13:23:46.156202
  6 |          2 |    12 | f      | 2005-09-16 13:35:36.843507
(2 rows)

It is clear to me that the value 6 in the id column is correct because I've
changed the increment to 3.
However the value of 12 in the yield column (Set by yield = new.id in the
rule) can only be obtained by SELECT nextval('table_b_id_seq') (or postgres
internal equiv) being called twice.

I'm using (PostgreSQL) 7.4.5 and can obviously work round this problem
easily but want to understnad what is going wrong.

thanks in advance

Ian

"Ian McFarland" <ian@lightershade.com> writes:
> CREATE RULE table_b_insert_rule  AS ON INSERT TO table_b
>     DO (UPDATE table_b SET active = 'f', yield = new.id WHERE table_a_id
> = new.table_a_id AND id != new.id;);

Rules are macros.  Since what is substituted for new.id in this case is
a nextval() call, you have a multiple-evaluation problem.  The above is
a pretty horrid way to do things anyway --- you should probably be using
a trigger instead.

            regards, tom lane