特集：Npgsql入門
.NETライブラリ「Npgsql」によるPostgreSQLの活用
オープンソースのRDBMSであるPostgreSQLの特徴を紹介。さらに、それにC#から接続して利用する方法として「Npgsql」というオープンソース・ライブラリの基本的な使い方を紹介する。
PostgreSQLはオープンソースのリレーショナル・データベース管理システム(以降、RDBMS)であり、1997年1月に初めてその名が付けられてから、MySQLと共にオープンソースRDBMSの中心を担ってきた歴史のあるソフトウェアである。
PostgreSQL自体の開発は、PostgreSQL Global Development GroupというPostgreSQLの開発者からなるコミュニティによって活発に行われている。日本国内でも日本PostgreSQLユーザ会を中心としてSRA OSS,Inc.、NECソフト、NTTなどで普及活動や活用を行っており、商用製品にも劣らない高い信頼性を持ったRDBMSである。
本稿では、PostgreSQLの紹介とC#（やVB： Visual Basic .NET。本稿ではVBに関しては省略）から利用する方法について紹介する。
PostgreSQLの特徴と利点
PostgreSQLの大きな特徴として、
- MultiVersion Concurrency Control（後述。以降、MVCC）による可用性向上のため、追記型アーキテクチャを採用
- 関数記述のための独自言語「PL/pgSQL」を持つ
- テーブル間に継承関係を定義できる（オブジェクト指向における継承関係と同じ）
- バージョン8.3以降、組み込みの全文検索エンジンを持つ
- 1億超のレコードであっても対応できる性能と信頼性
- pgpool-IIやSlony-I、PL/Proxyなど、充実した周辺プロダクト
などがある。それぞれについてもう少し詳しく説明していこう。
MVCC
MVCCは特に大きな特徴で、Web上でPostgreSQLの特徴として見かけたことがある方も多いだろう＊1。MVCCの詳細については専門書などを参照してほしいが、簡単に説明すると、例えばあるユーザーが書き込み中のデータに対して別のユーザーから読み込み要求があった場合、書き込みを開始する前のスナップショットを返すことで読み込み処理へのロックを回避する、という仕組みだ。
MVCCと対をなすロック方式では、書き込みが終了するまで読み込み要求をブロックしてしまう。PostgreSQLでは、この仕組みによって高い同時並行処理性能を実現している。
- ＊1なお、SQL ServerやMySQLでも設定次第で利用可能なので、PostgreSQLだけの特徴ではないことに注意。そもそもMVCCの概念はデータベース管理システムの制御技術であって、特定のプロダクトには依存するものではない。
高い性能と信頼性
筆者が勤める会社の事例になるが、これまでOracleを利用していたあるアプリケーションを、諸事情あってPostgreSQLに移行したのだが、今のところRDBMSの変更に起因する問題は起っていない。
また、移行当時はそこまででもなかったデータ量もサービスを継続するうちに増え続け、今や1億超のレコードを持つテーブルも出始めている。しかし、適切なデータベース／SQLチューニングを行っていれば、JOIN処理を行ってもなお、性能に大きな問題なく運用できている。もちろん限度はあるし、チューニングを切り詰めていけば性能が上がるのはほかのRDBMSでも同様ではあるが、1つの参考にはなるだろう。データ規模に起因する問題も一度も発生していない。
このように、高性能、高信頼性もPostgreSQLの特徴の1つである。
充実した周辺プロダクト
実はPostgreSQLでは、Version 9.0まで組み込みのレプリケーション機能をサポートしていなかった＊2。そのため、pgpool-IIのような非常に機能の充実したサードパーティ製品が存在する。pgpool-IIはもともとコネクション・プーリングを行うツールとしてスタートしたが、今ではレプリケーション、自動フェイルオーバー、パラレル・クエリなどによる負荷分散などの機能を持つ。単体でそれらの機能を持つRDBMSより運用が複雑になりがちであるともいえるが、おのおのの製品独自の機能などもあるため、結果として選択肢が多くなり、厳しい要件にも耐え得るともいえる。
- ＊2ウォーム・スタンバイはサポートされていたが、その機能では参照が行えないため、冗長化はできても負荷分散にはならない。バージョン9.0からは組み込みでストリーミング・レプリケーションがサポートされ、待機系の参照が可能になった。また、無停止で待機系の追加／削除も行えたり、9.1では同期レプリケーションもサポートされたりするなど、短期間で機能として非常に洗練されてきている。
■
PostgreSQLは、Windows／Linux／Mac OS Xとそれぞれのプラットフォーム向けにバイナリが提供されており、特にLinuxやMac OS Xではパッケージ・マネージャーを使って簡単に環境を構築できる。pgAdminIIIなど、GUI管理ツールも同様に主要プラットフォーム向けに存在するため、興味を持たれた方は是非一度試してみてほしい。
簡単にではあるが、ここまでPostgreSQLの特徴について説明した。以降は、C#でPostgreSQLに接続、利用していく方法について説明していく。
.NET環境向けData Provider「Npgsql」
C#／VBからは、ODBCやOLE DB経由でPostgreSQLに接続することもできるが、本稿では代表的なオープンソース・ライブラリである「Npgsql」を紹介する。
Npgsqlは、古くは2005年ごろから存在し、.NET Frameworkの大きな躍進となったVersion 2.0のころからすでに利用することのできた、歴史あるライブラリである。また、Mono向けのバイナリが用意されていたり、インターフェイスがSQL Server用データ・プロバイダー・ライクに設計されたりするなど、C#を頻繁に利用する開発者にとってはプラットフォームを問わない、非常になじみやすいライブラリになっている。Npgsql自身もC#で作成されていることから、一度ソース・コードを読んでみるのもよいだろう。
バイナリ（例：「Npgsql1.0.1-bin-ms2.0.zip」）やソース・コードはpgFoundryのNpgsqlグループから取得できる。あるいは、Visual StudioやMonoDevelopを利用している場合は、NuGetからバイナリを取得するのが簡単だろう。
Npgsqlを使ってPostgreSQLを利用する
ここからは、Npgsqlを使って実際にどのように接続するかを見ていく。
テーブルの参照
Npgsqlを使ってSELECT文を発行するコードは以下のようになる。
|
using Npgsql;
using System.Data;
……省略……
var connString = @"Server=localhost;Port=5432;User Id=postgres;Password=password#;Database=postgres";
// DataReaderを利用したSELECT
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
var command = new NpgsqlCommand(@"select * from table_name", conn);
var dataReader = command.ExecuteReader();
while (dataReader.Read())
{
Console.WriteLine("value : {0},", dataReader["column_name"]);
}
}
// DataAdapterを利用したSELECT
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
var dataAdapter = new NpgsqlDataAdapter(@"select * from table_name", conn);
var dataSet = new DataSet();
dataAdapter.Fill(dataSet);
Console.WriteLine(dataSet.Tables[0].Rows[0]["column_name"]);
}
|
トランザクションを利用したデータの更新
トランザクションを使ってデータの更新を行う場合のコードは以下のとおりだ。また、更新対象を特定する条件にパラメーターも利用している。
|
var connString = @"Server=localhost;Port=5432;User Id=postgres;Password=password;Database=postgres";
// Commandを利用したUPDATE
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
var command = new NpgsqlCommand(@"update table set column = 'new value' where id = :p", conn);
command.Parameters.Add(new NpgsqlParameter("p", DbType.Int32) { Value = 123 });
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
// DataAdapterを利用したUPDATE
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
using (var transaction = conn.BeginTransaction())
{
var dataAdapter = new NpgsqlDataAdapter(@"select * from table", conn);
dataAdapter.UpdateCommand = new NpgsqlCommand(@"update table set column = :p_value where id = :p", conn, transaction);
dataAdapter.UpdateCommand.Parameters.Add(new NpgsqlParameter("p", DbType.Int32) { Value = 123 });
dataAdapter.UpdateCommand.Parameters.Add(new NpgsqlParameter("p_value", DbType.String, 10, "column")); ……1
var dataSet = new DataSet();
dataAdapter.Fill(dataSet);
var rows = dataSet.Tables[0].Rows;
rows[0].BeginEdit();
rows[0]["column"] = "new value";
rows[0].EndEdit();
try
{
dataAdapter.Update(dataSet);
transaction.Commit();
}
catch (NpgsqlException)
{
transaction.Rollback();
throw;
}
}
}
|
- 1NpgsqlParameterクラスのコンストラクタに渡している第4引数は、後にDataRowの列名として使いたい名前を指定する。
コネクション・プーリング
ある程度以上のプロジェクトであったり、小規模でも多くのユーザーがいるようなプロダクトであれば、パフォーマンスの向上やリソースの節約を考えて、コネクション・プーリングを行いたいと思うだろう。Npgsqlでは、既定の設定であれば、自動的に内部でPostgreSQLとのコネクションを全てプールしているため、利用側は何も意識する必要はない。
最小／最大プール数も指定可能だ。例えば以下のコードは、最小プール数が「10」、最大プール数が「100」、プール内の使用されていない接続が閉じられるまでの時間を「1分」に設定したときの接続文字列になる。
|
var connString = @"Server=localhost;Port=5432;User Id=postgres;Password=password;Database=postgres;MinPoolSize=10;MaxPoolSize=100;ConnectionLifeTime=60";
|
Npgsqlを利用するうえでの注意点
ここまで見てきたとおり、Npgsqlを利用することでC#からでも非常に簡単にPostgreSQLへの接続を行えるが、わずかながら注意を要する点もある。
まず、NpgsqlはPostgreSQLの全てのデータ型をサポートしているわけではない。
また、Npgsqlは非常に歴史があり、C#からPostgreSQLを利用する際の定番ライブラリではあるものの、今でも枯れきっているというわけではない。筆者が実際に遭遇した例としては、Npgsqlでは接続文字列に「timeout」を指定することで接続のタイムアウト時間を調整できるが、Npgsqlのライフサイクルを通して初回の接続のみ、既定値でタイムアウトしてしまうバグがあった。ほかにも、Npgsqlのプロジェクト・サイトにあるバグ・トラッカーで確認できるとおり、まだOpen状態のチケットが数多く残っているので、Npgsqlを利用していて何かおかしな挙動があれば、まずここを確認するようにしよう。
Npgsqlに限った話ではなく、オープンソース・ソフトウェアを利用するうえでの一般論ではあるが、場合によっては自分でソース・コードを確認・デバッグし、修正する必要も出てくるということには注意されたい。前述の、筆者が遭遇したバグも、自らソース・コードを確認して判明したものだ。
もう1つのData Provider「dotConnect」
ここまでNpgsqlを紹介してきたが、選択肢は必ずしもそれだけではない。こちらは有償になってしまうが、Devart社の「dotConnect for PostgreSQL」というライブラリでも、同様にPostgreSQLへの接続を行える。
こちらは、有償＊3だけあって単純な接続のほかにも、LINQ to SQLやEntity Frameworkのサポートなど多くの機能を利用可能だ。許されるのであればこちらも選択肢の1つとして考慮し、プロジェクトにとって最適な手段を選択してほしい。
- ＊3無償で利用可能なExpress Editionもあるが、そちらは大幅に機能制限が行われている。詳しくは公式サイトのEdition比較ページを確認してもらいたい。
最後に
C#を使った開発であればRDBMSはSQL Serverを使うべき、という声はよく聞く。確かに、言語との親和性、各種ライブラリの充実度を考えれば、それが第一選択肢に挙がることは十分理解できる。だが、本来、RDBMSは開発生産性だけで選択するものでもない。ここまで見てきたように、PostgreSQLを選択したとしても大きな問題なく利用は可能であるし、本稿では紹介しなかったがMySQLにも「Connector/Net」という著名なデータ・プロバイダーが存在する。
「C#だからSQL Server」ではなく、それぞれのRDBMSの特性を理解したうえで最適な選択を行うべきである。本稿がその一助になれば幸いである。
