Vadim Mikheev Transcribed 1998-01-16 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 alternate 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'll use function to mean SPI interface functions and procedure for user-defined C-functions using SPI. SPI procedures are always called by some (upper) Executor and the SPI manager uses the Executor to run your queries. Other procedures may be called by the Executor running queries from your procedure. Note, that if during execution of a query from a procedure the transaction is aborted then control will not be returned to your 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 will be returned. SPI_connect SPI - Connection Management SPI_connect Connects your procedure to the SPI manager. SPIconnecting SPI_connect 1997-12-24 int SPI_connect(void) 1997-12-24 None 1997-12-24 int Return status SPI_OK_CONNECT if connected SPI_ERROR_CONNECT if not connected 1997-12-24 SPI_connect opens a connection to the Postgres backend. You should call this function if you will need to execute queries. Some utility SPI functions may be called from un-connected procedures. You may get SPI_ERROR_CONNECT error if SPI_connect is called from an already connected procedure - e.g. if you directly call one procedure from another connected one. Actually, while the child procedure will be able to use SPI, your parent procedure will not be able to continue to use SPI after the child returns (if SPI_finish is called by the child). It's bad practice. XXX thomas 1997-12-24 SPI_connect performs the following: • Initializes the SPI internal structures for query execution and memory management. SPI_finish SPI - Connection Management SPI_finish Disconnects your procedure from the SPI manager. SPIdisconnecting SPI_finish 1997-12-24 SPI_finish(void) 1997-12-24 None 1997-12-24 int SPI_OK_FINISH if properly disconnected SPI_ERROR_UNCONNECTED if called from an un-connected procedure 1997-12-24 SPI_finish closes an existing connection to the Postgres backend. You should call this function after completing operations through the SPI manager. You may get the error return 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. 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_finish performs the following: • Disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations can't be used any more! See Memory management. SPI_exec SPI - Connection Management SPI_exec Creates an execution plan (parser+planner+optimizer) and executes a query. SPIexecuting SPI_exec 1997-12-24 SPI_exec(query, tcount) 1997-12-24 char *query String containing query plan int tcount Maximum number of tuples to return 1997-12-24 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_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 shouldn't 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 (e.g. 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 1997-12-24 SPI_exec creates an execution plan (parser+planner+optimizer) and executes the query for tcount tuples. This should only be called 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); will allow at most 5 tuples to be inserted into table. If execution of your query was successful then a non-negative value will be returned. 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! (See Memory management). 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 shouldn't occur). SPI_exec performs the following: • Disconnects your procedure from the SPI manager and frees all memory allocations made by your procedure via palloc since the SPI_connect. These allocations can't be used any more! See Memory management. SPI_prepare SPI - Plan Preparation SPI_prepare Connects your procedure to the SPI manager. SPIconnecting SPI_prepare 1997-12-24 SPI_prepare(query, nargs, argtypes) 1997-12-24 query Query string nargs Number of input parameters ($1 ... $nargs - as in SQL-functions) argtypes Pointer list of type OIDs to input arguments 1997-12-24 void * Pointer to an execution plan (parser+planner+optimizer) 1997-12-24 SPI_prepare creates and returns an execution plan (parser+planner+optimizer) but doesn't execute the query. Should only be called from a connected procedure. 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'll 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 - Plan Storage SPI_saveplan Saves a passed plan SPIconnecting SPI_saveplan 1997-12-24 SPI_saveplan(plan) 1997-12-24 void *query Passed plan 1997-12-24 void * Execution plan location. NULL if unsuccessful. SPI_result SPI_ERROR_ARGUMENT if plan is NULL SPI_ERROR_UNCONNECTED if procedure is un-connected 1997-12-24 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 Postgres 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. 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 (see below). 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 - Plan Execution SPI_execp Executes a plan from SPI_saveplan SPIconnecting SPI_execp 1997-12-24 SPI_execp(plan, values, nulls, tcount) 1997-12-24 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 1997-12-24 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 1997-12-24 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 Postgres 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 arround, 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. 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. All functions described below may be used by connected and unconnected procedures. SPI_copytuple SPI - Tuple Copy SPI_copytuple Makes copy of tuple in upper Executor context SPIcopying tuples SPI_copytuple 1997-12-24 SPI_copytuple(tuple) 1997-12-24 HeapTuple tuple Input tuple to be copied 1997-12-24 HeapTuple Copied tuple non-NULL if tuple is not NULL and the copy was successful NULL only if tuple is NULL 1997-12-24 SPI_copytuple makes a copy of tuple in upper Executor context. See the section on Memory Management. TBD SPI_modifytuple SPI - Tuple Modify SPI_modifytuple Modifies tuple of relation SPImodifying tuples SPI_modifytuple 1997-12-24 SPI_modifytuple(rel, tuple , nattrs , attnum , Values , Nulls) 1997-12-24 Relation rel HeapTuple tuple Input tuple to be modified int nattrs Number of attribute numbers in attnum int * attnum Array of numbers of the attributes which are to be changed Datum * Values New values for the attributes specified char * Nulls Which attributes are NULL, if any 1997-12-24 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) 1997-12-24 SPI_modifytuple Modifies a tuple in upper Executor context. See the section on Memory Management. If successful, a pointer to the new tuple is returned. The new tuple is allocated in upper Executor context (see Memory management). Passed tuple is not changed. SPI_fnumber SPI - Tuple Information SPI_fnumber Finds the attribute number for specified attribute SPIdecoding tuples SPI_fnumber 1997-12-24 SPI_fnumber(tupdesc, fname) 1997-12-24 TupleDesc tupdesc Input tuple description char * fname Field name 1997-12-24 int Attribute number Valid one-based index number of attribute SPI_ERROR_NOATTRIBUTE if the named attribute is not found 1997-12-24 SPI_fnumber returns the attribute number for the attribute with name in fname. Attribute numbers are 1 based. SPI_fname SPI - Tuple Information SPI_fname Finds the attribute name for the specified attribute SPIdecoding tuples SPI_fname 1997-12-24 SPI_fname(tupdesc, fname) 1997-12-24 TupleDesc tupdesc Input tuple description char * fnumber Attribute number 1997-12-24 char * Attribute name NULL if fnumber is out of range SPI_result set to SPI_ERROR_NOATTRIBUTE on error 1997-12-24 SPI_fname returns the attribute name for the specified attribute. Attribute numbers are 1 based. Returns a newly-allocated copy of the attribute name. SPI_getvalue SPI - Tuple Information SPI_getvalue Returns the string value of the specified attribute SPIdecoding tuples SPI_getvalue 1997-12-24 SPI_getvalue(tuple, tupdesc, fnumber) 1997-12-24 HeapTuple tuple Input tuple to be examined TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 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) 1997-12-24 SPI_getvalue returns an external (string) representation of the value of the specified attribute. Attribute numbers are 1 based. Allocates memory as required by the value. SPI_getbinval SPI - Tuple Information SPI_getbinval Returns the binary value of the specified attribute SPIdecoding tuples SPI_getbinval 1997-12-24 SPI_getbinval(tuple, tupdesc, fnumber, isnull) 1997-12-24 HeapTuple tuple Input tuple to be examined TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 Datum Attribute binary value bool * isnull flag for null value in attribute SPI_result SPI_ERROR_NOATTRIBUTE 1997-12-24 SPI_getbinval returns the binary value of the specified attribute. Attribute numbers are 1 based. Does not allocate new space for the binary value. SPI_gettype SPI - Tuple Information SPI_gettype Returns the type name of the specified attribute SPIdecoding tuples SPI_gettype 1997-12-24 SPI_gettype(tupdesc, fnumber) 1997-12-24 TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 char * The type name for the specified attribute number SPI_result SPI_ERROR_NOATTRIBUTE 1997-12-24 SPI_gettype returns a copy of the type name for the specified attribute. Attribute numbers are 1 based. Does not allocate new space for the binary value. SPI_gettypeid SPI - Tuple Information SPI_gettypeid Returns the type OID of the specified attribute SPIdecoding tuples SPI_gettypeid 1997-12-24 SPI_gettypeid(tupdesc, fnumber) 1997-12-24 TupleDesc tupdesc Input tuple description int fnumber Attribute number 1997-12-24 OID The type OID for the specified attribute number SPI_result SPI_ERROR_NOATTRIBUTE 1997-12-24 SPI_gettypeid returns the type OID for the specified attribute. Attribute numbers are 1 based. TBD SPI_getrelname SPI - Tuple Information SPI_getrelname Returns the name of the specified relation SPIdecoding tuples SPI_getrelname 1997-12-24 SPI_getrelname(rel) 1997-12-24 Relation rel Input relation 1997-12-24 char * The name of the specified relation 1997-12-24 SPI_getrelname returns the name of the specified relation. TBD Copies the relation name into new storage. SPI_palloc SPI - Memory Management SPI_palloc Allocates memory in upper Executor context SPIallocating space SPI_palloc 1997-12-24 SPI_palloc(size) 1997-12-24 Size size Octet size of storage to allocate 1997-12-24 void * New storage space of specified size 1997-12-24 SPI_palloc allocates memory in upper Executor context. See section on memory management. TBD SPI_repalloc SPI - Memory Management SPI_repalloc Re-allocates memory in upper Executor context SPIallocating space SPI_repalloc 1997-12-24 SPI_repalloc(pointer, size) 1997-12-24 void * pointer Pointer to existing storage Size size Octet size of storage to allocate 1997-12-24 void * New storage space of specified size with contents copied from existing area 1997-12-24 SPI_repalloc re-allocates memory in upper Executor context. See section on memory management. TBD SPI_pfree SPI - Memory Management SPI_pfree Frees memory from upper Executor context SPIallocating space SPI_pfree 1997-12-24 SPI_pfree(pointer) 1997-12-24 void * pointer Pointer to existing storage 1997-12-24 None 1997-12-24 SPI_pfree frees memory in upper Executor context. See section on memory management. TBD Server allocates memory in memory contexts in such way that allocations made in one context may be freed by context destruction without affecting allocations made in other contexts. All allocations (via palloc, etc) are made in the context which are chosen as current one. You'll get unpredictable results if you'll try to free (or reallocate) memory allocated not in current context. Creation and switching between memory contexts are subject of SPI manager memory management. SPI procedures deal with two memory contexts: upper Executor memory context and procedure memory context (if connected). Before a procedure is connected to the SPI manager, current memory context is upper Executor context so all allocation made by the procedure itself via palloc/repalloc or by SPI utility functions before connecting to SPI are made in this context. After SPI_connect is called current context is the procedure's one. All allocations made via palloc/repalloc or by SPI utility functions (except for SPI_copytuple, SPI_modifytuple, SPI_palloc and SPI_repalloc) 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 can't be used any more! If you want to return something to the upper Executor then you have to allocate memory for this in the upper context! SPI has no ability to automatically free allocations in the upper Executor context! SPI automatically frees memory allocated during execution of a query when this query is done! Postgres data changes visibility rule: during a query execution, data changes made by the query itself (via SQL-function, SPI-function, triggers) are invisible to the query scan. For example, in query INSERT INTO a SELECT * FROM a tuples inserted are invisible for SELECT' scan. In effect, this duplicates the database table within itself (subject to unique index rules, of course) without recursing. Changes made by query Q are visible by queries which are started after query Q, no matter whether they are started inside Q (during the execution of Q) or after Q is done. This example of SPI usage demonstrates the visibility rule. There are more complex examples in in src/test/regress/regress.c and in contrib/spi. This is a very simple example of SPI usage. The procedure execq accepts an SQL-query in its first argument and tcount in its second, executes the query using SPI_exec and returns the number of tuples for which the query executed: #include "executor/spi.h" /* this is what you need to work with SPI */ int execq(text *sql, int cnt); int execq(text *sql, int cnt) { int ret; int proc = 0; SPI_connect(); ret = SPI_exec(textout(sql), cnt); proc = SPI_processed; /* * If this is SELECT and some tuple(s) fetched - * returns tuples to the caller via elog (NOTICE). */ if ( ret == SPI_OK_SELECT && SPI_processed > 0 ) { TupleDesc tupdesc = SPI_tuptable->tupdesc; SPITupleTable *tuptable = SPI_tuptable; char buf[8192]; int i; for (ret = 0; ret < proc; ret++) { HeapTuple tuple = tuptable->vals[ret]; for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++) sprintf(buf + strlen (buf), " %s%s", SPI_getvalue(tuple, tupdesc, i), (i == tupdesc->natts) ? " " : " |"); elog (NOTICE, "EXECQ: %s", buf); } } SPI_finish(); return (proc); } Now, compile and create the function: create function execq (text, int4) returns int4 as '...path_to_so' language 'c'; vac=> select execq('create table a (x int4)', 0); execq ----- 0 (1 row) vac=> insert into a values (execq('insert into a values (0)',0)); INSERT 167631 1 vac=> select execq('select * from a',0); NOTICE:EXECQ: 0 <<< inserted by execq NOTICE:EXECQ: 1 <<< value returned by execq and inserted by upper INSERT execq ----- 2 (1 row) vac=> select execq('insert into a select x + 2 from a',1); execq ----- 1 (1 row) vac=> select execq('select * from a', 10); NOTICE:EXECQ: 0 NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 <<< 0 + 2, only one tuple inserted - as specified execq ----- 3 <<< 10 is max value only, 3 is real # of tuples (1 row) vac=> delete from a; DELETE 3 vac=> insert into a values (execq('select * from a', 0) + 1); INSERT 167712 1 vac=> select * from a; x - 1 <<< no tuples in a (0) + 1 (1 row) vac=> insert into a values (execq('select * from a', 0) + 1); NOTICE:EXECQ: 0 INSERT 167713 1 vac=> select * from a; x - 1 2 <<< there was single tuple in a + 1 (2 rows) -- This demonstrates data changes visibility rule: vac=> insert into a select execq('select * from a', 0) * x from a; NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 NOTICE:EXECQ: 1 NOTICE:EXECQ: 2 NOTICE:EXECQ: 2 INSERT 0 2 vac=> select * from a; x - 1 2 2 <<< 2 tuples * 1 (x in first tuple) 6 <<< 3 tuples (2 + 1 just inserted) * 2 (x in second tuple) (4 rows) ^^^^^^^^ tuples visible to execq() in different invocations