On Oct9, 2011, at 17:56 , Tom Lane wrote:
> Florian Pflug <fgp@phlo.org> writes:
>> On Oct9, 2011, at 14:20 , Kevin Grittner wrote:
>>> Yeah. It would be nice to see at least one use case. The only
>>> comment I recall is a vague suggestion that that people might want to
>>> select data from a table and infer table attributes from the result
>>> set metadata. That seems marginal.
>
>> Well, there is one other, namely SQL standards compliance. It does
>> mandate that "CREATE TABLE ... AS SELECT" creates NOT NULL constraints
>> on non-nullable columns I think (I didn't re-check, though). I'm not sure
>> I see the value in that either, but, hey, standards compliance ought
>> to be a value it in itself, right?
>
> Um ... but that case has nothing to do with protocol changes.
No, that was meant as a use-case for the deduction of nullability, not
for it's transmission to the client. While those are obviously two distinct
things, I figured we'd probably tackle them at the same time (if ever). It'd
be strange to infer NOT NULL constraints for CREATE TABLE ... AS SELECT, yet
provide no way for clients to obtain that information for simple SELECT
statements.
And you're right, the Wiki already contains a wish list for the next protocol
version, and that wish list includes an entry for extending Describe to report
the nullability of columns. The entry, BTW, was added by one Tom Lane ;-)
The wish list can be found on http://wiki.postgresql.org/wiki/Todo under
"Wire Protocol Changes". The referenced thread on -hackers includes a rather
interesting use-case.
The idea presented there is to infer the type of a statement's result columns
at application compile-time, and inject the result into the compiler's type
checking and deduction algorithm. Since most statically types languages don't
have a general concept of "undefined" (i.e., there's no special "undefined" value
included in the domain of every type), there's a lot of value in knowing that a
columns cannot be null. It allows you to map the column directly to a string, int
or whatever on the client side, instead of going through some intermediate type
which adds "undefined" to the list of possible values. (That "intermediate type"
is the "Maybe" monad in Haskell, in C++ it'd be boost::optional or something
similar)
best regards,
Florian Pflug