Pleasanter(プリザンター) × PostgreSQLの意外な落とし穴について

Pleasanter を利用する理由として、 OSS だからという理由があると思われます。 OSS であるため、ソースコードを無料公開しておりますし、基本的に無料で利用できます。
そんな Pleasanter と相性が良いと思うのは同じ OSS の PostgreSQL です。
私がアサインされているSIのお客様では、 DB に PostgreSQL を利用されております。そして、開発を推進するにあたり、開発環境と本番環境を同期することが多くあります。
そんな環境下で作業をしている際に出会った「Pleasanter(プリザンター) × PostgreSQLの意外な落とし穴について」を記載します。

はじめに

PostgreSQLは前述した通りOSS製品で、BSDライセンスをもとにしたPostgreSQLライセンスとなります。そのため、比較的制約が少ない製品です。
OSSであるため無料で使うことができる製品ではありますが、多機能であることは特筆すべき点だと思います。とくに SEQUENCE(シーケンス) ですが・・・

SEQUENCE(シーケンス)

シーケンスはIDの自動採番機能です。一意制約を設定した列は必ず一意(ユニーク)である必要があります。つまり、重複してはいけないという制約です。
SEQUENCEはこの制約を補助するような機能という認識でよいかと思います。
データを追加するときに重複しないようにデータを登録するのは面倒ですよね・・・もちろん、重複すれば一意制約違反というエラーになります。

[一意制約違反]

そんな時、SEQUENCEを設定すれば、データ追加するときに自動採番機能によって自動で重複しない値を設定してくれます。

[SEQUENCEによる自動採番]

開発環境 & 本番環境

Pleasanterの機能では、エディタの選択肢一覧にて、リンクするサイトIDを設定しているとサイトパッケージのエクスポート/インポートでサイトIDを自動で置換してくれます。

[サイトパッケージのインポート・エクスポートによるサイトID自動置換]

ただし、Pleasanterではスクリプトやサーバスクリプトに記載されているサイトIDは自動置換してくれません・・・

そんな中、開発環境で作成したサイトを本番環境にリリースするということはしばしばあります。そして、移行対象サイトのスクリプトでサイトIDを記載しているときサイトパッケージのエクスポート/インポートでは、スクリプトの修正を実施しなくてはいけません。
ソースコード管理も実施しているため、本番環境へのリリースのたびにスクリプトの修正等を実施するのは面倒です。。。
そこで、サイトIDを同期させるための手順が存在します。

[サイトIDの同期]

上記「サイトパッケージのインポート・エクスポートによるサイトID自動置換」と「サイトIDの同期」を比較していただけるとわかるかと思いますが、移行後の親サイトIDが変わっていません。これによって、スクリプトを修正しなくてもよいことがわかります。
「サイトIDの同期」方法としては、SQL を使い、Pleasanter の Items テーブルと Sites テーブルにIDを確定させたデータを挿入します(もちろん、IDは重複しないデータを挿入します)。

PostgreSQLの落とし穴について

やっと本題に入るわけですが、上記の方法でサイトIDを同期するとサイトやレコードを新規作成する際ある問題が発生します・・・

アプリケーションエラーの発生！！！

開発環境で作業していた当時の私はとても焦りました。心の中で「本番環境リリース前でよかった・・・」と安堵していたことを覚えています。
アプリケーションエラーが発生した原因としては・・・

PostgreSQL の SEQUENCE とDBに存在する ReferenceId が重複したことによる「一意制約違反」が発生したからです。

では、なぜ「一意制約違反」が発生してしまったのでしょうか。
感の良い方はお気づきかもしれませんね。「一意制約違反」が発生した原因としては・・・

DBの ReferenceId と SEQUENCE は連動していないからです。

つまり、手動でプライマリーキーに値を設定した場合、SEQUENCEは自動で同期してくれません。
SEQUENCE について簡単な説明としては・・・

• システムでプライマリーキーを設定した場合(明示的に値を設定しなかった場合)、
  → SEQUENCE はプライマリーキーと連動し、インクリメントされる
• 明示的に値を設定した場合、
  → SEQUENCE はプライマリーキーと連動せず、インクリメントされない

上記の仕組みによって、手動でデータを挿入する(明示的な値の設定をする)ことでSEQUENCEはインクリメントされません。

[SEQUENCEによるIDの設定]

[手動による明示的な値の設定]

上記「手動による明示的な値の設定」の状態で、Pleasanterの画面上からサイトやレコードなどを新規作成した際にアプリケーションエラーが発生します。
なぜなら、SEQUENCE と ID が重複してしまい「一意制約違反」が発生するからです。
そのため、正常にサイトIDの同期を実施するためには、SEQUENCE の値を更新する必要があります。

SEQUENCE を更新するためには・・・？

PostgreSQL には SEQUENCE を操作するための関数が用意されています(詳しくは参考資料をご参照ください)。
その関数のうち、「setval()」を利用すれば SEQUENCE を更新できます。
後述のサンプルをご参考にしてください。

[ReferenceIdに設定されているシーケンスを更新するSQL]

SELECT setval('Items_ReferenceId_seq', {任意の数値});

これにより、ReferenceIdに設定されている SEQUENCE の値を更新できます。

さいごに

いかがでしたでしょうか。私が調査した当時、断片的な情報はありましたが、なかなか体系立てられている情報はありませんでしたため、こちらに記載させていただきました。
今回の記事によって Pleasanter ユーザがさらに増えることと共に、快適な Pleasanter ライフが送ることができるようにさまざまな記事を投稿させていただきますため、今後もよろしくお願いいたします。

参考資料

• PostgreSQL 15.4文書 - 9.17. シーケンス操作関数