Re: Bug with plpgsql handling of NULL argument of compound type

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