LoginSignup
6
0

More than 1 year has passed since last update.

PostgreSQLAdvent Calendar 2022Day 22
@seinoyu

SPIインタフェースでPostgreSQLにアクセス

Last updated at Posted at 2022-12-21

PostgreSQLのアドベントカレンダー2022の22日目の記事です。
昨日は@sawada_masahikoさんの最速でVacuumを完了させる方法とその副作用という記事でした。
XID周回問題の20億トランザクション制約については、昔はFreeze頻度も少なくあまり気にしていませんでしたが、今は20億は簡単に到達してしまうのでFreeze頻度も高まっている印象がありますね。改善パッチについて大いに期待です。

PostgreSQL 拡張機能 を開発する際のTipsになります。
ある PostgreSQL関連の拡張機能をメンテナンスしていた際にSPI(Server Programming Interface)について触れることがあったのですが、ググっても情報があまりなく少し苦しんだので残しておければと思います。

はじめに

C言語プログラムで PostgreSQLデータベース へのアクセスを実装する方法として、libpq や ECPG 等がありますが、これらはクライアントサイドの実装用に提供されるものです。
そのため、PostgreSQLのサーバサイド(バックエンド)からこれらの関数を使おうとするとエラーになります。(おそらくサーバサイドからは使えない仕様になっていると思われます。)
拡張機能を開発する際にサーバサイドで完結させたい処理であり、PostgreSQL データベース へのアクセスを実装する場合には、SPI を使用することになります。
特に今回性能検証などはしていませんが、TCPやUNIXドメインソケットを経由しないため、いくらかのオーバヘッドが削減できる可能性も期待できます。
コネクションももたないため、実装もかなりシンプルです。
今回はそんな、SPI 使用方法等について、簡単にまとめていきたいと思います。

環境

PostgreSQL : 16devel

単純な SELECT のサンプルプログラム

早速ですが、SPIを使ったサンプルプログラムです。
今回はログに参照結果を出力します。
SPIインターフェースとして、SPI_connect, SPI_execute, SPI_finish を利用します。

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

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(spi_test);

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

    // SPIマネージャに接続
	SPI_connect();
    // SELECT文を実行
	SPI_execute("SELECT * FROM spitest_tbl", false, 0);

    // 実行結果を取得
	tuptable = SPI_tuptable;	
	tup = tuptable->vals[0];
	desc = tuptable->tupdesc;

    // 実行結果をログに出力
	elog(LOG, "[id=%s][text=%s]", SPI_getvalue(tup, desc, 1), SPI_getvalue(tup, desc, 2));
    
    // SPIマネージャから切断
	SPI_finish();

	PG_RETURN_BOOL(true);
}

Makefileを作成します

Makefile
MODULE_big = spitest
OBJS = spitest.o

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

コンパイルしてインストールします。

$ make USE_PGXS=1 PG_CONFIG=/home/hoge/pgsql/master/bin/pg_config 
$ make USE_PGXS=1 PG_CONFIG=/home/hoge/pgsql/master/bin/pg_config install

参照するテーブルを事前に作成しておきます。

postgres=# CREATE TABLE spitest_tbl(col1 integer, col2 text);
postgres=# INSERT INTO spitest_tbl VALUES(1, 'test');
postgres=# select * from spitest_tbl;
 col1 | col2 
------+------
    1 | test
(1 row)

参照するためのファンクションを用意して実行します。

postgres=# CREATE FUNCTION spitest_func()
postgres-# RETURNS boolean
postgres-# AS '$libdir/spitest', 'spi_test'
postgres-# LANGUAGE C STRICT;
postgres=# select spitest_func();
 spitest_func 
--------------
 t
(1 row)

ログファイルを確認します。

logfile
2022-12-20 01:23:45.785 JST [501985] LOG:  statement: select spitest_func();
2022-12-20 01:23:45.786 JST [501985] LOG:  [id=1][text=test]

トランザクション有りのサンプルプログラム

トランザクションを利用した SQL も実行可能です。
ファンクションから呼び出すとエラーとなるので、プロシージャを使います。
これは、ファンクションはトランザクション処理が使用できない制約があるためです。
SPIインターフェースとして、SPI_connect_ext, SPI_execute, SPI_rollback, SPI_commit, SPI_finish を利用して、トランザクション(コミット、ロールバック)を試してみます。

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

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(spi_test);

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

    // SPIマネージャに接続(非原子的モード)
	SPI_connect_ext(SPI_OPT_NONATOMIC);
	
	// トランザクション1
	// INSERT文を実行
	SPI_execute("INSERT INTO spitest_tbl2 VALUES (1)", false, 0);
    // SELECT文を実行
	SPI_execute("SELECT count(*) FROM spitest_tbl2", false, 0);
    // 実行結果を取得(ログ出力)
	tuptable = SPI_tuptable;		
	tup = tuptable->vals[0];
	desc = tuptable->tupdesc;
	elog(LOG, "[count = %s]", SPI_getvalue(tup, desc, 1));
    // ROLLBACK
	SPI_rollback();

	// トランザクション2
	// SELECT文を実行
	SPI_execute("SELECT count(*) FROM spitest_tbl2", false, 0);
	// 実行結果を取得(ログ出力)
	tuptable = SPI_tuptable;		
	tup = tuptable->vals[0];
	desc = tuptable->tupdesc;
	elog(LOG, "[count = %s]", SPI_getvalue(tup, desc, 1));
	// INSERT文を実行
	SPI_execute("INSERT INTO spitest_tbl2 VALUES (1)", false, 0);
	// SELECT文を実行
	SPI_execute("SELECT count(*) FROM spitest_tbl2", false, 0);
	// 実行結果を取得(ログ出力)
	tuptable = SPI_tuptable;		
	tup = tuptable->vals[0];
	desc = tuptable->tupdesc;
	elog(LOG, "[count = %s]", SPI_getvalue(tup, desc, 1));
	// COMMIT
	SPI_commit();

	// トランザクション3
	// SELECT文を実行
	SPI_execute("SELECT count(*) FROM spitest_tbl2", false, 0);
	// 実行結果を取得(ログ出力)
	tuptable = SPI_tuptable;		
	tup = tuptable->vals[0];
	desc = tuptable->tupdesc;
	elog(LOG, "[count = %s]", SPI_getvalue(tup, desc, 1));

    // SPIマネージャから切断
    SPI_finish();

	PG_RETURN_BOOL(true);
}

コンパイルしてインストールします。

$ make USE_PGXS=1 PG_CONFIG=/home/hoge/pgsql/master/bin/pg_config 
$ make USE_PGXS=1 PG_CONFIG=/home/hoge/pgsql/master/bin/pg_config install

テーブルを事前に作成しておきます。

postgres=# CREATE TABLE spitest_tbl2(id integer);

プロシージャを用意して実行します。

postgres=# CREATE PROCEDURE spitest_proc2()
postgres-# AS '$libdir/spitest', 'spi_test_2'
postgres-# LANGUAGE C;
CREATE PROCEDURE
postgres=# call spitest_proc2 ();
CALL

ログファイルを確認します。

logfile
2022-12-20 19:05:56.658 JST [518872] LOG:  statement: CALL spitest_proc2 ();
2022-12-20 19:05:56.659 JST [518872] LOG:  [count = 1] // トランザクション1のINSERT
2022-12-20 19:05:56.659 JST [518872] LOG:  [count = 0] // トランザクション1のROLLBACKによりレコードが削除
2022-12-20 19:05:56.659 JST [518872] LOG:  [count = 1] // トランザクション1のINSERT
2022-12-20 19:05:56.662 JST [518872] LOG:  [count = 1] // トランザクション1のCOMMITによりレコードが残る

引数有りのサンプルプログラム

引数を持たせたい SQL も実行可能です。

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

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(spi_test_3);

Datum
spi_test_3(PG_FUNCTION_ARGS)
{
	Oid     argtypes[2] = { INT8OID, INT8OID }; // 型OIDを指定
	Datum   params[2];							// パラメータ
	char    nulls[2] = { ' ', ' '};			    // パラメータのnull値を設定('n' or ' ')
	params[0] = Int64GetDatum(1);				// パラメータ1に「1」を設定
	params[1] = Int64GetDatum(2);				// パラメータ2に「2」を設定

    // SPIマネージャに接続
	SPI_connect();
	
	// INSERT文を実行
	SPI_execute_with_args("INSERT INTO spitest_tbl3 VALUES( $1, $2)", 2, argtypes, params, nulls, false, 0);

    // SPIマネージャから切断
    SPI_finish();

	PG_RETURN_BOOL(true);
}

コンパイルしてインストールします。

$ make USE_PGXS=1 PG_CONFIG=/home/hoge/pgsql/master/bin/pg_config 
$ make USE_PGXS=1 PG_CONFIG=/home/hoge/pgsql/master/bin/pg_config install

テーブルを事前に作成しておきます。

postgres=# CREATE TABLE spitest_tbl3(col1 integer, col2 integer);

プロシージャを用意して実行して、実行結果を確認します。

postgres=# CREATE PROCEDURE spitest_proc3()
postgres-# AS '$libdir/spitest', 'spi_test_3'
postgres-# LANGUAGE C;
CREATE PROCEDURE
postgres=# call spitest_proc3 ();
CALL
postgres=# SELECT * FROM spitest_tbl3;
 col1 | col2 
------+------
    1 |    2
(1 row)

さいごに

SPI(Server Programming Interface) について紹介しました。実は PostgreSQL 本体でも、SPI はあまり使われておらず参考になるソースが少ないです。
しかしながら、 libpq 等と比べてコネクション情報を持ちまわる必要がなく、単純で使いやすいものになっています。
サーバサイドプログラムを作成する際は、ぜひ利用してみてください。

6
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
0