PostgreSQLの最近のブログ記事

SELECT
  relname,
  relkind,
  to_char(reltuples, '999,999,999') as rows,
  to_char(pg_relation_size(relname), '999,999,999,999') as bytes
FROM pg_class
WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public')
order by bytes desc;

参考

PostgreSQLでテーブルサイズを確認する by y-kawaz

概要

PostgreSQL 上に作成したテーブルに対し、Java で時刻を timestamp 型ではなくミリ秒(long 値)のまま格納した場合、それを select で timestamp 型として取り出すには以下のように SQL を記述する。

SELECT
    (lastaccess / 1000) :: text :: interval + '1970-01-01' :: timestamp
FROM
    table1;

lastaccess は bigint 型のカラム。これを1000分の1して秒単位にし、 epoch 秒（グリニッジ標準時間1970年1月1日00:00:00からの経過秒数）　に足している。

例: DB に格納された tomcat のセッションでサイズの大きいものを見つける

次のような SQL を実行する。

SELECT
	length(data) as len,
	session_id,
	(lastaccess / 1000) :: text :: interval + '1970-01-01' :: timestamp
FROM
	tomcat_sessions
ORDER BY len desc
LIMIT 20;

概要

PostgreSQLの各プロセスがどのテーブルでどの種類のロックを行っているか調べる方法について説明。

さらに、そのプロセスを下記手順で終了することで、ロックしていた処理を強制的に中断できる。

手順

プロセスIDの確認

コマンドラインなどで、次のSQLを実行する。

SELECT l.pid, db.datname, c.relname, l.locktype, l.mode
FROM pg_locks l
        LEFT JOIN pg_class c ON l.relation=c.relfilenode
        LEFT JOIN pg_database db ON l.database = db.oid
ORDER BY l.pid;

pid がプロセスIDを意味する。

プロセスの停止

プロセスIDが分かったら、次のSQLでプロセスを停止できる。

SELECT pg_cancel_backend(プロセスID);

背景 - ロック手法について

データベース更新時のロックの方法として、次の２種類が存在する。

• 悲観的ロック (Pessimistic lock)
• 楽観的ロック (Optimistic lock)

悲観的ロックは、更新対象を更新完了までロックし、他の人が更新を行ったり、場合によっては参照も禁止する方法。
データをより安全に更新・参照できるメリットがあるが、ロック待ちによるアプリケーションの性能低下要因となりやすい。

楽観的ロックは「自分が操作している情報は他人が同時に更新する可能性が低い」更新を行うのに向いたロック方法。
更新対象を本当に更新する段階までぎりぎりまでロックしない。

例えば、「一旦データを取得したあと、内容を変更して実際に変更しようとしたら、データ取得後に誰かが同じレコードを更新していた」というケースの場合、悲観的ロックであれば「データ取得時に行ロックを行い、誰も更新できない状況」を作ってから更新を行う。その間、他の更新処理は自分の処理が完了するまで待ち状態となる。
一方、楽観的ロックであれば、自分がデータ取得後に他人がデータを更新できてしまう。自分がその後に更新しようとすると JPA 利用時、 OptimisticLockException が発生する。

概要

JPA(Java Persistence API) 1.0では楽観的ロックのみサポートしている。2.0 からは悲観的ロックもサポートするらしい。

Seasar2 + Hibernate + JPA 1.0 の構成において悲観的ロックでデータを更新したいと思い試行錯誤したが、意外とつまづいた。
EntityManager に lock() というメソッドがあって、write lock もできそうだが、うまくいかない。seasar がサポートしていないのか、設定がうまくいっていないのか...

結局、以下の方法で実現できた。

悲観的ロックによる更新方法

次のようなコードになる。

package jp.trasis.sample.service;

import javax.persistence.EntityManager;

import jp.trasis.sample.entity.User;

import org.seasar.extension.tx.annotation.RequiresNewTx;
import org.seasar.framework.container.annotation.tiger.Binding;

public class TestService {
	@Binding
	private EntityManager entityManager;
	
	@RequiresNewTx
	public void test(Long userId, String name) {
		User user = (User) entityManager.createNativeQuery(
				"select * from User_ where id=:userId for update", User.class)
			.setParameter("userId", userId)
			.getSingleResult();
		
		user.setName(name);

		entityManager.persist(user);
	}
}

解説

@RequiresNewTx アノテーションにより、新たなトランザクションの中で更新処理を行う。

まず、更新対象のエンティティを select ... for update により、行ロックを行いながら取得する。
この SQL は createNativeQuery() メソッドで実行する必要がある。crateQuery() で使用する HQL では、 for update 文は利用できない。

そしてエンティティに対して修正を行い、 persist() でコミットを行う。メソッドを抜けるときに実際に DB へのコミット処理が行われ、トランザクションが終了し、ロックも開放される。

概要

PostgreSQL上で実行中のSQLを停止する方法について説明する。

Javaサーバ上で時間のかかるSQLを実行してしまい、Javaサーバを停止せずにそのスレッドだけを停止したい場合などにおいて、次の手順で実行された SQL を停止できる。

手順

プロセスID(pid)の確認

まず、問題のSQLが実行されるプロセスのIDを調べる。
コマンドラインなどで、次のSQLを実行。

SELECT
    procpid,
    start,
    now() - start AS lap,
    current_query
FROM
    (SELECT
        backendid,
        pg_stat_get_backend_pid(S.backendid) AS procpid,
        pg_stat_get_backend_activity_start(S.backendid) AS start,
        pg_stat_get_backend_activity(S.backendid) AS current_query
    FROM
        (SELECT pg_stat_get_backend_idset() AS backendid) AS S
    ) AS S
WHERE
    current_query <> ''
ORDER BY
    lap DESC;

それぞれの項目は次の意味を表す。

procpid	プロセスID
start	プロセス実行開始時間
lap	経過時間
current_query	実行中のSQL

プロセスの停止

プロセスIDが分かったら、次のSQLでプロセスを停止できる。

SELECT pg_cancel_backend(プロセスID);

参考URL

PostgreSQLで実行中のSQLの情報を取得する @ ELFの業務日誌