update problem in partitioned tables

From: "Josh Harrison" <joshques(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: update problem in partitioned tables
Date: 2007-09-12 15:35:10
Message-ID: 8d89ea1d0709120835o50d2020y514a812d40891f55@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi
I have the master-child tables as follows

Master table
create table foo(a numeric(10,0));

first child table foo1 as
create table foo1 (check(a>=0 and a<10)) inherits(foo)

second child table foo2 as
create table foo1 (check(a>=10) inherits(foo)

Now I create this function to redirect the inserts and updates on the
master table.

CREATE OR REPLACE FUNCTION foo_function () RETURNS trigger AS
'
BEGIN
IF (TG_OP = ''INSERT'') THEN
IF NEW.a >= 0 and NEW.a <10 THEN
INSERT INTO foo1 (a) values(new.a);

ELSIF (NEW.a >=10 ) THEN
INSERT INTO foo2 (a) values(new.a);
end if;

ELSIF (TG_OP = ''UPDATE'') THEN
IF (OLD.a>=0 AND OLD.a < 10 AND NEW.a >= 10) THEN

INSERT INTO foo2 (a) VALUES (NEW.a);
DELETE FROM foo1 WHERE foo1.a = OLD.a;

END IF;
END IF;
RETURN NULL;
END
' LANGUAGE 'plpgsql'
GO

And I create a before insert/update trigger on foo

CREATE TRIGGER FOO_TRIGGER
BEFORE INSERT OR UPDATE
ON FOO
FOR EACH ROW
EXECUTE PROCEDURE foo_function();

The insert statements are working properly.ie., they are inserting the rows
into the corresponding child tables.
But the update statements involving migration of row from 1 child to another
is not working

for example
UPDATE FOO SET A=5 WHERE A=4 works fine becoz the updated row remains in the
same child

But
UPDATE FOO SET A =20 WHERE A=4 gives the following error
"new row for relation "foo1" violates check constraint "foo1_a_check"

Why? All I wanted to do in this case is to insert that row into 'foo2'
table and delete it from 'foo1' table . What am I doing wrong here?
(Note: If I implement the same using rules it works fine!!!)

Thanks in advance
josh

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mike Charnoky 2007-09-12 15:42:20 problems with large table
Previous Message Dennis Muhlestein 2007-09-12 14:51:19 Re: Timestamp from an OID?