NonNullValue() error in 8.4

Hi,

Sorry in advance if this is the wrong mailing list to use, but I am a
bit new at this.

We have recently upgraded to 8.4 from 8.3 and are experiencing problems
with a client application. The log files record multiple entries of -

SELECT NonNullValue(attname) AS has_oid FROM pg_attribute

ERROR: function nonnullvalue(name) does not exist at character 664

Was this function written out of 8.4? I can't find much about this
function in the documentation, but I will keep looking in advance of a
response.

Many thanks,

Rebecca

This e-mail has been scanned for all viruses by Star Internet Services. The service is powered by MessageLabs.

On Tue, Oct 12, 2010 at 11:28 AM, Rebecca Cooper
<rebecca(dot)cooper(at)newforestnpa(dot)gov(dot)uk> wrote:
> We have recently upgraded to 8.4 from 8.3 and are experiencing problems with
> a client application.  The log files record multiple entries of -
>
> SELECT NonNullValue(attname) AS has_oid FROM pg_attribute
>
> ERROR:  function nonnullvalue(name) does not exist at character 664
>
> Was this function written out of 8.4?  I can’t find much about this function
> in the documentation, but I will keep looking in advance of a response.

It looks like these functions were intentionally never documented:
http://archives.postgresql.org/pgsql-docs/2004-08/msg00015.php

At any rate, the nonnullvalue() function your code is trying to use
was ripped out in this commit in 2008:
http://archives.postgresql.org/pgsql-committers/2008-10/msg00034.php

which is why it's not in 8.4. I think a mention of this change should
be made in the 8.4 release notes, it's the first time I'm seeing this
change documented anywhere.

Anyway, if you are able to modify your client application, I think you
should be able to change queries using nonnullvalue() like this:
SELECT NonNullValue(attname) AS has_oid FROM pg_attribute;

to use a CASE statement like so:

SELECT (CASE WHEN attname IS NULL THEN true ELSE false END) AS
has_oid FROM pg_attribute;

which should work fine on 8.4.

Josh

On Oct 12, 2010, at 12:53 , Josh Kupershmidt wrote:

> On Tue, Oct 12, 2010 at 11:28 AM, Rebecca Cooper
> <rebecca(dot)cooper(at)newforestnpa(dot)gov(dot)uk> wrote:
>> We have recently upgraded to 8.4 from 8.3 and are experiencing problems with
>> a client application. The log files record multiple entries of -
>>
>> SELECT NonNullValue(attname) AS has_oid FROM pg_attribute
>>
>> ERROR: function nonnullvalue(name) does not exist at character 664
>>
>

> to use a CASE statement like so:
>
> SELECT (CASE WHEN attname IS NULL THEN true ELSE false END) AS
> has_oid FROM pg_attribute;
>
> which should work fine on 8.4.

Or just "SELECT attname IS NULL AS has_oid"

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> On Oct 12, 2010, at 12:53 , Josh Kupershmidt wrote:
>> SELECT (CASE WHEN attname IS NULL THEN true ELSE false END) AS
>> has_oid FROM pg_attribute;

> Or just "SELECT attname IS NULL AS has_oid"

Actually I believe nonnullvalue(x) means x IS NOT NULL. It was never
documented because you were always supposed to use that SQL-standard
syntax instead. Before about 7.2, the parser converted IS NOT NULL
to nonnullvalue(), but it was only meant as an implementation detail.

But there is a bigger issue here, if Rebecca's quote from her logfile is
accurate:

>>> SELECT NonNullValue(attname) AS has_oid FROM pg_attribute

namely, what the heck the client-side code thinks it's doing with that.
pg_attribute.attname is not null by definition, so this query appears to
reduce to constant TRUE --- and whether it's true or not doesn't seem to
have anything to do with whether the attribute has an OID, because table
attributes don't have their own OIDs, and have not had them since PG 7.1.
So that AS label is really making me wonder what is going on here.
I am thinking this code is left over from ancient history and was
already badly patched at least once. I'd advise looking into what is
really needed according to the client logic rather than just papering
over the observable symptom.

regards, tom lane