1. 概要
えらいことになっちゃったのであります。
最近バージョンを「postgresSQL 14」から「postgreSQL 16」へ更新したのですが。
前のバージョンでフルバックアップとったものを、インストールしたバージョンへインストールしたのですが。
どうも手違いがあったようなのです。
本ページは、下記のサイトを参考にさせていただきました。
「PostgreSQL で重複するデータの内の1件だけを削除する(システム列の ctid を使った削除) #PostgreSQL 」
「重複レコードの削除方法【PostgreSQL】」
2. 状況
どうも、インデックスがなくなったテーブルがあって、本来インデックスで重複しないはずのデータが重複しております。
テーブルをこんな感じで作成しまして。
create table public.contents (
index integer not null
, uri text not null
, document text
, title text
, modified timestamp(6) without time zone
, description text
, content text
);
１、２カラムをインデックスにしていたはずなのですが・・・。
インデックスがなくなっているし。
そのせいか、どうも重複行が発生しています。
毎日定期的に動かしている、「SQL」も。
psycopg2.errors.InvalidColumnReference: there is no unique or exclusion constraint matching the ON CONFLICT specification
てなエラー、「CONFLICT」が発生しております。
確認してみると。
select count(*) from contents;
count
-------
62386
select count(*) from (select distinct on (index, uri) * from contents);
count
-------
31193
と、重複行を除くと、ちょうど半分なので、綺麗に（？）すべての行が重複しているようです。
これは、重複行を取り除いてから、インデックスを作成するしかないかな。
とりあえず、現状のバックアップは、とっておきます。
3. 重複行削除
前項といい、本項といい、本来は、「データベース - SQL 構文」に書くべき内容があるのですが、それは、いずれ機会があれば・・・ということで。
まず、重複行を削除します。
DELETE FROM contents t1
WHERE EXISTS(SELECT *
FROM contents t2
WHERE
t2.index = t1.index
AND t2.uri = t1.uri
AND t2.ctid > t1.ctid
)
;
ここで、参考サイトの説明をそのまま掲載。
PostgreSQL によって暗黙的に定義されている ctid システム列を利用します。ctid は、テーブル内において行の物理的位置を表していて、一意であることを利用します。
ですって。
実行後に、件数を確認してみます。
select count(*) from contents;
count
-------
62386
綺麗に半分になったようです。
4. インデックス作成
さて、その上で、インデックスを作成します。
CREATE UNIQUE INDEX contents_key ON public.contents USING btree (index, uri);
後は、定期的な「SQL」が動作するか、確認します。
翌日確認したら、問題なく動作するように復活したようです。めでたしめでたし・
