Re: BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset - Mailing list pgsql-bugs
| From | Daniel Einspanjer |
|---|---|
| Subject | Re: BUG #15085: Domain "not null" constraint doesn't detect a nullreturned from a resultset |
| Date | |
| Msg-id | CAEfq8O1CbO8Ktg=G=3uLEPYzyJ-dCWsqC_VF4XOdTz4BiYt8oA@mail.gmail.com Whole thread Raw |
| In response to | Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset (Tom Lane <tgl@sss.pgh.pa.us>) |
| Responses |
Re: BUG #15085: Domain "not null" constraint doesn't detect a null returned from a resultset
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-bugs |
Oops. I am very sorry for missing that note in the docs. I agree it does cover the case I've described quite well.
Tom, please forgive me if this is a non-starter for a suggestion, but what about forcing a not null constraint on a column that is declared to be of the type of a domain that has a not null constraint?
Just to be clear, I'm thinking about something like the following:
postgres=>CREATE DOMAIN example TEXT NOT NULL;
CREATE DOMAIN
postgres=>CREATE TABLE usage(myexample EXAMPLE NULL);
ERROR: conflicting NULL/NOT NULL declarations for domain and column "myexample" of table "usage"
-- I imagine we could also get fancier if it was helpful and do something like this:
postgres=>CREATE TABLE usage2(myexample EXAMPLE);
NOTICE: NOT NULL constraint from domain added to column "myexample" of table "usage"
That automatic constraint idea came from my understanding of how PRIMARY KEY automatically marks the column as NOT NULL. (Oddly, pg allows you to use both the "NULL" and "PRIMARY KEY" keywords in a column declaration, it just ignores the NULL.)
This doesn't do anything to fix the underlying issue of being able to generate a value that violates the domain constraint, but at least it would prevent the inconsistency of having a table that doesn't allow the insert of some rows but allows others, and it would prevent users from inadvertently setting themselves up to allow this.
-Daniel
On Sat, Feb 24, 2018 at 10:35 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Saturday, February 24, 2018, PG Bug reporting form <
> noreply@postgresql.org> wrote:
>> After some testing, I came across this
>> unusual behavior that I believe might be a bug.
> This appears to fall within the exceptions for not null noted in the
> documentation for create domain.
> https://www.postgresql.org/docs/9.6/static/sql-createdomain.html
Yeah, the core issue here is that at no point does the query attempt to
convert a value of some other type to the domain type. Rather, the
appearance of the null result falls out of the semantics of a scalar
subquery that returns no rows. Like the case for a "not null" domain
column on the right side of a left join, this is a basic inconsistency
in the SQL language, and it's not clear that anyone would thank us for
throwing errors. (But they *would* complain if we slowed down these
fundamental operations to check for such cases :-(.)
I've occasionally wondered if we should force domain types that appear in
these contexts to decay to their base type, so as to avoid the situation
of returning a value that claims to be of a domain type but is actually
null. (That is, the reported output column type in an outer join or
whatever would be the base type not the domain type.) I'm doubtful
that any but pedants would find that to be an improvement, though; and
I've not come across anything in the SQL standard that suggests an
implementation should do that.
regards, tom lane
pgsql-bugs by date: