Postgresql 中文操作指南

SPI_execute

SPI_execute — 执行命令

SPI_execute — execute a command

Synopsis

int SPI_execute(const char * command, bool read_only, long count)

Description

SPI_executecount 行执行指定的 SQL 命令。如果 read_onlytrue ,命令必须为只读,并且会相应减少执行开销。

SPI_execute executes the specified SQL command for count rows. If read_only is true, the command must be read-only, and execution overhead is somewhat reduced.

此函数只能从已连接的 C 函数中调用。

This function can only be called from a connected C function.

如果 count 为零,则针对所有适用行执行命令。如果 count 大于零,则最多检索 count 行;达到计数后执行停止,就像在查询中添加 LIMIT 子句一样。例如,

If count is zero then the command is executed for all rows that it applies to. If count is greater than zero, then no more than count rows will be retrieved; execution stops when the count is reached, much like adding a LIMIT clause to the query. For example,

SPI_execute("SELECT * FROM foo", true, 5);

最多从表中检索 5 行。注意,仅当命令实际返回行时,这种限制才有效。例如,

will retrieve at most 5 rows from the table. Note that such a limit is only effective when the command actually returns rows. For example,

SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);

插入 bar 中的所有行,忽略 count 参数。但是,对于

inserts all rows from bar, ignoring the count parameter. However, with

SPI_execute("INSERT INTO foo SELECT * FROM bar RETURNING *", false, 5);

最多插入 5 行,因为在检索第五个 RETURNING 结果行后执行将停止。

at most 5 rows would be inserted, since execution would stop after the fifth RETURNING result row is retrieved.

可以在一个字符串中传递多个命令; SPI_execute 返回最后执行的命令的结果。 count 限制分别应用于每个命令(即使实际上只会返回最后的结果)。该限制不适用于规则生成的任何隐藏命令。

You can pass multiple commands in one string; SPI_execute returns the result for the command executed last. The count limit applies to each command separately (even though only the last result will actually be returned). The limit is not applied to any hidden commands generated by rules.

read_onlyfalse 时, SPI_execute 递增命令计数器并在执行字符串中的每个命令之前计算一个新的 snapshot 。如果当前事务隔离级别为 SERIALIZABLEREPEATABLE READ ,快照实际上不会改变,但在 READ COMMITTED 模式下,快照更新允许每个命令查看来自其他会话的新提交事务的结果。当命令修改数据库时,这对于一致的行为至关重要。

When read_only is false, SPI_execute increments the command counter and computes a new snapshot before executing each command in the string. The snapshot does not actually change if the current transaction isolation level is SERIALIZABLE or REPEATABLE READ, but in 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.

read_onlytrue 时, SPI_execute 不会更新快照或命令计数器,并且只允许普通 SELECT 命令出现在命令字符串中。命令使用之前为环绕查询建立的快照执行。这种执行模式由于消除了每个命令的开销,因此比读/写模式稍快。它还允许构建真正的 stable 函数:由于连续执行都将使用相同的快照,结果将不会改变。

When read_only is true, SPI_execute does not update either the snapshot or the command counter, and it allows only plain 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 stable functions to be built: since successive executions will all use the same snapshot, there will be no change in the results.

在使用 SPI 的单个函数内混合读写命令和只读命令通常是不明智的;这会导致非常混乱的行为,因为只读查询将看不到读写查询所做的任何数据库更新的结果。

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.

(最后)命令执行的实际行数保存在全局变量 SPI_processed 中。如果函数的返回值是 SPI_OK_SELECTSPI_OK_INSERT_RETURNINGSPI_OK_DELETE_RETURNINGSPI_OK_UPDATE_RETURNING ,则可以使用全局指针 SPITupleTable *SPI_tuptable 访问结果行。一些实用程序命令(如 EXPLAIN )也会返回行集,并且 SPI_tuptable 在这些情况下也会包含结果。一些实用程序命令( COPYCREATE TABLE AS )不会返回行集,因此 SPI_tuptable 为 NULL,但它们仍然会在 SPI_processed 中返回已处理的行数。

The actual number of rows for which the (last) command was executed is returned in the global variable SPI_processed. If the return value of the function is SPI_OK_SELECT, SPI_OK_INSERT_RETURNING, SPI_OK_DELETE_RETURNING, or SPI_OK_UPDATE_RETURNING, then you can use the global pointer SPITupleTable *SPI_tuptable to access the result rows. Some utility commands (such as EXPLAIN) also return row sets, and SPI_tuptable will contain the result in these cases too. Some utility commands (COPY, CREATE TABLE AS) don’t return a row set, so SPI_tuptable is NULL, but they still return the number of rows processed in SPI_processed.

结构 SPITupleTable 这样定义:

The structure SPITupleTable is defined thus:

typedef struct SPITupleTable
{
    /* Public members */
    TupleDesc   tupdesc;        /* tuple descriptor */
    HeapTuple  *vals;           /* array of tuples */
    uint64      numvals;        /* number of valid tuples */

    /* Private members, not intended for external callers */
    uint64      alloced;        /* allocated length of vals array */
    MemoryContext tuptabcxt;    /* memory context of result table */
    slist_node  next;           /* link for internal bookkeeping */
    SubTransactionId subid;     /* subxact in which tuptable was created */
} SPITupleTable;

SPI 调用者可以使用字段 tupdescvalsnumvals ;其余字段为内部字段。 vals 是指向行的指针数组。行数由 numvals 给出(出于某些历史原因,此计数也以 SPI_processed 返回)。 tupdesc 是可以传递给涉及行的 SPI 函数的行描述符。

The fields tupdesc, vals, and numvals can be used by SPI callers; the remaining fields are internal. vals is an array of pointers to rows. The number of rows is given by numvals (for somewhat historical reasons, this count is also returned in SPI_processed). tupdesc is a row descriptor which you can pass to SPI functions dealing with rows.

SPI_finish 释放所有 SPITupleTable_s allocated during the current C function. You can free a particular result table earlier, if you are done with it, by calling _SPI_freetuptable

SPI_finish frees all SPITupleTable_s allocated during the current C function. You can free a particular result table earlier, if you are done with it, by calling _SPI_freetuptable.

Arguments

  • const char * _command_

    • string containing command to execute

  • bool _read_only_

    • true for read-only execution

  • long _count_

    • maximum number of rows to return, or 0 for no limit

Return Value

如果命令执行成功,则将返回以下(非负的)值之一:

If the execution of the command was successful then one of the following (nonnegative) values will be returned:

  • SPI_OK_SELECT

    • if a SELECT (but not SELECT INTO) was executed

  • SPI_OK_SELINTO

    • if a SELECT INTO was executed

  • SPI_OK_INSERT

    • if an INSERT was executed

  • SPI_OK_DELETE

    • if a DELETE was executed

  • SPI_OK_UPDATE

    • if an UPDATE was executed

  • SPI_OK_MERGE

    • if a MERGE was executed

  • SPI_OK_INSERT_RETURNING

    • if an INSERT RETURNING was executed

  • SPI_OK_DELETE_RETURNING

    • if a DELETE RETURNING was executed

  • SPI_OK_UPDATE_RETURNING

    • if an UPDATE RETURNING was executed

  • SPI_OK_UTILITY

    • if a utility command (e.g., CREATE TABLE) was executed

  • SPI_OK_REWRITTEN

    • if the command was rewritten into another kind of command (e.g., UPDATE became an INSERT) by a rule.

如果出错,将返回以下负值之一:

On error, one of the following negative values is returned:

  • SPI_ERROR_ARGUMENT

    • if command is NULL or count is less than 0

  • SPI_ERROR_COPY

    • if COPY TO stdout or COPY FROM stdin was attempted

  • SPI_ERROR_TRANSACTION

    • if a transaction manipulation command was attempted (BEGIN, COMMIT, ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED, or any variant thereof)

  • SPI_ERROR_OPUNKNOWN

    • if the command type is unknown (shouldn’t happen)

  • SPI_ERROR_UNCONNECTED

    • if called from an unconnected C function

Notes

所有 SPI 查询执行函数都会设置 SPI_processedSPI_tuptable (仅指针,不设置结构的内容)。如果需要在稍后的调用中访问 SPI_execute 或另一个查询执行函数的结果表,则将这两个全局变量保存到本地 C 函数变量中。

All SPI query-execution functions set both SPI_processed and SPI_tuptable (just the pointer, not the contents of the structure). Save these two global variables into local C function variables if you need to access the result table of SPI_execute or another query-execution function across later calls.