PostgreSQL中的SPI模块

开源软件联盟PostgreSQL分会 2020-08-05

3293

作者：李传成

中国PG分会认证专家，瀚高软件资深内核研发工程师

前言

*Server Programming Interface（SPI）*是PostgreSQL内核中的一个模块，这个模块让内核开发者可以在C函数中执行SQL语句，并具备管理事务的能力。这个模块通过其特性为PostgreSQL的插件开发提供了巨大的便利，使开发者可以便利的在内核中调用各种数据库语言。在PostgreSQL的过程语言的实现中使用了SPI模块，在学习使用SPI的时候我们可以参考这些模块调用SPI接口的方法。官方文档给出了SPI模块的接口，但是对调用方式的介绍很少，在学习SPI模块的时候，写下这篇博客记录一些主要SPI接口的使用方法。

简单示例

SPI模块是面向内核开发者的模块，它使得在数据库内核C代码中调用SQL成为可能。有些读者可能不知道SPI是什么，这里通过一个简单的示例，直观的说明SPI模块的魅力。另外你可以从这里获取本博客中提到的代码(如果您想深读这个博客，建议下载代码对比阅读)，并拷贝到PostgreSQL源码目录contrib\spi路径下，然后编译安装autoinc插件，就可以执行本博客提到的所有测试了。

这是我要执行的C代码，其中SPI_exec(),SPI_connect(),SPI_finish()是SPI模块提供的接口，我们后面会再提到这些接口，outExecResult()函数是显示SELECT结果的函数，后面还会提到, 在这个例子中我主要是想展示SPI的基本功能：在C语言中调用SQL。

static void
my_exec_spi(char *sql)
{
intret = 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();
}

我改了contrib\spi\autoinc插件，并在这个插件里创建了一个test_spt_feature()函数来调用我们的测试代码。现在我们再洗脑一下这句话“在C语言中调用SQL”，是的test_spt_feature()调用了spi_simple_test(),然后成功执行了sql10到sql14的SQL语句。至于DROP和CREATE表t1的输出是哪来的，读者可以去阅读代码。

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)

接口分析

C代码使用SPI模块必需使用SPI_connect()开始一个SPI管理器，在使用完成之后需要使用SPI_finish()把这个SPI管理器关闭。在本例中我们使用SPI_exec()接口来执行SQL语句，类似功能的接口还有SPI_execute(), SPI_execute_with_args()。当然仅仅执行SQL还不够我们需要获取执行的结果，需要进行事务控制，需要实现游标管理，错误处理，下面将详细展开每一个子项。

获取执行结果

在简单示例模块中，我们知道了SPI可以使在C中调用SQL这一事实，这里我们再来看一下怎么通过C语言获取一个SELECT的结果。

/*
 * This function is for we can output a exec result
 * of SPI_exec()
 */
static void
outExecResult(int ret)
{
int64rows = 0;
inti = 0;
intj = 0;
 
if(ret <= 0)
return;
rows = SPI_processed;
if (SPI_tuptable)
    {
        TupleDesc tupdesc = SPI_tuptable->tupdesc;
        SPITupleTable*tuptable = SPI_tuptable;
        charbuf[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);
intremain_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++)
        {
            HeapTupletuple = 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);
intremain_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);
        }
    }
}

接口分析

执行完SELECT之后，SPI管理器会将查询结果暂存于SPI_tuptable全局变量中，参照上面的代码，SPI_fname()接口可以返回列名，SPI_gettype()可以返回列的类型，SPI_tuptable中的vals数组存储了返回的每一行数据，SPI_getvalue()接口可以返回每一行数据的某一列的值。

对于处理返回值的接口，还有SPI_fnumber(),SPI_getbinval(),SPI_gettypeid(),SPI_getrelname(),SPI_getnspname(),在有了这个代码实例之后，相信读者已学会调用这些接口的方法。

游标

某一张表的数据可能很多，当我们想查询一张大表中的数据时，不可能一次性返回所有的数据，需要借助游标来分批次返回数据。SPI模块也提供了游标功能的接口,下面是SPI游标的调用代码。在下面的代码中，我首先向t1表中插入了20条数据，然后通过SPI的游标功能，每次读取3条数据。

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;
SPIPlanPtrSPIpp = 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();
}

如下测试

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)

接口分析

SPI_prepare()是创建一个执行计划，以供创建游标时使用，相同的功能的接口还有SPI_prepare_cursor(),SPI_prepare_params()，SPI_execute_plan(), SPI_execute_plan_with_paramlist()他们实现了多样化需求的执行计划，由于篇幅所限这里不再继续展开。

SPI_cursor_open()是打开游标，同样有多样化的接口SPI_cursor_open_with_args()和SPI_cursor_open_with_paramlist().

SPI_cursor_fetch()是执行元组的获取，这个接口调用后，我们就可以在SPI_tuptable全局变量中找到它返回的结果了。

SPI_cursor_close()关闭一个游标。

还有一些其他的相关接口如SPI_cursor_find(),SPI_cursor_move()

绑定参数

很多情况下，用户需要通过绑定参数的方法来调用SQL，而不是将每一个值都放在SQL内部。比如INSERT INTO t1 VALUES(1,1,$1),SPI也为这种绑定变量的情况提供了接口。

static void
spi_with_args_test(void)
{
char*sql10 = "INSERT INTO t1 VALUES($1,$2,$3)";
intnargs = 3;
Oidargtypes[3] = {23, 23, 25};
char nulls[3] = {' ', ' ', ' '};
Datumvalues[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();
}

如下测试

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=#

接口说明

本例中仅有一个SPI_execute_with_args()接口，它是前面提到的SPI_execute()的一个变体，为了实现绑定变量的功能，这个函数有复杂的参数。

事务控制

之前提到的SPI_exec()接口可以执行SQL，但是这个接口不支持事务相关的SQL，如果输入了事务相关SQL，那么这个接口总是返回一个SPI_ERROR_TRANSACTION。想要实现事务，需要还需使用PG内核的接口，如下示例，tx_begin(),tx_commit(),tx_abort()是我实现的可以完成事务控制的函数，spi_transaction_test()是测试函数。

static void
tx_begin(MemoryContextoldcontext)
{
output_str("SQL: BEGIN");
BeginInternalSubTransaction(NULL);
MemoryContextSwitchTo(oldcontext);
}
 
static void
tx_commit(MemoryContextoldcontext, ResourceOwner oldowner)
{
output_str("SQL: COMMIT");
ReleaseCurrentSubTransaction();
MemoryContextSwitchTo(oldcontext);
CurrentResourceOwner = oldowner;
}
 
static void
tx_abort(MemoryContextoldcontext, 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')";
MemoryContextoldcontext = NULL;
ResourceOwneroldowner = 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();
}

好了，下面测一测这个函数

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=#

使用错误&&执行错误

SPI的错误处理分为两种，一是错误的使用了SPI接口，接口使用者应该规避这种错误。另外一种是SQL执行产生的比如主键冲突，表不存在等错误。

对于第一种情况，你应该每次执行SPI_execute()系类函数的时候，人为的判断返回值是不是正确的，对于这个返回值的准则可以去阅读官方文档的条目,这里不在赘述。

对于第二种错误情况，需要使用PG_TRY()机制，抓取不可预见的错误，并进行处理。

对标my_exec_spi()，这次我完成了一个带有错误处理能力的实现。

static void
my_exec_spi_with_try(char *sql)
{
intret = 0;
MemoryContextoldcontext = NULL;
ResourceOwneroldowner = 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();
 
}

测试一下

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=#

可以看到我们catch到了这个错误，否则test_spt_feature(5)函数会报错停止。

接口分析

使用PG_TRY()机制，捕获执行SQL发生的任何可能的ERROR。

结论

这篇博客介绍了什么是SPI模块和它的使用方法，并对通过一些具体的示例，说明一些SPI接口的使用方法，当然也有一些SPI接口没有提到，相信读者可以通过本博客中的示例中轻松学会出其他SPI接口的使用方法。


I Love PG

关于我们
中国开源软件推进联盟PostgreSQL分会（简称：中国PG分会）于2017年成立，由国内多家PostgreSQL生态企业所共同发起，业务上接受工信部中国电子信息产业发展研究院指导。中国PG分会是一个非盈利行业协会组织。我们致力于在中国构建PostgreSQL产业生态，推动PostgreSQL产学研用发展。

欢迎投稿

做你的舞台，show出自己的才华。

投稿邮箱：partner@postgresqlchina.com

——愿能安放你不羁的灵魂

技术文章精彩回顾




PostgreSQL学习的九层宝塔
PostgreSQL职业发展与学习攻略
2019，年度数据库舍 PostgreSQL 其谁?
Postgres是最好的开源软件
PostgreSQL是世界上最好的数据库
从Oracle迁移到PostgreSQL的十大理由
从“非主流”到“潮流”，开源早已值得拥有

PG活动精彩回顾




创建PG全球生态！PostgresConf.CN2019大会盛大召开
首站起航！2019“让PG‘象’前行”上海站成功举行
走进蓉城丨2019“让PG‘象’前行”成都站成功举行
中国PG象牙塔计划发布，首批合作高校授牌仪式在天津举行
群英论道聚北京，共话PostgreSQL
相聚巴厘岛| PG Conf.Asia 2019  DAY0、DAY1简报
相知巴厘岛| PG Conf.Asia 2019 DAY2简报
独家|硅谷Postgres大会简报
直播回顾 | Bruce Momjian：原生分布式将在PG 14版本发布

PG培训认证精彩回顾




中国首批PGCA认证考试圆满结束，203位考生成功获得认证！
中国第二批PGCA认证考试圆满结束，115位考生喜获认证！
重要通知：三方共建，中国PostgreSQL认证权威升级！
近500人参与！首次PGCE中级、第三批次PGCA初级认证考试落幕！
2020年首批 | 中国PostgreSQL初级认证考试圆满结束
一分耕耘一分收获，第五批次PostgreSQL认证考试成绩公布

postgresql

文章转载自开源软件联盟PostgreSQL分会，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL中的SPI模块

评论