The SPI feature under PostgreSQL kernel

Enterprise PostgreSQL Solutions

Leave a Comment

The SPI feature under PostgreSQL kernel

Server Programming Interface(SPI) is a module in the PostgreSQL kernel which allows kernel developers to execute SQL statements in C functions and have the ability to manage transactions. This module provides great convenience for PostgreSQL plug-in development through its characteristics so that developers can conveniently call various database languages in the kernel. SPI modules are used in the realization of procedural languages and when learning to use SPI, we can refer to the methods of calling SPI interfaces by these modules. The official document gives the interface of the SPI module, but there is a very little introduction to the calling method. When learning the SPI module, this blog can come in handy in understanding the major SPI interfaces and how they are used inside a C function.

1. Basic Example

The SPI module is a module for kernel developers, which makes it possible to call SQL in the database kernel C code. Here is a simple example to illustrate the charm of the SPI module intuitively. Besides, you can get the code mentioned in this blog from here (if you want to read this blog in-depth, it is recommended to download the code to compare and read), and copy it to the PostgreSQL source directory contrib\spi path, then compile and install the autoinc contrib, you can execute all the tests mentioned in this blog.

The C code snippet is calling the SPI_exec(), SPI_connect(), SPI_finish() interfaces provided by the SPI module, we will refer to these interfaces in the blog. The purpose of outExecResult() function is to show the results fo the SELECT query, it is mentioned in the example primarily to show the basic function of SPI.

static void
my_exec_spi(char *sql)
{
    int     ret = 0;

    output_str("SQL: %s", sql);
    ret = SPI_exec(sql, 0);
    outExecResult(ret);
}
static void
spi_simple_test(void)
{   
    char    *sql10 = "INSERT INTO t1 VALUES(1,1,'I will be deleted')";
    char    *sql11 = "INSERT INTO t1 VALUES(2,2,'I will be updated')";
    char    *sql12 = "INSERT INTO t1 VALUES(3,2,'I will be there all the time')";
    char    *sql13 = "UPDATE t1 SET k = 'updated from i=2' where i = 2";
    char    *sql14 = "DELETE FROM t1 WHERE i = 1";
    
    SPI_connect();

    my_exec_spi(sql10);
    my_exec_spi(sql11);
    my_exec_spi(sql12);
    my_exec_spi(sql13);
    my_exec_spi(sql14);
    my_exec_spi(sql2);

    SPI_finish();
}

I have changed the contrib\spi\autoinc contrib module to include a test_spt_feature() function, the purpose of this function is to call our test code. I want to say it again that “calling SQL in C language”, yes test_spt_feature()called spi_simple_test(), and then successfully executed SQL statements from sql10 to sql14. Maybe you doute where the output of DROP and CREATE table t1 comes from, you can get it from the code.

postgres=# select test_spt_feature(0);
SQL: DROP TABLE IF EXISTS t1
SQL: CREATE TABLE t1(i int, j int, k varchar)
SQL: INSERT INTO t1 VALUES(1,1,'I will be deleted')
SQL: INSERT INTO t1 VALUES(2,2,'I will be updated')
SQL: INSERT INTO t1 VALUES(3,2,'I will be there all the time')
SQL: UPDATE t1 SET k = 'updated from i=2' where i = 2
SQL: DELETE FROM t1 WHERE i = 1
SQL: SELECT * FROM t1
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                3 |          2 | I will be there all the time 
                2 |          2 | updated from i=2 
 test_spt_feature 
------------------
 SPI TEST!
(1 row)

Interface Interpretation

C code using SPI module must use SPI_connect() to start an SPI manager, after the SPI manager has started SPI_finish() to used close the SPI manager. In this example, we use the SPI_exec() interface to execute SQL statements. Other SPI interfaces use for executing a query are PI_execute(), SPI_execute_with_args(). In most case a user program needs to do more than just executing a SQL query, the results of the SQL query needs to be fetches, need to use cursor for querying returns a large dataset, transaction control is needed. The blog will delve into most of these and demonstrate how each of these operations are done through SPI.

2. Get the results of a SQL

By now we know that basic function of SPI is to execute a SQL query from a C function, in the example below we look at the retrieving the results of the SQL query in the C function.

/*
 * This function is for we can output a exec result
 * of SPI_exec()
 */
static void
outExecResult(int ret)
{
    int64       rows = 0;
    int         i = 0;
    int     j = 0;

    if(ret <= 0)
        return;
    rows = SPI_processed;
    if (SPI_tuptable)
    {
        TupleDesc       tupdesc = SPI_tuptable->tupdesc;
        SPITupleTable   *tuptable = SPI_tuptable;
        char            buf[8192] = {0};
        

        for (i = 1; i <= tupdesc->natts; i++)
        {
            char    *value = NULL;
            char    *type = NULL;
            char    *inter = (i == tupdesc->natts) ? " " : " |";
            char    *cur_loc = buf + strlen (buf);
            int     remain_base = sizeof(buf) - strlen(buf);

            value = SPI_fname(tupdesc, i);
            type = SPI_gettype(tupdesc, i);
            snprintf(cur_loc, remain_base, " %4s(%s)%s", value, type, inter);
        }
        output_str("%5c %s", ' ', buf);
        output_str("%5c --------------------------------------------------", ' ');

        for (j = 0; j < rows; j++)
        {
            HeapTuple   tuple = tuptable->vals[j];

            memset(buf, 0, 8192);
            for (i = 1; i <= tupdesc->natts; i++)
            {
                char    *value = NULL;
                char    *inter = (i == tupdesc->natts) ? " " : " |";
                char    *cur_loc = buf + strlen (buf);
                int     remain_base = sizeof(buf) - strlen(buf);

                value = SPI_getvalue(tuple, tupdesc, i);
                snprintf(cur_loc, remain_base, " %10s%s", value, inter);
            }
            output_str("%5c %s", ' ', buf);
        }
    }
}

Interface Interpretation

After executing the SELECT statement, the SPI manager will temporarily store the query results in the SPI_tuptable global variable. Referring to the above code, the SPI_fname() interface can return the column name, SPI_gettype() can return the type of the column, and the vals array in SPI_tuptable store every row of returned data, the SPI_getvalue() interface can get the value of a certain column of each row of data.

There are SPI_fnumber(), SPI_getbinval(), SPI_gettypeid(), SPI_getrelname(), SPI_getnspname() interfaces which can handle the return data. I hope by now the readers have understand how to invoke the SPI main interfaces.

3. Cursor

There may be a lot of data in a certain table, when we want to query the data in a large table, it is impossible to return all the data at once. We need to use the cursor to return the data in batches. The SPI module also provides an interface for the cursor function. The code snippet below shows how to make use of cursors using the SPI interface functions, the example below inserts 20 pieces of data in t1 table and then read 3 rows in every round.

static void
spi_cursor_test(void)
{
    char        *sql10 = "INSERT INTO t1 SELECT generate_series(1,20),0,'Insert by generate_series'";
    char        *sql11 = "SELECT * FROM t1";
    Portal      pt = NULL;
    SPIPlanPtr  SPIpp = NULL;
    
    SPI_connect();

    my_exec_spi(sql10);

    SPIpp = SPI_prepare(sql11, 0, NULL);
    output_str("SQL:Create Cursor");
    pt = SPI_cursor_open("Test_pt", SPIpp, NULL, NULL, false);

    output_str("SQL:Cursor Fetch3");
    SPI_cursor_fetch(pt, true, 3);
    outExecResult(1);
    output_str("SQL:Cursor Fetch3");
    SPI_cursor_fetch(pt, true, 3);
    outExecResult(1);

    SPI_cursor_close(pt);
    SPI_finish();
}

Test it below

postgres=# select test_spt_feature(4);
SQL: DROP TABLE IF EXISTS t1
SQL: CREATE TABLE t1(i int, j int, k varchar)
SQL: INSERT INTO t1 SELECT generate_series(1,20),0,'Insert by generate_series'
SQL:Create Cursor
SQL:Cursor Fetch3
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                1 |          0 | Insert by generate_series 
                2 |          0 | Insert by generate_series 
                3 |          0 | Insert by generate_series 
SQL:Cursor Fetch3
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                4 |          0 | Insert by generate_series 
                5 |          0 | Insert by generate_series 
                6 |          0 | Insert by generate_series 
 test_spt_feature 
------------------
 SPI TEST!
(1 row)

Interface Interpretation

SPI_prepare() is to create an execution plan for use when creating a cursor. The interfaces with the same function include SPI_prepare_cursor(), SPI_prepare_params(), SPI_execute_plan(), SPI_execute_plan_with_paramlist(). They realize the execution plan with diversified requirements, due to the space limitation here will not continue to expand.

SPI_cursor_open() is to open the cursor, and there are also diversified interfaces SPI_cursor_open_with_args() and SPI_cursor_open_with_paramlist().

SPI_cursor_fetch() is to perform tuple fetching. After this interface is called, we can find the result it returns in the SPI_tuptable global variable.

SPI_cursor_close() closes a cursor.

There are some other related interfaces such as SPI_cursor_find(), SPI_cursor_move()

4. Binding parameters

In many cases, users need to call SQL by binding parameters instead of putting every value inside SQL. For example, INSERT INTO t1 VALUES(1,1,$1), SPI also provides an interface for this binding variable situation.

static void
spi_with_args_test(void)
{
    char    *sql10 = "INSERT INTO t1 VALUES($1,$2,$3)";
    int     nargs = 3;
    Oid     argtypes[3] = {23, 23, 25};
    char    nulls[3] = {' ', ' ', ' '};
    Datum   values[3];
    char    *text_value = "I am inserted by SPI_execute_with_args";

    values[0] = Int32GetDatum(100);
    values[1] = Int32GetDatum(100);
    values[2] = PointerGetDatum(cstring_to_text(text_value));

    SPI_connect();

    output_str("SQL:%s", sql10);
    SPI_execute_with_args(sql10, nargs, argtypes, values, nulls, false, 0);
    my_exec_spi(sql2);

    SPI_finish();
}

Test it below

postgres=# select test_spt_feature(2);
SQL: DROP TABLE IF EXISTS t1
SQL: CREATE TABLE t1(i int, j int, k varchar)
SQL:INSERT INTO t1 VALUES($1,$2,$3)
SQL: SELECT * FROM t1
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
              100 |        100 | I am inserted by SPI_execute_with_args 
 test_spt_feature 
------------------
 SPI TEST!
(1 row)

postgres=#

Interface Interpretation

In this example, there is only one SPI_execute_with_args() interface, which is a variant of the SPI_execute() mentioned earlier. In order to achieve the function of binding variables, this function has complex parameters.

5. Transaction Control

The aforementioned SPI_exec() interface can execute SQL, but this interface does not support transaction-related SQL. If transaction-related SQL is entered, this interface always returns an SPI_ERROR_TRANSACTION. To implement transactions, you need to use the PG kernel interface. In the following example, tx_begin(), tx_commit(), tx_abort() are functions that I implement to complete transaction control, and spi_transaction_test() is a test function.

static void
tx_begin(MemoryContext  oldcontext)
{
    output_str("SQL: BEGIN");
    BeginInternalSubTransaction(NULL);
    MemoryContextSwitchTo(oldcontext);
}

static void
tx_commit(MemoryContext oldcontext, ResourceOwner oldowner)
{
    output_str("SQL: COMMIT");
    ReleaseCurrentSubTransaction();
    MemoryContextSwitchTo(oldcontext);
    CurrentResourceOwner = oldowner;
}

static void
tx_abort(MemoryContext  oldcontext, ResourceOwner oldowner)
{
    output_str("SQL: ABORT");
    RollbackAndReleaseCurrentSubTransaction();
    MemoryContextSwitchTo(oldcontext);
    CurrentResourceOwner = oldowner;
}
static void
spi_transaction_test(void)
{
    char    *sql10 = "INSERT INTO t1 VALUES(1,1,'I will be commited')";
    char    *sql11 = "INSERT INTO t1 VALUES(2,2,'I will be abort')";
    MemoryContext       oldcontext = NULL;
    ResourceOwner       oldowner = NULL;

    oldcontext = CurrentMemoryContext;
    oldowner = CurrentResourceOwner;
    
    SPI_connect();

    tx_begin(oldcontext);
    my_exec_spi(sql10);
    my_exec_spi(sql2);
    tx_commit(oldcontext, oldowner);

    tx_begin(oldcontext);
    my_exec_spi(sql11);
    my_exec_spi(sql2);
    tx_abort(oldcontext, oldowner);

    my_exec_spi(sql2);

    SPI_finish();
}

OK, let’s test it

postgres=# select test_spt_feature(1);
SQL: DROP TABLE IF EXISTS t1
SQL: CREATE TABLE t1(i int, j int, k varchar)
SQL: BEGIN
SQL: INSERT INTO t1 VALUES(1,1,'I will be commited')
SQL: SELECT * FROM t1
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                1 |          1 | I will be commited 
SQL: COMMIT
SQL: BEGIN
SQL: INSERT INTO t1 VALUES(2,2,'I will be abort')
SQL: SELECT * FROM t1
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                1 |          1 | I will be commited 
                2 |          2 | I will be abort 
SQL: ABORT
SQL: SELECT * FROM t1
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                1 |          1 | I will be commited 
 test_spt_feature 
------------------
 SPI TEST!
(1 row)

postgres=#

Interface Interpretation

NULL on this area.

6. Call Error && Run Error

There are two types of SPI error handling, one is the wrong use of the SPI interface, and interface users should avoid this error, the other is errors such as primary key conflicts and table non-existence caused by SQL execution.

For the first case, you should artificially judge whether the return value is correct every time you execute the SPI_execute() function. For the return value criterion, you can read the entry in the official document, which is not repeated here.

For the second error situation, you need to use the PG_TRY() mechanism to capture unforeseen errors and deal with them.

For my_exec_spi(), this time I completed an implementation with error handling capabilities.

static void
my_exec_spi_with_try(char *sql)
{
    int                 ret = 0;
    MemoryContext       oldcontext = NULL;
    ResourceOwner       oldowner = NULL;

    oldcontext = CurrentMemoryContext;
    oldowner = CurrentResourceOwner;

    tx_begin(oldcontext);
    PG_TRY();
    {
        output_str("SQL: %s", sql);
        ret = SPI_exec(sql, 0);
        outExecResult(ret);
        tx_commit(oldcontext, oldowner);
    }
    PG_CATCH();
    {
        output_str("WARNING:Fail to exec %s, will ignore it.", sql);
        tx_abort(oldcontext, oldowner);
    }
    PG_END_TRY();
}
static void
spi_error_handle(void)
{
    char    *sql10 = "INSERT INTO t1 VALUES(1,1,'Test error handle')";
    char    *sql11 = "INSERT INTO t2 VALUES(1,1,'Where is the table t2?')";
    char    *sql12 = "INSERT INTO t1 VALUES(2,2,'Test error handle')";

    SPI_connect();

    my_exec_spi_with_try(sql10);
    my_exec_spi_with_try(sql11);
    my_exec_spi_with_try(sql12);

    my_exec_spi(sql2);

    SPI_finish();

}

Test it below

postgres=# select test_spt_feature(5);
SQL: DROP TABLE IF EXISTS t1
SQL: CREATE TABLE t1(i int, j int, k varchar)
SQL: BEGIN
SQL: INSERT INTO t1 VALUES(1,1,'Test error handle')
SQL: COMMIT
SQL: BEGIN
SQL: INSERT INTO t2 VALUES(1,1,'Where is the table t2?')
WARNING:Fail to exec INSERT INTO t2 VALUES(1,1,'Where is the table t2?'), will ignore it.
SQL: ABORT
SQL: BEGIN
SQL: INSERT INTO t1 VALUES(2,2,'Test error handle')
SQL: COMMIT
SQL: SELECT * FROM t1
          i(int4) |    j(int4) |    k(varchar) 
      --------------------------------------------------
                1 |          1 | Test error handle 
                2 |          2 | Test error handle 
 test_spt_feature 
------------------
 SPI TEST!
(1 row)

postgres=#

You can see that we caught this error, otherwise the test_spt_feature(5) function will report an error and stop.

Interface Interpretation

Use the PG_TRY() mechanism to capture any possible ERROR that occurs when executing SQL

7. Conclusion

The blog provides a detailed introduction to SPI module and its usage. It use code snippets and various examples to demonstrate the usage of the SPI functions, the readers can use the code snippets to understand the usage and how to embed SPI calls in their C programs. The blog mentions the main and important interface functions while it doesn’t go over all the SPI interface functions. Hopefully the blog will also help in understanding how other SPI function can be used.

Leave a Reply

Your email address will not be published. Required fields are marked *