Server Programming Interface

PostgreSQL拡張機能の開発について、わかりやすい解説が下記にありましたので、参考にさせていたたぎ、自環境で試してみました。

「SPIインタフェースでPostgreSQLにアクセス」
https://qiita.com/seinoyu/items/5f31a871261f84f273e9

環境) PostgreSQL 14.6 / Ubuntu 22.04
開発に必要なパッケージ下記コマンドでインストールします。

sudo apt install postgresql postgresql-contrib postgresql-server-dev-all

テーブル準備

CREATE TABLE spitest01_tbl(col1 integer, col2 integer);

spitest01.c

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(spitest01);

Datum
spitest01(PG_FUNCTION_ARGS)
{
        SPITupleTable *tuptable;
        HeapTuple  tup;
        TupleDesc desc;

        Oid     argtypes[2] = { INT8OID, INT8OID };
        Datum   params[2];
        char    nulls[2] = { ' ', ' '};                     // パラメータのnull値を設定('n' or ' ')

        //SPI_connect();
        SPI_connect_ext(SPI_OPT_NONATOMIC);

        params[0] = Int64GetDatum(10);
        params[1] = Int64GetDatum(11);
        SPI_execute_with_args("INSERT INTO spitest01_tbl VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);
        SPI_commit();
        params[0] = Int64GetDatum(20);
        params[1] = Int64GetDatum(21);
        SPI_execute_with_args("INSERT INTO spitest01_tbl VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);
        SPI_rollback();
        params[0] = Int64GetDatum(30);
        params[1] = Int64GetDatum(31);
        SPI_execute_with_args("INSERT INTO spitest01_tbl VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);
        SPI_commit();

        SPI_execute("SELECT * FROM spitest01_tbl", false, 0);
        tuptable = SPI_tuptable;
        desc = tuptable->tupdesc;
        tup = tuptable->vals[0];
        elog(LOG, "[col1=%s][col2=%s]", SPI_getvalue(tup, desc, 1), SPI_getvalue(tup, desc, 2));
        tup = tuptable->vals[1];
        elog(LOG, "[col1=%s][col2=%s]", SPI_getvalue(tup, desc, 1), SPI_getvalue(tup, desc, 2));

        SPI_finish();

        PG_RETURN_BOOL(true);
}

#include "postgres.h"

#include "fmgr.h"

#include "executor/spi.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(spitest01);

Datum

spitest01(PG_FUNCTION_ARGS)

{

SPITupleTable *tuptable;

HeapTuple tup;

TupleDesc desc;

Oid argtypes[2] = { INT8OID, INT8OID };

Datum params[2];

char nulls[2] = { ' ', ' '}; // パラメータのnull値を設定('n' or ' ')

//SPI_connect();

SPI_connect_ext(SPI_OPT_NONATOMIC);

params[0] = Int64GetDatum(10);

params[1] = Int64GetDatum(11);

SPI_execute_with_args("INSERT INTO spitest01_tbl VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);

SPI_commit();

params[0] = Int64GetDatum(20);

params[1] = Int64GetDatum(21);

SPI_execute_with_args("INSERT INTO spitest01_tbl VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);

SPI_rollback();

params[0] = Int64GetDatum(30);

params[1] = Int64GetDatum(31);

SPI_execute_with_args("INSERT INTO spitest01_tbl VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);

SPI_commit();

SPI_execute("SELECT * FROM spitest01_tbl", false, 0);

tuptable = SPI_tuptable;

desc = tuptable->tupdesc;

tup = tuptable->vals[0];

elog(LOG, "[col1=%s][col2=%s]", SPI_getvalue(tup, desc, 1), SPI_getvalue(tup, desc, 2));

tup = tuptable->vals[1];

elog(LOG, "[col1=%s][col2=%s]", SPI_getvalue(tup, desc, 1), SPI_getvalue(tup, desc, 2));

SPI_finish();

PG_RETURN_BOOL(true);

}

Makefile

MODULE_big = spitest01
OBJS = spitest01.o

PGXS := $(shell pg_config –pgxs)
include $(PGXS)

pg_configにはパスはすでに通っているものとして、(/usr/bin/pg_config)

make
sudo make install

でビルド、インストールします。

pgxsは下記をさしています。

pg_config –pgxs
/usr/lib/postgresql/14/lib/pgxs/src/makefiles/pgxs.mk

soは下記にインストールされました。
/usr/lib/postgresql/14/lib/spitest01.so

呼び出し

postgres=# CREATE PROCEDURE spitest01() AS ‘$libdir/spitest01’, ‘spitest01’ LANGUAGE C;
CREATE PROCEDURE
postgres=# call spitest01();
CALL
postgres=# select * from spitest01_tbl;
col1 | col2
——+——
10 | 11
30 | 31
(2 rows)

ログ確認

tail -f /var/log/postgresql/postgresql-14-main.log

2023-01-29 05:55:44.612 UTC [12779] postgres@postgres LOG: [col1=10][col2=11]
2023-01-29 05:55:44.612 UTC [12779] postgres@postgres STATEMENT: call spitest01();
2023-01-29 05:55:44.612 UTC [12779] postgres@postgres LOG: [col1=30][col2=31]
2023-01-29 05:55:44.612 UTC [12779] postgres@postgres STATEMENT: call spitest01();

INSERTを3回やりますが、2回目をロールバックして、正常に書き込まれているかの確認です。

以前からDBサーバの拡張機能の開発に興味がありましたが、まずはここまで簡単にその手順の確認でした。
これをベースにまた発展させていきたいと思います。