Posted at

C#(.NET系)からPostgreSQLへ接続(TransactionScopeや分散トランザクションについて)


はじめに

今更ながらJavaに引き続き、色んな言語からPostgreSQLへ接続するシリーズです。

今回はC#(.NET系)です。C#はODBC接続などの方法もありますが、今回はNpgsqlによる接続を説明します。


Npgsqlとは

.NET環境からPostgreSQLへ接続するためのツールです。

今回は以下の環境でNpgsqlの導入と検証をしました。

環境
バージョン

OS
Windows 10

.NET Framework
4.5

Visual Studio
2013

Npgsql
4.0.3

PostgreSQL
10.0

実際に使用する場合、.NET、Visual Studio、Npgsqlのバージョンを各々考慮する必要があります。

ただ結論を述べると注意すべき点は以下の通りです。



  • Visual Studio 2013(特に.NET 4.5.X)を使っている場合、サポートが切れている上に、古いNpgsqlだとバグがあるため注意する。
    (該当する.NET 4.5.Xに対応した最新のNpgsqlを必ず用いる)

  • 次期バージョンのNpgsql 4.1からは、.NET 4.5.2と.NET Standard 2.0がサポートされる最低バージョンとなる。


  • 最新版のVisual Studio上で、最新の.NET Frameworkを選択し、最新のNpgsqlを用いれば問題ない

そもそも.NET Frameworkと.NET Standardって何? となった方(私はなった)や、詳細な各環境の対応を知りたい方は参考にしたサイトの.NET関連をご参照ください。


Npgsqlの導入

NpgsqlはNuGetを用いて導入可能です。以下のサイトから最新版を確認し、インストール用のコマンドをコピーします。

その後、Visual Studioを開き、メニューバーの[ツール]→[NuGetパッケージマネージャ]→[パッケージマネージャコンソール]を選択する。

するとVisual Studioの下部に、PM(パッケージマネージャ)コンソールが開くため、先程コピーしたコマンドを入力し、結果を確認する。


NuGetによるNpgsqlの導入(失敗)

PM> Install-Package Npgsql -Version 4.0.3

依存関係 'System.Runtime.CompilerServices.Unsafe (≥ 4.5.0)' の解決を試みています。
Install-Package : 'System.Runtime.CompilerServices.Unsafe 4.5.0' パッケージには NuGet クライアント バージョン '2.12' 以降が必要ですが、現在の NuGet バージョンは '2.8.60610.756' です。
発生場所 行:1 文字:1
+ Install-Package Npgsql -Version 4.0.3
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Install-Package], NuGetVersionNotSatisfiedException
+ FullyQualifiedErrorId : NuGetCmdletUnhandledException,NuGet.PowerShell.Commands.InstallPackageCommand

今回はNuGetのバージョンが古いため失敗しました。

そのためNuGetをバージョンアップします。

メニューバーの[ツール]→[拡張機能と更新プログラム]→左メニューから[更新プログラム]の[Visutal Studioギャラリー]を選択します。

NuGetの[更新]をクリックし、インストール後、案内されるままVisutal Studioを再起動します。

バージョンアップ完了後、再びNpgsqlを入手するコマンドを実行すると以下の通り、Npgsqlの導入に成功します。


NuGetによるNpgsqlの導入(失敗)

PM> Install-Package Npgsql -Version 4.0.3

依存関係 'System.Runtime.CompilerServices.Unsafe (≥ 4.5.0)' の解決を試みています。
依存関係 'System.Threading.Tasks.Extensions (≥ 4.5.0)' の解決を試みています。
依存関係 'System.ValueTuple (≥ 4.5.0)' の解決を試みています。
'System.Runtime.CompilerServices.Unsafe 4.5.0' をインストールしています。
'System.Runtime.CompilerServices.Unsafe 4.5.0' が正常にインストールされました。
'System.Threading.Tasks.Extensions 4.5.0' をインストールしています。
'System.Threading.Tasks.Extensions 4.5.0' が正常にインストールされました。
'System.ValueTuple 4.5.0' をインストールしています。
'System.ValueTuple 4.5.0' が正常にインストールされました。
'Npgsql 4.0.3' をインストールしています。
'Npgsql 4.0.3' が正常にインストールされました。
'System.Runtime.CompilerServices.Unsafe 4.5.0' を npgsql_test に追加しています。
'System.Runtime.CompilerServices.Unsafe 4.5.0' が npgsql_test に正常に追加されました。
'System.Threading.Tasks.Extensions 4.5.0' を npgsql_test に追加しています。
'System.Threading.Tasks.Extensions 4.5.0' が npgsql_test に正常に追加されました。
'System.ValueTuple 4.5.0' を npgsql_test に追加しています。
'System.ValueTuple 4.5.0' が npgsql_test に正常に追加されました。
'Npgsql 4.0.3' を npgsql_test に追加しています。
'Npgsql 4.0.3' が npgsql_test に正常に追加されました。


Npgsqlによる単純な接続

まずは単純にPostgreSQLへ接続できるかを確認します。

Visual StudioからC#の空のプロジェクトを作り、コードを書き込んでいきます。


下準備

C#のプロジェクトを生成後、下準備として参照の追加とPostgreSQL側の設定変更をします。


参照の追加

デフォルトの参照設定ではNpgsqlの機能を使うことはできません。そのため参照を追加します。

まずVisual Studioのメニューバーの[プロジェクト]→[参照の追加]を選択します。 その後、左メニューの[アセンブリ]→[フレームワーク]を選択し、"System.Data"と"System.Transaction"にチェックし、参照へ追加します。

また単純な接続では必要ありませんが、SELECT結果取得の際に使用するDataTableのために"System.Xml"もチェックします。


PostgreSQL側の設定

C#アプリ側とPostgreSQLサーバ側が接続可能であるか、ping等で確かめます。

(pingが通らない場合、ファイアウォールやOSのネットワーク設定を見直してください)

サーバ同士の通信が可能であることが判明した後は、PostgreSQLのネットワーク設定を調整します。

PostgreSQLのデータディレクトリ(環境変数PGDATA配下)のpostgresql.confとpg_hba.confの2つの設定ファイルを次のように編集します。


postgresql.conf

listen_addresses = '*'


上記はPostgreSQL側の接続を受け付けるIPアドレスです。

デフォルトでは"localhost"のみなため、"localhost, "または全てのIPで受け付ける"*"(アスタリスク)を設定します。

詳細はマニュアルをご参照ください。


pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

host all all <C#側のIP>/32 md5

接続を許可するクライアント側(今回の場合はC#プログラム側)の設定です。

接続先DB名や接続に用いるユーザ名も限定可能です。

また「METHOD」に"md5"を指定することでパスワード入力のみ受け付けます。

詳細はマニュアルをご参照ください。

上記設定後、PostgreSQLの設定変更を反映するためにPostgreSQLを再起動します。

※listen_addressesパラメータの反映には再起動が必要なためです。

※pg_hba.confの変更反映はreloadのみで可能です。


Npgsqlによる単純な接続の実行

準備が完了したため、早速接続を試行します。

今回作成するソースコードは次の通りです。


Class1.cs

using System;

using Npgsql;

namespace npgsql_test
{
class Class1
{
static void Main(String[] args)
{
//接続文字列
string conn_str = "Server=123.45.67.89;Port=5432;User ID=postgres;Database=postgres;Password=password;Enlist=true";

using (NpgsqlConnection conn = new NpgsqlConnection(conn_str))
{
//PostgreSQLへ接続
conn.Open();
Console.WriteLine("Connection success!");
}
}
}
}


※namespaceやclassは環境ごとに適切な名前にしてください。

接続文字列は以下の値を入力する必要があります。接続先の環境に合わせて設定下さい。


説明

Server
接続先サーバのIP or ホスト名

Port
接続先DBのポート番号

User ID
DBへの接続ユーザ名

Database
接続先DB名

Password
DB接続のパスワード

Enlist
既存のトランザクションへの自動参加の有効/無効を設定(Ttue/False)

上記を[ビルド]→[デバッグなしで開始]をすると、「Connection success!」の文字が標準出力されます。

エラーが発生する場合、そのエラー内容を確認し、PostgreSQLやOSの接続設定に誤りがないか確認してください。


成功時の標準出力

Connection success!



TransactionScopeの利用

C#(.NET系)にはTransactionScopeという、トランザクションを一括管理する頼もしい機能があります。

昔、Npgsql 3.0で検証した頃は、自身の環境のせいもありエラーが発生しましたが、最新版では問題なく使用できます。

C#でSQL処理をする際は頻繁に使うため、Npgsqlでも適切に利用できることを確認します。


TransactionScopeによるトランザクション管理

今回はINSERT、DELETE、SELECT処理を1つのトランザクション内で実施します。

検証に使用するテーブルは次の通りです。


検証用テーブル

postgres=# CREATE TABLE test (col1 INTEGER, col2 VARCHAR);

postgres=# \d test
テーブル "public.test"
| | 照合順序 | Null 値を許容 | デフォルト
------+-------------------+----------+---------------+------------
col1 | integer | | |
col2 | character varying | | |

実際に上記のtestテーブルにSQLを実行したソースコードが以下です。


Class1.cs

using System;

using Npgsql;
using System.Transactions;
using System.Data;

namespace npgsql_test
{
class Class1
{
static void Main(String[] args)
{
//SQL処理で用いる変数を予め宣言
NpgsqlCommand cmd = null;
string cmd_str = null;
DataTable dt = null;
NpgsqlDataAdapter da = null;

//接続文字列
string conn_str = "Server=123.45.67.89;Port=5432;User ID=postgres;Database=postgres;Password=password;Enlist=true";

//TransactionScopeの利用
using (TransactionScope ts = new TransactionScope())
{
//接続その1
using (NpgsqlConnection conn = new NpgsqlConnection(conn_str))
{
//PostgreSQLへ接続後、INSERT、DELETE処理を実施し、SELECT結果を取得
conn.Open();

//test対象のテーブルをリセット(全データDELETE)
cmd_str = "DELETE FROM test";
cmd = new NpgsqlCommand(cmd_str, conn);
cmd.ExecuteNonQuery();

//INSERT処理
cmd_str = "INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')";
cmd = new NpgsqlCommand(cmd_str, conn);
cmd.ExecuteNonQuery();

//DELETE処理
cmd_str = "DELETE FROM test WHERE col1 % 2 = 0";
cmd = new NpgsqlCommand(cmd_str, conn);
cmd.ExecuteNonQuery();

//SELECT処理
dt = new DataTable();
cmd_str = "SELECT * FROM test";
cmd = new NpgsqlCommand(cmd_str, conn);
da = new NpgsqlDataAdapter(cmd);
da.Fill(dt);

//SELECT結果表示
for (int i = 0; i < dt.Rows.Count; i++)
{
Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
}

}
//トランザクション完了
ts.Complete();
}
}
}
}


実行結果は以下の通りです。


成功時の標準出力

(col1, col2) = (1, AAA)

(col1, col2) = (3, CCC)

col1が1~3である行のINSERT後、偶数値の行だけ削除したため上記結果となります。

PostgreSQLのパラメータを「log_statement = 'all'」した状態で上記のC#プログラムの実行ログを見ると以下のようになります。


トランザクション処理の実行ログ

[3830] LOG:  statement: BEGIN

[3830] LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[3830] LOG: execute <unnamed>: DELETE FROM test
[3830] LOG: execute <unnamed>: INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')
[3830] LOG: execute <unnamed>: DELETE FROM test WHERE col1 % 2 = 0
[3830] LOG: execute <unnamed>: SELECT * FROM test
[3830] LOG: statement: COMMIT
[3830] LOG: statement: DISCARD ALL

TransactionScopeにより「BEGIN」~「COMMIT」が実行され、暗黙的にトランザクションが管理されていることが分かります。


TransactionScopeによる分散トランザクション

TransactionScopeを用いることで分散トランザクションが容易に実行可能です。

分散トランザクションは、複数のデータベースに対する処理を1つのトランザクションとして扱う技術です。

C#ではTransactionScopeを使うことで、意識することなく分散トランザクションを利用できます。

今回の検証では


  • test_db1データベースのtest1テーブルへのINSERT処理

  • test_db2データベースのtest2テーブルへのINSERT処理

を分散トランザクションとして実施します。


分散トランザクションのための下準備

下準備としてPostgreSQL側の「max_prepared_transactions」パラメータを0より大きくする必要があります。

(本パラメータは分散トランザクションにおける最大接続数を設定します。0の場合無効です)


postgresql.conf

max_prepared_transactions = 10


設定変更を反映するために、その後、PostgreSQLを再起動します。

本パラメータの詳細はマニュアルをご参照ください。


分散トランザクションの実施

準備が完了したため、以下のソースコードを記述し分散トランザクションを実行します。


Class1.cs

using System;

using Npgsql;
using System.Transactions;
using System.Data;

namespace npgsql_test
{
class Class1
{
static void Main(String[] args)
{
//SQL処理で用いる変数を予め宣言
NpgsqlCommand cmd = null;
string cmd_str = null;
DataTable dt = null;
NpgsqlDataAdapter da = null;

//接続文字列
string conn_str1 = "Server=192.168.56.111;Port=5432;User ID=postgres;Database=tes_db1;Password=password;Enlist=true";
string conn_str2 = "Server=192.168.56.111;Port=5432;User ID=postgres;Database=tes_db2;Password=password;Enlist=true";

//TransactionScopeの利用(分散トランザクション)
using (TransactionScope ts = new TransactionScope())
{
//接続その1(tes_db1へ接続)
using (NpgsqlConnection conn1 = new NpgsqlConnection(conn_str1))
{
//PostgreSQLへ接続後、INSERT処理を実施し、SELECT結果を取得
conn1.Open();

//test対象のテーブルをリセット(全データDELETE)
cmd_str = "DELETE FROM test1";
cmd = new NpgsqlCommand(cmd_str, conn1);
cmd.ExecuteNonQuery();

//INSERT処理
cmd_str = "INSERT INTO test1 VALUES(1, 'AAA'), (2, 'BBB')";
cmd = new NpgsqlCommand(cmd_str, conn1);
cmd.ExecuteNonQuery();

//SELECT処理
dt = new DataTable();
cmd_str = "SELECT * FROM test1";
cmd = new NpgsqlCommand(cmd_str, conn1);
da = new NpgsqlDataAdapter(cmd);
da.Fill(dt);

//SELECT結果表示
Console.WriteLine("tes_db1:");
for (int i = 0; i < dt.Rows.Count; i++)
{
Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
}
}

//接続その2(tes_db2へ接続)
using (NpgsqlConnection conn2 = new NpgsqlConnection(conn_str2))
{
//PostgreSQLへ接続後、INSERT処理を実施し、SELECT結果を取得
conn2.Open();

//test対象のテーブルをリセット(全データDELETE)
cmd_str = "DELETE FROM test2";
cmd = new NpgsqlCommand(cmd_str, conn2);
cmd.ExecuteNonQuery();

//INSERT処理
cmd_str = "INSERT INTO test2 VALUES(3, 'ccc'), (4, 'ddd')";
cmd = new NpgsqlCommand(cmd_str, conn2);
cmd.ExecuteNonQuery();

//SELECT処理
dt = new DataTable();
cmd_str = "SELECT * FROM test2";
cmd = new NpgsqlCommand(cmd_str, conn2);
da = new NpgsqlDataAdapter(cmd);
da.Fill(dt);

//SELECT結果表示
Console.WriteLine("tes_db2:");
for (int i = 0; i < dt.Rows.Count; i++)
{
Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
}
}

//トランザクション完了
ts.Complete();
}
}
}
}


実行結果は以下の通りです。


成功時の標準出力

tes_db1:

(col1, col2) = (1, AAA)
(col1, col2) = (2, BBB)
tes_db2:
(col1, col2) = (3, ccc)
(col1, col2) = (4, ddd)

また実行ログは以下の通りです。


分散トランザクション処理の実行ログ

[5368] LOG:  statement: BEGIN

[5368] LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[5368] LOG: execute <unnamed>: DELETE FROM test1
[5368] LOG: execute <unnamed>: INSERT INTO test1 VALUES(1, 'AAA'), (2, 'BBB')
[5368] LOG: execute <unnamed>: SELECT * FROM test1
(中略)
[5369] LOG: statement: BEGIN
[5369] LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[5369] LOG: execute <unnamed>: DELETE FROM test2
[5369] LOG: execute <unnamed>: INSERT INTO test2 VALUES(3, 'ccc'), (4, 'ddd')
[5369] LOG: execute <unnamed>: SELECT * FROM test2
[5368] LOG: statement: PREPARE TRANSACTION '00000000-0000-0000-0000-000000000000/5368'
[5369] LOG: statement: PREPARE TRANSACTION '00000000-0000-0000-0000-000000000000/5369'
[5368] LOG: statement: COMMIT PREPARED '00000000-0000-0000-0000-000000000000/5368'
[5369] LOG: statement: COMMIT PREPARED '00000000-0000-0000-0000-000000000000/5369'
[5368] LOG: statement: DISCARD ALL
[5369] LOG: statement: DISCARD ALL

接続その1(tes_db1)はプロセス[5368]で、接続その2(tes_db2)はプロセス[5369]で処理され、分散トランザクションが実施されています。


同一DBへ複数回接続する場合のTransactionScopeの動き

上記の通りTransacrionScopeは自動的に分散トランザクションを扱うことが可能です。

しかし、1つのトランザクション内で同一のデータベースに対して、複数回接続した場合どうなるでしょうか。

コネクタやNpgsqlのバージョンによっては、分散トランザクションに昇格してしまいますが、最近のNpgsqlではちゃんと同一接続として扱ってくれます。

実際のソースコードは次の通りです。


Class1.cs

using System;

using Npgsql;
using System.Transactions;
using System.Data;

namespace npgsql_test
{
class Class1
{
static void Main(String[] args)
{
//SQL処理で用いる変数を予め宣言
NpgsqlCommand cmd = null;
string cmd_str = null;
DataTable dt = null;
NpgsqlDataAdapter da = null;

//接続文字列
string conn_str = "Server=123.45.67.89;Port=5432;User ID=postgres;Database=postgres;Password=password;Enlist=true";

//TransactionScopeの利用
using (TransactionScope ts = new TransactionScope())
{
//接続その1
using (NpgsqlConnection conn1 = new NpgsqlConnection(conn_str))
{
//PostgreSQLへ接続後、INSERT処理を実施
conn1.Open();

//test対象のテーブルをリセット(全データDELETE)
cmd_str = "DELETE FROM test";
cmd = new NpgsqlCommand(cmd_str, conn1);
cmd.ExecuteNonQuery();

//INSERT処理
cmd_str = "INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')";
cmd = new NpgsqlCommand(cmd_str, conn1);
cmd.ExecuteNonQuery();
}

//接続その2
using (NpgsqlConnection conn2 = new NpgsqlConnection(conn_str))
{
//PostgreSQLへ接続後、DELETE処理を実施し、SELECT結果を取得
conn2.Open();

//DELETE処理
cmd_str = "DELETE FROM test WHERE col1 % 2 = 0";
cmd = new NpgsqlCommand(cmd_str, conn2);
cmd.ExecuteNonQuery();

//SELECT処理
dt = new DataTable();
cmd_str = "SELECT * FROM test";
cmd = new NpgsqlCommand(cmd_str, conn2);
da = new NpgsqlDataAdapter(cmd);
da.Fill(dt);

//SELECT結果表示
for (int i = 0; i < dt.Rows.Count; i++)
{
Console.WriteLine("(col1, col2) = (" + dt.Rows[i][0] + ", " + dt.Rows[i][1] + ")");
}
}

//トランザクション完了
ts.Complete();
}
}
}
}


上記のプログラムは、接続その1,2で同じDBへ接続しています(接続文字列が同一なため)。

Npgsqlは賢いため、ログを見れば分かる通り、ログインしたまま一貫したトランザクション処理をしています。

※古いNpgsql(3.2より古いバージョン?)では勝手に分散トランザクション扱いにし、エラーが発生する場合があります。


同一データベースへの複数回接続のログ

[5112] LOG:  statement: BEGIN

[5112] LOG: statement: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
[5112] LOG: execute <unnamed>: DELETE FROM test
[5112] LOG: execute <unnamed>: INSERT INTO test VALUES(1, 'AAA'), (2, 'BBB'), (3, 'CCC')
[5112] LOG: execute <unnamed>: DELETE FROM test WHERE col1 % 2 = 0
[5112] LOG: execute <unnamed>: SELECT * FROM test
[5112] LOG: statement: COMMIT
[5112] LOG: statement: DISCARD ALL

接続1,2の処理においてプロセス番号が共に[5112]であることから分かる通り、1回の接続でそのまま処理を実行しています。

接続1,2でどちらもconnX.Open();を実行していますが、内部的には接続を使いまわしているようです。


参考にしたサイト


.NET関連


Npgsql関連