SRF in SFRM_ValuePerCall mode
Hi all,
I am working on implementation of custom "C" SRF for our team. The SRF uses
SFRM_ValuePerCall mode. I know that sometimes even in SFRM_ValuePerCall mode
all the rows returned from SRF are "materialized" (for performing JOINs, for
example). But it looks like even in cases when SELECT is very simple and
it's obvious that no more rows will be read from the SRF, SRF is being
iterated till it returns SRF_RETURN_DONE(...). So, in case when the SRF
returns 1000 rows but the SELECT is querying only the first one (with LIMIT
1), all 1000 iterations will be performed and 999 rows will be created,
allocated, returned and thrown away.
Is there a way to avoid unnecessary calls of SRF in this case? Is it a
"feature"?
In the attachment you can find the simplest example of SRF function working
in SFRM_ValuePerCall mode.
I have created it while researching the issue. After building it, use the
following SQLs:
-- this creates the test function
CREATE OR REPLACE FUNCTION vpc() RETURNS setof record
AS 'plbsh.dll', 'vpc' LANGUAGE 'C';
-- this returns 10 rows
SELECT * FROM vpc() AS T(a INT, b TEXT);
-- this returns 1 row, but performs 10 calls
SELECT * FROM vpc() AS T(a INT, b TEXT) LIMIT 1;
Regards,
Denis
Attachments:
dv @ nabble wrote:
I am working on implementation of custom "C" SRF for our team. The SRF uses
SFRM_ValuePerCall mode. I know that sometimes even in SFRM_ValuePerCall
mode
all the rows returned from SRF are "materialized" (for performing JOINs,
for
example).
Yep, they are unfortunately always materialized. Back when set returning
functions were implemented, the original patch did actually support true
"value per call" mode, where the whole result set was not materialized.
However, it was dropped because of some issues I can't remember off the
top of my head. The value-per-call API was committed, so that it was
already in place when someone gets around to implement the backend
support for it.
However, no-one has bothered to do that to this date. Hannu Krosing
showed some interest in it recently, though:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00345.php. I
would love to see it happen.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
dv @ nabble wrote:
I am working on implementation of custom "C" SRF for our team. The SRF uses
SFRM_ValuePerCall mode. I know that sometimes even in SFRM_ValuePerCall
mode
all the rows returned from SRF are "materialized" (for performing JOINs,
for
example).
Yep, they are unfortunately always materialized. Back when set returning
functions were implemented, the original patch did actually support true
"value per call" mode, where the whole result set was not materialized.
However, it was dropped because of some issues I can't remember off the
top of my head. The value-per-call API was committed, so that it was
already in place when someone gets around to implement the backend
support for it.
That's a rather revisionist view of history ;-) Value-per-call mode has
always been there, just not in nodeFunctionscan.c.
If you're not joining to the function result, and you don't need the
ability to determine its result type on the fly, you could declare it
as returning a specific rowtype and then call it in the targetlist:
select vpc();
regards, tom lane
OK,
Thank you for the explanation, I hope this will be implemented in future.
We will try and find a workaround to this issue until then.
Denis
----- Original Message -----
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
To: "dv @ nabble" <dvnabble@gmail.com>
Cc: "pgsql-hackers list" <pgsql-hackers@postgresql.org>
Sent: Monday, April 28, 2008 1:56 PM
Subject: Re: [HACKERS] SRF in SFRM_ValuePerCall mode
Show quoted text
dv @ nabble wrote:
I am working on implementation of custom "C" SRF for our team. The SRF
uses
SFRM_ValuePerCall mode. I know that sometimes even in SFRM_ValuePerCall
mode
all the rows returned from SRF are "materialized" (for performing JOINs,
for
example).Yep, they are unfortunately always materialized. Back when set returning
functions were implemented, the original patch did actually support true
"value per call" mode, where the whole result set was not materialized.
However, it was dropped because of some issues I can't remember off the
top of my head. The value-per-call API was committed, so that it was
already in place when someone gets around to implement the backend support
for it.However, no-one has bothered to do that to this date. Hannu Krosing showed
some interest in it recently, though:
http://archives.postgresql.org/pgsql-hackers/2008-04/msg00345.php. I would
love to see it happen.--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Heikki Linnakangas" <heikki@enterprisedb.com>
Cc: "dv @ nabble" <dvnabble@gmail.com>; "pgsql-hackers list"
<pgsql-hackers@postgresql.org>
Sent: Monday, April 28, 2008 5:07 PM
Subject: Re: [HACKERS] SRF in SFRM_ValuePerCall mode
"Heikki Linnakangas" <heikki@enterprisedb.com> writes:
dv @ nabble wrote:
I am working on implementation of custom "C" SRF for our team. The SRF
uses
SFRM_ValuePerCall mode. I know that sometimes even in SFRM_ValuePerCall
mode
all the rows returned from SRF are "materialized" (for performing JOINs,
for
example).Yep, they are unfortunately always materialized. Back when set returning
functions were implemented, the original patch did actually support true
"value per call" mode, where the whole result set was not materialized.
However, it was dropped because of some issues I can't remember off the
top of my head. The value-per-call API was committed, so that it was
already in place when someone gets around to implement the backend
support for it.That's a rather revisionist view of history ;-) Value-per-call mode has
always been there, just not in nodeFunctionscan.c.If you're not joining to the function result, and you don't need the
ability to determine its result type on the fly, you could declare it
as returning a specific rowtype and then call it in the targetlist:select vpc();
You mean make the function return the only row?
This is not the functionality we need. What we want is to create a SETOF
function that will
emulate a table and query this "table" with WHERE filter and LIMIT clauses
to limit the row
count we want to return. We might pass the filter and the limit to the
function, but we want to
implement it in more natural way.
Thanks,
Denis
"dv @ nabble" <dvnabble@gmail.com> writes:
From: "Tom Lane" <tgl@sss.pgh.pa.us>
If you're not joining to the function result, and you don't need the
ability to determine its result type on the fly, you could declare it
as returning a specific rowtype and then call it in the targetlist:select vpc();
You mean make the function return the only row?
No, I'm pointing out that ValuePerCall SRFs can be called from the
targetlist.
regards, tom lane