X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 010BB52888;
	Sat, 30 Jul 2005 21:35:22 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 94744-04; Sun, 31 Jul 2005 00:35:16 +0000 (GMT)
Received: from fetter.org (dsl092-188-065.sfo1.dsl.speakeasy.net
	[66.92.188.65])
	by svr1.postgresql.org (Postfix) with ESMTP id DE2435285F;
	Sat, 30 Jul 2005 21:35:14 -0300 (ADT)
Received: from fetter.org (localhost.localdomain [127.0.0.1])
	by fetter.org (8.13.4/8.12.10) with ESMTP id j6V0YxEm026494;
	Sat, 30 Jul 2005 17:34:59 -0700
Received: (from shackle@localhost)
	by fetter.org (8.13.4/8.13.4/Submit) id j6V0YwQg026493;
	Sat, 30 Jul 2005 17:34:58 -0700
Date: Sat, 30 Jul 2005 17:34:58 -0700
From: David Fetter <david@fetter.org>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: PostgreSQL Patches <pgsql-patches@postgresql.org>,
	PostgreSQL Docs <pgsql-docs@postgresql.org>,
	Bruce Momjian <pgman@candle.pha.pa.us>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>,
	Michael Fuhr <mike@fuhr.org>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] PL/Perl list value return causes segfault
Message-ID: <20050731003458.GA22073@fetter.org>
References: <42D44731.90306@dunslane.net>
	<200507300324.j6U3Ob713189@candle.pha.pa.us>
	<20050730054354.GL27147@fetter.org> <42EB850E.9080205@dunslane.net>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="CE+1k2dSO48ffgeK"
Content-Disposition: inline
In-Reply-To: <42EB850E.9080205@dunslane.net>
User-Agent: Mutt/1.4.2.1i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO
X-Spam-Level: 
X-Archive-Number: 200507/36
X-Sequence-Number: 3162


--CE+1k2dSO48ffgeK
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sat, Jul 30, 2005 at 09:47:58AM -0400, Andrew Dunstan wrote:
> 
> 
> David Fetter wrote:
> 
> You have rolled 2 problems into one - spi_query+spi_fetchrow does not 
> address the issue of returning large data sets.
> 
> Suggest instead:

[suggestion]

Revised patch attached.  Thanks for catching this :)

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

--CE+1k2dSO48ffgeK
Content-Type: text/plain; charset=us-ascii
Content-Disposition: attachment; filename="plperl_new_features.diff"

Index: doc/src/sgml/plperl.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plperl.sgml,v
retrieving revision 2.42
diff -c -r2.42 plperl.sgml
*** doc/src/sgml/plperl.sgml	13 Jul 2005 02:10:42 -0000	2.42
--- doc/src/sgml/plperl.sgml	31 Jul 2005 00:33:00 -0000
***************
*** 46,52 ****
    <para>
     To create a function in the PL/Perl language, use the standard
     <xref linkend="sql-createfunction" endterm="sql-createfunction-title">
!    syntax:
  <programlisting>
  CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
      # PL/Perl function body
--- 46,57 ----
    <para>
     To create a function in the PL/Perl language, use the standard
     <xref linkend="sql-createfunction" endterm="sql-createfunction-title">
!    syntax.  A PL/Perl function must always return a scalar value.  You
!    can return more complex structures (arrays, records, and sets) 
!    in the appropriate context by returning a reference.
!    Never return a list.  Here follows an example of a PL/Perl
!    function.
! 
  <programlisting>
  CREATE FUNCTION <replaceable>funcname</replaceable> (<replaceable>argument-types</replaceable>) RETURNS <replaceable>return-type</replaceable> AS $$
      # PL/Perl function body
***************
*** 282,288 ****
    </para>
  
    <para>
!    PL/Perl provides two additional Perl commands:
  
     <variablelist>
      <varlistentry>
--- 287,293 ----
    </para>
  
    <para>
!    PL/Perl provides three additional Perl commands:
  
     <variablelist>
      <varlistentry>
***************
*** 293,303 ****
  
       <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
       <term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
       <listitem>
        <para>
!        Executes an SQL command.  Here is an example of a query
!        (<command>SELECT</command> command) with the optional maximum
!        number of rows:
  <programlisting>
  $rv = spi_exec_query('SELECT * FROM my_table', 5);
  </programlisting>
--- 298,315 ----
  
       <term><literal><function>spi_exec_query</>(<replaceable>query</replaceable> [, <replaceable>max-rows</replaceable>])</literal></term>
       <term><literal><function>spi_exec_query</>(<replaceable>command</replaceable>)</literal></term>
+      <term><literal><function>spi_query</>(<replaceable>command</replaceable>)</literal></term>
+      <term><literal><function>spi_fetchrow</>(<replaceable>command</replaceable>)</literal></term>
+ 
       <listitem>
        <para>
!        <literal>spi_exec_query</literal> executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references.  <emphasis>You should only use this command when you know
! that the result set will be relatively small.</emphasis>  Here is an
! example of a query (<command>SELECT</command> command) with the
! optional maximum number of rows:
! 
  <programlisting>
  $rv = spi_exec_query('SELECT * FROM my_table', 5);
  </programlisting>
***************
*** 345,351 ****
  INSERT INTO test (i, v) VALUES (3, 'third line');
  INSERT INTO test (i, v) VALUES (4, 'immortal');
  
! CREATE FUNCTION test_munge() RETURNS SETOF test AS $$
      my $rv = spi_exec_query('select i, v from test;');
      my $status = $rv-&gt;{status};
      my $nrows = $rv-&gt;{processed};
--- 357,363 ----
  INSERT INTO test (i, v) VALUES (3, 'third line');
  INSERT INTO test (i, v) VALUES (4, 'immortal');
  
! CREATE OR REPLACE FUNCTION test_munge() RETURNS SETOF test AS $$
      my $rv = spi_exec_query('select i, v from test;');
      my $status = $rv-&gt;{status};
      my $nrows = $rv-&gt;{processed};
***************
*** 360,366 ****
  
  SELECT * FROM test_munge();
  </programlisting>
!       </para>
       </listitem>
      </varlistentry>
  
--- 372,416 ----
  
  SELECT * FROM test_munge();
  </programlisting>
!     </para>
!     <para>
!     <literal>spi_query</literal> and <literal>spi_fetchrow</literal>
!     work together as a pair for rowsets which may be large, or for cases
!     where you wish to return rows as they arrive.
!     <literal>spi_fetchrow</literal> works <emphasis>only</emphasis> with
!     <literal>spi_query</literal>. The following example illustrates how
!     you use them together:
! 
! <programlisting>
! CREATE TYPE foo_type AS (the_num INTEGER, the_text TEXT);
! 
! CREATE OR REPLACE FUNCTION lotsa_md5 (INTEGER) RETURNS SETOF foo_type AS $$
!     use Digest::MD5 qw(md5_hex);
!     my $file = '/usr/share/dict/words';
!     my $t = localtime;
!     elog(NOTICE, "opening file $file at $t" );
!     open my $fh, '&lt;', $file # ooh, it's a file access!
!         or elog(ERROR, "Can't open $file for reading: $!");
!     my @words = &lt;$fh&gt;;
!     close $fh;
!     $t = localtime;
!     elog(NOTICE, "closed file $file at $t");
!     chomp(@words);
!     my $row;
!     my $sth = spi_query("SELECT * FROM generate_series(1,$_[0]) AS b(a)");
!     while (defined ($row = spi_fetchrow($sth))) {
!         return_next({
!             the_num =&gt; $row-&gt;{a},
!             the_text =&gt; md5_hex($words[rand @words])
!         });
!     }
!     return;
! $$ LANGUAGE plperlu;
! 
! SELECT * from lotsa_md5(500);
! </programlisting>
!     </para>
! 
       </listitem>
      </varlistentry>
  
***************
*** 716,725 ****
  
      <listitem>
       <para>
!       In the current implementation, if you are fetching or returning
!       very large data sets, you should be aware that these will all go
!       into memory.
       </para>
      </listitem>
     </itemizedlist>
    </para>
--- 766,785 ----
  
      <listitem>
       <para>
!       If you are fetching very large data sets using
!       <literal>spi_exec_query</literal>, you should be aware that
!       these will all go into memory.  You can avoid this by using
!       <literal>spi_query</literal>/<literal>spi_fetchrow</literal> as
!       illustrated earlier.
       </para>
+      <para>
+         A similar problem occurs if a set-returning function passes a
+         large set of rows back to postgres via <literal>return</literal>. You
+         can avoid this problem too by instead using
+         <literal>return_next</literal> for each row returned, as shown
+         previously.
+      </para>
+ 
      </listitem>
     </itemizedlist>
    </para>

--CE+1k2dSO48ffgeK--