partitioning / rules - strange behavior - Mailing list pgsql-general

Hi,

we're running PostgreSQL 8.1.4 and I've encountered some strange
problems with partitioning / rules, and I really don't know how to
fix it.

We have a table 'sessions' partitioned along the 'id' column which is a
primary key. Each partition holds at most 500.000 rows, i.e. partition
'sessions_0' holds rows with ids 0 - 499.999, 'sessions_500000' holds
rows with ids 500.000 - 999.999 and so on. We're currently using
partition 'sessions_8500000'.

I think the structure of the table is not important here, but there's
nothing special about it - several columns, some foreign keys etc. There
are no triggers on it or on the partitions.

Inserts are redirected into the correct partition by RULES on the
'sessions' table, i.e.

CREATE OR REPLACE RULE insert_8500000 AS ON INSERT TO sessions WHERE
(id BETWEEN 8500000 AND 8999999) DO INSTEAD INSERT INTO sessions_8500000
(... columns here ...) VALUES (... NEW.columns ...);

The problem is that once I create a rule for the next partition (it
already exists), it simply stops working. It seems AS if the INSERT is
succesfully redirected into the correct partition, but no data are inserted.

So for example when I create a rule

CREATE OR REPLACE RULE insert_9000000 AS ON INSERT TO sessions WHERE
(id BETWEEN 9000000 AND 9499999) DO INSTEAD INSERT INTO sessions_9000000
(... columns here ...) VALUES (... NEW.columns ...);

the rule insert_8500000 does not work anymore. When I insert a row with
id between 8500000 and 8999999 it prints 'INSERT 0 0' as usual but when
I try to fetch it no rows are found. That is

db=> INSERT INTO sessions(... non-pk columns ...) VALUES (... data ...);
INSERT 0 0
db=> SELECT * FROM sessions WHERE id = currval('sessions_id_seq');
(0 rows)

which seems really strange to me. We're using sequence to generate the
ids, but that shouldn't be a problem (at least it was not till today).

All these problems started when one of our stored procedures used for a
maintenance crashed. This procedure checks the currently used partition,
checks if the 'next one' exists and creates the partition & rules / drop
the old rules if needed. So there are some dynamic SQL, basically

  EXECUTE 'CREATE TABLE ...';
  EXECUTE 'CREATE RULE ...';
  EXECUTE 'CREATE INDEX ...';
  EXECUTE 'DROP RULE ...';

I did some changes to this procedure recently and a stupid mistype in
one of the SQL commands caused a runtime EXCEPTION yesterday. It created
the next partition (sessions_9000000), and then failed because of the
mistype, and this is the moment the problems described above began.

This is really strange, as I thought all the procedures are running as a
transaction, so a failure shouldn't cause such problems. I've fixed the
mistype and rerun the procedure (succesfully) but it didn't help.

I've tried to recreate the rules (drop / create) manually, but still no
change. But when I drop the new rule (insert_9000000) it works fine, and
once I create it again it stops working.

What I suspect is the failure in the stored procedure did something
wrong to the catalogs but maybe I'm completely wrong. Anyway I really
don't know how to fix it.

thanks for all advices
Tomas