Re: Unstable C Function

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