diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/plpython.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_function.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/release.sgml | 42 | ||||
| -rw-r--r-- | doc/src/sgml/spi.sgml | 416 | ||||
| -rw-r--r-- | doc/src/sgml/xfunc.sgml | 146 |
5 files changed, 523 insertions, 93 deletions
diff --git a/doc/src/sgml/plpython.sgml b/doc/src/sgml/plpython.sgml index a19b4a3585d..6655219d3e6 100644 --- a/doc/src/sgml/plpython.sgml +++ b/doc/src/sgml/plpython.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.23 2004/05/16 23:22:07 neilc Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/plpython.sgml,v 1.24 2004/09/13 20:05:18 tgl Exp $ --> <chapter id="plpython"> <title>PL/Python - Python Procedural Language</title> @@ -175,7 +175,7 @@ def __plpython_procedure_myfunc_23456(): row number and column name. It has these additional methods: <function>nrows</function> which returns the number of rows returned by the query, and <function>status</function> which is the - <function>SPI_exec()</function> return value. The result object + <function>SPI_execute()</function> return value. The result object can be modified. </para> diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 2c43fe9b460..1a8f5e41d8e 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.58 2004/07/11 23:23:43 momjian Exp $ +$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.59 2004/09/13 20:05:38 tgl Exp $ --> <refentry id="SQL-CREATEFUNCTION"> @@ -172,7 +172,7 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> These attributes inform the system whether it is safe to replace multiple evaluations of the function with a single evaluation, for run-time optimization. At most one choice - should be specified. If none of these appear, + may be specified. If none of these appear, <literal>VOLATILE</literal> is the default assumption. </para> @@ -206,6 +206,10 @@ CREATE [ OR REPLACE ] FUNCTION <replaceable class="parameter">name</replaceable> to prevent calls from being optimized away; an example is <literal>setval()</>. </para> + + <para> + For additional details see <xref linkend="xfunc-volatility">. + </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml index ddf93a04ff9..1d91953a0aa 100644 --- a/doc/src/sgml/release.sgml +++ b/doc/src/sgml/release.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.295 2004/09/10 18:39:54 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.296 2004/09/13 20:05:18 tgl Exp $ --> <appendix id="release"> @@ -339,6 +339,26 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.295 2004/09/10 18:39:54 tgl Exp <listitem> <para> + In <literal>READ COMMITTED</> serialization mode, volatile functions + now see the results of concurrent transactions committed up to the + beginning of each statement within the function, rather than up to the + beginning of the interactive command that called the function. + </para> + </listitem> + + <listitem> + <para> + Functions declared <literal>STABLE</> or <literal>IMMUTABLE</> always + use the snapshot of the calling query, and therefore do not see the + effects of actions taken after the calling query starts, whether in + their own transaction or other transactions. Such a function must be + read-only, too, meaning that it cannot use any SQL commands other than + <command>SELECT</>. + </para> + </listitem> + + <listitem> + <para> Non-deferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query @@ -1436,6 +1456,26 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.295 2004/09/10 18:39:54 tgl Exp <listitem> <para> + In <literal>READ COMMITTED</> serialization mode, volatile functions + now see the results of concurrent transactions committed up to the + beginning of each statement within the function, rather than up to the + beginning of the interactive command that called the function. + </para> + </listitem> + + <listitem> + <para> + Functions declared <literal>STABLE</> or <literal>IMMUTABLE</> always + use the snapshot of the calling query, and therefore do not see the + effects of actions taken after the calling query starts, whether in + their own transaction or other transactions. Such a function must be + read-only, too, meaning that it cannot use any SQL commands other than + <command>SELECT</>. + </para> + </listitem> + + <listitem> + <para> Non-deferred AFTER triggers are now fired immediately after completion of the triggering query, rather than upon finishing the current interactive command. This makes a difference when the triggering query diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index 4018c2e3e1b..a5a832d2e73 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.34 2004/04/01 21:28:43 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/spi.sgml,v 1.35 2004/09/13 20:05:25 tgl Exp $ --> <chapter id="spi"> @@ -206,7 +206,7 @@ int SPI_finish(void) <refnamediv> <refname>SPI_push</refname> - <refpurpose>pushes SPI stack to allow recursive SPI calls</refpurpose> + <refpurpose>pushes SPI stack to allow recursive SPI usage</refpurpose> </refnamediv> <indexterm><primary>SPI_push</primary></indexterm> @@ -221,8 +221,24 @@ void SPI_push(void) <title>Description</title> <para> - <function>SPI_push</function> pushes a new environment on to the - SPI call stack, allowing recursive calls to use a new environment. + <function>SPI_push</function> should be called before executing another + procedure that might itself wish to use SPI. + After <function>SPI_push</function>, SPI is no longer in a + <quote>connected</> state, and SPI function calls will be rejected unless + a fresh <function>SPI_connect</function> is done. This ensures a clean + separation between your procedure's SPI state and that of another procedure + you call. After the other procedure returns, call + <function>SPI_pop</function> to restore access to your own SPI state. + </para> + + <para> + Note that <function>SPI_execute</function> and related functions + automatically do the equivalent of <function>SPI_push</function> before + passing control back to the SQL execution engine, so it is not necessary + for you to worry about this when using those functions. + Only when you are directly calling arbitrary code that might contain + <function>SPI_connect</function> calls do you need to issue + <function>SPI_push</function> and <function>SPI_pop</function>. </para> </refsect1> @@ -237,7 +253,7 @@ void SPI_push(void) <refnamediv> <refname>SPI_pop</refname> - <refpurpose>pops SPI stack to allow recursive SPI calls</refpurpose> + <refpurpose>pops SPI stack to return from recursive SPI usage</refpurpose> </refnamediv> <indexterm><primary>SPI_pop</primary></indexterm> @@ -253,7 +269,7 @@ void SPI_pop(void) <para> <function>SPI_pop</function> pops the previous environment from the - SPI call stack. For use when returning from recursive SPI calls. + SPI call stack. See <function>SPI_push</function>. </para> </refsect1> @@ -261,21 +277,21 @@ void SPI_pop(void) <!-- *********************************************** --> -<refentry id="spi-spi-exec"> +<refentry id="spi-spi-execute"> <refmeta> - <refentrytitle>SPI_exec</refentrytitle> + <refentrytitle>SPI_execute</refentrytitle> </refmeta> <refnamediv> - <refname>SPI_exec</refname> + <refname>SPI_execute</refname> <refpurpose>execute a command</refpurpose> </refnamediv> - <indexterm><primary>SPI_exec</primary></indexterm> + <indexterm><primary>SPI_execute</primary></indexterm> <refsynopsisdiv> <synopsis> -int SPI_exec(const char * <parameter>command</parameter>, int <parameter>count</parameter>) +int SPI_execute(const char * <parameter>command</parameter>, bool <parameter>read_only</parameter>, int <parameter>count</parameter>) </synopsis> </refsynopsisdiv> @@ -283,27 +299,65 @@ int SPI_exec(const char * <parameter>command</parameter>, int <parameter>count</ <title>Description</title> <para> - <function>SPI_exec</function> executes the specified SQL command - for <parameter>count</parameter> rows. + <function>SPI_execute</function> executes the specified SQL command + for <parameter>count</parameter> rows. If <parameter>read_only</parameter> + is <literal>true</>, the command must be read-only, and execution overhead + is somewhat reduced. + </para> + + <para> + This function may only be called from a connected procedure. </para> <para> - This function should only be called from a connected procedure. If - <parameter>count</parameter> is zero then it executes the command + If <parameter>count</parameter> is zero then the command is executed for all rows that it applies to. If <parameter>count</parameter> is greater than 0, then the number of rows for which the command will be executed is restricted (much like a <literal>LIMIT</literal> clause). For example, <programlisting> -SPI_exec("INSERT INTO tab SELECT * FROM tab", 5); +SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5); </programlisting> will allow at most 5 rows to be inserted into the table. </para> <para> - You may pass multiple commands in one string, and the command may - be rewritten by rules. <function>SPI_exec</function> returns the - result for the command executed last. + You may pass multiple commands in one string, and the commands may + be rewritten by rules. <function>SPI_execute</function> returns the + result for the command executed last. The <parameter>count</parameter> + limit applies to each command separately, but it is not applied to + hidden commands generated by rules. + </para> + + <para> + When <parameter>read_only</parameter> is <literal>false</>, + <function>SPI_execute</function> increments the command + counter and computes a new <firstterm>snapshot</> before executing each + command in the string. The snapshot does not actually change if the + current transaction isolation level is <literal>SERIALIZABLE</>, but in + <literal>READ COMMITTED</> mode the snapshot update allows each command to + see the results of newly committed transactions from other sessions. + This is essential for consistent behavior when the commands are modifying + the database. + </para> + + <para> + When <parameter>read_only</parameter> is <literal>true</>, + <function>SPI_execute</function> does not update either the snapshot + or the command counter, and it allows only plain <command>SELECT</> + commands to appear in the command string. The commands are executed + using the snapshot previously established for the surrounding query. + This execution mode is somewhat faster than the read/write mode due + to eliminating per-command overhead. It also allows genuinely + <firstterm>stable</> functions to be built: since successive executions + will all use the same snapshot, there will be no change in the results. + </para> + + <para> + It is generally unwise to mix read-only and read-write commands within + a single function using SPI; that could result in very confusing behavior, + since the read-only queries would not see the results of any database + updates done by the read-write queries. </para> <para> @@ -311,7 +365,7 @@ SPI_exec("INSERT INTO tab SELECT * FROM tab", 5); is returned in the global variable <varname>SPI_processed</varname> (unless the return value of the function is <symbol>SPI_OK_UTILITY</symbol>). If the return value of the - function is <symbol>SPI_OK_SELECT</symbol> then you may the use + function is <symbol>SPI_OK_SELECT</symbol> then you may use the global pointer <literal>SPITupleTable *SPI_tuptable</literal> to access the result rows. </para> @@ -330,7 +384,7 @@ typedef struct } SPITupleTable; </programlisting> <structfield>vals</> is an array of pointers to rows. (The number - of valid entries is given by <varname>SPI_processed</varname>). + of valid entries is given by <varname>SPI_processed</varname>.) <structfield>tupdesc</> is a row descriptor which you may pass to SPI functions dealing with rows. <structfield>tuptabcxt</>, <structfield>alloced</>, and <structfield>free</> are internal @@ -359,6 +413,15 @@ typedef struct </varlistentry> <varlistentry> + <term><literal>bool <parameter>read_only</parameter></literal></term> + <listitem> + <para> + <literal>true</> for read-only execution + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>int <parameter>count</parameter></literal></term> <listitem> <para> @@ -504,14 +567,15 @@ typedef struct <title>Notes</title> <para> - The functions <function>SPI_exec</function>, - <function>SPI_execp</function>, and - <function>SPI_prepare</function> change both + The functions <function>SPI_execute</function>, + <function>SPI_exec</function>, + <function>SPI_execute_plan</function>, and + <function>SPI_execp</function> change both <varname>SPI_processed</varname> and <varname>SPI_tuptable</varname> (just the pointer, not the contents of the structure). Save these two global variables into local - procedure variables if you need to access the result of - <function>SPI_exec</function> or <function>SPI_execp</function> + procedure variables if you need to access the result table of + <function>SPI_execute</function> or a related function across later calls. </para> </refsect1> @@ -519,6 +583,70 @@ typedef struct <!-- *********************************************** --> +<refentry id="spi-spi-exec"> + <refmeta> + <refentrytitle>SPI_exec</refentrytitle> + </refmeta> + + <refnamediv> + <refname>SPI_exec</refname> + <refpurpose>execute a read/write command</refpurpose> + </refnamediv> + + <indexterm><primary>SPI_exec</primary></indexterm> + + <refsynopsisdiv> +<synopsis> +int SPI_exec(const char * <parameter>command</parameter>, int <parameter>count</parameter>) +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <function>SPI_exec</function> is the same as + <function>SPI_execute</function>, with the latter's + <parameter>read_only</parameter> parameter always taken as + <literal>false</>. + </para> + </refsect1> + + <refsect1> + <title>Arguments</title> + + <variablelist> + <varlistentry> + <term><literal>const char * <parameter>command</parameter></literal></term> + <listitem> + <para> + string containing command to execute + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>int <parameter>count</parameter></literal></term> + <listitem> + <para> + maximum number of rows to process or return + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Return Value</title> + + <para> + See <function>SPI_execute</function>. + </para> + </refsect1> +</refentry> + +<!-- *********************************************** --> + <refentry id="spi-spi-prepare"> <refmeta> <refentrytitle>SPI_prepare</refentrytitle> @@ -551,14 +679,14 @@ void * SPI_prepare(const char * <parameter>command</parameter>, int <parameter>n may be advantageous to perform the planning only once. <function>SPI_prepare</function> converts a command string into an execution plan that can be executed repeatedly using - <function>SPI_execp</function>. + <function>SPI_execute_plan</function>. </para> <para> A prepared command can be generalized by writing parameters (<literal>$1</>, <literal>$2</>, etc.) in place of what would be constants in a normal command. The actual values of the parameters - are then specified when <function>SPI_execp</function> is called. + are then specified when <function>SPI_execute_plan</function> is called. This allows the prepared command to be used over a wider range of situations than would be possible without parameters. </para> @@ -610,10 +738,10 @@ void * SPI_prepare(const char * <parameter>command</parameter>, int <parameter>n <title>Return Value</title> <para> - <function>SPI_prepare</function> returns non-null pointer to an - execution plan. On error, <symbol>NULL</symbol> will be returned. - In both cases, <varname>SPI_result</varname> will be set analogous - to the value returned by <function>SPI_exec</function>, except that + <function>SPI_prepare</function> returns a non-null pointer to an + execution plan. On error, <symbol>NULL</symbol> will be returned, + and <varname>SPI_result</varname> will be set to one of the same + error codes used by <function>SPI_execute</function>, except that it is set to <symbol>SPI_ERROR_ARGUMENT</symbol> if <parameter>command</parameter> is <symbol>NULL</symbol>, or if <parameter>nargs</> is less than 0, or if <parameter>nargs</> is @@ -642,7 +770,7 @@ void * SPI_prepare(const char * <parameter>command</parameter>, int <parameter>n <refnamediv> <refname>SPI_getargcount</refname> - <refpurpose>returns the number of arguments needed when executing a plan + <refpurpose>returns the number of arguments needed by a plan prepared by <function>SPI_prepare</function></refpurpose> </refnamediv> @@ -659,7 +787,7 @@ int SPI_getargcount(void * <parameter>plan</parameter>) <para> <function>SPI_getargcount</function> returns the number of arguments needed - when executing a plan prepared by <function>SPI_prepare</function>. + to execute a plan prepared by <function>SPI_prepare</function>. </para> </refsect1> @@ -681,7 +809,7 @@ int SPI_getargcount(void * <parameter>plan</parameter>) <refsect1> <title>Return Value</title> <para> - The expected argument count for the <parameter>plan</parameter> or + The expected argument count for the <parameter>plan</parameter>, or <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan </parameter> is <symbol>NULL</symbol> </para> @@ -697,8 +825,8 @@ int SPI_getargcount(void * <parameter>plan</parameter>) <refnamediv> <refname>SPI_getargtypeid</refname> - <refpurpose>returns the expected typeid for the specified argument when - executing a plan prepared by <function>SPI_prepare</function></refpurpose> + <refpurpose>returns the expected typeid for the specified argument of + a plan prepared by <function>SPI_prepare</function></refpurpose> </refnamediv> <indexterm><primary>SPI_getargtypeid</primary></indexterm> @@ -714,7 +842,7 @@ Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int <parameter>argIndex <para> <function>SPI_getargtypeid</function> returns the Oid representing the type - id for argument at <parameter>argIndex</parameter> in a plan prepared by + id for the <parameter>argIndex</parameter>'th argument of a plan prepared by <function>SPI_prepare</function>. First argument is at index zero. </para> </refsect1> @@ -746,11 +874,11 @@ Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int <parameter>argIndex <refsect1> <title>Return Value</title> <para> - The type id of the argument at the given index or <symbol> - SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is + The type id of the argument at the given index, or + <symbol>SPI_ERROR_ARGUMENT</symbol> if the <parameter>plan</parameter> is <symbol>NULL</symbol> or <parameter>argIndex</parameter> is less than 0 or - not less than the number of arguments declared for the <parameter>plan - </parameter> + not less than the number of arguments declared for the + <parameter>plan</parameter> </para> </refsect1> </refentry> @@ -765,8 +893,8 @@ Oid SPI_getargtypeid(void * <parameter>plan</parameter>, int <parameter>argIndex <refnamediv> <refname>SPI_is_cursor_plan</refname> <refpurpose>returns <symbol>true</symbol> if a plan - prepared by <function>SPI_prepare</function> can be passed - as an argument to <function>SPI_cursor_open</function></refpurpose> + prepared by <function>SPI_prepare</function> can be used with + <function>SPI_cursor_open</function></refpurpose> </refnamediv> <indexterm><primary>SPI_is_cursor_plan</primary></indexterm> @@ -784,7 +912,7 @@ bool SPI_is_cursor_plan(void * <parameter>plan</parameter>) <function>SPI_is_cursor_plan</function> returns <symbol>true</symbol> if a plan prepared by <function>SPI_prepare</function> can be passed as an argument to <function>SPI_cursor_open</function> and <symbol> - false</symbol> if that is not the case. The criteria is that the + false</symbol> if that is not the case. The criteria are that the <parameter>plan</parameter> represents one single command and that this command is a <command>SELECT</command> without an <command>INTO</command> clause. @@ -819,21 +947,22 @@ bool SPI_is_cursor_plan(void * <parameter>plan</parameter>) <!-- *********************************************** --> -<refentry id="spi-spi-execp"> +<refentry id="spi-spi-execute-plan"> <refmeta> - <refentrytitle>SPI_execp</refentrytitle> + <refentrytitle>SPI_execute_plan</refentrytitle> </refmeta> <refnamediv> - <refname>SPI_execp</refname> + <refname>SPI_execute_plan</refname> <refpurpose>executes a plan prepared by <function>SPI_prepare</function></refpurpose> </refnamediv> - <indexterm><primary>SPI_execp</primary></indexterm> + <indexterm><primary>SPI_execute_plan</primary></indexterm> <refsynopsisdiv> <synopsis> -int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, int <parameter>count</parameter>) +int SPI_execute_plan(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, + bool <parameter>read_only</parameter>, int <parameter>count</parameter>) </synopsis> </refsynopsisdiv> @@ -841,9 +970,10 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <title>Description</title> <para> - <function>SPI_execp</function> executes a plan prepared by - <function>SPI_prepare</function>. <parameter>tcount</parameter> - has the same interpretation as in <function>SPI_exec</function>. + <function>SPI_execute_plan</function> executes a plan prepared by + <function>SPI_prepare</function>. <parameter>read_only</parameter> and + <parameter>count</parameter> have the same interpretation as in + <function>SPI_execute</function>. </para> </refsect1> @@ -861,10 +991,11 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par </varlistentry> <varlistentry> - <term><literal>Datum *<parameter>values</parameter></literal></term> + <term><literal>Datum * <parameter>values</parameter></literal></term> <listitem> <para> - actual parameter values + An array of actual parameter values. Must have same length as the + plan's number of arguments. </para> </listitem> </varlistentry> @@ -873,7 +1004,8 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <term><literal>const char * <parameter>nulls</parameter></literal></term> <listitem> <para> - An array describing which parameters are null. + An array describing which parameters are null. Must have same length as + the plan's number of arguments. <literal>n</literal> indicates a null value (entry in <parameter>values</> will be ignored); a space indicates a nonnull value (entry in <parameter>values</> is valid). @@ -881,17 +1013,26 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <para> If <parameter>nulls</parameter> is <symbol>NULL</symbol> then - <function>SPI_execp</function> assumes that no parameters are + <function>SPI_execute_plan</function> assumes that no parameters are null. </para> </listitem> </varlistentry> <varlistentry> + <term><literal>bool <parameter>read_only</parameter></literal></term> + <listitem> + <para> + <literal>true</> for read-only execution + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>int <parameter>count</parameter></literal></term> <listitem> <para> - number of row for which plan is to be executed + maximum number of rows to process or return </para> </listitem> </varlistentry> @@ -902,8 +1043,8 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <title>Return Value</title> <para> - The return value is the same as for <function>SPI_exec</function> - or one of the following: + The return value is the same as for <function>SPI_execute</function>, + with the following additional possible error (negative) results: <variablelist> <varlistentry> @@ -931,7 +1072,7 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <para> <varname>SPI_processed</varname> and <varname>SPI_tuptable</varname> are set as in - <function>SPI_exec</function> if successful. + <function>SPI_execute</function> if successful. </para> </refsect1> @@ -941,7 +1082,106 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <para> If one of the objects (a table, function, etc.) referenced by the prepared plan is dropped during the session then the result of - <function>SPI_execp</function> for this plan will be unpredictable. + <function>SPI_execute_plan</function> for this plan will be unpredictable. + </para> + </refsect1> +</refentry> + +<!-- *********************************************** --> + +<refentry id="spi-spi-execp"> + <refmeta> + <refentrytitle>SPI_execp</refentrytitle> + </refmeta> + + <refnamediv> + <refname>SPI_execp</refname> + <refpurpose>executes a plan in read/write mode</refpurpose> + </refnamediv> + + <indexterm><primary>SPI_execp</primary></indexterm> + + <refsynopsisdiv> +<synopsis> +int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, int <parameter>count</parameter>) +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <function>SPI_execp</function> is the same as + <function>SPI_execute_plan</function>, with the latter's + <parameter>read_only</parameter> parameter always taken as + <literal>false</>. + </para> + </refsect1> + + <refsect1> + <title>Arguments</title> + + <variablelist> + <varlistentry> + <term><literal>void * <parameter>plan</parameter></literal></term> + <listitem> + <para> + execution plan (returned by <function>SPI_prepare</function>) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>Datum * <parameter>values</parameter></literal></term> + <listitem> + <para> + An array of actual parameter values. Must have same length as the + plan's number of arguments. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>const char * <parameter>nulls</parameter></literal></term> + <listitem> + <para> + An array describing which parameters are null. Must have same length as + the plan's number of arguments. + <literal>n</literal> indicates a null value (entry in + <parameter>values</> will be ignored); a space indicates a + nonnull value (entry in <parameter>values</> is valid). + </para> + + <para> + If <parameter>nulls</parameter> is <symbol>NULL</symbol> then + <function>SPI_execp</function> assumes that no parameters are + null. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>int <parameter>count</parameter></literal></term> + <listitem> + <para> + maximum number of rows to process or return + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Return Value</title> + + <para> + See <function>SPI_execute_plan</function>. + </para> + + <para> + <varname>SPI_processed</varname> and + <varname>SPI_tuptable</varname> are set as in + <function>SPI_execute</function> if successful. </para> </refsect1> </refentry> @@ -962,7 +1202,9 @@ int SPI_execp(void * <parameter>plan</parameter>, Datum * <parameter>values</par <refsynopsisdiv> <synopsis> -Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <parameter>plan</parameter>, Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>) +Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <parameter>plan</parameter>, + Datum * <parameter>values</parameter>, const char * <parameter>nulls</parameter>, + bool <parameter>read_only</parameter>) </synopsis> </refsynopsisdiv> @@ -972,7 +1214,9 @@ Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <paramet <para> <function>SPI_cursor_open</function> sets up a cursor (internally, a portal) that will execute a plan prepared by - <function>SPI_prepare</function>. + <function>SPI_prepare</function>. The parameters have the same + meanings as the corresponding parameters to + <function>SPI_execute_plan</function>. </para> <para> @@ -1013,22 +1257,36 @@ Portal SPI_cursor_open(const char * <parameter>name</parameter>, void * <paramet <term><literal>Datum * <parameter>values</parameter></literal></term> <listitem> <para> - actual parameter values + An array of actual parameter values. Must have same length as the + plan's number of arguments. </para> </listitem> </varlistentry> <varlistentry> - <term><literal>const char *<parameter>nulls</parameter></literal></term> + <term><literal>const char * <parameter>nulls</parameter></literal></term> <listitem> <para> - An array describing which parameters are null values. + An array describing which parameters are null. Must have same length as + the plan's number of arguments. <literal>n</literal> indicates a null value (entry in <parameter>values</> will be ignored); a space indicates a - nonnull value (entry in <parameter>values</> is valid). If - <parameter>nulls</parameter> is <symbol>NULL</> then - <function>SPI_cursor_open</function> assumes that no parameters - are null. + nonnull value (entry in <parameter>values</> is valid). + </para> + + <para> + If <parameter>nulls</parameter> is <symbol>NULL</symbol> then + <function>SPI_cursor_open</function> assumes that no parameters are + null. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>bool <parameter>read_only</parameter></literal></term> + <listitem> + <para> + <literal>true</> for read-only execution </para> </listitem> </varlistentry> @@ -1168,7 +1426,7 @@ void SPI_cursor_fetch(Portal <parameter>portal</parameter>, bool <parameter>forw <para> <varname>SPI_processed</varname> and <varname>SPI_tuptable</varname> are set as in - <function>SPI_exec</function> if successful. + <function>SPI_execute</function> if successful. </para> </refsect1> </refentry> @@ -1320,7 +1578,7 @@ void * SPI_saveplan(void * <parameter>plan</parameter>) 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_execp</function>. + an already prepared plan in <function>SPI_execute_plan</function>. </para> </refsect1> @@ -1374,7 +1632,7 @@ void * SPI_saveplan(void * <parameter>plan</parameter>) <para> If one of the objects (a table, function, etc.) referenced by the prepared plan is dropped during the session then the results of - <function>SPI_execp</function> for this plan will be unpredictable. + <function>SPI_execute_plan</function> for this plan will be unpredictable. </para> </refsect1> </refentry> @@ -1386,7 +1644,7 @@ void * SPI_saveplan(void * <parameter>plan</parameter>) <para> The functions described here provide an interface for extracting - information from result sets returned by <function>SPI_exec</> and + information from result sets returned by <function>SPI_execute</> and other SPI functions. </para> @@ -2360,7 +2618,8 @@ HeapTuple SPI_modifytuple(Relation <parameter>rel</parameter>, HeapTuple <parame <term><literal>const char * <parameter>Nulls</parameter></literal></term> <listitem> <para> - which new values are null, if any (see <function>SPI_execp</function> for the format) + which new values are null, if any (see + <function>SPI_execute_plan</function> for the format) </para> </listitem> </varlistentry> @@ -2466,7 +2725,8 @@ void SPI_freetuple(HeapTuple <parameter>row</parameter>) <refnamediv> <refname>SPI_freetuptable</refname> - <refpurpose>free a row set created by <function>SPI_exec</> or a similar function</refpurpose> + <refpurpose>free a row set created by <function>SPI_execute</> or a similar + function</refpurpose> </refnamediv> <indexterm><primary>SPI_freetuptable</primary></indexterm> @@ -2483,7 +2743,7 @@ void SPI_freetuptable(SPITupleTable * <parameter>tuptable</parameter>) <para> <function>SPI_freetuptable</function> frees a row set created by a prior SPI command execution function, such as - <function>SPI_exec</>. Therefore, this function is usually called + <function>SPI_execute</>. Therefore, this function is usually called with the global variable <varname>SPI_tupletable</varname> as argument. </para> diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 684da2a3749..70a00ae032a 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.86 2004/08/24 00:06:50 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.87 2004/09/13 20:05:25 tgl Exp $ --> <sect1 id="xfunc"> @@ -2405,14 +2405,6 @@ CREATE FUNCTION make_array(anyelement) RETURNS anyarray </para> <para> - A function may also have the same name as an attribute. (Recall - that <literal>attribute(table)</literal> is equivalent to - <literal>table.attribute</literal>.) In the case that there is an - ambiguity between a function on a complex type and an attribute of - the complex type, the attribute will always be used. - </para> - - <para> When creating a family of overloaded functions, one should be careful not to create ambiguities. For instance, given the functions @@ -2428,6 +2420,18 @@ CREATE FUNCTION test(smallint, double precision) RETURNS ... </para> <para> + A function that takes a single argument of a composite type should + generally not have the same name as any attribute (field) of that type. + Recall that <literal>attribute(table)</literal> is considered equivalent + to <literal>table.attribute</literal>. In the case that there is an + ambiguity between a function on a composite type and an attribute of + the composite type, the attribute will always be used. It is possible + to override that choice by schema-qualifying the function name + (that is, <literal>schema.func(table)</literal>) but it's better to + avoid the problem by not choosing conflicting names. + </para> + + <para> When overloading C-language functions, there is an additional constraint: The C name of each function in the family of overloaded functions must be different from the C names of all @@ -2437,7 +2441,7 @@ CREATE FUNCTION test(smallint, double precision) RETURNS ... (usually the internal one). The alternative form of the <literal>AS</> clause for the SQL <command>CREATE FUNCTION</command> command decouples the SQL function name from - the function name in the C source code. E.g., + the function name in the C source code. For instance, <programlisting> CREATE FUNCTION test(int) RETURNS int AS '<replaceable>filename</>', 'test_1arg' @@ -2450,6 +2454,128 @@ CREATE FUNCTION test(int, int) RETURNS int </para> </sect1> + <sect1 id="xfunc-volatility"> + <title>Function Volatility Categories</title> + + <indexterm zone="xfunc-volatility"> + <primary>volatility</primary> + <secondary>functions</secondary> + </indexterm> + + <para> + Every function has a <firstterm>volatility</> classification, with + the possibilities being <literal>VOLATILE</>, <literal>STABLE</>, or + <literal>IMMUTABLE</>. <literal>VOLATILE</> is the default if the + <command>CREATE FUNCTION</command> command does not specify a category. + The volatility category is a promise to the optimizer about the behavior + of the function: + + <itemizedlist> + <listitem> + <para> + A <literal>VOLATILE</> function can do anything, including modifying + the database. It can return different results on successive calls with + the same arguments. The optimizer makes no assumptions about the + behavior of such functions. A query using a volatile function will + re-evaluate the function at every row where its value is needed. + </para> + </listitem> + <listitem> + <para> + A <literal>STABLE</> function cannot modify the database and is + guaranteed to return the same results given the same arguments + for all calls within a single surrounding query. This category + allows the optimizer to optimize away multiple calls of the function + within a single query. In particular, it is safe to use an expression + containing such a function in an indexscan condition. (Since an + indexscan will evaluate the comparison value only once, not once at + each row, it is not valid to use a <literal>VOLATILE</> function in + an indexscan condition.) + </para> + </listitem> + <listitem> + <para> + An <literal>IMMUTABLE</> function cannot modify the database and is + guaranteed to return the same results given the same arguments forever. + This category allows the optimizer to pre-evaluate the function when + a query calls it with constant arguments. For example, a query like + <literal>SELECT ... WHERE x = 2 + 2</> can be simplified on sight to + <literal>SELECT ... WHERE x = 4</>, because the function underlying + the integer addition operator is marked <literal>IMMUTABLE</>. + </para> + </listitem> + </itemizedlist> + </para> + + <para> + For best optimization results, you should label your functions with the + strictest volatility category that is valid for them. + </para> + + <para> + Any function with side-effects <emphasis>must</> be labeled + <literal>VOLATILE</>, so that calls to it cannot be optimized away. + Even a function with no side-effects needs to be labeled + <literal>VOLATILE</> if its value can change within a single query; + some examples are <literal>random()</>, <literal>currval()</>, + <literal>timeofday()</>. + </para> + + <para> + There is relatively little difference between <literal>STABLE</> and + <literal>IMMUTABLE</> categories when considering simple interactive + queries that are planned and immediately executed: it doesn't matter + a lot whether a function is executed once during planning or once during + query execution startup. But there is a big difference if the plan is + saved and reused later. Labeling a function <literal>IMMUTABLE</> when + it really isn't may allow it to be prematurely folded to a constant during + planning, resulting in a stale value being re-used during subsequent uses + of the plan. This is a hazard when using prepared statements or when + using function languages that cache plans (such as + <application>PL/pgSQL</>). + </para> + + <para> + Because of the snapshotting behavior of MVCC (see <xref linkend="mvcc">) + a function containing only <command>SELECT</> commands can safely be + marked <literal>STABLE</>, even if it selects from tables that might be + undergoing modifications by concurrent queries. + <productname>PostgreSQL</productname> will execute a <literal>STABLE</> + function using the snapshot established for the calling query, and so it + will see a fixed view of the database throughout that query. + Also note + that the <function>current_timestamp</> family of functions qualify + as stable, since their values do not change within a transaction. + </para> + + <para> + The same snapshotting behavior is used for <command>SELECT</> commands + within <literal>IMMUTABLE</> functions. It is generally unwise to select + from database tables within an <literal>IMMUTABLE</> function at all, + since the immutability will be broken if the table contents ever change. + However, <productname>PostgreSQL</productname> does not enforce that you + do not do that. + </para> + + <para> + A common error is to label a function <literal>IMMUTABLE</> when its + results depend on a configuration parameter. For example, a function + that manipulates timestamps might well have results that depend on the + <xref linkend="guc-timezone"> setting. For safety, such functions should + be labeled <literal>STABLE</> instead. + </para> + + <note> + <para> + Before <productname>PostgreSQL</productname> release 8.0, the requirement + that <literal>STABLE</> and <literal>IMMUTABLE</> functions cannot modify + the database was not enforced by the system. Release 8.0 enforces it + by requiring SQL functions and procedural language functions of these + categories to contain no SQL commands other than <command>SELECT</>. + </para> + </note> + </sect1> + <!-- Keep this comment at the end of the file Local variables: mode:sgml |