problem with rules

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

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.

best regards,
Uwe


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux