Thread: NULL as an argument in plpgsql functions
Hi,
this select produces error message:
test=> select test2(NULL);
ERROR: typeidTypeRelid: Invalid type - oid = 0
test2:
CREATE FUNCTION test2 (int4) RETURNS int4 AS '
Declare
keyval Alias For $1;
cnt int4;
Begin
Update hits set count = count +1 where msg_id = keyval;
return cnt;
End;
' LANGUAGE 'plpgsql';
When I do manually update
Update hits set count = count +1 where msg_id = NULL;
it works fine. What's the problem ?
Regards,
Oleg
test=> \d hits
Table = hits
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| msg_id | int4 | 4 |
| count | int4 | 4 |
+----------------------------------+----------------------------------+-------+
test=> select version();
version
------------------------------------------------------------------
PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1
(1 row)
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
> Hi, > > this select produces error message: > test=> select test2(NULL); > ERROR: typeidTypeRelid: Invalid type - oid = 0 > Not sure how to pass NULL's into functions. > test2: > CREATE FUNCTION test2 (int4) RETURNS int4 AS ' > Declare > keyval Alias For $1; > cnt int4; > Begin > Update hits set count = count +1 where msg_id = keyval; > return cnt; > End; > ' LANGUAGE 'plpgsql'; > > When I do manually update > Update hits set count = count +1 where msg_id = NULL; > it works fine. What's the problem ? > > Regards, > > Oleg > > > test=> \d hits > Table = hits > +----------------------------------+----------------------------------+-------+ > | Field | Type | Length| > +----------------------------------+----------------------------------+-------+ > | msg_id | int4 | 4 | > | count | int4 | 4 | > +----------------------------------+----------------------------------+-------+ > test=> select version(); > version > ------------------------------------------------------------------ > PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1 > (1 row) > > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > ************ > -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
On Mon, 27 Sep 1999, Bruce Momjian wrote: > Date: Mon, 27 Sep 1999 15:26:08 -0400 (EDT) > From: Bruce Momjian <maillist@candle.pha.pa.us> > To: Oleg Bartunov <oleg@sai.msu.su> > Cc: pgsql-hackers@postgreSQL.org > Subject: Re: [HACKERS] NULL as an argument in plpgsql functions > > > Hi, > > > > this select produces error message: > > test=> select test2(NULL); > > ERROR: typeidTypeRelid: Invalid type - oid = 0 > > > > Not sure how to pass NULL's into functions. I'm unable to pass NULL also to sql function not only to plpgsql one. I don't see any reason for this :-) I'm wondering if I'm the only have this problem. Regards, Oleg > > > > test2: > > CREATE FUNCTION test2 (int4) RETURNS int4 AS ' > > Declare > > keyval Alias For $1; > > cnt int4; > > Begin > > Update hits set count = count +1 where msg_id = keyval; > > return cnt; > > End; > > ' LANGUAGE 'plpgsql'; > > > > When I do manually update > > Update hits set count = count +1 where msg_id = NULL; > > it works fine. What's the problem ? > > > > Regards, > > > > Oleg > > > > > > test=> \d hits > > Table = hits > > +----------------------------------+----------------------------------+-------+ > > | Field | Type | Length| > > +----------------------------------+----------------------------------+-------+ > > | msg_id | int4 | 4 | > > | count | int4 | 4 | > > +----------------------------------+----------------------------------+-------+ > > test=> select version(); > > version > > ------------------------------------------------------------------ > > PostgreSQL 6.5.2 on i586-pc-linux-gnulibc1, compiled by gcc 2.95.1 > > (1 row) > > > > _____________________________________________________________ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > > > ************ > > > > > -- > Bruce Momjian | http://www.op.net/~candle > maillist@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes:
> this select produces error message:
> test=> select test2(NULL);
> ERROR: typeidTypeRelid: Invalid type - oid = 0
[ where test2 is a plpgsql function ]
Actually this is not a plpgsql issue; with current sources you get the
same error with any function, for example
regression=> select int4fac(NULL);
ERROR: typeidTypeRelid: Invalid type - oid = 0
Digging into this, I find that (a) make_const() in parse_node.c produces
a Const node for the NULL that has consttype = 0; (b) ParseFuncOrColumn
applies ISCOMPLEX() which tries to get the type tuple for the argument
of the function; (c) that fails because the type ID is 0.
I am not sure whether there are two bugs here or only one. It would
probably be better to mark the Const node as having type UNKNOWN instead
of type 0 (but make_const is not the only place that makes null
constants this way! we'd need to find all the others...). But I am not
sure whether ParseFuncOrColumn would then do the right thing in terms of
resolving the type of the function; for that matter I'm not real sure
what the right thing for it to do is.
Thomas, this stuff is mostly your bailiwick; what do you think?
regards, tom lane
> probably be better to mark the Const node as having type UNKNOWN instead
> of type 0 (but make_const is not the only place that makes null
> constants this way! we'd need to find all the others...). But I am not
> sure whether ParseFuncOrColumn would then do the right thing in terms of
> resolving the type of the function; for that matter I'm not real sure
> what the right thing for it to do is.
> Thomas, this stuff is mostly your bailiwick; what do you think?
My recollection is that UNKNOWN usually applies to strings of
unspecified type, while "0" applies to NULL fields. I can put this on
my list to look at later.
Another side issue; any function called with a null parameter will
actually not get called at all! Postgres assumes that a function
called with null must return null, so doesn't bother calling the
routine...
- Thomas
--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California
Thus spake Thomas Lockhart
> Another side issue; any function called with a null parameter will
> actually not get called at all! Postgres assumes that a function
> called with null must return null, so doesn't bother calling the
> routine...
Did this get changed recently? AFAIK the routine gets called. It's just
that the result is ignored and null is then returned. This bit me in the
ass when I was working on the inet stuff. If I didn't check for NULL and
return something my function would dump core but if I tried to deal with
the NULL and return something sensible, the function returned NULL anyway.
There was a discussion at the time about fixing this so that the function
never got called as investigation showed that there were existing ones
that would also crash if given null inputs. Did this ever happen?
--
D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> probably be better to mark the Const node as having type UNKNOWN instead
>> of type 0 (but make_const is not the only place that makes null
>> constants this way! we'd need to find all the others...). But I am not
>> sure whether ParseFuncOrColumn would then do the right thing in terms of
>> resolving the type of the function; for that matter I'm not real sure
>> what the right thing for it to do is.
>> Thomas, this stuff is mostly your bailiwick; what do you think?
> My recollection is that UNKNOWN usually applies to strings of
> unspecified type, while "0" applies to NULL fields. I can put this on
> my list to look at later.
OK, but after mulling it over it seems that UNKNOWN is pretty much what
we want for an explicit null constant. If you want to consider NULL
as having a type different from UNKNOWN, then most of the places that
currently check for UNKNOWN would have to check for both, no?
> Another side issue; any function called with a null parameter will
> actually not get called at all! Postgres assumes that a function
> called with null must return null, so doesn't bother calling the
> routine...
Actually, it's even sillier than that: the function *is* called, but
then the OR of the input values' nullflags is attached to the output,
so you get back a null no matter what the function did. (This is why
all the functions that take pass-by-ref args have to be careful about
getting null pointers.)
In any case, I hope to see that fixed before 6.6/7.0/whatever our
next release is. So we do need a fix for the parser issue.
regards, tom lane
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> There was a discussion at the time about fixing this so that the function
> never got called as investigation showed that there were existing ones
> that would also crash if given null inputs. Did this ever happen?
Nothing's changed yet, but you are right that one of the many problems
with the existing fmgr interface is that checking for null inputs is
both necessary and tedious (= frequently omitted).
I have a rough proposal on the table for cleaning this up so that null
handling is done properly, ie, a function can see *which* of its inputs
are null and can choose whether to return null or not. The most common
case of a "strict" function (any null input -> null result) would be
painless, but we wouldn't force all functions into that straitjacket.
See my pghackers message of 14 Jun 99.
regards, tom lane
D'Arcy J.M. Cain wrote:
> Thus spake Thomas Lockhart
> > Another side issue; any function called with a null parameter will
> > actually not get called at all! Postgres assumes that a function
> > called with null must return null, so doesn't bother calling the
> > routine...
>
> Did this get changed recently? AFAIK the routine gets called. It's just
> that the result is ignored and null is then returned. This bit me in the
> ass when I was working on the inet stuff. If I didn't check for NULL and
> return something my function would dump core but if I tried to deal with
> the NULL and return something sensible, the function returned NULL anyway.
>
> There was a discussion at the time about fixing this so that the function
> never got called as investigation showed that there were existing ones
> that would also crash if given null inputs. Did this ever happen?
It wasn't changed. But the isNull bool pointer (in-/out-
param) is only handed down as the second call argument if a
function is called via fmgr_c() and has exactly one argument
as defined in pg_proc.
Handling NULL on a per argument/return value base is one of
the long standing TODO's.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #