Re: partitioning / rules - strange behavior

> If there´s only the insert_8500000 RULE then everything works as expected - the
> insert prints "INSERT 0 0", the row is inserted into the correct partition
> which is sessions_8500000 - I can fetch it using either
>
> SELECT * FROM sessions WHERE id = currval('sessions_id_seq');
>
> or direcly by
>
> SELECT * FROM sessions_8500000 WHERE id = currval('sessions_id_seq');
>
> When I create the next next rule (insert_9000000 for ids between 9000000 and
> 9499999) it stops working - it prints "INSERT 0 0" just as before, everything
> seems fine, but the row disappears - it's not available .
>
> I'm not sure about the query plans, but I think I've checked that and everything
> seemed ok - all the partitions were used as far as I remember. But this
> shouldn't be a problem as we have not reached the 9000000 limit yet (so the new
> partition is not used at all). And we've tried to restart the PostgreSQL as the
> last hope, yesterday, so there really should be no old plans.
>
> I don't have an access to the production database (I have not been able to
> simulate this on the development/testing system) - I'll play with that at night
> (european time). I'll try to drop / recreate the partition (I've tried to
> recreate only the RULEs, not the partitions).
>
> Tomas

OK, I did some tests a while ago and the bad news is I still was not
able to fix it. The table structure is this

=======================================================================

db=> \d sessions
Table "public.sessions"
Column | Type |
Modifiers
------------------+-----------------------------+---------------------
id | integer | not null default
nextval('sessions_id_seq'::regclass)
browser_id | integer |
os_id | integer |
arch_id | integer |
language_id | character(2) |
country_id | character(2) |
visitor_id | integer | not null
ip | inet | not null
ip_forward | inet |
session_date | timestamp without time zone | not null default now()
user_agent | character varying(255) |
screen_width | smallint |
screen_height | smallint |
screen_bit_depth | smallint |
javascript | boolean | default false
browser_minor | character varying(16) |
browser_major | character varying(16) |
referer | text |
last_action | integer | not null default 0
Indexes:
"sessions_pkey" PRIMARY KEY, btree (id)
Check constraints:
... some foreign keys, not important here ...
Rules:
insert_8500000 AS
ON INSERT TO sessions
WHERE new.id >= 8500000 AND new.id <= 8999999 DO INSTEAD INSERT INTO
sessions_8500000 (id, browser_id, os_id, arch_id, language_id,
country_id, visitor_id, ip, ip_forward, session_date, user_agent,
screen_width, screen_height, screen_bit_depth, javascript,
browser_minor, browser_major, referer, last_action)
VALUES (new.id, new.browser_id, new.os_id, new.arch_id,
new.language_id, new.country_id, new.visitor_id, new.ip, new.ip_forward,
new.session_date, new.user_agent, new.screen_width, new.screen_height,
new.screen_bit_depth, new.javascript, new.browser_minor,
new.browser_major, new.referer, new.last_action)

=======================================================================

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).

The I create the 'next partition' for values between 9000000 and 9499999
using

=======================================================================

CREATE TABLE sessions_9000000 (
CHECK (id BETWEEN 9000000 AND 9499999),
PRIMARY KEY (id)
) INHERITS (sessions);

=======================================================================

and everything still seems fine, even the execution plans reflect this
new child table:

=======================================================================

db=> explain select * from sessions;
QUERY PLAN
----------------------------------------------------------------------
Result (cost=0.00..52262.48 rows=1052924 width=775)
-> Append (cost=0.00..52262.48 rows=1052924 width=775)
-> Seq Scan on sessions (cost=0.00..12.00 rows=100 width=775)
-> Seq Scan on sessions_8000000 sessions (cost=0.00..23128.78
rows=500539 width=280)
-> Seq Scan on sessions_8500000 sessions (cost=0.00..6147.60
rows=51230 width=775)
-> Seq Scan on sessions_9000000 sessions (cost=0.00..12.00
rows=100 width=775)

=======================================================================

but one I create a RULE for the new partition, thing go wrong. That is I
execute this (I ommited the list of columns)

=======================================================================

CREATE RULE insert_9000000 AS ON INSERT TO sessions WHERE (id BETWEEN
9000000 AND 9499999) DO INSTEAD INSERT INTO sessions_9000000 ( ... all
the columns in sessions) VALUES ( ... all the columns in sessions
prefixed with 'NEW' ...);

=======================================================================

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).

=======================================================================

db=> EXPLAIN ANALYZE INSERT INTO sessions(id,visitor_id,ip) VALUES
(8900000,0,'127.0.0.1');
QUERY PLAN
----------------------------------------------------------------------
Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.002..0.002
rows=0 loops=1)
One-Time Filter: ((true IS NOT TRUE) AND (false IS NOT TRUE))
Total runtime: 0.063 ms

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.012..0.013
rows=1 loops=1)
... some triggers for foreign keys on 'sessions' ...
Total runtime: 0.209 ms

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.001..0.001
rows=0 loops=1)
One-Time Filter: false
Total runtime: 0.052 ms
(15 rows)

====================================================================

The 'funny' thing is once I drop that new rule (insert_9000000) it
starts working again.

I really don't know how to solve this - today I've tried to drop /
recreate the new _9000000 partitions (which are still empty) but no
luck. Tomorrow I'll restore a fresh backup on a development system, and
try if it 'works' in the same way.

Maybe I'm missing something, but I see nothing wrong in the partitions
and rules. I've taken the current backup (taken at night) and loaded
that at the development system - everything works exactly as expected
with exactly the same set-up. BTW we're using PostgreSQL 8.1.4 (on
Linux) on both machines.

Thanks in advance for all your advices how to fix this, optimally with
as little downtime as possible.

Tomas