What is SFRM_Materialize_Preferred and how can it be used to write more performant functions?

Posted on

Question :

Researching this question, I see there is a value for SetFunctionReturnMode called SFRM_Materialize_Preferred. What is this? Can this be used to write more performant functions?

There are multiple occurrances of SRFM_Materialize in contrib/ but none of SRFM_Materalize_Preferred

Answer :

SFRM_Materialize_Preferred allows you to write a function that accepts a hint from the caller that can accept Materialize or ValuePerCall mode from the documentation in the README

Callers that can support both ValuePerCall and Materialize mode will set SFRM_Materialize_Preferred, or not, depending on which mode they prefer.

This is documented as hint — an extension to SFRM_Materialize — in the source,

SFRM_Materialize_Random and SFRM_Materialize_Preferred are auxiliary flags about SFRM_Materialize mode, rather than separate modes.

You can also see this is used in the PostgreSQL Function Manager code for SQL fmgr_sql

lazyEvalOK = !(rsi->allowedModes & SFRM_Materialize_Preferred);

My own Demonstration

I created my own very simplified demonstration of SFRM_Materialize_Preferred for the purposes of benchmarking it. You can see that in my repo for my pg-srf-repeat. Basically the template boils down to this,

Datum myfunc(PG_FUNCTION_ARGS);

typedef struct
  ; your function state only used in value-per-call mode
} myfunc_fctx;

    ; get variables
        ReturnSetInfo   *rsinfo       = (ReturnSetInfo *) fcinfo->resultinfo;

        // If we prefer materialize, get it done
        if ( rsinfo->allowedModes & SFRM_Materialize_Preferred ) {

            MemoryContext   per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
            MemoryContext   oldcontext    = MemoryContextSwitchTo(per_query_ctx);

            Tuplestorestate *tupstore     = tuplestore_begin_heap(false, false, work_mem);
            rsinfo->setResult             = tupstore;
            rsinfo->returnMode            = SFRM_Materialize;
            TupleDesc   tupdesc = rsinfo->expectedDesc;

            Datum values[1]             = { ;stuff };
            bool  nulls[sizeof(values)] = {0}; # no nulls

            while ( times-- ) {
                tuplestore_putvalues(tupstore, tupdesc, values, nulls);


        // Initialize for multicall
        else {
            FuncCallContext *funcctx = SRF_FIRSTCALL_INIT();
            MemoryContext oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
            myfunc_fctx *fctx = (myfunc_fctx *) palloc(sizeof(myfunc_fctx));

            # set myfunc_fctx
            funcctx->user_fctx = fctx;

    FuncCallContext *funcctx  = SRF_PERCALL_SETUP();
    myfunc_fctx *fctx         = funcctx->user_fctx;

    # Call this
    SRF_RETURN_NEXT(funcctx, fctx->object);
    # Or this,


This generally seems to perform the faster of Materialize or ValuePerCall and the overhead of the implementation is minor (as compared to a pure implementation).

In Pg-Python

I am maintaining this here because of the overall scarcity of information pertaining. I have determined that this macro SRF_SHOULD_MATERIALIZE case is actually pretty silly (I’ve opened up an issue on it); the problem with the SRF_SHOULD_MATERIALIZE macro is SFRM_Materialize and SFRM_ValuePerCall are always true so essentially it’s only ever a slower version of SFRM_Materialize_Preferred**

It also seems to be used in the pg-python project which allows you Access Python 3 from PostgreSQL Functions, but shy of that there are really no use cases on all of GitHub. The pg-python macro for SRF_SHOULD_MATERIALIZE is as follows (see above note)

    ((ReturnSetInfo *) FCINFO->resultinfo)->allowedModes & SFRM_Materialize
  && (
      ((ReturnSetInfo *) FCINFO->resultinfo)->allowedModes & SFRM_ValuePerCall
    || ((
      ((ReturnSetInfo *) FCINFO->resultinfo)->allowedModes & SFRM_Materialize_Preferred

You can see the SRF_SHOULD_MATERIALIZE macro used once at the call site

Leave a Reply

Your email address will not be published. Required fields are marked *