spi.sgml

关系型数据库 Postgresql 6.5.2

<TITLE>Structures
</TITLE>
<PARA>None
</PARA>
</REFSECT1>
-->
</REFENTRY>

<!-- *********************************************** -->
<!-- *********************************************** -->
<!-- *********************************************** -->

<REFENTRY ID="SPI-SPIPALLOC">
<REFMETA>
<REFENTRYTITLE>SPI_palloc</REFENTRYTITLE>
<REFMISCINFO>SPI - Memory Management</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>SPI_palloc
</REFNAME>
<REFPURPOSE>
Allocates memory in upper Executor context
</REFPURPOSE>
<INDEXTERM ID="IX-SPI-SPIPALLOC-1"><PRIMARY>SPI</PRIMARY><SECONDARY>allocating space</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-SPI-SPIPALLOC-2"><PRIMARY>SPI_palloc</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
SPI_palloc(<REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>)
</SYNOPSIS>

<REFSECT2 ID="R2-SPI-SPIPALLOC-1">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
Size <REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
Octet size of storage to allocate
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-SPI-SPIPALLOC-2">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
void *
</TERM>
<LISTITEM>
<PARA>
New storage space of specified size
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-SPI-SPIPALLOC-1">
<REFSECT1INFO>
<DATE>1997-12-24</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>
<PARA>
<FUNCTION>SPI_palloc</FUNCTION> 
   allocates memory in upper Executor context. See section on memory management.
</PARA>
</REFSECT1>
<REFSECT1 ID="R1-SPI-SPIPALLOC-2">
<TITLE>Usage
</TITLE>
<Para>
TBD
</PARA>
</REFSECT1>
<!--
<REFSECT1 ID="R1-SPI-SPIPALLOC-3">
<TITLE>Algorithm
</TITLE>
<PARA>
TBD
</PARA>
</REFSECT1>
-->
<!--
<REFSECT1 ID="R1-SPI-SPIPALLOC-4">
<TITLE>Structures
</TITLE>
<PARA>None
</PARA>
</REFSECT1>
-->
</REFENTRY>

<!-- *********************************************** -->
<!-- *********************************************** -->
<!-- *********************************************** -->

<REFENTRY ID="SPI-SPIREPALLOC">
<REFMETA>
<REFENTRYTITLE>SPI_repalloc</REFENTRYTITLE>
<REFMISCINFO>SPI - Memory Management</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>SPI_repalloc
</REFNAME>
<REFPURPOSE>
Re-allocates memory in upper Executor context
</REFPURPOSE>
<INDEXTERM ID="IX-SPI-SPIREPALLOC-1"><PRIMARY>SPI</PRIMARY><SECONDARY>allocating space</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-SPI-SPIREPALLOC-2"><PRIMARY>SPI_repalloc</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
SPI_repalloc(<REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>, <REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>)
</SYNOPSIS>

<REFSECT2 ID="R2-SPI-SPIREPALLOC-1">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
void * <REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
Pointer to existing storage
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
Size <REPLACEABLE CLASS="PARAMETER">size</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
Octet size of storage to allocate
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-SPI-SPIREPALLOC-2">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
void *
</TERM>
<LISTITEM>
<PARA>
New storage space of specified size with contents copied from existing area
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-SPI-SPIREPALLOC-1">
<REFSECT1INFO>
<DATE>1997-12-24</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>
<PARA>
<FUNCTION>SPI_repalloc</FUNCTION> 
   re-allocates memory in upper Executor context. See section on memory management.
</PARA>
</REFSECT1>
<REFSECT1 ID="R1-SPI-SPIREPALLOC-2">
<TITLE>Usage
</TITLE>
<Para>
TBD
</PARA>
</REFSECT1>
<!--
<REFSECT1 ID="R1-SPI-SPIREPALLOC-3">
<TITLE>Algorithm
</TITLE>
<PARA>
TBD
</PARA>
</REFSECT1>
-->
<!--
<REFSECT1 ID="R1-SPI-SPIREPALLOC-4">
<TITLE>Structures
</TITLE>
<PARA>None
</PARA>
</REFSECT1>
-->
</REFENTRY>

<!-- *********************************************** -->
<!-- *********************************************** -->
<!-- *********************************************** -->

<REFENTRY ID="SPI-SPIPFREE">
<REFMETA>
<REFENTRYTITLE>SPI_pfree</REFENTRYTITLE>
<REFMISCINFO>SPI - Memory Management</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>SPI_pfree
</REFNAME>
<REFPURPOSE>
Frees memory from upper Executor context
</REFPURPOSE>
<INDEXTERM ID="IX-SPI-SPIPFREE-1"><PRIMARY>SPI</PRIMARY><SECONDARY>allocating space</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-SPI-SPIPFREE-2"><PRIMARY>SPI_pfree</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
SPI_pfree(<REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>)
</SYNOPSIS>

<REFSECT2 ID="R2-SPI-SPIPFREE-1">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
void * <REPLACEABLE CLASS="PARAMETER">pointer</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
Pointer to existing storage
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-SPI-SPIPFREE-2">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
None
</TERM>
<LISTITEM>
<PARA>
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-SPI-SPIPFREE-1">
<REFSECT1INFO>
<DATE>1997-12-24</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>
<PARA>
<FUNCTION>SPI_pfree</FUNCTION> 
   frees memory in upper Executor context. See section on memory management.
</PARA>
</REFSECT1>
<REFSECT1 ID="R1-SPI-SPIPFREE-2">
<TITLE>Usage
</TITLE>
<Para>
TBD
</PARA>
</REFSECT1>
<!--
<REFSECT1 ID="R1-SPI-SPIPFREE-3">
<TITLE>Algorithm
</TITLE>
<PARA>
TBD
</PARA>
</REFSECT1>
-->
<!--
<REFSECT1 ID="R1-SPI-SPIPFREE-4">
<TITLE>Structures
</TITLE>
<PARA>None
</PARA>
</REFSECT1>
-->
</REFENTRY>

</Sect1>

<Sect1>
<Title>Memory Management</Title>

<Para>
   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 <Function>palloc</Function>, 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.
</Para>

<Para>
   Creation and switching between memory contexts are subject of SPI manager
memory management.
</Para>

<Para>

   SPI procedures deal with two memory contexts: upper Executor memory
context and procedure memory context (if connected). 
</Para>

<Para>

   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
<Function>palloc</Function>/<Function>repalloc</Function> or by SPI utility functions before connecting to SPI are
made in this context.
</Para>

<Para>

   After <Function>SPI_connect</Function> is called current context is the procedure's one.  All
allocations made via <Function>palloc</Function>/<Function>repalloc</Function> or by SPI utility functions (except
for <Function>SPI_copytuple</Function>, <Function>SPI_modifytuple</Function>,
 <Function>SPI_palloc</Function> and <Function>SPI_repalloc</Function>) are
made in this context.
</Para>

<Para>

   When a procedure disconnects from the SPI manager (via <Function>SPI_finish</Function>) 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!
</Para>

<Para>

   If you want to return something to the upper Executor then you have to
allocate memory for this in the upper context!
</Para>

<Para>

   SPI has no ability to automatically free allocations in the upper Executor
context!
</Para>

<Para>

   SPI automatically frees memory allocated during execution of a query when
this query is done!
</Para>

</Sect1>

<Sect1>
<Title>Visibility of Data Changes</Title>

<Para>
<ProductName>Postgres</ProductName> 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.
</Para>

<Para>
   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.
</Para>
</Sect1>

<Sect1>
<Title>Examples</Title>

<Para>
   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.
</Para>

<Para>
   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:

<ProgramListing>
#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);
}
</ProgramListing>
</Para>

<Para>
   Now, compile and create the function:

<ProgramListing>
create function execq (text, int4) returns int4 as '...path_to_so' language 'c';
</ProgramListing>

<ProgramListing>
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
</ProgramListing>
</Para>
</Sect1>
</Chapter>