[pgsql-jp: 41820] Re: PostgreSQL暗黙の型変換の仕様について

Tomoaki Sato sato @ sraoss.co.jp
2015年 9月 2日 (水) 00:25:53 JST


佐藤です。

> お世話になっております。
> 小松と申します。
> 
> ■背景
> 当システムにてPostgreSQLのバージョンアップを行うに当たって影響調査を行っています。
> 
> ■環境
> 現行バージョン:PostgreSQL 8.1.3
> 次期バージョン:PostgreSQL 9.3.5
> 
> ■事象
> 次期バージョン環境において、
> 以下SQLをJavaから文字列型を?へバインドさせて実行した所、キャストエラーとなりました。
> 現行バージョンでは同じ条件で問題なく動作しています。
> 
> select count(*) from TABLEA where columna = ?
>  ※columnaはnumeric型
> 
> こちらについて8.3にバージョンアップされた際の型変換の仕様変更によるものと考え、
> 以下対処で明示的にキャストを追加する事でエラーは発生しなくなりました。
> 
> CREATE CAST (numeric AS character) WITH INOUT AS IMPLICIT;
> 
> https://www.postgresql.jp/document/8.3/html/release-8-3.html
>  E.8.2.1. 一般
>  ・文字以外のデータ型がTEXTへの自動キャストが行われないようになりました。
> 
> ■お伺い事項(1)
> 上記対処によりキャストエラーが発生しなくなった事から、
> 上記HPに記載されている型変換の仕様変更によるものと考えます。
> 
> ただし本件、型変換の向きが逆(TEXT→数値)のようにも思えるのですが、
> 条件句で渡している文字列型(SQLの?にあたるもの)がカラムの数値型に変換されているのではなく、
> カラムの数値型(SQLのcolumnaにあたるもの)が文字列型に変換されている認識で合っていますでしょうか。

はい、numeric 型から text 型への変換です。

> (カラムの型に対して暗黙の型変換される事はあるのでしょうか。)

はい、暗黙的な text 型へのキャストを定義すれば変換されます。

> ちなみにキャストの明確なルールが記載されているページをご存知でしょうか。
> 以下の公式マニュアルには詳細な部分は触れられていませんでした。
> https://www.postgresql.jp/document/9.4/html/typeconv.html

キャストの明確なルールというのがどういったものを期待されているかは分か
りませんが、psql の \dC コマンドでキャストの一覧を確認できます。

numeric 型からのキャストはデフォルトでは以下のものが定義されています。

=# \dC
                                      キャスト一覧
         ソースの型          |       ターゲットの型        |        関数        | 暗黙?
-----------------------------+-----------------------------+--------------------+-------
(snip)
 numeric                     | bigint                      | int8               | 代入
 numeric                     | double precision            | float8             | yes
 numeric                     | integer                     | int4               | 代入
 numeric                     | money                       | money              | 代入
 numeric                     | numeric                     | numeric            | yes
 numeric                     | real                        | float4             | yes
 numeric                     | smallint                    | int2               | 代入

numeric 型から text 型への暗黙的なキャストを定義すると、

=# CREATE CAST (numeric AS text) WITH INOUT AS IMPLICIT;
CREATE CAST

一覧に以下のキャストが追加されます。

 numeric                     | text                        | (binary coercible) | yes

> ■お伺い事項(2)
> 上記方法で対処を行った場合、
> 以下NTT オープンソースソフトウェアセンタが公表している情報によると
> インデックスが効かなくなるようなのですが、それ以外の影響は何かありますでしょうか。

いいえ、以下の URL に書いてあること以外には思いつかないです。

> http://lets.postgresql.jp/documents/tutorial/cast/
> (内容抜粋)
>  AS IMPLICIT
>   多くの場合で自動的に変換されます。ただし、条件句で型変換が発生すると、
>   インデックスを使えないなどのトラブルの原因になるので注意が必要です。
> 
> ■お伺い事項(3)
> 上記HPで「キャストが発生することでインデックスが使用されない場合もあり」
> という文言があるのですが、される場合とされない場合の具体的なケースについて
> 何か情報をお持ちでしたら共有頂けないでしょうか。

以下のとおりです。

=# CREATE TABLE t (c numeric);
CREATE TABLE
=# INSERT INTO t SELECT n FROM generate_series(1, 100000) AS n;
INSERT 0 100000
=# CREATE INDEX ON t (c);
CREATE INDEX
=# ANALYZE;
ANALYZE
=# EXPLAIN ANALYZE SELECT * FROM t WHERE c = 1;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_c_idx on t  (cost=0.29..8.31 rows=1 width=6) (actual time=3.111..3.115 rows=1 loops=1)
   Index Cond: (c = 1::numeric)
   Heap Fetches: 1
 Planning time: 0.305 ms
 Execution time: 3.177 ms
(5 行)

=# EXPLAIN ANALYZE SELECT * FROM t WHERE c = '1'::text;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..2193.00 rows=500 width=6) (actual time=0.127..61.895 rows=1 loops=1)
   Filter: ((c)::text = '1'::text)
   Rows Removed by Filter: 99999
 Planning time: 0.149 ms
 Execution time: 62.011 ms
(5 行)

> ■お伺い事項(4)
> お伺い事項(2)において「条件句で型変換が発生すると、インデックスを使えない」とありますが、
> これは現行バージョンにおいても当てはまり、現在も条件句で暗黙の型変換を行っている場合、
> インデックスが適応されていない認識で合っていますでしょうか。

はい、最新バージョンでも列に対するインデックスは互換性のない型へのキャ
ストを含む式の演算結果には使えません。今後のバージョンでも使えるように
はなることはないと思います。

インデックスを使いたいのであれば、暗黙的なキャストを定義せず、リテラル
を適切な型で渡すか、

PreparedStatement st = conn.prepareStatement("SELECT * FROM t WHERE c = ?");
st.setObject(1, new BigDecimal(str));

明示的にキャストするようにしてください。

PreparedStatement st = conn.prepareStatement("SELECT * FROM t WHERE c = ?::numeric");
st.setObject(1, str);

あとは、text 型へのキャストに対してインデックスを定義するというやり型も
なくはないです。

=# CREATE INDEX ON t ((c::text));
CREATE INDEX
=# ANALYZE;
ANALYZE
support=# EXPLAIN ANALYZE SELECT * FROM t WHERE c = '1'::text;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Index Scan using t_c_idx1 on t  (cost=0.30..8.31 rows=1 width=6) (actual time=0.020..0.023 rows=1 loops=1)
   Index Cond: ((c)::text = '1'::text)
 Planning time: 0.320 ms
 Execution time: 0.066 ms
(4 行)


----
Tomoaki Sato <sato @ sraoss.co.jp>
SRA OSS, Inc. Japan


pgsql-jp メーリングリストの案内