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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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);
PG_FUNCTION_INFO_V1(myfunc);

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

Datum
myfunc(PG_FUNCTION_ARGS)
{
  
    ; get variables
    
    if (SRF_IS_FIRSTCALL())
    {
        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);
            }

            tuplestore_donestoring(tupstore);
            MemoryContextSwitchTo(oldcontext);
    
            PG_RETURN_NULL();
        }

        // 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;
            MemoryContextSwitchTo(oldcontext);
        }

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

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

}

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)

#define SRF_SHOULD_MATERIALIZE(FCINFO) (
  (
    ((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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply