\pset pager実行するたびにon/offとなるようだ
SET enable_seqscan TO off;
-- nameがnullのレコードが先に表示される select * from <TABLE> order by name nulls first;
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から設定をはずすこと
-- datnameはDB名、datdbaはpg_user.usesysid select datdba from pg_database where datname = '<DBNAME>'; datname | datdba ---------+-------- kurt | 16656pg_user or pg_shadowで変更したownerのusesysidを調べたうえで
update pg_database set datdba = <NEW SYSID> where datname = '<DBNAME>';とすればよい。\lの結果も当然変更前とは違う結果になる
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)
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
select current_database(); current_database ------------------ kurt (1 row)
=# select current_timestamp - pg_postmaster_start_time() as uptime; uptime ----------------- 00:47:29.219217 (1 row)
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
select pg_stat_get_backend_idset(); pg_stat_get_backend_idset --------------------------- 1 2 3 (3 rows)
-- わかりにくい場合は一時テーブル作ったほうがきっとよい 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)
SELECT pg_cancel_backend(procpid); pg_cancel_backend ------------------- t