sql - 不稳定的 Postgresql C 函数

Question

我在 Win 10 64 位上运行 PG 9.5。我正在 VS 2016 下编译 C。

我正在形成一个函数，它将演变成一个有点复杂的野兽。为了测试我最初的努力，该函数接受一个 int4 数组(这工作正常，此处未显示处理它的代码)。然后，该函数从表中抓取几行，将值插入 FIFO，然后将它们拉出并呈现结果。这种方法对于功能完成后的运行方式具有战略意义。

该函数在第一次调用时工作正常，然后在任何进一步的调用中抛出此错误:

ERROR: cache lookup failed for type 0 SQL state: XX000

我不知道是什么原因造成的。

但是，有时它不会抛出错误，而是不停地执行。

这是我出于问题目的而得到的最简洁的代码:

PG:

CREATE TABLE md_key
(
  id    serial NOT NULL PRIMARY KEY,
  pid   int4 NOT NULL,
  key   integer NOT NULL,
  vals  int4[]
);
…

CREATE OR REPLACE FUNCTION md_key_query(int4[])
  RETURNS TABLE (
    id int4,
    vals int4[]) AS E'\RoctPG', --abreviated for question
    'md_key_query'
  LANGUAGE c IMMUTABLE STRICT;
…

select * from md_key_query(array[1,2,3,4]::int4[])

C:

    PG_FUNCTION_INFO_V1(md_key_query);

    typedef struct
    {
        Datum              id;
        Datum              vals;
    } MdKeyNode;

    typedef struct fifoAry
    {
        MdKeyNode           nodes[32];
        struct fifoAry     *next;
        int32               readpos;
        int32               writepos;
    } FifoAry;

    typedef struct
    {
        FifoAry            *fifo;
        FifoAry            *tail;
        FifoAry            *head;
        uint32              nodescount;
        Datum              *retvals[2];
        bool               *retnulls[2];
    } CtxArgs;

    inline void push(CtxArgs *args, Datum id, Datum vals)
    {
        if (args->head->writepos == 32)
            args->head = args->head->next = (FifoAry*)palloc0(sizeof(FifoAry));

        MdKeyNode          *node = &(args->head->nodes[args->head->writepos++]);
        node->id = id;
        node->vals = vals;
        args->nodescount++;
    }


inline MdKeyNode* pop(CtxArgs *args)
{
//  if (!args->nodescount)
//      return NULL;
    if (args->tail->readpos == 32)
        args->tail = args->tail->next;

    args->nodescount--;

    return &(args->tail->nodes[args->tail->readpos++]);
}

// use STRICT in the caller wrapper to ensure a null isn't passed in
PGMODULEEXPORT Datum md_key_query(PG_FUNCTION_ARGS)
{
    uint32              i;
    FuncCallContext    *funcctx;
    HeapTuple           tuple;
    MdKeyNode          *node;
    CtxArgs            *args;

    if (SRF_IS_FIRSTCALL())
    {
        funcctx = SRF_FIRSTCALL_INIT();

        MemoryContext   oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        ArrayType      *a = PG_GETARG_ARRAYTYPE_P(0);
        Datum          *in_datums;
        bool           *in_nulls;
        bool            fieldNull;
        SPITupleTable  *tuptable = SPI_tuptable;
        int32           ret;
        uint32          proc;

        if (get_call_result_type(fcinfo, NULL, &funcctx->tuple_desc) != TYPEFUNC_COMPOSITE)
            ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context that cannot accept type record")));

        deconstruct_array(a, INT4OID, 4, true, 'i', &in_datums, &in_nulls, &ret);

        if (!ret)
            PG_RETURN_NULL();

        (SPI_connect();

        // initialize and set the cross-call structure
        funcctx->user_fctx = args = (CtxArgs*)palloc0(sizeof(CtxArgs));
        args->fifo = args->tail = args->head = (FifoAry*)palloc0(sizeof(FifoAry));
        args->retvals = (Datum*)palloc(sizeof(Datum) * 2);
        args->retnulls = (bool*)palloc0(sizeof(bool) * 2);

        BlessTupleDesc(funcctx->tuple_desc);

// do some work here

        // this is simply a test to see if this function is behaving as expected
        ret = SPI_execute("select id, vals from public.md_key where vals is not null limit 64", true, 0);

        if (ret <= 0)
            ereport(ERROR, (errcode(ERRCODE_SQL_ROUTINE_EXCEPTION), errmsg("could not execute SQL")));

        proc = SPI_processed;

        if (proc > 0)
        {
            TupleDesc       tupdesc = SPI_tuptable->tupdesc;
            SPITupleTable  *tuptable = SPI_tuptable;

            for (i = 0; i < proc; i++)
            {
                tuple = tuptable->vals[i];
                push(args, SPI_getbinval(tuple, tupdesc, 1, &fieldNull), SPI_getbinval(tuple, tupdesc, 2, &fieldNull));
            }
        }

        SPI_finish();
        MemoryContextSwitchTo(oldcontext);
    }

    funcctx = SRF_PERCALL_SETUP();
    args = funcctx->user_fctx;

    if (args->nodescount > 0)
    {
        node = pop(args);
        args->retvals[0] = node->id;
        args->retvals[1] = node->vals;
        tuple = heap_form_tuple(funcctx->tuple_desc, args->retvals, args->retnulls);
        SRF_RETURN_NEXT(funcctx, HeapTupleGetDatum(tuple));
    }
    else
    {
        SRF_RETURN_DONE(funcctx);
    }
}

Answer 1

已修复。移动了一个命令，如下所示:

{
        // function is unstable if this is put earlier
        SPI_finish();

        SRF_RETURN_DONE(funcctx);
}