PostgreSQL Part.11©2ch.net
レス数が1000を超えています。これ以上書き込みはできません。
外部キー制約を一時的に変更するにはどうすればいいでしょうか？
通常は、ON UPDATE RESTRICT で、一時的に ON UPDATE CASCADE にしてクエリ実行後に RESTRICT に戻すにはどうすればいいのでしょうか？
ADD CONSTRAINT と DROP CONSTRAINT の方法は分かるのですが・・・ ALTER TABLEのALTER CONSTRAINTではできないのでDROP/ADDする
ALTER TABLE <table_name>
DROP CONSTRAINT <fk_name>,
ADD CONSTRAINT <fk_name> FOREGIN KEY … ON UPDATE CASCADE; 物理外部キーを作るくらいなら、論理外部キーとしておいた方が楽。
ガチガチにしておいても、エラーハンドリングの実装がなければ意味がない。 update on conflictを使いまくってるとシーケンス値が上がりまくって、primary keyのidの値が飛びまくるんだけど
気にしたら負けかな。
もちろん綺麗に1 2 3とならない事は認識してるけど、1 1235 5493 29849 みたいに膨大な数飛びまくると…。
99％がupdateの場合なんだけど、update returningして件だったらinsertにした方がいいのかな
その場合ロックとか考えなきゃいけないのが大変なんだけど、もう避けられないのかな insert on conflict do update(いわゆるupsert)のことだよね？
conflictの条件をprimary keyにできないならPKのシーケンスが飛びまくるのはしょうがないと思う
ただ99%がupdateならupdateしてからinsertのほうが性能は良くなるような気がする
ロックを考えなきゃいけないというのはよくわからない
今も同じじゃない？ 言いたいのはロックじゃなくてトランザクションじゃないかな >>957-958
ありがとうございます。
on conflict do updateであれば、トランザクション貼らずともコマンドを実行するだけで確実にinsertかupdateが確定で成功するけど
update → insertだと両方updateが0件になって両方insertしようとして片方がエラー という事が起きるかなと認識していました update → insert on conflict do updateをトランザクションでくくれば今と同じなんじゃないかな
ただREAD COMMITEDならlost updateが発生してるだろうから
本当にそれが望ましいのかよく考えたほうがいいと思う insert into 〜 on conflict do updateでシーケンス値が増えるのが我慢できなくて
insert into 〜 on conflict do update相当の処理をトランザクションで書いているのですが
これって最終的にテーブルに対してACCESS exclusiveロック(selectすら妨害する最強ロック)をかける事が必須だったりしますか？
上記のクエリと同じ処理を行いたい場合、以下の処理を行う必要があると思います
・select文で該当の行が既にあるかをチェック
・select文の結果を見て、該当の行があればupdate
・select文の結果を見て、該当の行が無ければinsert
トランザクション1と2が両方同じタイミングでselect文を発行して、どっちもresultが0だった場合、どっちもinsertをしようとしてしまう。
だからどちらか片方のselect文は、他のinsertが終わるまで待ってから行って、result 1を取得する必要がある。
最初は行ロックでいいかな？と思ったのですが、最初にselectをした段階で対象の行は「存在しない」ので行ロックがかけれません。
となると、対象のテーブルに一番強い権限のACCESS exclusiveロックをかける必要がある？と思います。
ですが、それだとdo updateとは無関係のただのselect文に対してもロックがかかってしまいます。
この一連のdo update〜の処理でだけ排他ロックをかけたいのですが、そんな事出来るのでしょうか？ 違った。
SHARE UPDATE exclusiveモードを使えばよかったのか
失礼しました 同じ話題を引っ張り続けて申し訳ないけど、自分でinsert〜on conflict do update(upsert)相当の事をするのが面倒すぎて、趣味ですら面倒で手が止まる。
仕事じゃ絶対提案出来ないな(ダルすぎて)
自分の場合は実際には1000件単位のデータをupsertしてるんだけど、この数だと1件づつselect→(update or insert)は遅すぎる。
select文で1000件のデータのユニークキーに対して長いwhere 文を作る。
( SELECT id,key FROM tbl WHERE key in ( $1 , $2 , $3 .... , $1000) )
select文の結果を入れたい1000件単位のデータと比較して、1件づつinsertするかupdateするかを判定する
insert、updateも1件づつやるとトランザクションしても遅いから1リクエストで済むように動的にクエリ文を作る。
( INSERT INTO tbl (key) VALUES ($1),($2)... )
( UPDATE tbl SET key=c.key FROM( VALUES ( ($1,$2),($3,$4),...) AS c(id,key) WHERE tbl.id=c.id )
そしてupsertから加えた機能で、含まれてないデータをDELETEもするようにしてるからさらにクエリ文が増える。
SQL文の文字列をプログラムから動的に作るんじゃなくて、
O/Rマッパーというのを使ってもっと構造的に出来るようにするべきなんだろうか…。
ちなみにnode.jsです。 その1000件とかの入力データに同じユニークキーを対象としたデータがあった場合にどうしたいの？
何か累積値や合計値を計算してて必ずカウントアップしていかないといけないとか
入力データ内の順序で後のデータを正として先のデータは捨てられてもいいとか
>>962
>SHARE UPDATE exclusiveモードを使えばよかったのか
これだとテーブルロックになるので1つのトランザクションが終了するまで次のトランザクションは待つことになる
これで十分なユースケースならそもそも同じキーに対して同時にinsertが実行される心配しなくてもいいよね？分離レベルをSerializableにすれば該当キーがロックされるだけで済むはず ごめんなさい、SQLクエリについてはSHARE UPDATE exclusiveのロックを取得する。で何の問題もありません。
自分が今悩んでいるのは、SQL文が動的になって書くのが大変という事です。
1000件あるデータ以下のような1回のinsert文で全ての値を登録するなどの事をしています。
insert into tbl (key1,key2,key3) values(1,2,3),(4,5,6),(7,8,9);
nodejsの場合、プログラムの中からSQLを実行する時は以下のような書き方をするのですが
query(`insert into tbl(key)values($1)`,[1]);
これが可変になると、以下のような書き方になってものすごく読みにくく感じます。実際はパラメーター複数あってさらに複雑ですし。
query(`insert into tbl(key)values ${insertDatas.map((v,i)=>`($${i+1})`).join(",")}`,insertDatas);
もちろんプログラムとしては全く難しくないのですが、素直に言ってダルい。$が連続しているのも読みにくさが増す理由になってる。
なので、こういう場合はO/Rマッパーというものを使えば
文字列操作ではなく見やすい書き方になったり
1回頑張って作れば他のテーブルに横展開出来るから楽なのかな？という趣旨でした。
言語化するとposgresqlのスレで言う事じゃない気もして、申し訳ない。 ORMじゃなくてもbulk insert用のSQLを生成/フォーマットする機能のあるライブラリを選べばいいよ
>query(`insert into tbl(key)values ${insertDatas.map((v,i)=>`($${i+1})`).join(",")}`,insertDatas);
string interpolationに入れた場合に適切にエスケープしてくれるのかどうかちょっと怪しくない？ ストアドファンクションでINSERTしたレコードを取得するにはどうすればいいでしょうか？
INSERTは下記のクエリで行っています。
RETURNS VOID になっている所を、RETURNS TABLEにしてレコードを取得したいのですが、具体的なソースコードが思いつきません。
https://ideone.com/5JPpQ4 >>968
解決した方法も書いておけよ。そうすることでQ&Aが成り立つし、次に困ったときに返信があることが期待できるようになるんだぞ。 RETURNINGやろ
それ以外で自決してたら知らん 解決した方法は下記です。
RETURNS TABLE - RETURN QUERY です。
https://ideone.com/kgQo96
もっといい方法もあると思いますが、自分では思い付きませんでした。 初心者はわかっている値を再度、SELECTしたりするよな。 すみません。
どこが悪いのでしょうか。
RETURN QUERY SELECT については、
使用時はプログラムで INSERTINCIDENT() に引数(incident_name, full_text, registered_by) を与えて呼び出し、
プログラムで与えた引数の値と、INSERT された値が一致するかどうかを比較する必要があるので、意図して SELECT しています。 ネタじゃなければ、確認することをくっつけて実行するのは素人だと思ってください。
そもそもincident_nameとfull_textが引数になってないでしょうに。 '件名',
'本文本文本文',
公表しているコードだとリテラル値を使っているだけなので、いきなり脳内情報を書き込まれてもわかりません。 >公表しているコードだとリテラル値を使っているだけなので、いきなり脳内情報を書き込まれてもわかりません。
すみません。引数をつけて書き直しました。
https://ideone.com/6Ufeev
>ネタじゃなければ、確認することをくっつけて実行するのは素人だと思ってください。
この件が、ソースコードのどの部分にあたるのか理解出来ていません。
おかしな理解のまま進みたくないので、教えて頂けないでしょうか。 グルグル回ってるな。
INSERTしたレコードの列値がすべてわかっているのに、そのレコードをSELECTするのはPostgreSQLを信用していないということなのか？ idがストアドの中で採番されるからそれを取りたいんだろ。タイムスタンプなんかも。 SELECT文のFROM句にINSERTしてSELECTするファンクションを置きたい理由がわからない。
手続きをファンクションとして隠蔽したいんだろう。
idの最大値の求め方も同時実行の考慮なしだし、INSERTが想定通りだったか、自分で確認するらしいし、もはや目的がわからない。 >>984
>idの最大値の求め方も同時実行の考慮なしだし
SERIALIZABLEかもしれないよ INSERTが想定通りだったかSELECTで確認する
さらにそのSELECTが想定通りだったか・・・・詰み INSERT 時に採番される ID とタイムスタンプを取得したいので、ストアドプロシージャではなくストアドファンクションにしました。
ファンクションではトランザクションが使えないので、 serializable にする事で妥協しました。
全てのクエリが、ファンクションを呼び出すプログラム側 ( Npgsql ) の NpgsqlTransaction を使用するので、
プログラム側で IsolationLevel に Serializable を設定しています。
・ファンクションでトランザクションを使う方法
・ストアドプロシージャで戻り値を戻す方法
のいずれかが分かれば serializable 以外に出来るのですが、どうするべきなのかがよく分かりませんでした。 >>988
ネタじゃなく、知識がない状態で突貫でやらざるを得ない状態になっているのです・・・。 ストアドプロシージャにはOUTパラメータというものがあるんだよ Windowsの15.3をインストールしたけどpgadminが動かないね
海外の掲示板では15.2に戻せって言ってるっぽい
原因がPython側にあって修正する時間がないって回答きてるっぽいからしばらくバージョンアップ無理かな
やっぱネイティブじゃないとこんな事になっちゃうね どんな製品でも最新バージョンは様子見しておくもんなんだよ PostgreSQLは最新の15.3をインストール
添付のpgAdmin7.4はインストールしない
別途古いpgAdmin7.3をインストール
これでいけた Pgadmin4自体のデバッグログを出すにはどうしたら良いのでしょうか？
ググると「DBにxxの拡張を入れて〜」とかあるけど、そうじゃなくてpgadmin4のプログラム自体のログが見たいです。
AWSのrdsにssm経由で繋ごうとして、
psqlコマンドではpgpass.conf ファイルに設定したパスワードも読み込んで何も問題なく接続出来るんだけど、
psql -h localhost -p 57851 -U postgres -d postgres
pgadmin4ではconnection timeout expiredしか表示されなくて何も手がかりがなくて困ってます。
素のpsqlで繋がらないなら、DB側の設定やAWSのセキュリティグループを見るとかやりようはあるのでしょうが、pgadminだけで繋がらない状態です >>995
ありがとうございます。まさにここでした。
そしてログレベルを上げても接続エラーの詳細なログは出なくて
結局バックエンドのpythonにログを追加してデバッグして
最終的に接続できない理由はlocalhostと書いてあるからで127.0.0.1と書いたら繋がりました。 このスレッドは１０００を超えました。
新しいスレッドを立ててください。
life time: 2658日 17時間 26分 57秒 5ちゃんねるの運営はプレミアム会員の皆さまに支えられています。
運営にご協力お願いいたします。
───────────────────
《プレミアム会員の主な特典》
★ 5ちゃんねる専用ブラウザからの広告除去
★ 5ちゃんねるの過去ログを取得
★ 書き込み規制の緩和
───────────────────
会員登録には個人情報は一切必要ありません。
月300円から匿名でご購入いただけます。
▼ プレミアム会員登録はこちら ▼
https://premium.5ch.net/
▼ 浪人ログインはこちら ▼
https://login.5ch.net/login.php レス数が1000を超えています。これ以上書き込みはできません。
