From: | AI Rumman <rummandba(at)gmail(dot)com> |
---|---|
To: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | 8.1 Table partition and getting error |
Date: | 2010-10-07 11:06:44 |
Message-ID: | AANLkTim3Se8WbJam--eEibWjsUC3gJccicH9UyqSgTj3@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am using POstgreql 8.1.
I create table partition as follows:
alter table crm rename to crm_bak;
CREATE TABLE crm
(
crmid integer NOT NULL,
description text,
deleted integer NOT NULL DEFAULT 0
)
WITHOUT OIDS;
ALTER TABLE crm OWNER TO vcrm;
create table crm_deleted ( check ( deleted = 1 ) ) inherits (crm);
create table crm_active ( check ( deleted = 0 ) ) inherits (crm);
create index crm_deleted_idx on crm_active(deleted);
analyze crm_active;
CREATE OR REPLACE FUNCTION crm_insert_p()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.deleted = 0 ) THEN
INSERT INTO crm_active VALUES (NEW.*);
ELSE
INSERT INTO crm_deleted VALUES (NEW.*);
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER crm_insert_t
BEFORE INSERT ON crm
FOR EACH ROW EXECUTE PROCEDURE crm_insert_p();
CREATE OR REPLACE FUNCTION crm_update_deleted_p()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.deleted = 1) THEN
INSERT INTO crm_deleted VALUES (NEW.*);
DELETE FROM crm_active WHERE crmid = NEW.crmid;
ELSE
RETURN (NEW.*);
END IF;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER crm_update_t
BEFORE UPDATE ON crm
FOR EACH ROW EXECUTE PROCEDURE crm_update_deleted_p();
INSERT INTO crm
SELECT * FROM crm_bak;
select count(*) from crm;
select count(*) from crm_active;
select count(*) from crm_deleted;
set constraint_exclusion = on;
----------------------------------------------
It works fine.
But when I want to use the following sql, I get error:
update crm set deleted = 1 where crmid = 3;
ERROR: new row for relation "crm_active" violates check constraint
"crm_active_deleted_check"
Any idea please.
From | Date | Subject | |
---|---|---|---|
Next Message | quickinfo quickinfo | 2010-10-07 11:28:39 | postgreSQL for Windows 7 |
Previous Message | Rajesh Kumar Mallah | 2010-10-07 11:04:59 | Re: Hot standby usage issue |