Q&A
「試したこと」で起きたエラーと当初発生したエラーは同じ内容でしょうか？違うようであればそのエラー内容を追記願います。
###前提・実現したいこと
タイトルのようにPostgreSQLにてパーティショニングしたテーブルを外部キーにすることを目指していいますが、エラーが出てしまいうまくいきません。
###発生している問題・エラーメッセージ
テーブルcompoundsのcompound_idを外部キーとしてテーブルbit_fingerprintsを紐付けることを目指していますが下に示すようにエラーが出てしまいます。
sql
1ALTER TABLE all_now.bit_fingerprints ADD FOREIGN KEY (compound_id) REFERENCES all_now.compounds (compound_id); 2ERROR: insert or update on table "bit_fingerprints" violates foreign key constraint "bit_fingerprints_compound_id_fkey" 3DETAIL: Key (compound_id)=(35) is not present in table "compounds".
テーブルcompoundsはcompound_idでパーティショニングを行っており実際のデータは子テーブルのcompounds_200000, compounds_400000...に入っています。
###試したこと
StackOverFlowの記事を参考にしたところ親テーブルを外部キーとしたらよさそうに思えたのですが、今回の件ではまた別なのでしょうか？(出力結果は上の画像やコードになります)
子テーブルを外部キーとすることも試していますがその場合はcompounds_200000に含まれないcompound_idのレコードに対して以下のエラーが出てしまいます。
sql
1ALTER TABLE all_now.bit_fingerprints ADD FOREIGN KEY (compound_id) REFERENCES all_now.compounds_200000 (compound_id); 2ERROR: insert or update on table "bit_fingerprints" violates foreign key constraint "bit_fingerprints_compound_id_fkey" 3DETAIL: Key (compound_id)=(200013) is not present in table "compounds_200000".
###バージョン情報
PostgreSQL (version9.5.7)
ご助言いただけたら幸いです。よろしくお願いします。
回答2件
0
DETAIL: Key (compound_id)=(35) is not present in table "compounds".
このメッセージから推測するとテーブルbit_fingerprintsのcompound_idの値35は、テーブルcompoundsのcompound_idに存在していませんと読めますが。
fkeyの整合性のエラーではないでしょうか。
投稿2017/09/20 03:16
総合スコア4070
回答ありがとうございます。少しわかりにくいのですが画像の方でcompound_id=35のデータが各テーブルにあることをお示ししています。
こちらでお答えになっていますでしょうか？
失礼いたしました、原因はcompoundsには値の35が無くパーティションテーブルに値を持っているからだと思います。見るところinsert時の様にAlterでは振替してくれていないみたいです。
但し、パーティションは、値200013がcompounds_200000に有れば行けると思います。
ALTER TABLE bit_fingerprints ADD FOREIGN KEY (compound_id) REFERENCES compounds_200000 (compound_id);
ありがとうございます。
おっしゃるとおりのようで、「試したこと」に記載しているように子テーブルに対してリレーションをはるようにしたのですが、記載しているようにcompound_id=35はエラーを出さないようになっても、compounds_200000にはいらないように設定しているcompound_idを持つものが今度はエラーを出すようになってしまいました。
このような場合はどう対処すればよろしいでしょうか？
bit_fingerprintsに存在する値はどこかのテーブルに無いとエラーになりますし、FKEYの参照先にテーブルに複数指定ができないので子テーブルとするのは難しいと思います。すみません解決策でなくて。
回答ありがとうございます。
いえいえ、難しいということがわかって助かりました。ありがとうございました！
回答にはなりませんが、compound_idのみを持つテーブルを別途用意してこれに対して双方(子）からFKEYするのはできます。compoundsのインサート時にトリガで一緒に自動作成すると便利かも。
おお、面白いアイディアですね！勉強になりました！ありがとうございました。
0
ベストアンサー
パーティションテーブルへの外部キー制約が対応できていないのかもしれませんね。
値によって子テーブルを振り分けないと駄目になりますが、制約で行ってみてはどうでしょうか。
追記
外部リンク（StackOverFlowの記事）で回答されている方はそれなりのスコアですけど、解決はしてないみたいですし、postgresの挙動としてはやってくれてもよさそうな気はしますが、未対応なのかもしれません。
私も案は提示していますが、根本的なところは推測なので、postgresのメーリングリストに投稿された方が良いかもしれません。
メンバーには、ほぼ中の人レベルの方がおられますし、レスポンスも早いので。
購読はこちらから。
投稿2017/09/20 02:26
編集2017/09/20 04:56
総合スコア25195
回答ありがとうございます。
不勉強で申し訳ないのですが、この場合の制約とは
CHECKを使ってbit_fingerpirntsにINSERTするたびにcompoundsに含まれていないなら
エラーを出すようにする、という意味でしょうか？
そうです。
但し、子テーブル全体を振り分け真偽を判定するストアドを作成して組み込む形になるかもしれません。
checkでexists(select ～)のような記述ができるかは試したことがないので。
尚、トリガーも選択肢の一つだとは思いますが。
因みに、要素の重複が発生しないように、パーティション分割出来たんですね。
あ、判定では子テーブルの振り分けはいらないですね。
ありがとうございます。
CHECKやトリガーを採用してみます。
今すでに入っているデータは採用とは別途FKがちゃんと存在するか確認する必要がありますね。
＞因みに、要素の重複が発生しないように、パーティション分割出来たんですね。
以前の私の質問ですね。覚えていてもらえて嬉しいです。
理想は化合物の構造で振り分けることでしたが無理そうでした。
単純にパーティショニングするだけで早くなるよという検証をしている方がいらっしゃったので、
無難にcompound_idで振り分けました。
自身のデータでもそれが当てはまるのか要検証ではありますね。
目的は構造体を含むかどうかの検索性能でしたが、パーティションをcompound_idで振り分けできたとしても、構造体がどのcompound_idかどうかを検索する必要が発生していませんか？
もしそうだとすると、当初の目的から逸れて、パーティションニングすることが目的になっていないでしょうか。
アドバイスありがとうございます。
＞構造体がどのcompound_idかどうかを検索する必要が発生していませんか？
完全に理解してないかもしれませんが、
構造体を含むかどうかの時にcompound_idを用いた検索をしてないので影響はないかなと思いました。
Explain Analyzeでみたら各テーブルの構造情報が保存されたカラムをIndexでサーチしていました。
該当する構造を含むカラムをみつけてそのcompound_idを保存するというタスクがよくされる作業ですが、その場合も律速段階になりますでしょうか？
前後が分からないので律速段階と呼べるかどうかは分かりませんが、
>構造を含むカラムをみつけて
（カラムではなくレコードだと思いますが）この部分の性能を良くしたいということだったのではありませんか？
この部分は、compound_idによるパーティショニングを行っても早くなるとは思えません。
パーティションニングは検索対象の母数を減らすことによって、効果を得るものなので、検索対象となるカラムのデータがパーティション分割されない限りは効果は得られないどころか、逆に遅くなると思います。
前の質問でも同様の事はお伝えしたはずですので、私の方が根本的に問題を取り違えしている気がしています。
>単純にパーティショニングするだけで早くなるよという検証をしている方がいらっしゃったので、
これって、compound_idでパーティショニングしてるんですかね？
効果が出るとしたら、要素を厳密にパーティションしているのではなくて、緩いけど重複しないように振り分けていて、遅い場合もあるけど概ね早くなっている、みたいな事じゃないでしょうか。
若しくは、重複を許すようにして、compound_idの取得だけを目的にしているとか。
ご回答ありがとうございます。
＞>構造を含むカラムをみつけて
＞（カラムではなくレコードだと思いますが）この部分の性能を良くしたいということだったのではあり＞ませんか？
＞この部分は、compound_idによるパーティショニングを行っても早くなるとは思えません。
はい。言葉足らずですいません。残念ながら構造を検索する時のスピードアップはあきらめて
compound_idで検索するタスクの時に少しでも早くなればいいかなと考えました。
ただ、今回のようにFKつけようとしてエラーが出るなら一般的なテーブルでもよさそうですね。
トレードオフとして見合ってない気がします。
>効果が出るとしたら、要素を厳密にパーティションしているのではなくて、緩いけど重複しないように振>り分けていて、遅い場合もあるけど概ね早くなっている、みたいな事じゃないでしょうか。
>若しくは、重複を許すようにして、compound_idの取得だけを目的にしているとか。
おっしゃるとおり、いいアイディアだと思います。
構造でゆるいけど重複ないってできたらいいんですけどね。むずかしいです。
化学構造に詳しい知人も加えてディスカッションすべき案件ですよね。
参考にしたのはこちらですね。
https://h50146.www5.hpe.com/services/ci/opensource/pdfs/PostgreSQL9_3_v1_1.pdf
単項目のプライマリーキーのデータをパーティショニングするのは、全然メリットを感じません。
手元にある、3,400,000件のデータでも、プライマリでの検索なら46msで、
132,300,000件に対し、プライマリの一部での検索でも100ms以下です。
自分なら、検索対象となるレベルの構造を要素の単位として、要素の配列を作成してginインデックスとするアプローチで試してみます。
単一の分子構造を抽出して、それにより要素に分解する処理は作成しないと駄目ですが、分離が難しいところは緩さとしておけば、イケそうな感じですけどね。
回答ありがとうございます。
＞単項目のプライマリーキーのデータをパーティショニングするのは、全然メリットを感じません。
＞手元にある、3,400,000件のデータでも、プライマリでの検索なら46msで、
＞132,300,000件に対し、プライマリの一部での検索でも100ms以下です。
PKはやはり大分高速なのですね！おっしゃるとおりパーティショニングの必要性を感じないですね。
＞自分なら、検索対象となるレベルの構造を要素の単位として、要素の配列を作成してginインデックスとするアプローチで試してみます。
＞単一の分子構造を抽出して、それにより要素に分解する処理は作成しないと駄目ですが、分離が難しいところは緩さとしておけば、イケそうな感じですけどね。
検索対象の最小構造をもつかどうかの情報をbit型で保存し、
それを用いてビット演算子の積を用いて絞り込みを試したことがあります。
今回おっしゃっているのはそれをインデックスとしてしぼりこみ検索に利用するということでしょうか？
その場合は最小単位が増えるごとに計算をしないといけないのが大変そうかなとやめてしまいました。
ビット演算子の積よりインデックスのほうがアイディアとして面白そうですね。
それとも、また別の方法でしたら詳しく説明いただいたら助かります。
>ビット演算子の積を用いて絞り込み
これやられたんだったら、それをファンクションインデックスでやってみたらどうですか。
アドバイスありがとうございます。
ファンクションインデックスで調べてもバージョンが古いPostgreSQLの情報しかなく
おそらく現在は「式に対するインデックス」と呼ばれているインデックスのことでしょうか？
関数インデックス(version 7.2)
https://www.postgresql.jp/document/7.2/user/indexes-functional.html
式に対するインデックス (version 11.7)
https://www.postgresql.jp/document/9.5/html/indexes-expressional.html
こちらを用いてビット演算子の積を用いたインデックスを作ればよいということでしょうか？
そうです。
但し、式との比較が単純でf(x)=yとなるような関係でなければ、そもそも式インデックスは成り立たちません。
回答ありがとうございます。試してみますね。
質問からそれてしまったのに詳しくアドバイスありがとうございました！
追記もありがとうございました！
あなたの回答
tips
プレビュー
