8.1 Table partition and getting error

-----Original Message-----
From: AI Rumman [mailto:rummandba@gmail.com]
Sent: Thursday, October 07, 2010 7:07 AM
To: pgsql-general General
Subject: 8.1 Table partition and getting error

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.

Change your on UPDATE trigger function to:

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;
RETURN NULL; -- so that that trigger doesn't proceed
with UPDATE on crm_active table
ELSE
RETURN (NEW.*);
END IF;
END;
$$
LANGUAGE plpgsql;

Regards,
Igor Neyman