Add functions to check unique indexes for duplicates and FKs for miss…

…ing rows
df7cb · Mar 17, 2022 · d1c2b89 · d1c2b89
1 parent c63bd76
commit d1c2b89
Show file tree

Hide file tree

Showing 2 changed files with 110 additions and 0 deletions.
diff --git a/contrib/foreign_key.sql b/contrib/foreign_key.sql
@@ -0,0 +1,58 @@
+create or replace function check_foreign_key(con_name name, max_missing bigint default 100, missing out text)
+  returns setof text
+  language plpgsql
+as $$-- Author: Christoph Berg
+declare
+  def text;
+  rel text; relcols text; -- referencing table
+  frel text; frelcols text; -- referenced table
+  fkpred text;
+  query text;
+  missing_count bigint default 0;
+begin
+  select into strict def, rel, relcols, frel, frelcols, fkpred
+    pg_get_constraintdef(oid, true),
+    conrelid::regclass, relcolumns,
+    confrelid::regclass, frelcolumns,
+    fkpredicate
+  from pg_constraint,
+    lateral (select
+        string_agg(format('%I', a1.attname), ', ') relcolumns,
+        string_agg(format('%I', a2.attname), ', ') frelcolumns,
+        string_agg(format('rel.%I = frel.%I', a1.attname, a2.attname), ' and ') fkpredicate
+      from generate_subscripts(conkey, 1) u,
+      lateral
+      (select attname from pg_attribute where attrelid = conrelid and attnum = conkey[u]) a1,
+      lateral
+      (select attname from pg_attribute where attrelid = confrelid and attnum = confkey[u]) a2
+    ) consub
+  where conname = con_name;
+
+  raise notice 'FK % on %: %', con_name, rel, def;
+
+  query := format('select (%s) from %s rel where (%s) is not null and not exists (select from %s frel where %s)', relcols, rel, relcols, frel, fkpred);
+  --raise notice '%', query;
+  for missing in execute query loop
+    missing_count := missing_count + 1;
+    if max_missing is not null and missing_count > max_missing then
+      raise notice 'Stopping after % missing keys', missing_count;
+      exit;
+    end if;
+    return next;
+  end loop;
+
+  if missing_count > 0 then
+    raise warning 'Found % rows in table % (%) missing in table % (%)',
+      missing_count, rel, relcols, frel, frelcols;
+  end if;
+
+  return;
+end$$;
+
+comment on function check_unique_index is 'Check FOREIGN KEY for missing rows';
+
+/* Suggested usage:
+\x
+\t
+select format('select * from check_foreign_key(%L)', conname) from pg_constraint where contype = 'f' \gexec
+*/
diff --git a/contrib/index_duplicates.sql b/contrib/index_duplicates.sql
@@ -0,0 +1,52 @@
+create or replace function check_unique_index(relid regclass, max_dupes bigint default 100, dupe out text, count out text)
+  returns setof record
+  language plpgsql
+  set enable_indexscan = off
+  set enable_indexonlyscan = off
+  set enable_bitmapscan = off
+as $$-- Author: Christoph Berg
+declare
+  tbl text;
+  isunique boolean;
+  key text;
+  query text;
+  dupe_count bigint default 0;
+begin
+  select into strict tbl, isunique indrelid::regclass, indisunique from pg_index where indexrelid = relid;
+
+  select into strict key string_agg(quote_ident(attname), ', ') from
+    pg_index i,
+    unnest(indkey) u(indcolumn),
+    lateral (select attrelid, attnum, attname from pg_attribute) a
+    where i.indrelid=a.attrelid and u.indcolumn = a.attnum and indexrelid = relid;
+
+  raise notice 'Checking index % on % (%)', relid, tbl, key;
+  if not isunique then
+    raise warning 'Index % is not UNIQUE', relid;
+  end if;
+
+  query := format('select (%s) dupe, count(*) from %s where (%s) is not null group by %s having count(*) > 1', key, tbl, key, key);
+  --raise notice '%', query;
+  for dupe, count in execute query loop
+    dupe_count := dupe_count + 1;
+    if max_dupes is not null and dupe_count > max_dupes then
+      raise notice 'Stopping after % duplicate keys', dupe_count;
+      exit;
+    end if;
+    return next;
+  end loop;
+
+  if dupe_count > 0 then
+    raise warning 'Found % duplicates in table %, index % on (%)', dupe_count, tbl, relid, key;
+  end if;
+
+  return;
+end$$;
+
+comment on function check_unique_index is 'Check UNIQUE index for duplicates';
+
+/* Suggested usage:
+\x
+\t
+select format('select * from check_unique_index(%L)', indexrelid::regclass) from pg_index where indisunique \gexec
+*/