Baffling behavior regarding tables as types - Mailing list pgsql-general
| From | Chris Travers |
|---|---|
| Subject | Baffling behavior regarding tables as types |
| Date | |
| Msg-id | CAKt_Zfs3jyp42x5vhi2ND1gXk+UKGZt=Sk_S-JzSXGi8J1OR4w@mail.gmail.com Whole thread Raw |
| List | pgsql-general |
Hi all;
I figure this is a good way of opening the question of "what should the behavior be?" We discussed this a bit on bugs, and in the past in general. However, the behavior of composite types (and table types) as columns of data is remarkably inconsistent and I think that if you work in this area much the only thing one can conclude is that it involves discovering how little consensus there is on how these should behave and negotiating all sorts of conflicting assumptions that can be confusing at times.
In my view I think we'd do better to push for some consistency one way or another. One line might be to check all type constraints at storage time, the way domains in complex types are currently checked. In other words, if the table type has a not null, check it on the column when used as a collection. If it has a check constraint check it.
The other option would be to make the table check all constraints internally within the collection types, which is the way it currently works when domains are not involved. I am hoping perhaps we can get at least on the same page and decide how, in our undefined future version, things will eventually work, then possibly it will be possible to get there. As it is right now, the discussions suggest to me (and the database behavior even moreso) that we are not on the same page.
So with this in mind, consider these two examples.
Example 1: Shows that domain constraints are checked on storage.
Not null positive int:
or_examples=# create domain pos_not_null_int as int not null check (value > 0);
CREATE DOMAIN
Table containing such:
or_examples=# create table rel_examples.domaintest (id pos_not_null_int);
CREATE TABLE
constraints are enforced as expected on the simple column so we can show there is no problem:
or_examples=# insert into rel_examples.domaintest values (-1);
ERROR: value for domain pos_not_null_int violates check constraint "pos_not_null_int_check"
or_examples=# insert into rel_examples.domaintest values (null);
ERROR: domain pos_not_null_int does not allow null values
Table using our above test table as a type:
or_examples=# create table comp_domain_test ( text rel_examples.domaintest);
CREATE TABLE
Same constraints are enforced on the element of the tuple in the column:
or_examples=# insert into comp_domain_test values (row(null));
ERROR: domain pos_not_null_int does not allow null values
or_examples=# insert into comp_domain_test values (row(-1));
ERROR: value for domain pos_not_null_int violates check constraint "pos_not_null_int_check"
Example 2: Shoes that non-domain constraints are not checked on storage
Same but without domain:
or_examples=# create table rel_examples.tabletest (id int not null check (id > 0));
CREATE TABLE
Show that the constraints are enforced in the simple table:
or_examples=# insert into rel_examples.tabletest values (null);
ERROR: null value in column "id" violates not-null constraint
or_examples=# insert into rel_examples.tabletest values (-1);
ERROR: new row for relation "tabletest" violates check constraint "tabletest_id_check"
Table using other table as type:
or_examples=# create table comp_table_test (test rel_examples.tabletest);
CREATE TABLE
Constraints not enforced:
or_examples=# insert into comp_table_test values (row(null));
INSERT 0 1 ^
or_examples=# insert into comp_table_test values (row(-1));
INSERT 0 1
Are both of these correct behavior long-term? Should they be made consistent long-term?
Best Wishes,
Chris Travers
pgsql-general by date: