Re: 8.1 Table partition and getting error

> -----Original Message-----
> From: AI Rumman [mailto:rummandba(at)gmail(dot)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