> It's likely to be a problem because of multiple evaluations of volatile expressions
> in the rule rewrite system....short example:
>
> CREATE TABLE sessions(id SERIAL PRIMARY KEY, value TEXT); ^
> CREATE TABLE sessions_100(CHECK(id BETWEEN 1 AND 100), PRIMARY KEY(id))
> INHERITS(sessions);
> CREATE TABLE sessions_200(CHECK(id BETWEEN 101 AND 200), PRIMARY KEY(id))
> INHERITS(sessions);
>
> CREATE OR REPLACE RULE insert_100
> AS ON INSERT TO sessions
> WHERE NEW.id BETWEEN 1 AND 100
> DO INSTEAD
> INSERT INTO sessions_100(id, value) VALUES(NEW.id, NEW.value);
>
> CREATE OR REPLACE RULE insert_200
> AS ON INSERT TO sessions
> WHERE NEW.id BETWEEN 101 AND 200
> DO INSTEAD
> INSERT INTO sessions_200(id, value) VALUES(NEW.id, NEW.value);
>
> INSERT INTO sessions(value) VALUES('bernd');
>
> SELECT * FROM sessions;
> id | value
> ----+-------
> 5 | bernd
> (1 row)
>
> but...
>
> SELECT * FROM sessions WHERE id = currval('sessions_id_seq');
> id | value
> ----+-------
> (0 rows)
>
> SELECT currval('sessions_id_seq');
> currval
> ---------
> 6
> (1 row)
Ouch! I've never noticed this behavior! When I do
db=> SELECT currval('sessions_id_seq');
currval
---------
6
db=> INSERT INTO sessions(value) VALUES('bernd');
INSERT 0 0
db=> SELECT currval('sessions_id_seq');
currval
---------
12
I'll check if this is the reason why it works on the development system
and not on the production.
But I don't understand why the nextval('sessions_id_seq') is evaluated
multiple times? Even when I do
INSERT INTO sessions(id,value) VALUES(nextval('sessions_id_seq','x');
it calls sessions_id_seq several times. I'll fix it by first fetching
the ID and then using it as a constant value in the INSERT, but I'd like
to know the reason why it works this way.
Tomas
