Thread: BUG #3415: plperl spi_exec_prepared variable undef value confusion

The following bug has been logged online:

Bug reference:      3415
Logged by:          Matt
Email address:      matt@lindenelevator.com
PostgreSQL version: 8.2.3
Operating system:   ubuntu 7.04, gentoo 2007.0
Description:        plperl spi_exec_prepared variable undef value confusion
Details:

When inserting a null timestamp from a variable, I encounter the following:
ERROR:  error from Perl function: invalid input syntax for type timestamp:
""

To replicate the problem
1. Prepare a statement: spi_prepare(...),
2. Set a variable: my $var = ...,
3. Re-set the variable's value: $var = undef,
4. Execute the prepared statement: spi_exec_prepared(...)

Matt Taylor

The following code should recreate the problem:


create table bug_demo_table ( x timestamp );

create function bug_demo() returns integer as $$

  use strict;
  use Data::Dumper;

  # prepare the statement
  my $sql = 'insert into bug_demo_table ( x ) ';
  $sql .= 'values ( $1 );' ;
  my $sth = spi_prepare( $sql, 'timestamp' );

  # first set the variable to some appropriate value
  my $var = '2007-01-01 01:01:01.000';
  elog(NOTICE, "\n". Dumper($var). "\n");

  # set the variable to undef
  $var = undef;     # fails
  elog(NOTICE, "\n". Dumper($var). "\n");

  # re-initialize the variable and set it to undef
  # uncomment this line to prevent the error
  #my $var = undef;  # works

  spi_exec_prepared( $sth, $var ); # fails

  return 1;

$$ LANGUAGE 'plperlu';

select bug_demo();

select * from bug_demo_table;

drop table bug_demo_table cascade;
drop function bug_demo() cascade;

"Matt" <matt@lindenelevator.com> writes:
> Description:        plperl spi_exec_prepared variable undef value confusion

[ pokes at it ... ]  Some of the places in plperl.c that are checking for
undef values use code like

    if (SvOK(val) && SvTYPE(val) != SVt_NULL)

and some just test the SvTYPE part.  It looks to me like the SvOK test
is essential --- in fact I'm not sure the SvTYPE test is even bringing
anything to the party.  Any perl-extension gurus around here?

            regards, tom lane

I wrote:
> [ pokes at it ... ]  Some of the places in plperl.c that are checking for
> undef values use code like

>     if (SvOK(val) && SvTYPE(val) != SVt_NULL)

> and some just test the SvTYPE part.  It looks to me like the SvOK test
> is essential --- in fact I'm not sure the SvTYPE test is even bringing
> anything to the party.  Any perl-extension gurus around here?

Google turned up this comp.lang.perl.misc thread:
http://www.dbforums.com/showthread.php?s=6aaf30de92e7732ff45d667075f997bf&t=1071763
which seems to establish pretty conclusively that SvOK() is *the* way
to check for defined-ness, and the SVt_NULL test is wrong as well as
useless.  So barring objections, I'll go make the code do it that way.

            regards, tom lane

Tom Lane wrote:
> "Matt" <matt@lindenelevator.com> writes:
>
>> Description:        plperl spi_exec_prepared variable undef value confusion
>>
>
> [ pokes at it ... ]  Some of the places in plperl.c that are checking for
> undef values use code like
>
>     if (SvOK(val) && SvTYPE(val) != SVt_NULL)
>
> and some just test the SvTYPE part.  It looks to me like the SvOK test
> is essential --- in fact I'm not sure the SvTYPE test is even bringing
> anything to the party.  Any perl-extension gurus around here?
>
>
>

The perlapi docs explicitly state that one should always use SvOK() to
check for undef. IIRC some SvOK() tests were added in some places where
it was found to be necessary, and the old tests kept out of an abundance
of caution, but a little googling suggests that you are correct.

cheers

andrew