カテゴリー別アーカイブ: Programming

プログラミング関連の話題。

PostgreSQLでSQL発行時のエラー対応

PostgreSQLでselect句に定数が含まれていた場合、下記エラーが出力されることがあります。後に続いて型を決めてやれば問題は解消します。::textの部分です。

failed to find conversion function from unknown to text
select *
from (select a_column, 'const'::text as "b_column" from c_table)
  as d_table

メモでした。

PostgreSQLのpg_dumpで単一のfunctionのみ取得する

pg_dumpでは-tオプションで単一または複数のtable/view/sequenceを指定して定義の抽出が可能ですが、functionの指定はできません。これを解決する、裏技チックな方法が英語ページにしか掲載されていなかったので、日本語ページの一人目になるべく本記事を執筆しました。

pg_dump -Fc -s | pg_restore -P 'funcname(args)' > function_define.dmp

-sオプションでスキーマ(定義)のみを抽出します。(ここでいうスキーマとは、テーブルの完全修飾名の前半に使う名称(schema)ではないので注意。単なる定義情報(雑に言うとcreate table)のことを指します。この注意事項は本家にも記されています。ややこしいですね。)

これと–schemaオプションと混乱しないでください。”schema”という単語を異なる意味で使用しています。pg_dump

結果をパイプでpg_restoreに繋いでいます。pg_restoreはcustom形式しか処理できないため、-Fcオプションを付与しています。(デフォルトはplainです。)

-Pは名称を指定してcustomからfunctionの定義を取り出すことができます。また、pg_restoreは特に接続先データベースが指定されていない場合、標準出力にplain形式で吐き出すため、リダイレクトでファイルに出力しています。

データベース名が指定された場合、pg_restoreはそのデータベースに接続し、アーカイブを直接そのデータベースにリストアします。 データベース名が指定されなかった場合は、データベースを再構築するために必要となるSQLコマンドが含まれたスクリプトが作成されます(ファイルもしくは標準出力に書き出されます)。pg_restore

functionに引数(args)がある場合、正しい引数の数だけ、型を指定してやる必要がありますので注意が必要です。不明の場合は下記コマンドで確認できるでしょう。

pg_dump -Fp -s | less

注意事項

私の環境では2回以上-Pを指定した場合、最後に指定したfunction以外が無視されました。そのため、下記のように工夫する必要があります。

pg_dump -Fc -s | pg_restore -P 'funcname(args)' > function_define.dmp
pg_dump -Fc -s | pg_restore -P 'funcname2(args)' >> function_define.dmp
pg_dump -Fc -s | pg_restore -P 'funcname3(args)' >> function_define.dmp

最終的な出力ファイルを見ると、SETが重複して出力されていますが、特に弊害はありません。

参考

同じテーブル構造で違うスキーマにデータをコピーする

違うschemaに存在する、同じ構造のtableに対して、pg_dumpでデータを抽出/挿入する方法を記します。最初にオチを言うと、\COPYを使用したほうが楽です。

環境

PostgreSQL
8.4.20
CentOS
6.7

前提条件

下記の通り、test1.testからtest2.testに対してデータをコピーします。

postgres=# \dn
        List of schemas
        Name        |  Owner   
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 public             | postgres
 test1              | postgres
 test2              | postgres
(7 rows)

postgres=# \d test1.test
    Table "test1.test"
 Column | Type | Modifiers 
--------+------+-----------
 val    | text | 

postgres=# \d test2.test
    Table "test2.test"
 Column | Type | Modifiers 
--------+------+-----------
 val    | text | 

postgres=# select * from test1.test;
    val     
------------
 testvalue1
 testvalue2
 testvalue3
(3 rows)

postgres=# select * from test2.test;
 val 
-----
(0 rows)

手順

対象テーブルに対して、pg_dumpをplainでダンプします。customで色々と試してみましたが、結局徒労に終わりました。

pg_dump --file=./test.dmp --format=p --data-only --table=test1.test

plainのため、中身はviで見ることができます。

-bash-4.1$ ll
total 12
drwx------.  2 postgres postgres 4096 Jun 29 08:59 backups
drwx------. 12 postgres postgres 4096 Oct  8 22:38 data
-rw-r--r--.  1 postgres postgres  463 Oct  8 23:06 test.dmp
-bash-4.1$ cat test.dmp 
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = test1, pg_catalog;

--
-- Data for Name: test; Type: TABLE DATA; Schema: test1; Owner: postgres
--

COPY test (val) FROM stdin;
testvalue1
testvalue2
testvalue3
\.


--
-- PostgreSQL database dump complete
--

スキーマが書かれている箇所を手で直してしまっても構いませんが、コマンドで自動化してしまいます。

sed -i.`date "+%Y%m%d-%H%M%S"` -e 's/test1/test2/g' test.dmp

念のため、diffで確認してみます。

diff test.dmp*
12c12
< SET search_path = test2, pg_catalog;
---
> SET search_path = test1, pg_catalog;
15c15
< -- Data for Name: test; Type: TABLE DATA; Schema: test2; Owner: postgres
---
> -- Data for Name: test; Type: TABLE DATA; Schema: test1; Owner: postgres

後は普通にpsqlで流し込みます。

psql --file=test.dmp

下記の通り確認してみます。成功です。

postgres=# select * from test2.test;
    val     
------------
 testvalue1
 testvalue2
 testvalue3
(3 rows)

最後に

今回はpg_dumpにこだわってみたのですが、\COPYを使用するほうが楽だと思います。対象のテーブルをコマンド実行時に指定できますしね。

「”1年前の今日”に投入されたデータを処理」する仕組みの閏日考慮

閏日を処理する過程で不具合を生み出しかねない実装に思い当たったので、思考整理を兼ねてメモしておきます。

きっかけ

何気なくWikipediaを見ていて下記の記述を見つけ「なるほどなぁ」と思ったことをtwitterに投稿したところ、「システム化するときに頭が痛い」というreplyをいただきました。(仕様と処理が複雑になり、コード等の保守性が下がる。)

2月29日生まれの者の誕生日は閏年に限り到来し、平年に誕生日は存在しない。日本の法律では、誕生日を基準とした行政手続に限り「みなし誕生日」を2月28日としている。

閏年(Wikipedia)−誕生日

その瞬間は「確かに」とだけreplyしようとしたのですが、よく考えてみると、当てはまる事例が他にもあるのではと思いました。

不具合の例

例えば、記事のタイトルにもしている『「”1年前の今日”に投入されたデータを処理」する夜間バッチ』があったとします。この夜間バッチは毎日1:00になるとcronで1度だけキックされます。仕様書通り素直にコーディングすると、下記のSQLが製造されます。

SELECT date_sub(current_date(), INTERVAL 1 year);

更に、バッチの処理対象を取得する処理において、上記SQLは下記のような使われ方をされることでしょう。(createdはデータが投入された日付を表現するとします。)

SELECT * FROM HOGE_DATA WHERE created = date_sub(current_date(), INTERVAL 1 year);

このような実装をした場合、閏日(2/29)に投入されたデータの処理が抜け落ちます

どう整理すべきか

いろいろと解法はあるように思いますが、私はまず仕様がまずいと考えます。上流工程の段階で、顧客とは『「”1年前の今日”以前に投入された、未処理のデータを処理」する夜間バッチ』と合意すべきでした。

仕様整理により、2012-02-29のデータは、2013-03-01のバッチで正常に処理対象とされます。また、元の仕様に立ち返ると、ひとつのデータが2度処理されることは有り得ないため、その要件を満たすように処理未済フラグ等のカラム(タプル)をテーブルに追加して、検索条件に付与します。但し、業務上は処理日時を各データに持たせることが多いため、フラグとして新たに定義する必要はない場面が多いと思います。(processedはデータが処理された日付を表現するとします。バッチの処理対象となったデータは必ず日付でupdateされる仕様です。)

SELECT * FROM HOGE_DATA WHERE created <= date_sub(current_date(), INTERVAL 1 year) and processed IS NULL;

indexを考慮した設計にする必要は別途ありますが。

以上、雑多な思考整理でした。日付関連の仕様を整理するときは、閏日閏年を頭の片隅に置いておいたほうがいいですね……。

インスタンス初期化子の存在意義が理解できました

Javaの機能として用意されているインスタンス初期化子とクラス初期化子について、クラス初期化子は用途がなんとなくわかった(他に代替がない機能)のですが、インスタンス初期化子はコンストラクタで良くないのかなあ、と思っていたのですが、無名クラスの定義の際に必要だということに気づきました。

確かに要るなぁと感心したのでメモしときます。

続きを読む

Linqが怖い

Linqって強力ですね。「こういう機能実装できないかな」って考えたときに、真っ先にSQLのDISTINCTが思い浮かびました。そこで、「LINQ使って楽に実装できないのか?」と考えつき、やってみました。

予想通りに動きすぎて、本当に怖いですよね。最近、マイコンプログラミングばかりやってるもので、書いてるとおりに動かないことなんて日常茶飯事なのです。実際のところは、書いてるとおりにしか動いてないわけですがそこには突っ込まないでくださいね。

では、単純にすげーと思ったソースコードを記しておきます。

続きを読む