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
Views: Raw Message | Whole Thread | Download mbox
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

Browse pgsql-bugs by date

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