Re: partitioning / rules - strange behavior

On Tue, 06 Feb 2007 21:28:49 +0100, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:

>
> We're using sequence to generate the sessions(id) value, but that should
> not be a problem - with the structure / rules everything works fine (the
> current value in sessions_id_seq is about 8700000 so the values are
> inserted into the sessions_8500000 partition).

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)

[...]

>
> Now when I do for example
>
> =======================================================================
>
> INSERT INTO sessions(id,visitor_id,ip) VALUES (8900000,0,'127.0.0.1');
>
> =======================================================================
>
> this new row should be inserted into the session_8500000 partition as
> the 8900000 is clearly between 8500000 AND 8999999. It even seems
> succesfully inserted (no exception, returns INSERT 0 0 as usual), but
> once I do
>
> SELECT * FROM sessions WHERE id = 8900000
>
> it returns no rows. Even
>
> SELECT * FROM sessions_8500000 WHERE id = 8900000
>
> returns no rows. Here is the execution plan for the INSERT (the
> execution plan for the SELECT can be found above).
>

Maybe i'm missing something, but with constant values i'm not able
to reproduce this in my example above:

INSERT INTO sessions VALUES(200, 'xyz');
SELECT * FROM sessions_200 WHERE id = 200;
id | value
-----+-------
200 | xyz
(1 row)

INSERT INTO sessions VALUES(87, 'xyz');
SELECT * FROM sessions_100 WHERE id = 87;
id | value
----+-------
87 | xyz
(1 row)

Bernd