SPI_executeor a similar function
PostgreSQL allocates memory
within memory contexts, which provide a
convenient method of managing allocations made in many different
places that need to live for differing amounts of time.
Destroying a context releases all the memory that was allocated
in it. Thus, it is not necessary to keep track of individual
objects to avoid memory leaks; instead only a relatively small
number of contexts have to be managed.
palloc and related functions allocate memory
from the "current" context.
SPI_connect creates a new memory
context and makes it current.
SPI_finish restores the previous current memory
context and destroys the context created by
SPI_connect. These actions ensure that
transient memory allocations made inside your procedure are
reclaimed at procedure exit, avoiding memory leakage.
However, if your procedure needs to return an object in
allocated memory (such as a value of a pass-by-reference data
type), you cannot allocate that memory using
palloc, at least not while you are connected to
SPI. If you try, the object will be deallocated by
SPI_finish, and your procedure will not work
reliably. To solve this problem, use
SPI_palloc to allocate memory for your return
SPI_palloc allocates memory
in the "upper executor context", that
is, the memory context that was current when
SPI_connect was called, which is precisely the
right context for a value returned from your procedure.
SPI_palloc is called while
the procedure is not connected to SPI, then it acts the same as a
palloc. Before a procedure
connects to the SPI manager, the current memory context is the
upper executor context, so all allocations made by the procedure
palloc or by SPI utility
functions are made in this context.
SPI_connect is called, the
private context of the procedure, which is created by
SPI_connect, is made the current
context. All allocations made by
or SPI utility functions (except for
SPI_palloc) are made in this context. When a
procedure disconnects from the SPI manager (via
SPI_finish) the current context is restored to
the upper executor context, and all allocations made in the
procedure memory context are freed and cannot be used any
All functions described in this section can be used by both
connected and unconnected procedures. In an unconnected
procedure, they act the same as the underlying ordinary server