pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with Rules
3+ messages / 3 participants
[nested] [flat]
* Problem with Rules
@ 1999-07-22 14:35 Peter Eisentraut <peter@pathwaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Eisentraut @ 1999-07-22 14:35 UTC (permalink / raw)
To: pgsql-sql
I am trying to create some rules for referential integrity. A generic
example:
test=> create table one (id int4, name text);
test=> create table two (ref int4, descr text);
So lets say the "ref" references the "id" and I want to ensure update and
delete cascade when table "one" is modified:
test=> create rule one_delete_cascade as on delete to one do delete from
two where ref = current.id;
ERROR: current: Table does not exist.
test=> create rule one_update_cascade as on update to one do update two
set ref=new.id where ref=current.id;
ERROR: current: Table does not exist.
Apparently, it recognizes the "new" pseudo-instance but not the "current"
one. Please don't tell me "current" is not yet implemented.
test=> select version();
version
--------------------------------------------------------------
PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3
Thanks.
--
Peter Eisentraut
PathWay Computing, Inc.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] Problem with Rules
@ 1999-07-22 16:58 Jan Wieck <wieck@debis.com>
parent: Peter Eisentraut <peter@pathwaynet.com>
0 siblings, 0 replies; 3+ messages in thread
From: wieck@debis.com @ 1999-07-22 16:58 UTC (permalink / raw)
To: peter@pathwaynet.com; +Cc: pgsql-sql
>
> I am trying to create some rules for referential integrity. A generic
> example:
>
> test=> create table one (id int4, name text);
>
> test=> create table two (ref int4, descr text);
>
> So lets say the "ref" references the "id" and I want to ensure update and
> delete cascade when table "one" is modified:
>
> test=> create rule one_delete_cascade as on delete to one do delete from
> two where ref = current.id;
> ERROR: current: Table does not exist.
>
> test=> create rule one_update_cascade as on update to one do update two
> set ref=new.id where ref=current.id;
> ERROR: current: Table does not exist.
>
> Apparently, it recognizes the "new" pseudo-instance but not the "current"
> one. Please don't tell me "current" is not yet implemented.
>
>
> test=> select version();
> version
> --------------------------------------------------------------
> PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc 2.7.2.3
CURRENT has been replaced by OLD in v6.4 but still supported.
It has been removed in v6.5 so you have to use OLD now.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 3+ messages in thread
* problem with rules
@ 2010-11-02 07:54 Uwe Bartels <uwe.bartels@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Uwe Bartels @ 2010-11-02 07:54 UTC (permalink / raw)
To: pgsql-sql
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] 3+ messages in thread
end of thread, other threads:[~2010-11-02 07:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-22 14:35 Problem with Rules Peter Eisentraut <peter@pathwaynet.com>
1999-07-22 16:58 ` Jan Wieck <wieck@debis.com>
2010-11-02 07: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