On 7/22/16 1:13 PM, Tom Lane wrote:
There is a rather squishy question as to whether NULL::composite_type
should be semantically equivalent to ROW(NULL,NULL,...)::composite_type.
If it is, then the SELECT should have failed before even getting into the
plpgsql function, because ROW(NULL,NULL) is surely not a valid value of
type c.  The SQL standard seems to believe that these things *are*
equivalent (at least, that was how we read the spec for IS [NOT] NULL).
We're not very good at making them actually act alike, but if they do act
alike in plpgsql, it's hard to call that a bug.

I was afraid this was an artifact of the spec...

> FWIW, the only reason I created 'text_not_null' in my real-word case is
> because I have a compound type that I don't want to allow NULLS for some
> of it's fields.
FWIW, there is a very good argument that any not-null restriction on a
datatype (as opposed to a stored column) is broken by design.  How do
you expect a LEFT JOIN to a table with such a column to work?  We
certainly are not going to enforce the not-nullness in that context,
and that leads to the thought that maybe we should just deny the validity
of such restrictions across the board.

Because if the column storing the compound type is NULL itself, that means the only thing you know is what the type of the column is. While that does mean you know what it's structure would be if it was actually a known quantity, the reality is it's not a known quantity. I would argue that if test_table.c IS NULL that's not the same thing as test_table.c = row(NULL,NULL).

Likewise, while pondering actually enforcing NOT NULL on types I worried about how you'd handle SELECT test_func(NULL) until I realized that (again), that's not the same thing as test_func(row(NULL,NULL)), nor is it the same as test_func(row(1,row(NULL,NULL))).

The reason any of this actually matters is it seriously diminishes the usefulness of composite types if you want a type that does useful validation. In my case, it would be completely invalid for any of the fields in the composite type to be NULL, but I should still be able to allow something (a table or type) that uses that composite type to be NULL.

It occurs to me... does the spec actually indicate that row(NULL,NULL)::c should work? I can see arguments for why (NULL::c).t IS NULL might be allowed (special case retrieving field values from a composite that's not actually defined).
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to