i'm trying to reorganize a big table without having a downtime at all. I'm trying to realize this via partitioning and rules.
- step 1 preparation create table uglybloatedtable (id bigserial, textfield text, primary key(id)); INSERT INTO uglybloatedtable (textfield) values (''); INSERT INTO uglybloatedtable (textfield) values (''); INSERT INTO uglybloatedtable (textfield) values (''); INSERT INTO uglybloatedtable (textfield) values (''); INSERT INTO uglybloatedtable (textfield) values (''); INSERT INTO uglybloatedtable (textfield) values ('');
- step 2 transform table into a partitioned table with an additional partition key create table nicenonbloatedtable1 (id bigint not null default nextval('uglybloatedtable_id_seq'::regclass), textfield text, partitioningkey char(1) default '1') inherits (uglybloatedtable);
alter table nicenonbloatedtable1 add partitioningkey char(1) default '1'; alter table add constraint pcheck_uglybloatedtable1 CHECK (partitioningkey=1); alter table nicenonbloatedtable1 add constraint pcheck_uglybloatedtable1 CHECK (partitioningkey='1');
- step 3 redirect inserts via rule CREATE RULE nicenonbloatedtable1_insert AS ON INSERT TO uglybloatedtable DO INSTEAD INSERT INTO nicenonbloatedtable1 (id,textfield) VALUES (NEW.*);
- step 4 handle updates - this does not work yet. CREATE or REPLACE RULE nicenonbloatedtable1_update AS ON UPDATE TO uglybloatedtable DO INSTEAD ( DELETE FROM uglybloatedtable where id=OLD.id; DELETE FROM nicenonbloatedtable1 where id=OLD.id; INSERT INTO nicenonbloatedtable1 (id,textfield) VALUES (NEW.*); );
running an insert, redirects the insert into the partition - fine. running an update like update uglybloatedtable set id=2, textfield='migrated' where id=8; removes the row in uglybloatedtable, but does not insert the row into the table nicenonbloatedtable1.
So now I have 2 questions: - what's wrong? - how can i debug these rules? is there a way to see the executed statements? explain plan did not help, set client_min_messages=debug5 neither.
