Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
Patch applied. Thanks. --- David Fetter wrote: 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! [ Attachment, skipping... ] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
David Fetter wrote: *** 716,724 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 --- 766,776 listitem para ! If you are fetching or returning very large data sets using ! literalspi_exec_query/literal, you should be aware that ! these will all go into memory. You can avoid this by using ! literalspi_query/literal/literalspi_fetchrow/literal as ! illustrated earlier. /para /listitem /itemizedlist You have rolled 2 problems into one - spi_query+spi_fetchrow does not address the issue of returning large data sets. Suggest instead: para If you are fetching very large data sets using literalspi_exec_query/literal, you should be aware that these will all go into memory. You can avoid this by using literalspi_query/literal and literalspi_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 literalreturn/literal. You can avoid this problem too by instead using literalreturn_next/literal for each row returned, as shown previously. /para cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
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.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml31 Jul 2005 00:33:00 -
***
*** 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 replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-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 replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-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
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
listitem
para
!Executes an SQL command. Here is an example of a query
!(commandSELECT/command command) with the optional maximum
!number of rows:
programlisting
$rv = spi_exec_query('SELECT * FROM my_table', 5);
/programlisting
--- 298,315
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_fetchrow/(replaceablecommand/replaceable)/literal/term
+
listitem
para
!literalspi_exec_query/literal executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. emphasisYou should only use this command when you know
! that the result set will be relatively small./emphasis Here is an
! example of a query (commandSELECT/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
! literalspi_query/literal and literalspi_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.
! literalspi_fetchrow/literal works emphasisonly/emphasis with
! literalspi_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
Re: [PATCHES] [HACKERS] PL/Perl list value return causes segfault
On Fri, Jul 29, 2005 at 11:24:37PM -0400, Bruce Momjian wrote:
Would someone who knows perl update plperl.sgml and send me a patch?
Also, is this still true in 8.1:
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.
That's no longer true. Please find enclosed a new patch :)
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.sgml13 Jul 2005 02:10:42 - 2.42
--- doc/src/sgml/plperl.sgml30 Jul 2005 05:42:56 -
***
*** 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 replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-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 replaceablefuncname/replaceable
(replaceableargument-types/replaceable) RETURNS
replaceablereturn-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
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
listitem
para
!Executes an SQL command. Here is an example of a query
!(commandSELECT/command command) with the optional maximum
!number of rows:
programlisting
$rv = spi_exec_query('SELECT * FROM my_table', 5);
/programlisting
--- 298,315
termliteralfunctionspi_exec_query/(replaceablequery/replaceable [,
replaceablemax-rows/replaceable])/literal/term
termliteralfunctionspi_exec_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_query/(replaceablecommand/replaceable)/literal/term
+
termliteralfunctionspi_fetchrow/(replaceablecommand/replaceable)/literal/term
+
listitem
para
!literalspi_exec_query/literal executes an SQL command and
! returns the entire rowset as a reference to an array of hash
! references. emphasisYou should only use this command when you know
! that the result set will be relatively small./emphasis Here is an
! example of a query (commandSELECT/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
! literalspi_query/literal and literalspi_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.
! literalspi_fetchrow/literal works emphasisonly/emphasis with
! literalspi_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);
