Insert into partitioned table return violates check constraint but shouldn't

Question

I've a table in a postgresql and I want it to be partitioned. The structure is below

TABLE "DTD1"."logAdminActivity" ( 
    "username" CHARACTER VARYING( 200 ) NOT NULL, 
    "action" CHARACTER VARYING( 100 ) NOT NULL, 
    "pk" CHARACTER VARYING( 5 )  NOT NULL, 
    "tabel" CHARACTER VARYING( 200 )NOT NULL, 
    "timestamp" TIMESTAMP WITHOUT TIME ZONE
);

Then I've create some partition table that inherit Tabel "DTD1"."logAdminActivity" above look like this:

CREATE TABLE "DTD1".logAdminActivity_kategori ( 
    CHECK ('tabel'='kategori')
) INHERITS ("DTD1"."logAdminActivity");

CREATE TABLE "DTD1".logAdminActivity_subyek (
    CHECK ('tabel'='subyek')
 ) INHERITS ("DTD1"."logAdminActivity");

...

CREATE TABLE "DTD1".logAdminActivity_satuan (
    CHECK ('tabel'='satuan')
 ) INHERITS ("DTD1"."logAdminActivity");

CREATE TABLE "DTD1".logAdminActivity_memberfilter (
    CHECK ('tabel'='memberFilter')
 ) INHERITS ("DTD1"."logAdminActivity");

After that I create indexes each partitioned table in username coloumn. Then I create this trigger function and trigger that call that trigger function in below. So, when I insert the data, the coresponding tabel coloumn will be redirected into proper partition table.

CREATE OR REPLACE FUNCTION "DTD1".logAdminActivity_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( New."tabel" = 'kategori' ) THEN
        INSERT INTO "DTD1".logAdminActivity_kategori VALUES (NEW.*);
    ELSIF ( New."tabel" = 'subyek' ) THEN
        INSERT INTO "DTD1".logAdminActivity_subyek VALUES (NEW.*);
    ..    
    ELSE
        RAISE EXCEPTION 'Tabel out of range.  Fix the logAdminActivity_insert_trigger() function!' ;
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_logAdminActivity_trigger
    BEFORE INSERT ON "DTD1"."logAdminActivity"
    FOR EACH ROW EXECUTE PROCEDURE "DTD1".logAdminActivity_insert_trigger();

Then I test it by insert procedure such as

INSERT INTO "DTD1"."logadminactivity_subyek" ( "action", "pk", "tabel", "timestamp", "username") 
VALUES ( 'bla', '12312', 'subyek', '2014-01-01 02:02:03', 'asdf' );

But why it return error look like this

ERROR: new row for relation "logadminactivity_subyek" violates check constraint "logadminactivity_subyek_check" DETAIL: Failing row contains (asdf, bla, subyek, 12312, 2014-01-01 02:02:03).

How it can be happened because I've try to follow the documentation in this ?

http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

I think the 'tabel' value from query above ('subyek') is not match with trigger function but when I check with check constrain it pass. Is there any part I miss about it or is there any solution to solve this problem?

Regards

score 2 · Answer 1 · answered Jun 08 '14 at 18:42

2

CHECK ('tabel'='subyek')

That check constraint is incorrect because 'tabel' is a constant.

It is equivalent to CHECK (false).

You want

CHECK ("tabel"='subyek')

answered Jun 08 '14 at 18:42

Daniel Vérité

58,074
15
129
156

Oh, it's make tabel become a constant, thanks. And it works but is it true if data also is stored in master table (in my place it did)? – m hanif f Jun 08 '14 at 19:50
The trigger ends with `RETURN NULL`, so that prevents the insertion into the master table. I'm afraid I don't get what you mean by: _in my place it did_. – Daniel Vérité Jun 08 '14 at 20:08
When I insert some case, there are some data in master table which is already exist in proper partition table(redundant between table master and child table). Is there any wrong in trigger funtion? – m hanif f Jun 08 '14 at 22:09
I've check it again and reposition the function trigger into trigger part of master table not general trigger but when I try to insert some cases, the redudancy data(childrean-parent) always appear. Is there any configuration/script/something to fix around this? – m hanif f Jun 09 '14 at 01:50

Insert into partitioned table return violates check constraint but shouldn't

1 Answers1