Skip site navigation (1) Skip section navigation (2)

BUG #4741: Domain constraint violation with trigger

From: "Brice Maron & Duchesne P-A" <bmaron(at)naturalsciences(dot)be>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #4741: Domain constraint violation with trigger
Date: 2009-03-30 14:20:24
Message-ID: 200903301420.n2UEKORu030569@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      4741
Logged by:          Brice Maron & Duchesne P-A
Email address:      bmaron(at)naturalsciences(dot)be
PostgreSQL version: 8.3.7
Operating system:   i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20071124 (Red Hat 4.1.2-42)
Description:        Domain constraint violation with trigger
Details: 

Hi, 
we've got a problem with a NOT Null constraint on a domain in a trigger.

When, in a trigger, we put a null into the not nullable domain, postgresql
doesn't throw an error.
We extracted the simplified test case below from our application:

CREATE LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_function() RETURNS TRIGGER AS $function$
    BEGIN
        SELECT NULL INTO new.text;
        RETURN new;
    END;
$function$ LANGUAGE plpgsql;

DROP TABLE IF EXISTS test;

-- Table without a domain throws an error

CREATE TABLE test(id integer not null, text varchar default '' not null);
CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE test_function();

INSERT INTO test VALUES(1);

-- ERROR:  null value in column "text" violates not-null constraint

DROP TABLE IF EXISTS test;

-- Table with the same trigger AND a domain let a null value go into the
table

CREATE DOMAIN test_domain AS varchar DEFAULT '' NOT NULL;

CREATE TABLE test(id integer not null, text test_domain);
CREATE TRIGGER test_trigger BEFORE INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE test_function();

INSERT INTO test VALUES(1);

-- INSERT 0 1

SELECT * FROM test WHERE text IS null;

--  id | text 
-- ----+------
--   1 | 
-- (1 row)

Responses

pgsql-bugs by date

Next:From: Martin PittDate: 2009-03-30 14:34:36
Subject: shared_buffers/SHMMAX defaults?
Previous:From: Tom LaneDate: 2009-03-28 19:14:05
Subject: Re: BUG #4739: strange behavior of EXTRACT(quarter from INTERVAL)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group