Re: PostgreSQL domains and NOT NULL constraint - Mailing list pgsql-hackers

From Vik Fearing
Subject Re: PostgreSQL domains and NOT NULL constraint
Date
Msg-id 1b31d5f4-ad97-4502-8bcd-ec575e703668@postgresfriends.org
Whole thread Raw
In response to Re: PostgreSQL domains and NOT NULL constraint  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: PostgreSQL domains and NOT NULL constraint
List pgsql-hackers
On 10/13/23 02:44, Tom Lane wrote:
> Vik Fearing <vik@postgresfriends.org> writes:
>> On 10/12/23 15:54, Tom Lane wrote:
>>> There's been some discussion of treating the output of such a join,
>>> subselect, etc as being of the domain's base type not the domain
>>> proper.  That'd solve this particular issue since then we'd decide
>>> we have to cast the base type back up to the domain type (and hence
>>> check its constraints) before inserting the row.  But that choice
>>> just moves the surprise factor somewhere else, in that queries that
>>> used to produce one data type now produce another one.  There are
>>> applications that this would break.  Moreover, I do not think there's
>>> any justification for it in the SQL spec.
> 
>> I do not believe this is a defect of the SQL standard at all.
>> SQL:2023-2 Section 4.14 "Domains" clearly states "The purpose of a
>> domain is to constrain the set of valid values that can be stored in a
>> column of a base table by various operations."
> 
> So I wonder what is the standard's interpretation of
> 
> regression=# create domain dpos as integer not null check (value > 0);
> CREATE DOMAIN
> regression=# create table t1 (x int, d dpos);
> CREATE TABLE
> regression=# create view v1 as select ty.d from t1 tx left join t1 ty using (x);
> CREATE VIEW
> regression=# \d+ v1
>                              View "public.v1"
>   Column | Type | Collation | Nullable | Default | Storage | Description
> --------+------+-----------+----------+---------+---------+-------------
>   d      | dpos |           |          |         | plain   |
> View definition:
>   SELECT ty.d
>     FROM t1 tx
>       LEFT JOIN t1 ty USING (x);
> 
> If we are incorrect in ascribing the type "dpos" to v1.d, where
> in the spec contradicts that?  (Or in other words, 4.14 might lay
> out some goals for the feature, but that's just empty words if
> it's not supported by accurate details in other places.)
Objection, Your Honor: Relevance.

Regardless of what the spec may or may not say about v1.d, it still 
remains that nulls should not be allowed in a *base table* if the domain 
says nulls are not allowed.  Not mentioned in this thread but the 
constraints are also applied when CASTing to the domain.

Now, to answer your straw man, this might be helpful:

SQL:2023-2 Section 11.4 <column definition> Syntax Rule 9, "If the 
descriptor of D includes any domain constraint descriptors, then T shall 
be a persistent base table.".  Your v1 is not that and therefore 
arguably illegal.

As you know, I am more than happy to (try to) amend the spec where 
needed, but Erki's complaint of a null value being allowed in a base 
table is clearly a bug in our implementation regardless of what we do 
with views.
-- 
Vik Fearing




pgsql-hackers by date:

Previous
From: Nathan Bossart
Date:
Subject: Re: Improve the log message output of basic_archive when basic_archive.archive_directory parameter is not set
Next
From: Andrei Lepikhov
Date:
Subject: Re: Removing unneeded self joins