Server Programming Interface(SPI)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
Movead.Li is kernel development of Highgo Software. Since joining into Highgo Software in 2016, Movead takes the most time on researching the code of Postgres and is good at ‘Write Ahead Log’ and ‘Database Backup And Recovery’. Base on the experience Movead has two open-source software on the Postgres database. One is Walminer which can analyze history wal file to SQL. The other one is pg_lightool which can do a single table or block recovery base on base backup and walfiles or walfiles only.
Hello
Now he has joined the HighGo community team and hopes to make more contributions to the community in the future.
Recent Comments