pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with rules
2+ messages / 2 participants
[nested] [flat]
* Problem with rules
@ 2005-08-25 21:53 Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Chris Hoover @ 2005-08-25 21:53 UTC (permalink / raw)
To: pgsql-admin
I am having a problem with the rule system in 7.3.4.
I have a view with an on insert do instead rule that is calling a
function and passing new.*. When try to insert a row into the view, I
get ERROR: ResolveNew: can't handle whole-tuple reference.
I was working with this in 8.0 on an R&D project. However, management
liked the concept I was working on so much, they asked me to try and
see if I can get it to run in our current environment of 7.3.4.
So far, this does not look good. Any way to get around this issue
(and no, a db upgrade at the time is not available. But one is
scheduled in the next 6 months).
Thanks,
Chris
^ permalink raw reply [nested|flat] 2+ messages in thread
* problem with rules
@ 2010-10-28 15:54 Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Uwe Bartels @ 2010-10-28 15:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-10-28 15:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-25 21:53 Problem with rules Chris Hoover <revoohc@gmail.com>
2010-10-28 15:54 problem with rules Uwe Bartels <uwe.bartels@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox