Обсуждение: 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.cooper@newforestnpa.gov.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.cooper@newforestnpa.gov.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@seespotcode.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