Bug when creating domain from composite types
От | Florian Koch |
---|---|
Тема | Bug when creating domain from composite types |
Дата | |
Msg-id | CAMaXqR-0eJZc3jQDp=h4jXoGp2HOfA8LoFgwrCt_HPrKyoePtA@mail.gmail.com обсуждение исходный текст |
Список | 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
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #16119: pg_dump omits columns specification for matviews