holly's wiki

環境変数$PAGERの設定に基づいて、行数が多い時の出力方法が適用されるっぽいが、pagerの適用をはずしたい時。つまり行数が多い時でもlessっぽくならないようにする

\pset pager

実行するたびにon/offとなるようだ

件数が少ないとoptimizerがseq scanで検索したりするのを抑制。作成したindexが正しく使用されているかを確認したい時など

SET enable_seqscan TO off;

いろいろ書き方があるようだ。intervalを例にとると

select current_timestamp - interval '1 days';

select current_timestamp - cast('1 days' as interval);

select current_timestamp - '1 days'::interval;

一般的には非null値でソートされてから、nullの結果がでることになるが、nulls first/lastというものがあるので、nullのソートの扱いを変更することが可能

-- nameがnullのレコードが先に表示される
select * from <TABLE> order by name nulls first;

-- 全ての項目
SHOW ALL
-- 一部表示
SHOW <NAME>

SETでは変更できない項目もあるので注意

SET <NAME> TO <NEWVALUE>

デフォルトに戻す

RESET ALL
RESET <NAME>

よくつかいそうなの

  CREATE OR REPLACE FUNCTION auto_updated() RETURNS TRIGGER AS $$
  BEGIN
    IF TG_OP = 'UPDATE' THEN
      NEW.updated := CURRENT_TIMESTAMP ;
    END IF;
    -- AFTERの場合はNULLを返せばよい（結果を捨てられるため）
    RETURN NEW;
  END;
  $$ LANGUAGE 'plpgsql' VOLATILE;

というfunctionを作成し、あとは仕込みたいテーブルにtriggerを設定すればよい

  // 削除はDROP TRIGGER <trigger> ON <tbl>;
  CREATE TRIGGER accounts_auto_updated BEFORE UPDATE ON accounts FOR EACH ROW EXECUTE PROCEDURE auto_updated();

何かするたびにupdatedが必ず更新されるが、updated field自体を編集してもtriggerが発動するので、そういう場合は一時的にtriggerから設定をはずすこと

削除データを削除データ用テーブルに移したいときとか。まあこういう要件もたまにあり

あんまりやることないだろうが
pg_databaseを調べる

-- datnameはDB名、datdbaはpg_user.usesysid 
select datdba from pg_database where datname = '<DBNAME>';
 datname | datdba
---------+--------
 kurt    |  16656

pg_user or pg_shadowで変更したownerのusesysidを調べたうえで

update pg_database set datdba = <NEW SYSID> where datname = '<DBNAME>';

とすればよい。\lの結果も当然変更前とは違う結果になる

byteで取得可能。oid2nameつかったほうが楽っぽい

SELECT pg_database_size('<DBNAME>');
 pg_database_size
------------------
          6390584
(1 row)

byteで返ってくるのでpg_size_prettyを使うと見やすくなる

SELECT pg_size_pretty(pg_database_size('<DBNAME>'));
 pg_size_pretty
----------------
 6201 kB
(1 row)

もしくはoidから直接ディレクトリサイズをみるでもいい

SELECT datid FROM pg_stat_database WHERE datname = '<DBNAME>';
 datid
-------
 16707
(1 row)

これでみれるのでduとかで取得可能

du -sh $PGDATA/base/16707
7.1M    /usr/local/pgsql/data/base/16707

SELECT pg_relation_size('<TABLENAME>');
------------------
             8192
(1 row)

とするかoidを調べて直接duするのもあり

 SELECT relid FROM pg_stat_all_tables WHERE relname = '<TABLENAME>';
 relid
-------
 16709
(1 row)

でoidを調べて以下を実行する

# 16707はDBのoid
 du -sh /usr/local/pgsql/data/base/16707/16709

http://www.postgresql.jp/document/pg820doc/html/fu...

select current_user;
 current_user
--------------
 postgres
(1 row)

userでもよい

select user;

current_database(). 括弧が必要

select current_database();
 current_database
------------------
 kurt
(1 row)

=# select current_timestamp - pg_postmaster_start_time() as uptime;
     uptime
-----------------
 00:47:29.219217
(1 row)

psqlで接続した時にでるバージョンはpsqlのバージョンなので要注意。version()を使うこと。括弧があるのでこれも注意すること

select version();
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1beta3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50), 64-bit

自分で書くよりもここ見とくほうがよさそう。http://lets.postgresql.jp/documents/technical/quer...

postgres pg_catalog 情報収集とも関連する事項。統計情報アクセス関数 参照

postgresを再起動でもいいが以下でも可能。pg_catalogの統計情報系viewも当然クリアされる

select pg_stat_reset();

接続しているpostgresプロセスのpidを返す

現在接続中のバックエンドIDを返す。この関数の情報を基点にいろいろと情報が取得できるようだ。以下は3接続されているということになる

select pg_stat_get_backend_idset();
 pg_stat_get_backend_idset
---------------------------
                         1
                         2
                         3
(3 rows)

指定したバックエンドプロセスのPID

指定したバックエンドプロセスが実行しているSQL

指定したバックエンドプロセスが実行しているSQLの開始時刻

これらの関数を組み合わせるとこういうことができる

-- わかりにくい場合は一時テーブル作ったほうがきっとよい
select
  procpid,
  start,
  current_timestamp - start as lap,
  current_query
FROM
  (
  select
    backendid,
    pg_stat_get_backend_pid(backendid) as procpid,
    pg_stat_get_backend_activity_start(backendid) as start,
    pg_stat_get_backend_activity(backendid) as current_query
  from
    (select pg_stat_get_backend_idset() as backendid) as S
  ) AS S
where
  procpid != pg_backend_pid() and
  current_query != '<IDLE>';
order by lap desc;
 procpid |             start             |       lap       |                      current_query
---------+-------------------------------+-----------------+---------------------------------------------------------
    1998 | 2011-11-21 01:50:34.154978+09 | 00:00:00.000985 | insert into t1(name) values('test name number 305322');
(1 row)

たとえば↑であまりにも長いことかかっているSQLを発行しているプロセスを指定すると実行中のSQLが停止する

SELECT pg_cancel_backend(procpid);
 pg_cancel_backend
-------------------
 t

pg_ctl reloadと同じ

select pg_reload_conf();