problem with rules - Mailing list pgsql-sql

Hi,<br /><br />i'm trying to reorganize a big table without having a downtime at all. I'm trying to realize this via
partitioningand rules.<br /><br /><span style="font-family: courier new,monospace;">- step 1 preparation</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">create table
uglybloatedtable(id bigserial, textfield text, primary key(id));</span><br /><span style="font-family: courier
new,monospace;">INSERTINTO uglybloatedtable (textfield<span style="font-family: courier new,monospace;">) values
('');</span><brstyle="font-family: courier new,monospace;" /></span><span style="font-family: courier
new,monospace;">INSERTINTO uglybloatedtable (textfield<span style="font-family: courier new,monospace;">) values
('');</span><brstyle="font-family: courier new,monospace;" /></span><span style="font-family: courier
new,monospace;">INSERTINTO uglybloatedtable (textfield<span style="font-family: courier new,monospace;">) values
('');</span><brstyle="font-family: courier new,monospace;" /></span><span style="font-family: courier
new,monospace;">INSERTINTO uglybloatedtable (textfield<span style="font-family: courier new,monospace;">) values
('');</span><brstyle="font-family: courier new,monospace;" /></span><span style="font-family: courier
new,monospace;">INSERTINTO uglybloatedtable (textfield<span style="font-family: courier new,monospace;">) values
('');</span><brstyle="font-family: courier new,monospace;" /></span><span style="font-family: courier
new,monospace;">INSERTINTO uglybloatedtable (textfield<span style="font-family: courier new,monospace;">) values
('');</span><brstyle="font-family: courier new,monospace;" /></span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier new,monospace;">- step 2 transform table into a partitioned table with an additional
partitionkey</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">createtable nicenonbloatedtable1 (id bigint not null default
nextval('uglybloatedtable_id_seq'::regclass),textfield text, partitioningkey char(1) default '1') inherits
(uglybloatedtable);</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">alter table nicenonbloatedtable1 add partitioningkey
char(1)default '1';</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">altertable add constraint pcheck_uglybloatedtable1 CHECK (partitioningkey=1);</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">alter table
nicenonbloatedtable1add constraint pcheck_uglybloatedtable1 CHECK (partitioningkey='1');</span><br style="font-family:
couriernew,monospace;" /><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">-step 3 redirect inserts via rule</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;">CREATE RULE nicenonbloatedtable1_insert AS</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;">ON INSERT TO uglybloatedtable</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">DO INSTEAD</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">    INSERT INTO
nicenonbloatedtable1(id,textfield) VALUES (NEW.*);</span><br style="font-family: courier new,monospace;" /><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">- step 4 handle updates
-this does not work yet.</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">CREATEor REPLACE RULE nicenonbloatedtable1_update AS</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">ON UPDATE TO uglybloatedtable</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">DO INSTEAD (</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">    DELETE FROM
uglybloatedtablewhere id=OLD.id;</span><br style="font-family: courier new,monospace;" /><span style="font-family:
couriernew,monospace;">    DELETE FROM nicenonbloatedtable1 where id=OLD.id;</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">    INSERT INTO nicenonbloatedtable1 (id,textfield)
VALUES(NEW.*);</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;">);</span><br/><br />running an insert, redirects the insert into the partition - fine.<br />running an
updatelike<br />update uglybloatedtable set id=2, textfield='migrated' where id=8;<br /> removes the row in
uglybloatedtable,but does not insert the row into the table <span style="font-family: courier
new,monospace;">nicenonbloatedtable1.<br/><br /></span>So now I have 2 questions:<br />- what's wrong?<br />- how can i
debugthese rules? is there a way to see the executed statements? explain plan did not help, set
client_min_messages=debug5neither.<br /><br />best regards,<br /><font color="#888888">Uwe<br /></font><br />