Top / PostgreSQL

PostgreSQL

目次 †

PostgreSQL †

• バイナリファイルを扱う場合はLOB（Large Object）型を使う。
  • LOBはCLOB（Character Large Object）とBLOB（Binary Large Object）の2つに大別される。前者は大きな文字テキスト用、後者は画像データなど用である。

テーブルやシーケンスを列挙する †

テーブルとシーケンスの列挙 †

\d ;

• 空白を入れる必要がある。

テーブルだけの列挙 †

psql †

$ psql -l

対話的 †

\dt;

シーケンスだけの列挙 †

\ds;

指定したテーブルの詳細を調べる †

\d [テーブル名];

外部参照一覧を取得する †

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE TABLE_SCHEMA = 'public' 
ORDER BY CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, ORDINAL_POSITION;

データ型の比較 †

　以上のようにSQLとPostgreSQLの差異は文字列のときである。しかも、PostgreSQLのTEXT型は文字列の上限を指定しなくてもよいので便利である。

PostgreSQLにおけるテーブルパーティショニングの手順 †

1. 親となるテーブルを作成する。
2. 子となるテーブルを作成する。
3. 子となるテーブルに制約を作成する。
4. 子テーブルにインデックスを作成する。
5. ルールもしくはトリガを設定する。
6. postgresql.confのconstraint_exclusionをONにする。

currval()関数 †

　currval()関数はPostgreSQLで直前に使ったシーケンス値を取得する関数である。

例：executeQuery()を使ってResultSetを取得することにより、シーケンス値を取得する。

　2つのテーブルに同時にデータを追加するとし、片方のテーブルに自動的にシーケンス番号を設定する列があり、もう片方のテーブルにシーケンス番号を参照する列があったとする。
　このときは1つずつテーブルに値を登録し、「rs.getInt(○);」でシーケンス番号を入手し、それを次のテーブルに値を登録する作業のときに利用する。
　なお、2つ目のテーブルに値を登録するときにエラーが起きたら、ロールバックする。

バックアップ †

• psqlからcopyコマンドを使用する方法と pg_dumpコマンドを使用する方法がある。
  • すべての構造（データベースの構造、 インデックスファイル、シーケンス番号の管理など）を含めてバックアップするにはpg_dumpコマンドを使用する。

$ pg_dump  データベース名 > 保存ファイル名
$ pg_dump  データベース名 | gzip > 圧縮ファイル名.gz

[補講]すべてのデータベースを対象する場合は、pg_dumpallを用いる。　◇

　pg_dumpで得た保存ファイルをリストアするには次のようにする。

$ createdb  データベース名
$ psql -e  データベース名  < 保存ファイル名

　圧縮したファイルを戻すには、

$ createdb  データベース名
$ gunzip -c 圧縮ファイル名.gz | psql -e データベース名

DB内のテーブルを列挙する †

SELECT schemaname, tablename FROM PG_TABLES WHERE NOT tablename LIKE 'pg%' AND NOT SCHEMANAME = 'information_schema' ORDER BY schemaname, tablename;

DBの所有者の変更 †

1：template1というDBにアクセスする。

$ psql template1

2：変更後の所有者のusesysidを取得する。ここで指定するユーザー名はcreateuserなどで作成したPostgreSQL用のユーザーを指す。

template1=# select usesysid from pg_user where usename='ユーザー名';
 usesysid 
----------
    16387
(1 row)

3：pg_databaseのdatdbaを更新する。

template1=# update pg_database set datdba = 16387 where datname='DB名';

4：確認。

$ psql -l

1つのSQLで調べる方法 †

SELECT 
    SCHEMANAME, 
    TABLENAME
FROM 
    PG_TABLES
WHERE 
    NOT TABLENAME LIKE 'pg%' 
    AND NOT SCHEMANAME = 'information_schema' 
ORDER BY 
    SCHEMANAME, 
    TABLENAME

実行したSQLクエリをログに記録する †

　postgresql.confファイル内のlog_statementのコメントアウトを解除して、値にはnone,mod,ddl,allのいずれかを設定する。

一定時間以上かかるクエリーをタイムアウトさせる †

設定ファイルで変更する †

# vi /usr/local/pgsql/data/postgresql.conf

　タイムアウトを30秒に設定する場合は、「CLIENT CONNECTION DEFAULTS」セクションのstatement_timeoutディレクティブを30000とする。

　設定ファイルを編集後、PostgreSQLを再起動する。

# /etc/rc.d/init.d/postgresql restart

動的にタイムアウトを設定する †

SET statement_timeout TO 1000; -- for a second
実行したいクエリ
RESET statement_timeout; -- reset

　上記のようににタイムアウト時間（ミリ秒単位）を指定する。上記の例は1000ミリ秒（＝1秒）としている。

　ただし、vacuumや、backup もタイムアウト対象になってしまうので注意すること。

シーケンス †

シーケンスを一覧表示する †

方法1 †

\ds;

方法2 †

select relid, schemaname, relname from pg_statio_all_sequences;

方法3 †

SELECT n.nspname AS "Schema", c.relname AS "Name", c.relkind AS "Kind", u.usename AS "Owner"
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_user u ON u.usesysid = c.relowner
WHERE c.relkind = 'S' ;

シーケンスの初期化 †

SELECT SETVAL('[シーケンス名]', 1, false);

　これはis_calledをfalseで、シーケンス番号を1に設定している。つまり、次のシーケンス番号が1になる。

指定したシーケンスを使用している個所（テーブルとその列）を調べる †

• シーケンスを外部参照している場合に、その外部参照している元を知りたい。

あるテーブルに使用したシーケンス番号を、別のテーブルにも使用したい場合は、pg_dumpでDBのダンプを取得して、その結果でgrepでシーケンス名を指定すればよい。 †

　あるテーブルAにINSERTした直後に、次に示すSQLを実行することで、テーブルAにINSERTしたときに使用したシーケンス番号を取得できる。

SELECT currval('[シーケンス名]);

　currval()は同一セッション中で、直前に設定されたシーケンス値を取得する関数である。同一セッション中であるため、排他制御を考える必要がないので便利である。

　次に示すSQLの場合、セッションに関係なく現在のシーケンス値が返る。そのため、同期処理によってはタイミングによっておかしな値が取得されてしまうため、排他制御を考える必要がある。

SELECT last_value FROM [シーケンス名];

参考文献 †

• 『データベースエキスパートへの道』