Thread: 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