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
Следующее
От: Magnus Hagander
Дата:
Сообщение: Re: BUG #16079: Question Regarding the BUG #16064