| From: | Uwe Bartels <uwe(dot)bartels(at)gmail(dot)com> |
|---|---|
| To: | pgsql-admin(at)postgresql(dot)org |
| Subject: | problem with rules |
| Date: | 2010-10-28 15:54:58 |
| Message-ID: | AANLkTinX=VZimwVJ-ZmF4HrqTTh94hbQRy790P3Bb0gT@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gémes Géza | 2010-10-29 05:12:04 | Re: Using LDAP with postgresql 8.2 |
| Previous Message | Kieren Scott | 2010-10-28 15:39:53 | Re: Using LDAP with postgresql 8.2 |