Skip to content

Commit 795c382

Browse files
tglsfdctglsfdc
committed
Improve findoidjoins to cover more cases.
Teach the program and script to deal with OID-array referencing columns, which we now have several of. Also, modify the recommended usage process to specify that the program should be run against the regression database rather than template1. This lets it find numerous joins that cannot be found in template1 because the relevant catalogs are entirely empty. Together these changes add seventeen formerly-missed cases to the oidjoins regression test.
1 parent d98711d commit 795c382

File tree

5 files changed

+342
-36
lines changed

5 files changed

+342
-36
lines changed

‎src/test/regress/expected/oidjoins.out

Lines changed: 136 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -241,6 +241,14 @@ WHERE amproc != 0 AND
241241
------+--------
242242
(0 rows)
243243

244+
SELECT ctid, adrelid
245+
FROM pg_catalog.pg_attrdef fk
246+
WHERE adrelid != 0 AND
247+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.adrelid);
248+
ctid | adrelid
249+
------+---------
250+
(0 rows)
251+
244252
SELECT ctid, attrelid
245253
FROM pg_catalog.pg_attribute fk
246254
WHERE attrelid != 0 AND
@@ -305,6 +313,14 @@ WHERE reltype != 0 AND
305313
------+---------
306314
(0 rows)
307315

316+
SELECT ctid, reloftype
317+
FROM pg_catalog.pg_class fk
318+
WHERE reloftype != 0 AND
319+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.reloftype);
320+
ctid | reloftype
321+
------+-----------
322+
(0 rows)
323+
308324
SELECT ctid, relowner
309325
FROM pg_catalog.pg_class fk
310326
WHERE relowner != 0 AND
@@ -369,6 +385,14 @@ WHERE connamespace != 0 AND
369385
------+--------------
370386
(0 rows)
371387

388+
SELECT ctid, conrelid
389+
FROM pg_catalog.pg_constraint fk
390+
WHERE conrelid != 0 AND
391+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conrelid);
392+
ctid | conrelid
393+
------+----------
394+
(0 rows)
395+
372396
SELECT ctid, contypid
373397
FROM pg_catalog.pg_constraint fk
374398
WHERE contypid != 0 AND
@@ -377,6 +401,22 @@ WHERE contypid != 0 AND
377401
------+----------
378402
(0 rows)
379403

404+
SELECT ctid, conindid
405+
FROM pg_catalog.pg_constraint fk
406+
WHERE conindid != 0 AND
407+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conindid);
408+
ctid | conindid
409+
------+----------
410+
(0 rows)
411+
412+
SELECT ctid, confrelid
413+
FROM pg_catalog.pg_constraint fk
414+
WHERE confrelid != 0 AND
415+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.confrelid);
416+
ctid | confrelid
417+
------+-----------
418+
(0 rows)
419+
380420
SELECT ctid, connamespace
381421
FROM pg_catalog.pg_conversion fk
382422
WHERE connamespace != 0 AND
@@ -449,6 +489,14 @@ WHERE classoid != 0 AND
449489
------+----------
450490
(0 rows)
451491

492+
SELECT ctid, enumtypid
493+
FROM pg_catalog.pg_enum fk
494+
WHERE enumtypid != 0 AND
495+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.enumtypid);
496+
ctid | enumtypid
497+
------+-----------
498+
(0 rows)
499+
452500
SELECT ctid, extowner
453501
FROM pg_catalog.pg_extension fk
454502
WHERE extowner != 0 AND
@@ -481,6 +529,22 @@ WHERE indrelid != 0 AND
481529
------+----------
482530
(0 rows)
483531

532+
SELECT ctid, inhrelid
533+
FROM pg_catalog.pg_inherits fk
534+
WHERE inhrelid != 0 AND
535+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhrelid);
536+
ctid | inhrelid
537+
------+----------
538+
(0 rows)
539+
540+
SELECT ctid, inhparent
541+
FROM pg_catalog.pg_inherits fk
542+
WHERE inhparent != 0 AND
543+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhparent);
544+
ctid | inhparent
545+
------+-----------
546+
(0 rows)
547+
484548
SELECT ctid, lanowner
485549
FROM pg_catalog.pg_language fk
486550
WHERE lanowner != 0 AND
@@ -777,6 +841,46 @@ WHERE spcowner != 0 AND
777841
------+----------
778842
(0 rows)
779843

844+
SELECT ctid, tgrelid
845+
FROM pg_catalog.pg_trigger fk
846+
WHERE tgrelid != 0 AND
847+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgrelid);
848+
ctid | tgrelid
849+
------+---------
850+
(0 rows)
851+
852+
SELECT ctid, tgfoid
853+
FROM pg_catalog.pg_trigger fk
854+
WHERE tgfoid != 0 AND
855+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.tgfoid);
856+
ctid | tgfoid
857+
------+--------
858+
(0 rows)
859+
860+
SELECT ctid, tgconstrrelid
861+
FROM pg_catalog.pg_trigger fk
862+
WHERE tgconstrrelid != 0 AND
863+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrrelid);
864+
ctid | tgconstrrelid
865+
------+---------------
866+
(0 rows)
867+
868+
SELECT ctid, tgconstrindid
869+
FROM pg_catalog.pg_trigger fk
870+
WHERE tgconstrindid != 0 AND
871+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrindid);
872+
ctid | tgconstrindid
873+
------+---------------
874+
(0 rows)
875+
876+
SELECT ctid, tgconstraint
877+
FROM pg_catalog.pg_trigger fk
878+
WHERE tgconstraint != 0 AND
879+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_constraint pk WHERE pk.oid = fk.tgconstraint);
880+
ctid | tgconstraint
881+
------+--------------
882+
(0 rows)
883+
780884
SELECT ctid, cfgnamespace
781885
FROM pg_catalog.pg_ts_config fk
782886
WHERE cfgnamespace != 0 AND
@@ -1025,3 +1129,35 @@ WHERE typcollation != 0 AND
10251129
------+--------------
10261130
(0 rows)
10271131

1132+
SELECT ctid, conpfeqop
1133+
FROM (SELECT ctid, unnest(conpfeqop) AS conpfeqop FROM pg_catalog.pg_constraint) fk
1134+
WHERE conpfeqop != 0 AND
1135+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conpfeqop);
1136+
ctid | conpfeqop
1137+
------+-----------
1138+
(0 rows)
1139+
1140+
SELECT ctid, conppeqop
1141+
FROM (SELECT ctid, unnest(conppeqop) AS conppeqop FROM pg_catalog.pg_constraint) fk
1142+
WHERE conppeqop != 0 AND
1143+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conppeqop);
1144+
ctid | conppeqop
1145+
------+-----------
1146+
(0 rows)
1147+
1148+
SELECT ctid, conffeqop
1149+
FROM (SELECT ctid, unnest(conffeqop) AS conffeqop FROM pg_catalog.pg_constraint) fk
1150+
WHERE conffeqop != 0 AND
1151+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conffeqop);
1152+
ctid | conffeqop
1153+
------+-----------
1154+
(0 rows)
1155+
1156+
SELECT ctid, proallargtypes
1157+
FROM (SELECT ctid, unnest(proallargtypes) AS proallargtypes FROM pg_catalog.pg_proc) fk
1158+
WHERE proallargtypes != 0 AND
1159+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.proallargtypes);
1160+
ctid | proallargtypes
1161+
------+----------------
1162+
(0 rows)
1163+

‎src/test/regress/sql/oidjoins.sql

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -121,6 +121,10 @@ SELECT ctid, amproc
121121
FROM pg_catalog.pg_amproc fk
122122
WHERE amproc != 0 AND
123123
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.amproc);
124+
SELECT ctid, adrelid
125+
FROM pg_catalog.pg_attrdef fk
126+
WHERE adrelid != 0 AND
127+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.adrelid);
124128
SELECT ctid, attrelid
125129
FROM pg_catalog.pg_attribute fk
126130
WHERE attrelid != 0 AND
@@ -153,6 +157,10 @@ SELECT ctid, reltype
153157
FROM pg_catalog.pg_class fk
154158
WHERE reltype != 0 AND
155159
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.reltype);
160+
SELECT ctid, reloftype
161+
FROM pg_catalog.pg_class fk
162+
WHERE reloftype != 0 AND
163+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.reloftype);
156164
SELECT ctid, relowner
157165
FROM pg_catalog.pg_class fk
158166
WHERE relowner != 0 AND
@@ -185,10 +193,22 @@ SELECT ctid, connamespace
185193
FROM pg_catalog.pg_constraint fk
186194
WHERE connamespace != 0 AND
187195
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_namespace pk WHERE pk.oid = fk.connamespace);
196+
SELECT ctid, conrelid
197+
FROM pg_catalog.pg_constraint fk
198+
WHERE conrelid != 0 AND
199+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conrelid);
188200
SELECT ctid, contypid
189201
FROM pg_catalog.pg_constraint fk
190202
WHERE contypid != 0 AND
191203
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.contypid);
204+
SELECT ctid, conindid
205+
FROM pg_catalog.pg_constraint fk
206+
WHERE conindid != 0 AND
207+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.conindid);
208+
SELECT ctid, confrelid
209+
FROM pg_catalog.pg_constraint fk
210+
WHERE confrelid != 0 AND
211+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.confrelid);
192212
SELECT ctid, connamespace
193213
FROM pg_catalog.pg_conversion fk
194214
WHERE connamespace != 0 AND
@@ -225,6 +245,10 @@ SELECT ctid, classoid
225245
FROM pg_catalog.pg_description fk
226246
WHERE classoid != 0 AND
227247
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.classoid);
248+
SELECT ctid, enumtypid
249+
FROM pg_catalog.pg_enum fk
250+
WHERE enumtypid != 0 AND
251+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.enumtypid);
228252
SELECT ctid, extowner
229253
FROM pg_catalog.pg_extension fk
230254
WHERE extowner != 0 AND
@@ -241,6 +265,14 @@ SELECT ctid, indrelid
241265
FROM pg_catalog.pg_index fk
242266
WHERE indrelid != 0 AND
243267
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.indrelid);
268+
SELECT ctid, inhrelid
269+
FROM pg_catalog.pg_inherits fk
270+
WHERE inhrelid != 0 AND
271+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhrelid);
272+
SELECT ctid, inhparent
273+
FROM pg_catalog.pg_inherits fk
274+
WHERE inhparent != 0 AND
275+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.inhparent);
244276
SELECT ctid, lanowner
245277
FROM pg_catalog.pg_language fk
246278
WHERE lanowner != 0 AND
@@ -389,6 +421,26 @@ SELECT ctid, spcowner
389421
FROM pg_catalog.pg_tablespace fk
390422
WHERE spcowner != 0 AND
391423
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_authid pk WHERE pk.oid = fk.spcowner);
424+
SELECT ctid, tgrelid
425+
FROM pg_catalog.pg_trigger fk
426+
WHERE tgrelid != 0 AND
427+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgrelid);
428+
SELECT ctid, tgfoid
429+
FROM pg_catalog.pg_trigger fk
430+
WHERE tgfoid != 0 AND
431+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_proc pk WHERE pk.oid = fk.tgfoid);
432+
SELECT ctid, tgconstrrelid
433+
FROM pg_catalog.pg_trigger fk
434+
WHERE tgconstrrelid != 0 AND
435+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrrelid);
436+
SELECT ctid, tgconstrindid
437+
FROM pg_catalog.pg_trigger fk
438+
WHERE tgconstrindid != 0 AND
439+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class pk WHERE pk.oid = fk.tgconstrindid);
440+
SELECT ctid, tgconstraint
441+
FROM pg_catalog.pg_trigger fk
442+
WHERE tgconstraint != 0 AND
443+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_constraint pk WHERE pk.oid = fk.tgconstraint);
392444
SELECT ctid, cfgnamespace
393445
FROM pg_catalog.pg_ts_config fk
394446
WHERE cfgnamespace != 0 AND
@@ -513,3 +565,19 @@ SELECT ctid, typcollation
513565
FROM pg_catalog.pg_type fk
514566
WHERE typcollation != 0 AND
515567
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_collation pk WHERE pk.oid = fk.typcollation);
568+
SELECT ctid, conpfeqop
569+
FROM (SELECT ctid, unnest(conpfeqop) AS conpfeqop FROM pg_catalog.pg_constraint) fk
570+
WHERE conpfeqop != 0 AND
571+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conpfeqop);
572+
SELECT ctid, conppeqop
573+
FROM (SELECT ctid, unnest(conppeqop) AS conppeqop FROM pg_catalog.pg_constraint) fk
574+
WHERE conppeqop != 0 AND
575+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conppeqop);
576+
SELECT ctid, conffeqop
577+
FROM (SELECT ctid, unnest(conffeqop) AS conffeqop FROM pg_catalog.pg_constraint) fk
578+
WHERE conffeqop != 0 AND
579+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_operator pk WHERE pk.oid = fk.conffeqop);
580+
SELECT ctid, proallargtypes
581+
FROM (SELECT ctid, unnest(proallargtypes) AS proallargtypes FROM pg_catalog.pg_proc) fk
582+
WHERE proallargtypes != 0 AND
583+
NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type pk WHERE pk.oid = fk.proallargtypes);

0 commit comments

Comments
 (0)