Thread: BUG #2817: Mixing spi_prepare and spi_exec_query causes postgresql to terminate itself on bad argument types

The following bug has been logged online:

Bug reference:      2817
Logged by:          Michael Andreen
Email address:      harv@ruin.nu
PostgreSQL version: 8.2
Operating system:   Gentoo Linux (unofficial postgresql 8.2 package)
Description:        Mixing spi_prepare and spi_exec_query causes postgresql
to terminate itself on bad argument types
Details:

Was playing around with the new release and changing some pl/perl
functions to use the new spi_prepare and friends when I noticed
postgresql terminating strangely, with no useful warnings at all, the
log contained the following:

Dec  7 16:58:28 [postgres] [7-1] LOG:  database system is ready
Dec  7 16:58:46 [postgres] [2-1] LOG:  could not receive data from
client: Connection reset by peer
Dec  7 16:58:56 [postgres] [2-1] LOG:  server process (PID 26649) was
terminated by signal 11
Dec  7 16:58:56 [postgres] [3-1] LOG:  terminating any other active
server processes
Dec  7 16:58:56 [postgres] [4-1] LOG:  all server processes terminated;
reinitializing
Dec  7 16:58:56 [postgres] [5-1] LOG:  database system was interrupted
at 2006-12-07 16:58:28 CET


After some playing around I tracked it down to a spi_prepare line where
I had specified 'integer' as type instead of 'int4'.

Tried to create a simple function to reproduce the bug:
CREATE OR REPLACE FUNCTION nocrash()
  RETURNS int4 AS
$BODY$$query = spi_prepare('SELECT 1 WHERE 1 = $1','integer');
return 1;$BODY$
  LANGUAGE 'plperl' VOLATILE;

But this gave a nice error message:
ERROR:  error from Perl function: type "integer" does not exist at line
1.

After some more work I realized that I still had a few spi_exec_query
calls in the script and adding one to my test case caused postgresql to
terminate just like before.

CREATE OR REPLACE FUNCTION crash()
  RETURNS int4 AS
$BODY$$rv = spi_exec_query("SELECT 1");
$query = spi_prepare('SELECT 1 WHERE 1 = $1','integer');
return 1;$BODY$
  LANGUAGE 'plperl' VOLATILE;

"Michael Andreen" <harv@ruin.nu> writes:
> CREATE OR REPLACE FUNCTION crash()
>   RETURNS int4 AS
> $BODY$$rv = spi_exec_query("SELECT 1");
> $query = spi_prepare('SELECT 1 WHERE 1 = $1','integer');
> return 1;$BODY$
>   LANGUAGE 'plperl' VOLATILE;

Thanks for the test case.  This is not plperl's fault, it's the
consequence of some anti-memory-leak code added very recently.
Here's the fix if you need it right away...

            regards, tom lane


*** src/backend/executor/spi.c.orig    Tue Nov 21 17:35:29 2006
--- src/backend/executor/spi.c    Thu Dec  7 19:33:33 2006
***************
*** 1543,1548 ****
--- 1543,1551 ----
      SPI_lastoid = my_lastoid;
      SPI_tuptable = my_tuptable;

+     /* tuptable now is caller's responsibility, not SPI's */
+     _SPI_current->tuptable = NULL;
+
      return my_res;
  }

***************
*** 1694,1699 ****
--- 1697,1705 ----
      /* Put the result into place for access by caller */
      SPI_processed = _SPI_current->processed;
      SPI_tuptable = _SPI_current->tuptable;
+
+     /* tuptable now is caller's responsibility, not SPI's */
+     _SPI_current->tuptable = NULL;

      /* Pop the SPI stack */
      _SPI_end_call(true);