Thread: How to use SPI_saveplan

The documentation on SPI_saveplan says:

     SPI_saveplan saves a passed plan (prepared by SPI_prepare) in
     memory ... and returns a pointer to the saved plan. This gives
     you the ability to reuse prepared plans in the subsequent
     invocations of your procedure in the current session. You may
     save the pointer returned in a local variable.

I'm missing something very basic. If I do this:

     PG_FUNCTION_INFO_V1(add_one);

     Datum foobar(PG_FUNCTION_ARGS)
     {
         ...
         void* plan = SPI_prepare(...);
         void* saved_plan = SPI_saveplan(plan);
         ...
     }

then how can I access the saved plan in a later invocation of foobar?
saved_plan goes out of scope on exit from foobar.

I've googled, but found little but copies of the postgres doc quoted
above.

Jack Orenstein

Jack Orenstein <jao@geophile.com> writes:
> I'm missing something very basic. If I do this:

>      Datum foobar(PG_FUNCTION_ARGS)
>      {
>          ...
>          void* plan = SPI_prepare(...);
>          void* saved_plan = SPI_saveplan(plan);
>          ...
>      }

> then how can I access the saved plan in a later invocation of foobar?

It's up to you to save the pointer somewhere where you can find it again
when you need it.  If there's only one interesting plan, use a static
variable.  Otherwise, you'll need some sort of data structure to map
from what-you-need to the-plan-that-does-it.  You can find examples of
these approaches in the PG sources (from memory, ruleutils.c has some
examples of the former approach while ri_triggers.c does the latter).

            regards, tom lane

Tom Lane wrote:
> Jack Orenstein <jao@geophile.com> writes:
>> I'm missing something very basic. If I do this:
>
>>      Datum foobar(PG_FUNCTION_ARGS)
>>      {
>>          ...
>>          void* plan = SPI_prepare(...);
>>          void* saved_plan = SPI_saveplan(plan);
>>          ...
>>      }
>
>> then how can I access the saved plan in a later invocation of foobar?
>
> It's up to you to save the pointer somewhere where you can find it again
> when you need it.  If there's only one interesting plan, use a static
> variable.  Otherwise, you'll need some sort of data structure to map
> from what-you-need to the-plan-that-does-it.  You can find examples of
> these approaches in the PG sources (from memory, ruleutils.c has some
> examples of the former approach while ri_triggers.c does the latter).

OK, that makes sense. It was use of the term "local variable" that threw
me.

Assigning SPI_prepare output to a local makes sense. Assigning
SPI_saveplan output to a static makes sense. But I don't see
the point in assigning SPI_saveplan output to a local, yet
that's what one case the doc specifically mentions.

Jack Orenstein

Jack Orenstein <jao@geophile.com> writes:
> Assigning SPI_prepare output to a local makes sense. Assigning
> SPI_saveplan output to a static makes sense. But I don't see
> the point in assigning SPI_saveplan output to a local, yet
> that's what one case the doc specifically mentions.

Hm, are you speaking of this?

  <para>
   <function>SPI_saveplan</function> saves a passed plan (prepared by
   <function>SPI_prepare</function>) in memory protected from freeing
   by <function>SPI_finish</function> and by the transaction manager
   and returns a pointer to the saved plan.  This gives you the
   ability to reuse prepared plans in the subsequent invocations of
   your procedure in the current session.  You may save the pointer
   returned in a local variable.  Always check if this pointer is
   <symbol>NULL</symbol> or not either when preparing a plan or using
   an already prepared plan in <function>SPI_execute_plan</function>.
  </para>

My inclination is to just delete the last two sentences, on the grounds
that they are essentially content-free.  Perhaps the original author's
intent would be met by s/local variable/static variable/, but this is
reference material not beginner hints.

            regards, tom lane

Tom Lane wrote:
> Jack Orenstein <jao@geophile.com> writes:
>> Assigning SPI_prepare output to a local makes sense. Assigning
>> SPI_saveplan output to a static makes sense. But I don't see
>> the point in assigning SPI_saveplan output to a local, yet
>> that's what one case the doc specifically mentions.
>
> Hm, are you speaking of this?
>
>   <para>
>    <function>SPI_saveplan</function> saves a passed plan (prepared by
>    <function>SPI_prepare</function>) in memory protected from freeing
>    by <function>SPI_finish</function> and by the transaction manager
>    and returns a pointer to the saved plan.  This gives you the
>    ability to reuse prepared plans in the subsequent invocations of
>    your procedure in the current session.  You may save the pointer
>    returned in a local variable.  Always check if this pointer is
>    <symbol>NULL</symbol> or not either when preparing a plan or using
>    an already prepared plan in <function>SPI_execute_plan</function>.
>   </para>
>
> My inclination is to just delete the last two sentences, on the grounds
> that they are essentially content-free.  Perhaps the original author's
> intent would be met by s/local variable/static variable/, but this is
> reference material not beginner hints.

Yes, that's the passage I was referring to. I agree that it would be
clearer with your suggested edit.

Jack Orenstein