Re: Unstable C Function

Thanks for personally replying, Tom. I appreciate it.

You are correct. In the interim, I found the following change solved the
issue:

SPI_finish(); // move to here
SRF_RETURN_DONE(funcctx);

I'll look into tuplestore. Thanks for the hint. Fixed IMMUTABLE.

Regards
Ian Campbell

On Thu, Sep 22, 2016 at 9:29 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ian Campbell <ianrc72(at)gmail(dot)com> writes:
> > The function works fine on first call, sometimes more, then either resets
> > the connection or throws this on any further calls:
> > ERROR: cache lookup failed for type 0 SQL state: XX000
>
> I think the core problem here is that you're dealing with
> pass-by-reference results from the SPI_execute() --- specifically,
> the int4[] "vals" values --- as if they were pass-by-value. You're
> just saving the Datums, which are only pointers, and expecting what
> they point to to still be good when you get around to doing
> heap_form_tuple with them. But in reality they stopped being good
> the moment you did SPI_finish().
>
> The failures would be a lot less intermittent if you were testing in
> a debug build (with CLOBBER_FREED_MEMORY defined).
>
> The two basic approaches you could take that would work reliably are
>
> 1. Copy all the int4[] values into the multi_call_memory_ctx before
> doing SPI_finish.
>
> 2. Instead of using multi-call mode, form all the result tuples during
> a single call and return them in a tuplestore, so that all the work
> is done before you call SPI_finish. You wouldn't really need the FIFO
> data structure if you did it that way.
>
> There are some other things I could criticize here, like labeling the
> function IMMUTABLE when its results depend on table contents, but
> they probably aren't causing your crashes.
>
> regards, tom lane
>