Chapter 11. Server Programming Interface
The Server Programming Interface (SPI) gives users the ability to run SQL queries inside user-defined C functions. The available Procedural Languages (PL) give an alternative means to access these capabilities.
In fact, SPI is just a set of native interface functions to simplify access to the Parser, Planner, Optimizer, and Executor. SPI also does some memory management.
To avoid misunderstanding, we will use functions to represent SPI interface functions and procedure for user-defined C-functions using SPI.
Procedures that use SPI are called by the Executor. The SPI calls recursively invoke the Executor in turn to run queries. When the Executor is invoked recursively, it may call procedures that make SPI calls.
If a transaction is aborted during execution of a query from a procedure, control will not be returned to the procedure. Rather, all work will be rolled back and the server will wait for the next command from the client. This will be changed in future versions.
Other restrictions are the inability to execute BEGIN, END, and ABORT (transaction control statements) and cursor operations. This will also be changed in the future.
If successful, SPI functions return a non-negative result (either via a returned integer value or in SPI_result global variable, as described below). On error, a negative or NULL result is returned.
Interface Functions
Connection Management Functions
SPI_connect
SPI_connect connects your procedure to the SPI manager.
int SPI_connect(void) |
Inputs
None
Outputs
- int
Return status
- SPI_OK_CONNECT
if connected.
- SPI_ERROR_CONNECT
if not connected.
Description
SPI_connect opens a connection to the PostgreSQL backend. You should call this function if you will need to execute queries. Some utility SPI functions may be called from un-connected procedures.
SPI_connect initializes the SPI internal structures for query execution and memory management.
If your procedure is already connected, SPI_connect will return an SPI_ERROR_CONNECT error. Note that this may happen if a procedure which has called SPI_connect directly calls another procedure which itself calls SPI_connect. While recursive calls to the SPI manager are permitted when an SPI query invokes another function which uses SPI , directly nested calls to SPI_connect and SPI_finish are forbidden.
SPI_finish
SPI_finish disconnects your procedure from the SPI manager.
int SPI_finish(void) |
Inputs
None
Outputs
- int
SPI_OK_FINISH if properly disconnected. SPI_ERROR_UNCONNECTED if called from an un-connected procedure.
Description
SPI_finish closes an existing connection to the Red Hat Database backend. You should call this function after completing operations through the SPI manager.
SPI_finish disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations cannot be used any more.
You may get the error SPI_ERROR_UNCONNECTED if SPI_finish is called without having a current valid connection. There is no fundamental problem with this; it means that nothing was done by the SPI manager.
Usage
SPI_finish must be called as a final step by a connected procedure or you may get unpredictable results. Note that you can safely skip the call to SPI_finish if you abort the transaction (via elog(ERROR)).
SPI_exec
SPI_exec creates an execution plan (parser+planner+optimizer) and executes a query.
int SPI_exec(query, tcount) |
Inputs
- char *query
String containing query plan.
- int tcount
Maximum number of tuples to return.
Outputs
- int
SPI_OK_EXEC if properly disconnected SPI_ERROR_UNCONNECTED if called from an un-connected procedure SPI_ERROR_ARGUMENT if query is NULL or tcount < 0. SPI_ERROR_COPY if COPY TO/FROM stdin. SPI_ERROR_CURSOR if DECLARE/CLOSE CURSOR, FETCH. SPI_ERROR_TRANSACTION if BEGIN/ABORT/END. SPI_ERROR_OPUNKNOWN if type of query is unknown (this should not occur). If execution of your query was successful then one of the following (non-negative) values will be returned:
SPI_OK_UTILITY if some utility (for example, CREATE TABLE ...) was executed SPI_OK_SELECT if SELECT (but not SELECT ... INTO!) was executed SPI_OK_SELINTO if SELECT ... INTO was executed SPI_OK_INSERT if INSERT (or INSERT ... SELECT) was executed SPI_OK_DELETE if DELETE was executed SPI_OK_UPDATE if UPDATE was executed
Description
SPI_exec creates an execution plan (parser+planner+optimizer) and executes the query for tcount tuples.
SPI_exec disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations cannot be used any more.
Usage
This should be called only from a connected procedure. If tcount is zero then it executes the query for all tuples returned by the query scan. Using tcount > 0 you may restrict the number of tuples for which the query will be executed. For example,
SPI_exec ("insert into table select * from table", 5); |
 | You may pass many queries in one string or query string may be re-written by RULEs. SPI_exec returns the result for the last query executed. |
The actual number of tuples for which the (last) query was executed is returned in the global variable SPI_processed (if not SPI_OK_UTILITY). If SPI_OK_SELECT returned and SPI_processed > 0 then you may use global pointer SPITupleTable *SPI_tuptable to access the selected tuples: Also note that SPI_finish frees and makes all SPITupleTables unusable.
SPI_exec may return one of the following (negative) values:
| SPI_ERROR_ARGUMENT if query is NULL or tcount < 0. |
| SPI_ERROR_UNCONNECTED if procedure is unconnected. |
| SPI_ERROR_COPY if COPY TO/FROM stdin. |
| SPI_ERROR_CURSOR if DECLARE/CLOSE CURSOR, FETCH. |
| SPI_ERROR_TRANSACTION if BEGIN/ABORT/END. |
| SPI_ERROR_OPUNKNOWN if type of query is unknown (this should not occur). |
Plan Management
SPI_prepare
SPI_prepare connects your procedure to the SPI manager.
SPI_prepare(query, nargs, argtypes) |
Inputs
- query
Query string
- nargs
Number of input parameters ($1 ... $nargs - as in SQL-functions)
- argtypes
Pointer list of type OIDs to input arguments
Outputs
- void *
Pointer to an execution plan (parser+planner+optimizer)
Description
SPI_prepare creates and returns an execution plan (parser+planner+optimizer) but does not execute the query. Should be called only from a connected procedure.
Usage
nargs is number of parameters ($1 ... $nargs - as in SQL-functions), and nargs may be 0 only if there is not any $1 in query.
Execution of prepared execution plans is sometimes much faster so this feature may be useful if the same query will be executed many times.
The plan returned by SPI_prepare may be used only in current invocation of the procedure since SPI_finish frees memory allocated for a plan. See SPI_saveplan.
If successful, a non-null pointer will be returned. Otherwise, you will get a NULL plan. In both cases SPI_result will be set like the value returned by SPI_exec, except that it is set to SPI_ERROR_ARGUMENT if query is NULL or nargs < 0 or nargs > 0 && argtypes is NULL.
SPI_saveplan
SPI_saveplan saves a passed plan.
SPI_saveplan(plan) |
Inputs
- void *plan
Passed plan.
Outputs
- void *
Execution plan location. NULL if unsuccessful.
- SPI_result
SPI_ERROR_ARGUMENT if plan is NULL SPI_ERROR_UNCONNECTED if procedure is un-connected
Description
SPI_saveplan stores a plan prepared by SPI_prepare in safe memory protected from freeing by SPI_finish or the transaction manager.
In the current version of Red Hat Database there is no ability to store prepared plans in the system catalog and fetch them from there for execution. This will be implemented in future versions. As an alternative, there is the ability to reuse prepared plans in the consequent invocations of your procedure in the current session. Use SPI_execp to execute this saved plan.
Usage
SPI_saveplan saves a passed plan (prepared by SPI_prepare) in memory protected from freeing by SPI_finish and by the transaction manager and returns a pointer to the saved plan. You may save the pointer returned in a local variable. Always check if this pointer is NULL or not either when preparing a plan or using an already prepared plan in SPI_execp.
| If one of the objects (a relation, function, etc.) referenced by the prepared plan is dropped during your session (by your backend or another process) then the results of SPI_execp for this plan will be unpredictable. |
SPI_execp
SPI_execp executes a plan from SPI_saveplan.
SPI_execp(plan, values, nulls, tcount) |
Inputs
- void *plan
Execution plan
- Datum *values
Actual parameter values
- char *nulls
Array describing what parameters get NULLs
'n' indicates NULL allowed ' ' indicates NULL not allowed - int tcount
Number of tuples for which plan is to be executed
Outputs
- int
Returns the same value as SPI_exec as well as
SPI_ERROR_ARGUMENT if plan is NULL or tcount < 0 SPI_ERROR_PARAM if values is NULL and plan was prepared with some parameters. - SPI_tuptable
initialized as in SPI_exec if successful
- SPI_processed
initialized as in SPI_exec if successful
Description
SPI_execp stores a plan prepared by SPI_prepare in safe memory protected from freeing by SPI_finish or the transaction manager.
In the current version of Red Hat Database there is no ability to store prepared plans in the system catalog and fetch them from there for execution. This will be implemented in future versions. As a work around, there is the ability to reuse prepared plans in the consequent invocations of your procedure in the current session. Use SPI_execp to execute this saved plan.
Usage
If nulls is NULL then SPI_execp assumes that all values (if any) are NOT NULL.
| If one of the objects (a relation, function, etc.) referenced by the prepared plan is dropped during your session (by your backend or another process) then the results of SPI_execp for this plan will be unpredictable. |
Interface Support Functions
All functions described in this section may be used by connected and unconnected procedures.
SPI_copytuple
SPI_copytuple makes copy of tuple in upper Executor context.
SPI_copytuple(tuple) |
Inputs
- HeapTuple tuple
Input tuple to be copied.
Outputs
- HeapTuple
Copied tuple
non-NULL if tuple is not NULL and the copy was successful NULL only if tuple is NULL
Description
SPI_copytuple makes a copy of tuple in upper Executor context.
SPI_modifytuple
SPI_modifytuple modifies tuple of relation.
SPI_modifytuple(rel, tuple, nattrs, attnum, Values, Nulls) |
Modifies tuple of relation.
Inputs
- Relation rel
Used only as a source of tuple descriptor for tuple. (Passing a relation rather than a tuple descriptor is a misfeature.)
- HeapTuple tuple
Input tuple to be modified
- int nattrs
Number of attribute numbers in attnum
- int * attnum
Array of numbers of the attributes that are to be changed
- Datum * Values
New values for the attributes specified
- char * Nulls
Which attributes are NULL, if any
Outputs
- HeapTuple
New tuple with modifications
non-NULL if tuple is not NULL and the modify was successful NULL only if tuple is NULL. - SPI_result
SPI_ERROR_ARGUMENT if rel is NULL or tuple is NULL or natts ≤ 0 or attnum is NULL or Values is NULL. SPI_ERROR_NOATTRIBUTE if there is an invalid attribute number in attnum (attnum ≤ 0 or > number of attributes in tuple)
Description
SPI_modifytuple creates a new tuple by substituting new values for selected attributes, copying the original tuple's attributes at other positions. The input tuple is not modified.
Usage
If successful, a pointer to the new tuple is returned. The new tuple is allocated in upper Executor context.
SPI_fnumber
SPI_fnumber finds the attribute number for a specified attribute.
int SPI_fnumber(tupdesc, fname) |
Inputs
- TupleDesc tupdesc
Input tuple description
- char * fname
Field name
Outputs
- int
Attribute number
Valid one-based index number of attribute SPI_ERROR_NOATTRIBUTE if the named attribute is not found.
Description
SPI_fnumber returns the attribute number for the attribute with name in fname.
Usage
Attribute numbers are 1 based. If the given fname refers to a system attribute, (for example, oid), the appropriate negative attribute number will be returned. The caller should be careful to test for exact equality to SPI_ERROR_NOATTRIBUTE to detect errors; a test for a result less than or equal to zero is not correct unless system attributes should be rejected.
SPI_fname
SPI_fname finds the attribute name for the specified attribute.
SPI_fname(tupdesc, fnumber) |
Returns a newly-allocated copy of the attribute number. (Use pfree() to release the copy when done with it.)
Inputs
- TupleDesc tupdesc
Input tuple description.
- char * fnumber
Attribute number
Outputs
- char *
Attribute name
NULL if fnumber is out of range SPI_result set to SPI_ERROR_NOATTRIBUTE on error.
Description
SPI_fname returns the attribute name for the specified attribute.
Usage
Attribute numbers are 1 based.
SPI_getvalue
SPI_getvalue returns the string value of the specified attribute.
SPI_getvalue(tuple, tupdesc, fnumber) |
Inputs
- HeapTuple tuple
Input tuple to be examined
- TupleDesc tupdesc
Input tuple description
- int fnumber
Attribute number
Outputs
- char *
Attribute value or NULL if
attribute is NULL fnumber is out of range (SPI_result set to SPI_ERROR_NOATTRIBUTE) no output function available (SPI_result set to SPI_ERROR_NOOUTFUNC)
Description
SPI_getvalue returns an external (string) representation of the value of the specified attribute. The result is returned as a palloc'd string. (Use pfree() to release the copy when done with it.)
Usage
Attribute numbers are 1 based.
SPI_getbinval
SPI_getbinval returns the binary value of the specified attribute.
SPI_getbinval(tuple, tupdesc, fnumber, isnull) |
Inputs
- HeapTuple tuple
Input tuple to be examined
- TupleDesc tupdesc
Input tuple description
- int fnumber
Attribute number
Outputs
- Datum
Attribute binary value
- bool * isnull
flag for NULL value in attribute
- SPI_result
SPI_ERROR_NOATTRIBUTE
Description
SPI_getbinval returns the specified attribute's value in internal form (as a datum). SPI_getbinval does not allocate a new space for the Datum. In the case of a passer-by-reference datatype, the Datum will be a pointer into the given tuple.
Usage
Attribute numbers are 1 based.
SPI_gettype
SPI_gettype returns the type name of the specified attribute.
SPI_gettype(tupdesc, fnumber) |
Inputs
- TupleDesc tupdesc
Input tuple description
- int fnumber
Attribute number
Outputs
- char *
The type name for the specified attribute number
- SPI_result
SPI_ERROR_NOATTRIBUTE
Description
SPI_gettype returns a copy of the type name for the specified attribute, or NULL on error.
Usage
Attribute numbers are 1 based.
Algorithm
Does not allocate new space for the binary value.
SPI_gettypeid
SPI_gettypeid returns the type OID of the specified attribute.
SPI_gettypeid(tupdesc, fnumber) |
Inputs
- TupleDesc tupdesc
Input tuple description
- int fnumber
Attribute number
Outputs
- OID
The type OID for the specified attribute number
- SPI_result
SPI_ERROR_NOATTRIBUTE
Description
SPI_gettypeid returns the type OID for the specified attribute.
Usage
Attribute numbers are 1 based.
SPI_getrelname
SPI_getrelname returns the name of the specified relation.
SPI_getrelname(rel) |
Inputs
- Relation rel
Input relation
Outputs
- char *
The name of the specified relation
Description
SPI_getrelname returns the name of the specified relation. SPI_getrelname allocates storage for the returned name. Use pfree() to release the copy when you are done with it.
Memory Management
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—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 allocated memory object (such as a value of a pass-by-reference datatype), you can't allocate the return object using palloc, at least not while you are connected to SPI. If you try, the object will be deallocated during SPI_finish, and your procedure will not work reliably!
To solve this problem, use SPI_palloc to allocate your return object. SPI_palloc allocates space from "upper Executor" memory—that is, the memory context that was current when SPI_connect was called, which is precisely the right context for return values of your procedure.
If called while not connected to SPI, SPI_palloc acts the same as plain 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 via palloc or by SPI utility functions are made in this context.
After SPI_connect is called, the current context is the procedure's private context made by SPI_connect. All allocations made via palloc/repalloc or by SPI utility functions (except for SPI_copytuple, SPI_copytupledesc, SPI_copytupleintoslot, SPI_modifytuple, and 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 more!
All functions described in this section may be used by both connected and unconnected procedures. In an unconnected procedure, they act the same as the underlying ordinary backend functions (palloc etc).
SPI_palloc
SPI_palloc allocates memory in upper Executor context.
SPI_palloc(size) |
Inputs
- Size size
Octet size of storage to allocate
Outputs
- void *
New storage space of specified size
Description
SPI_palloc allocates memory in upper Executor context.
SPI_repalloc
SPI_repalloc re-allocates memory in upper Executor context.
SPI_repalloc(pointer, size) |
Re-allocates memory in upper Executor context.
Inputs
- void * pointer
Pointer to existing storage.
- Size size
Octet size of storage to allocate.
Outputs
- void *
New storage space of specified size with contents copied from existing area
Description
SPI_repalloc re-allocates memory in upper Executor context.
SPI_pfree
SPI_pfree frees memory from upper Executor context.
SPI_pfree(pointer) |
Inputs
- void * pointer
Pointer to existing storage
Outputs
None
Description
SPI_pfree frees memory in upper Executor context.