Re: libpq, PQdescribePrepared -> PQftype, PQfmod, no PQnullable

On Oct8, 2011, at 23:07 , Christopher Browne wrote:
> General purpose queries are nowhere near so predetermined. Indeed, whether a column is nullable may not be at all visible, as the value of a column may be computed by a function and thereby be quite opaque to static analysis.

I don't agree. To me, nullability is part of a column's type, just as the type's OID and TYPMOD are. We do static analysis on the TYPMOD, so I don't see why we shouldn't or couldn't do that on nullability.

> That makes me think that guessing which attributes of a query may be null seems like a pretty futile exercise. At first blush, we could simplify to PQnullable() always returning true, but that's not terribly revealing. However, often, there mayn't be a much better solution that isn't really tough to implement.

Coming up with a reasonable algorithm isn't *that* hard. Here's what I think would be reasonable

A) All result columns which are not simple column references are nullable
B) All result columns which are simple references to nullable columns are nullable
C) All result columns which are simple references to column from the nullable side of an outer join are nullable
(i.e., columns from the "right" side of a LEFT JOIN, "left" side of a RIGHT JOIN, or any side of a FULL OUTER JOIN)
D) All others are nullable
(i.e. simple column references to non-nullable columns from the non-nullable side of a join)

If someone cared enough, (A) could be improved upon further. CASE constructs are an obvious candidate for deeper inspection (i.e., a CASE construct is non-nullable if all WHEN branches are non-nullable and a non-nullalbe ELSE branch exists), as is COALESCE (similar rule).

This is mostly how it works for typmod I think - we do some analysis, but at some point we give up and just return "-1".

As I see it, the hardest part of this feature is getting the information to the client. I don't think the reply to a DESCRIBE message is currently extensible, so we'd probably need to add a new version of the message. That might be a rather tough sell, as least as long as there's isn't a clear use-case for this. Which, unfortunately, nobody has provided so far.

> I'd not be keen on people putting much effort into futile exercises ; better to work on things that are "less futile."

Again, I think "futile" is the wrong word here. This is all perfectly doable, the question is simply whether one values to feature enough to put in the word. I certainly won't, because I don't really see the benefit. But since most of our competitors seem to support this, and since Sun even put this into the JDBC spec, I guess a whole lot of people disagree.

best regards,
Florian Pflug