public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Fetter <[email protected]>
To: Andrew Dunstan <[email protected]>
Cc: PostgreSQL Patches <[email protected]>
Cc: PostgreSQL Docs <[email protected]>
Cc: Bruce Momjian <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: Michael Fuhr <[email protected]>
Cc: [email protected]
Subject: Re: [HACKERS] PL/Perl list value return causes segfault
Date: Sat, 30 Jul 2005 17:34:58 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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 [email protected] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
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->{status};
my $nrows = $rv->{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->{status};
my $nrows = $rv->{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, '<', $file # ooh, it's a file access!
! or elog(ERROR, "Can't open $file for reading: $!");
! my @words = <$fh>;
! 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 => $row->{a},
! the_text => 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>
Attachments:
[text/plain] plperl_new_features.diff (6.4K, 2-plperl_new_features.diff)
download | inline 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->{status};
my $nrows = $rv->{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->{status};
my $nrows = $rv->{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, '<', $file # ooh, it's a file access!
! or elog(ERROR, "Can't open $file for reading: $!");
! my @words = <$fh>;
! 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 => $row->{a},
! the_text => 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>
view thread (13+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [HACKERS] PL/Perl list value return causes segfault
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox