[HACKERS] SRF in SFRM_ValuePerCall mode

[dv @ nabble]

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

#include "executor/spi.h"

#include "fmgr.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "postgres.h"
#include "access/heapam.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/trigger.h"
#include "storage/ipc.h"
#include "utils/date.h"
#include "utils/memutils.h"
#include "utils/syscache.h"



#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/*

CREATE OR REPLACE FUNCTION vpc() RETURNS setof record
        AS 'plbsh.dll', 'vpc' LANGUAGE 'C';

SELECT * FROM vpc() AS T(a INT, b TEXT);

SELECT * FROM vpc() AS T(a INT, b TEXT) LIMIT 1;

*/
extern Datum vpc(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(vpc);
Datum vpc(PG_FUNCTION_ARGS)
{
        ReturnSetInfo* rsinfo;
        FuncCallContext *funcctx;
        int current = 0;

        rsinfo = (ReturnSetInfo*) fcinfo->resultinfo;
        if (!rsinfo)
                ereport(ERROR, (errmsg("Invalid ReturnSetInfo in SRF")));
        if (!(rsinfo->allowedModes & SFRM_ValuePerCall))
                ereport(ERROR, (errmsg("Function must support returning a SETOF 
composite type in SFRM_ValuePerCall mode")));

        if (SRF_IS_FIRSTCALL())
        {
                MemoryContext oldcontext;

                funcctx = SRF_FIRSTCALL_INIT();
                oldcontext = 
MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

                funcctx->attinmeta = 
TupleDescGetAttInMetadata(rsinfo->expectedDesc);
                funcctx->user_fctx = (void*)(int*)palloc(sizeof(int));
                *((int*)funcctx->user_fctx) = 0;

                MemoryContextSwitchTo(oldcontext);
        }

        rsinfo->returnMode = SFRM_ValuePerCall;
        rsinfo->setDesc = rsinfo->expectedDesc;

        funcctx = SRF_PERCALL_SETUP();
        current = *((int*)funcctx->user_fctx);
        ereport(WARNING, (errmsg("## Call#%d", current)));
        if(current >= 10)
        {
                ereport(WARNING, (errmsg("## Last Call")));
                SRF_RETURN_DONE(funcctx);
        }
        else
        {
                char buffer[100];
                char* values[2];
                HeapTuple tuple;
                Datum result;

                sprintf(buffer, "%d", current++);
                *((int*)funcctx->user_fctx) = current;
                values[0] = buffer;
                values[1] = buffer;

                tuple = BuildTupleFromCStrings(funcctx->attinmeta, values);
                result = TupleGetDatum(slot, tuple);

                ereport(WARNING, (errmsg("## (return)", current)));
                SRF_RETURN_NEXT(funcctx, result);
        }
}
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers