Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit795c382

Browse files
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 processto specify that the program should be run against the regression databaserather than template1. This lets it find numerous joins that cannot befound in template1 because the relevant catalogs are entirely empty.Together these changes add seventeen formerly-missed cases to the oidjoinsregression test.
1 parentd98711d commit795c382

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 @@ WHEREamproc != 0 AND
241241
------+--------
242242
(0 rows)
243243

244+
SELECTctid, adrelid
245+
FROMpg_catalog.pg_attrdef fk
246+
WHEREadrelid != 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
SELECTctid, attrelid
245253
FROMpg_catalog.pg_attribute fk
246254
WHEREattrelid != 0 AND
@@ -305,6 +313,14 @@ WHEREreltype != 0 AND
305313
------+---------
306314
(0 rows)
307315

316+
SELECTctid, reloftype
317+
FROMpg_catalog.pg_class fk
318+
WHEREreloftype != 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
SELECTctid, relowner
309325
FROMpg_catalog.pg_class fk
310326
WHERErelowner != 0 AND
@@ -369,6 +385,14 @@ WHEREconnamespace != 0 AND
369385
------+--------------
370386
(0 rows)
371387

388+
SELECTctid, conrelid
389+
FROMpg_catalog.pg_constraint fk
390+
WHEREconrelid != 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
SELECTctid, contypid
373397
FROMpg_catalog.pg_constraint fk
374398
WHEREcontypid != 0 AND
@@ -377,6 +401,22 @@ WHEREcontypid != 0 AND
377401
------+----------
378402
(0 rows)
379403

404+
SELECTctid, conindid
405+
FROMpg_catalog.pg_constraint fk
406+
WHEREconindid != 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+
SELECTctid, confrelid
413+
FROMpg_catalog.pg_constraint fk
414+
WHEREconfrelid != 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
SELECTctid, connamespace
381421
FROMpg_catalog.pg_conversion fk
382422
WHEREconnamespace != 0 AND
@@ -449,6 +489,14 @@ WHEREclassoid != 0 AND
449489
------+----------
450490
(0 rows)
451491

492+
SELECTctid, enumtypid
493+
FROMpg_catalog.pg_enum fk
494+
WHEREenumtypid != 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
SELECTctid, extowner
453501
FROMpg_catalog.pg_extension fk
454502
WHEREextowner != 0 AND
@@ -481,6 +529,22 @@ WHEREindrelid != 0 AND
481529
------+----------
482530
(0 rows)
483531

532+
SELECTctid, inhrelid
533+
FROMpg_catalog.pg_inherits fk
534+
WHEREinhrelid != 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+
SELECTctid, inhparent
541+
FROMpg_catalog.pg_inherits fk
542+
WHEREinhparent != 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
SELECTctid, lanowner
485549
FROMpg_catalog.pg_language fk
486550
WHERElanowner != 0 AND
@@ -777,6 +841,46 @@ WHEREspcowner != 0 AND
777841
------+----------
778842
(0 rows)
779843

844+
SELECTctid, tgrelid
845+
FROMpg_catalog.pg_trigger fk
846+
WHEREtgrelid != 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+
SELECTctid, tgfoid
853+
FROMpg_catalog.pg_trigger fk
854+
WHEREtgfoid != 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+
SELECTctid, tgconstrrelid
861+
FROMpg_catalog.pg_trigger fk
862+
WHEREtgconstrrelid != 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+
SELECTctid, tgconstrindid
869+
FROMpg_catalog.pg_trigger fk
870+
WHEREtgconstrindid != 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+
SELECTctid, tgconstraint
877+
FROMpg_catalog.pg_trigger fk
878+
WHEREtgconstraint != 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
SELECTctid, cfgnamespace
781885
FROMpg_catalog.pg_ts_config fk
782886
WHEREcfgnamespace != 0 AND
@@ -1025,3 +1129,35 @@ WHEREtypcollation != 0 AND
10251129
------+--------------
10261130
(0 rows)
10271131

1132+
SELECTctid, conpfeqop
1133+
FROM(SELECT ctid, unnest(conpfeqop) AS conpfeqop FROM pg_catalog.pg_constraint) fk
1134+
WHEREconpfeqop != 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+
SELECTctid, conppeqop
1141+
FROM(SELECT ctid, unnest(conppeqop) AS conppeqop FROM pg_catalog.pg_constraint) fk
1142+
WHEREconppeqop != 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+
SELECTctid, conffeqop
1149+
FROM(SELECT ctid, unnest(conffeqop) AS conffeqop FROM pg_catalog.pg_constraint) fk
1150+
WHEREconffeqop != 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+
SELECTctid, proallargtypes
1157+
FROM(SELECT ctid, unnest(proallargtypes) AS proallargtypes FROM pg_catalog.pg_proc) fk
1158+
WHEREproallargtypes != 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 @@ SELECTctid, amproc
121121
FROMpg_catalog.pg_amproc fk
122122
WHEREamproc!=0AND
123123
NOT EXISTS(SELECT1FROMpg_catalog.pg_proc pkWHEREpk.oid=fk.amproc);
124+
SELECTctid, adrelid
125+
FROMpg_catalog.pg_attrdef fk
126+
WHEREadrelid!=0AND
127+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.adrelid);
124128
SELECTctid, attrelid
125129
FROMpg_catalog.pg_attribute fk
126130
WHEREattrelid!=0AND
@@ -153,6 +157,10 @@ SELECTctid, reltype
153157
FROMpg_catalog.pg_class fk
154158
WHEREreltype!=0AND
155159
NOT EXISTS(SELECT1FROMpg_catalog.pg_type pkWHEREpk.oid=fk.reltype);
160+
SELECTctid, reloftype
161+
FROMpg_catalog.pg_class fk
162+
WHEREreloftype!=0AND
163+
NOT EXISTS(SELECT1FROMpg_catalog.pg_type pkWHEREpk.oid=fk.reloftype);
156164
SELECTctid, relowner
157165
FROMpg_catalog.pg_class fk
158166
WHERErelowner!=0AND
@@ -185,10 +193,22 @@ SELECTctid, connamespace
185193
FROMpg_catalog.pg_constraint fk
186194
WHEREconnamespace!=0AND
187195
NOT EXISTS(SELECT1FROMpg_catalog.pg_namespace pkWHEREpk.oid=fk.connamespace);
196+
SELECTctid, conrelid
197+
FROMpg_catalog.pg_constraint fk
198+
WHEREconrelid!=0AND
199+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.conrelid);
188200
SELECTctid, contypid
189201
FROMpg_catalog.pg_constraint fk
190202
WHEREcontypid!=0AND
191203
NOT EXISTS(SELECT1FROMpg_catalog.pg_type pkWHEREpk.oid=fk.contypid);
204+
SELECTctid, conindid
205+
FROMpg_catalog.pg_constraint fk
206+
WHEREconindid!=0AND
207+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.conindid);
208+
SELECTctid, confrelid
209+
FROMpg_catalog.pg_constraint fk
210+
WHEREconfrelid!=0AND
211+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.confrelid);
192212
SELECTctid, connamespace
193213
FROMpg_catalog.pg_conversion fk
194214
WHEREconnamespace!=0AND
@@ -225,6 +245,10 @@ SELECTctid, classoid
225245
FROMpg_catalog.pg_description fk
226246
WHEREclassoid!=0AND
227247
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.classoid);
248+
SELECTctid, enumtypid
249+
FROMpg_catalog.pg_enum fk
250+
WHEREenumtypid!=0AND
251+
NOT EXISTS(SELECT1FROMpg_catalog.pg_type pkWHEREpk.oid=fk.enumtypid);
228252
SELECTctid, extowner
229253
FROMpg_catalog.pg_extension fk
230254
WHEREextowner!=0AND
@@ -241,6 +265,14 @@ SELECTctid, indrelid
241265
FROMpg_catalog.pg_index fk
242266
WHEREindrelid!=0AND
243267
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.indrelid);
268+
SELECTctid, inhrelid
269+
FROMpg_catalog.pg_inherits fk
270+
WHEREinhrelid!=0AND
271+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.inhrelid);
272+
SELECTctid, inhparent
273+
FROMpg_catalog.pg_inherits fk
274+
WHEREinhparent!=0AND
275+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.inhparent);
244276
SELECTctid, lanowner
245277
FROMpg_catalog.pg_language fk
246278
WHERElanowner!=0AND
@@ -389,6 +421,26 @@ SELECTctid, spcowner
389421
FROMpg_catalog.pg_tablespace fk
390422
WHEREspcowner!=0AND
391423
NOT EXISTS(SELECT1FROMpg_catalog.pg_authid pkWHEREpk.oid=fk.spcowner);
424+
SELECTctid, tgrelid
425+
FROMpg_catalog.pg_trigger fk
426+
WHEREtgrelid!=0AND
427+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.tgrelid);
428+
SELECTctid, tgfoid
429+
FROMpg_catalog.pg_trigger fk
430+
WHEREtgfoid!=0AND
431+
NOT EXISTS(SELECT1FROMpg_catalog.pg_proc pkWHEREpk.oid=fk.tgfoid);
432+
SELECTctid, tgconstrrelid
433+
FROMpg_catalog.pg_trigger fk
434+
WHEREtgconstrrelid!=0AND
435+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.tgconstrrelid);
436+
SELECTctid, tgconstrindid
437+
FROMpg_catalog.pg_trigger fk
438+
WHEREtgconstrindid!=0AND
439+
NOT EXISTS(SELECT1FROMpg_catalog.pg_class pkWHEREpk.oid=fk.tgconstrindid);
440+
SELECTctid, tgconstraint
441+
FROMpg_catalog.pg_trigger fk
442+
WHEREtgconstraint!=0AND
443+
NOT EXISTS(SELECT1FROMpg_catalog.pg_constraint pkWHEREpk.oid=fk.tgconstraint);
392444
SELECTctid, cfgnamespace
393445
FROMpg_catalog.pg_ts_config fk
394446
WHEREcfgnamespace!=0AND
@@ -513,3 +565,19 @@ SELECTctid, typcollation
513565
FROMpg_catalog.pg_type fk
514566
WHEREtypcollation!=0AND
515567
NOT EXISTS(SELECT1FROMpg_catalog.pg_collation pkWHEREpk.oid=fk.typcollation);
568+
SELECTctid, conpfeqop
569+
FROM(SELECT ctid, unnest(conpfeqop)AS conpfeqopFROMpg_catalog.pg_constraint) fk
570+
WHEREconpfeqop!=0AND
571+
NOT EXISTS(SELECT1FROMpg_catalog.pg_operator pkWHEREpk.oid=fk.conpfeqop);
572+
SELECTctid, conppeqop
573+
FROM(SELECT ctid, unnest(conppeqop)AS conppeqopFROMpg_catalog.pg_constraint) fk
574+
WHEREconppeqop!=0AND
575+
NOT EXISTS(SELECT1FROMpg_catalog.pg_operator pkWHEREpk.oid=fk.conppeqop);
576+
SELECTctid, conffeqop
577+
FROM(SELECT ctid, unnest(conffeqop)AS conffeqopFROMpg_catalog.pg_constraint) fk
578+
WHEREconffeqop!=0AND
579+
NOT EXISTS(SELECT1FROMpg_catalog.pg_operator pkWHEREpk.oid=fk.conffeqop);
580+
SELECTctid, proallargtypes
581+
FROM(SELECT ctid, unnest(proallargtypes)AS proallargtypesFROMpg_catalog.pg_proc) fk
582+
WHEREproallargtypes!=0AND
583+
NOT EXISTS(SELECT1FROMpg_catalog.pg_type pkWHEREpk.oid=fk.proallargtypes);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp