レプリケーション環境下のスレープサーバへの参照問い合わせ、主に重いクエリ発行時に発生する原因は以下のクエリで確認できる database => select * from pg_stat_database_conflicts ; datid | datname | confl_tablespace | confl_lock | confl_snapshot …

無期限を表すdate infinityが便利 INSERT INTO events(date) VALUES('infinity'); INSERT INTO events(date) VALUES('-infinity'); select * from event where date > now(); date ---------- infinity (1 行)select * from event where date < now(); date …

plpgsql内のBEGIN〜END間で暗黙的トランザクションが張られる。 insertが失敗した場合、deleteの処理がロールバックされるので、 原子性保証はしたいが、書き込みキュー等へ処理を委譲したい場合の解決の１案として。 CREATE OR REPLACE FUNCTION table_dele…

マスターとスレーブでシーケンスのlast_valueがずれる場合があるので注意master last_value | bigint | 50 start_value | bigint | 1 increment_by | bigint | 1 max_value | bigint | 9223372036854775807 min_value | bigint | 1 cache_value | bigint | 1…

頭の\がミソ。指定しないとスーパーユーザじゃないとダメと叱られる。 SQL=> \copy (select * from tablename where 1 = 1 limit 100 ) to '/path/to/dumpfile';

普段は $ psql database < filename.sqlとすることで事足りるんだけど、filename.sqlの結果をみてcommitする必要があった。 $ psql database database=> begin; database=>\i filename.sql ... database=> commit;のように\iでファイル入力を読み込める。

この記事はPostgreSQL Advent Calendar 2014 25日分となります。昨日は @_Naofumi_ さんの PostgreSQLにおけるPAXページレイアウトの実装 でした。さてさて私はというと、今年、運用中postgresqlを8.1から9.3へ移行しました。 移行した際に実施したことや変…

vacuumによって凍結したxidは SELECT datname, datfrozenxid FROM pg_database; によって取得可能。 周回対象は SELECT datname, age(datfrozenxid) FROM pg_database; にて確認できる。10億以前をvacuumによって凍結するので、age(datfrozenxid) の値が10億…

パフォーマンス対策の為、敢えて非正規化を行い配列型で持つ配列型カラムの追加 alter table hoge add foo_ids intger[]; データ移行 array型でhogeテーブルに紐づくrecordを配列化する update hoge set foo_ids = array( select foo_id from foo f where f.…

転ばぬ先のなんとやら。先ずは、tablespaceの保存先ディレクトリを作成 $ mkdir -p /var/lib/pgsql/9.1/tablespace/hogepostgresのスーパーユーザで # create TABLESPACE hoge_ts LOCATION '/var/lib/pgsql/9.1/tablespace/hoge'; CREATE TABLESPACE # \db+ …

動作確認した時の挙動のメモ create tableの反映を確認 master template1=# create table test(id serial primary key, comment text); NOTICE: CREATE TABLEはシリアル列"test.id"用に暗黙的なシーケンス"test_id_seq"を作成します。 NOTICE: CREATE TABLE …

レプリケーションポリシー WALアーカイブ:利用する 同期方式:local(スレーブ非同期) 参考にしたページ http://lets.postgresql.jp/documents/technical/replication/1/ http://codezine.jp/article/detail/7109 マスタ側での作業 レプリケーション用ユーザの…

初めて使ったのでメモ。 ALTER TABLE old_tablename rename to new_tablename;

たまに使って、すぐ忘れるので。 insert into new_tbl ( id, defined ) select new_id, 'defined value' from old_tbl where old_id = 10 データ移行時に利用すると大変便利。

今あるテーブルオブジェクトからORM興してみたかったので。 とっかかり。 https://gist.github.com/letitride/5070978 SELECT a.attname, ty.typname, a.atttypid, a.atthasdef, a.attnotnull, a.attisdropped, d.description from pg_stat_user_tables t jo…

svn のブランチ一覧取得 $ svn ls http://mysvnrepository/branchesデータ移行を考え中 複数行のselect結果をinsert insert into dest_table( org_id, dest_const_id ) select org_id, 1 from org_table where const_id=10; のような感じで。カラムのリネー…

こんなログが吐かれますよと ERROR: deadlock detected DETAIL: Process 27840 waits for ShareLock on transaction 232876765; blocked by process 27808. Process 27808 waits for ShareLock on transaction 232876479; blocked by process 27840. postgre…

range typeと相性が良さそうなので、作ってみた。 create or replace function generate_series (int4range, int4) returns setof int4 as $$ select generate_series( lower($1), upper($1), $2 ); $$ language sql; select * from generate_series( int4ra…

PostgreSQL Deep Dive / pg_filedumpでテーブル／インデックスファイルを覗いてみる を参考に。rpmでインストールしたpostgresqlのバージョンは9.1.6。 先ずはソースを取得postgresql $ wget http://ftp.postgresql.org/pub/source/v9.1.6/postgresql-9.1.6.…

slonyのレプリケーションが異常停止したようで、ひたすらログ調査。 2012-12-16 00:01:59 JST DEBUG2 remoteHelperThread_1_1: 63.716 seconds delay for first row 2012-12-16 00:01:59 JST DEBUG2 remoteHelperThread_1_1: 63.717 seconds until close cur…

このエントリはPostgreSQL Advent Calendarの12/17担当分のエントリとなります。postgreSQLにはテーブルの継承と配列型という、他RDBMSではあまり馴染みのない機能があります。 少し思いついたのでこの2つを利用してComposite パターンを試してみました。 (…

昨日に引き続きgenerate_seriesの内容が知りたかったので。 generate_seriesのオーバーロード関数一覧を取得 # select proname, prorettype, proargtypes, prosrc from pg_proc where proname = 'generate_series'; proname | prorettype | proargtypes | pr…

連続生成関数 generate_series を使用すると楽。 SELECT current_date + generate_series FROM generate_series(0,365) ---------- 2012-12-11 2012-12-12 2012-12-13 2012-12-14 2012-12-15 2012-12-16 2012-12-17 2012-12-18 2012-12-19 2012-12-20 2012-1…

こんなこと出来るなんて知りませんでした。 create or replace function virtual_table() RETURNS setof text AS $$ begin return next 'abc'; return next 'def'; end; $$ language plpgsql; RETURNS setof [type] と return next を忘れず追加。 from句で…

表題の通り。serial型で使われているシーケンスだとテーブルのオーナーも変更する必要がある。 example=# ALTER SEQUENCE example_table_id_seq OWNER TO username; ERROR: シーケンス"example_table_id_seq"の所有者を変更できません DETAIL: シーケンス"ex…

$ sudo brew install postgres $ initdb /usr/local/var/postgres $ postgres -D /usr/local/var/postgres & $ createdb mydb $ psql mydb mydb# ALTER USER myuser with password '****';自動起動の設定 $ cp /usr/local/Cellar/postgresql/9.1.3/homebrew.…

たまに使うので。 pg_dump -c -t tablename > dumpfile

slony-iのレプリケーション環境で以下のエラーが出た postgres7 error: [-1: ERROR: permission denied for sequence sl_action_seq] in EXECUTE("select lastval() AS lv;")該当部のコードを見てみると、 //レコード登録 pkeyはserial型 $db->query( insert…

SQLログの出力 log_statement = 'none' # none, ddl, mod, all none : 出力しない(デフォルト) ddl : DDLを出力 mod : DDLとDML all : 全てのSQL を設定する。設定後、log_directory, log_filenameの設定のファイルに実行SQLが吐かれる。スロークエリの出力 …

やってみたのでメモ。 pg_send_query または、pg_send_query_paramsを使う で、postgreSQL側でクエリの実行中の時は、次の問い合わせはできないとのこと pg_send_queryは $db = pg_connect("db_name=db1"); pg_send_query($db , "select pg_sleep(3); select…