Bug when creating domain from composite types

From: Florian Koch <florat(dot)min(at)gmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Bug when creating domain from composite types
Date: 2019-11-16 07:40:27
Message-ID: CAMaXqR-0eJZc3jQDp=h4jXoGp2HOfA8LoFgwrCt_HPrKyoePtA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello everyone,

when developing my typesystem I ran into the following related bugs:

CREATE DOMAIN test_domain1 AS text CHECK( VALUE ~ '^[a-z]{2}$');
CREATE DOMAIN test_domain2 AS text CHECK( VALUE ~ '^[0-9]{2}$');
CREATE TYPE test_type AS (
test1 test_domain1,
test2 test_domain2
);
CREATE TABLE table_test1 (
id test_domain1
);
INSERT INTO table_test1 (id)
VALUES
('aa'),
('bb');
CREATE TABLE table_test2 (
id test_domain2
);
INSERT INTO table_test2 (id)
VALUES
('11'),
('22');
CREATE OR REPLACE FUNCTION chk_test_input(test_input test_type)
RETURNS boolean AS
$$
BEGIN
IF ((test_input).test1 IS NULL OR EXISTS (SELECT 1 FROM table_test1
WHERE id = (test_input).test1))
OR
((test_input).test2 IS NULL OR EXISTS (SELECT 1 FROM table_test2
WHERE id = (test_input).test2))
THEN
RETURN true;
END IF;
RETURN false;
END;
$$ LANGUAGE PLPGSQL;
CREATE DOMAIN ref_test_type AS test_type CHECK (chk_test_input(value));
SELECT ('aa', '11')::test_type; -- works as expected
SELECT ('aa', '11')::ref_test_type; -- works as expected
CREATE TABLE table_test3 (
id ref_test_type
);
-- when inserting a single row the shorthand notation works as expected
INSERT INTO table_test3 ( id.test1, id.test2 ) VALUES ( 'aa', '11');
INSERT 0 1
-- but when inserting more than one row with the shorthand notation I
get an error
INSERT INTO table_test3 ( id.test1, id.test2 ) VALUES ( 'aa', '11'),
('bb', '22');
ERROR: 23514: value for domain test_domain1 violates check constraint
"test_domain1_check"
SCHEMA NAME: public
DATATYPE NAME: test_domain1
CONSTRAINT NAME: test_domain1_check
LOCATION: ExecEvalConstraintCheck, execExprInterp.c:3522
-- with the verbose form there is no problem inserting more than one row
INSERT INTO table_test3 (id) VALUES (('aa', '11')::ref_test_type),
(('bb', '22')::ref_test_type);
INSERT 0 2

-- this is a simpler construction, where the type is not build from
domain types and gives for the same operation a different error
CREATE TYPE test_type2 AS (
test3 text,
test4 int
);
CREATE TABLE table_test4 (
id text
);
INSERT INTO table_test4 (id)
VALUES
('aa'),
('bb');
CREATE TABLE table_test5 (
id int
);
INSERT INTO table_test5 (id)
VALUES
('11'),
('22');
CREATE OR REPLACE FUNCTION chk_test_input2(test_input test_type2)
RETURNS boolean AS
$$
BEGIN
IF ((test_input).test3 IS NULL OR EXISTS (SELECT 1 FROM table_test4
WHERE id = (test_input).test3))
OR
((test_input).test4 IS NULL OR EXISTS (SELECT 1 FROM table_test5
WHERE id = (test_input).test4))
THEN
RETURN true;
END IF;
RETURN false;
END;
$$ LANGUAGE PLPGSQL;
CREATE domain ref_test_type2 AS test_type2 CHECK (chk_test_input2(value));
CREATE TABLE table_test6 (
id ref_test_type2
);
-- when inserting a single row the shorthand notation works as expected
INSERT INTO table_test6 ( id.test3, id.test4 ) VALUES ( 'aa', '11');
INSERT 0 1
-- but when inserting more than one row with the shorthand notation I
get an error (this time different error message)
INSERT INTO table_test6 ( id.test3, id.test4 ) VALUES ( 'aa', '11'),
('bb', '22');
ERROR: 42804: subfield "test4" is of type integer but expression is
of type ref_test_type2
LINE 1: insert into table_test6 ( id.test3, id.test4 ) values ( 'aa'...
^
HINT: You will need to rewrite or cast the expression.
LOCATION: transformAssignmentIndirection, parse_target.c:877
-- the verbose form again works as expected
INSERT INTO table_test6 (id) VALUES (('aa', '11')::ref_test_type2),
(('bb', '22')::ref_test_type2);
INSERT 0 2

Tested with PostgreSQL 12.0 (Debian 12.0-2.pgdg100+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit in
docker container and with PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1)
on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04)
7.4.0, 64-bit on my local machine.

Best regards
Florat

Browse pgsql-bugs by date

  From Date Subject
Next Message Magnus Hagander 2019-11-16 13:29:58 Re: BUG #16079: Question Regarding the BUG #16064
Previous Message Tom Lane 2019-11-16 06:05:36 Re: BUG #16119: pg_dump omits columns specification for matviews