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

> 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