From bf56f0759bdfa87f143c3abd09f893a5f530fe88 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 10 Aug 2001 18:57:42 +0000 Subject: Make OIDs optional, per discussions in pghackers. WITH OIDS is still the default, but OIDS are removed from many system catalogs that don't need them. Some interesting side effects: TOAST pointers are 20 bytes not 32 now; pg_description has a three-column key instead of one. Bugs fixed in passing: BINARY cursors work again; pg_class.relhaspkey has some usefulness; pg_dump dumps comments on indexes, rules, and triggers in a valid order. initdb forced. --- src/test/regress/expected/oidjoins.out | 338 +++++++++++++++--------------- src/test/regress/expected/opr_sanity.out | 74 +++---- src/test/regress/expected/rules.out | 2 +- src/test/regress/expected/type_sanity.out | 29 +-- src/test/regress/sql/oidjoins.sql | 114 +++++----- src/test/regress/sql/opr_sanity.sql | 38 +--- src/test/regress/sql/type_sanity.sql | 15 +- 7 files changed, 283 insertions(+), 327 deletions(-) (limited to 'src/test') diff --git a/src/test/regress/expected/oidjoins.out b/src/test/regress/expected/oidjoins.out index 95c24879857..0ee0f83c70e 100644 --- a/src/test/regress/expected/oidjoins.out +++ b/src/test/regress/expected/oidjoins.out @@ -1,443 +1,451 @@ -- -- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check -- -SELECT oid, pg_aggregate.aggtransfn +SELECT ctid, pg_aggregate.aggtransfn FROM pg_aggregate WHERE pg_aggregate.aggtransfn != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn); - oid | aggtransfn ------+------------ + ctid | aggtransfn +------+------------ (0 rows) -SELECT oid, pg_aggregate.aggfinalfn +SELECT ctid, pg_aggregate.aggfinalfn FROM pg_aggregate WHERE pg_aggregate.aggfinalfn != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn); - oid | aggfinalfn ------+------------ + ctid | aggfinalfn +------+------------ (0 rows) -SELECT oid, pg_aggregate.aggbasetype +SELECT ctid, pg_aggregate.aggbasetype FROM pg_aggregate WHERE pg_aggregate.aggbasetype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggbasetype); - oid | aggbasetype ------+------------- + ctid | aggbasetype +------+------------- (0 rows) -SELECT oid, pg_aggregate.aggtranstype +SELECT ctid, pg_aggregate.aggtranstype FROM pg_aggregate WHERE pg_aggregate.aggtranstype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype); - oid | aggtranstype ------+-------------- + ctid | aggtranstype +------+-------------- (0 rows) -SELECT oid, pg_aggregate.aggfinaltype +SELECT ctid, pg_aggregate.aggfinaltype FROM pg_aggregate WHERE pg_aggregate.aggfinaltype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggfinaltype); - oid | aggfinaltype ------+-------------- + ctid | aggfinaltype +------+-------------- (0 rows) -SELECT oid, pg_am.amgettuple +SELECT ctid, pg_am.amgettuple FROM pg_am WHERE pg_am.amgettuple != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple); - oid | amgettuple ------+------------ + ctid | amgettuple +------+------------ (0 rows) -SELECT oid, pg_am.aminsert +SELECT ctid, pg_am.aminsert FROM pg_am WHERE pg_am.aminsert != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert); - oid | aminsert ------+---------- + ctid | aminsert +------+---------- (0 rows) -SELECT oid, pg_am.ambeginscan +SELECT ctid, pg_am.ambeginscan FROM pg_am WHERE pg_am.ambeginscan != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan); - oid | ambeginscan ------+------------- + ctid | ambeginscan +------+------------- (0 rows) -SELECT oid, pg_am.amrescan +SELECT ctid, pg_am.amrescan FROM pg_am WHERE pg_am.amrescan != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan); - oid | amrescan ------+---------- + ctid | amrescan +------+---------- (0 rows) -SELECT oid, pg_am.amendscan +SELECT ctid, pg_am.amendscan FROM pg_am WHERE pg_am.amendscan != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan); - oid | amendscan ------+----------- + ctid | amendscan +------+----------- (0 rows) -SELECT oid, pg_am.ammarkpos +SELECT ctid, pg_am.ammarkpos FROM pg_am WHERE pg_am.ammarkpos != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos); - oid | ammarkpos ------+----------- + ctid | ammarkpos +------+----------- (0 rows) -SELECT oid, pg_am.amrestrpos +SELECT ctid, pg_am.amrestrpos FROM pg_am WHERE pg_am.amrestrpos != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos); - oid | amrestrpos ------+------------ + ctid | amrestrpos +------+------------ (0 rows) -SELECT oid, pg_am.ambuild +SELECT ctid, pg_am.ambuild FROM pg_am WHERE pg_am.ambuild != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild); - oid | ambuild ------+--------- + ctid | ambuild +------+--------- (0 rows) -SELECT oid, pg_am.ambulkdelete +SELECT ctid, pg_am.ambulkdelete FROM pg_am WHERE pg_am.ambulkdelete != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete); - oid | ambulkdelete ------+-------------- + ctid | ambulkdelete +------+-------------- (0 rows) -SELECT oid, pg_am.amcostestimate +SELECT ctid, pg_am.amcostestimate FROM pg_am WHERE pg_am.amcostestimate != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate); - oid | amcostestimate ------+---------------- + ctid | amcostestimate +------+---------------- (0 rows) -SELECT oid, pg_amop.amopid +SELECT ctid, pg_amop.amopid FROM pg_amop WHERE pg_amop.amopid != 0 AND NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amop.amopid); - oid | amopid ------+-------- + ctid | amopid +------+-------- (0 rows) -SELECT oid, pg_amop.amopclaid +SELECT ctid, pg_amop.amopclaid FROM pg_amop WHERE pg_amop.amopclaid != 0 AND NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid); - oid | amopclaid ------+----------- + ctid | amopclaid +------+----------- (0 rows) -SELECT oid, pg_amop.amopopr +SELECT ctid, pg_amop.amopopr FROM pg_amop WHERE pg_amop.amopopr != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr); - oid | amopopr ------+--------- + ctid | amopopr +------+--------- (0 rows) -SELECT oid, pg_amproc.amid +SELECT ctid, pg_amproc.amid FROM pg_amproc WHERE pg_amproc.amid != 0 AND NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amproc.amid); - oid | amid ------+------ + ctid | amid +------+------ (0 rows) -SELECT oid, pg_amproc.amopclaid +SELECT ctid, pg_amproc.amopclaid FROM pg_amproc WHERE pg_amproc.amopclaid != 0 AND NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid); - oid | amopclaid ------+----------- + ctid | amopclaid +------+----------- (0 rows) -SELECT oid, pg_amproc.amproc +SELECT ctid, pg_amproc.amproc FROM pg_amproc WHERE pg_amproc.amproc != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc); - oid | amproc ------+-------- + ctid | amproc +------+-------- (0 rows) -SELECT oid, pg_attribute.attrelid +SELECT ctid, pg_attribute.attrelid FROM pg_attribute WHERE pg_attribute.attrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid); - oid | attrelid ------+---------- + ctid | attrelid +------+---------- (0 rows) -SELECT oid, pg_attribute.atttypid +SELECT ctid, pg_attribute.atttypid FROM pg_attribute WHERE pg_attribute.atttypid != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid); - oid | atttypid ------+---------- + ctid | atttypid +------+---------- (0 rows) -SELECT oid, pg_class.reltype +SELECT ctid, pg_class.reltype FROM pg_class WHERE pg_class.reltype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype); - oid | reltype ------+--------- + ctid | reltype +------+--------- (0 rows) -SELECT oid, pg_class.relam +SELECT ctid, pg_class.relam FROM pg_class WHERE pg_class.relam != 0 AND NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam); - oid | relam ------+------- + ctid | relam +------+------- (0 rows) -SELECT oid, pg_class.reltoastrelid +SELECT ctid, pg_class.reltoastrelid FROM pg_class WHERE pg_class.reltoastrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid); - oid | reltoastrelid ------+--------------- + ctid | reltoastrelid +------+--------------- (0 rows) -SELECT oid, pg_class.reltoastidxid +SELECT ctid, pg_class.reltoastidxid FROM pg_class WHERE pg_class.reltoastidxid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid); - oid | reltoastidxid ------+--------------- + ctid | reltoastidxid +------+--------------- (0 rows) -SELECT oid, pg_index.indexrelid +SELECT ctid, pg_description.classoid +FROM pg_description +WHERE pg_description.classoid != 0 AND + NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid); + ctid | classoid +------+---------- +(0 rows) + +SELECT ctid, pg_index.indexrelid FROM pg_index WHERE pg_index.indexrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid); - oid | indexrelid ------+------------ + ctid | indexrelid +------+------------ (0 rows) -SELECT oid, pg_index.indrelid +SELECT ctid, pg_index.indrelid FROM pg_index WHERE pg_index.indrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid); - oid | indrelid ------+---------- + ctid | indrelid +------+---------- (0 rows) -SELECT oid, pg_opclass.opcdeftype +SELECT ctid, pg_opclass.opcdeftype FROM pg_opclass WHERE pg_opclass.opcdeftype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcdeftype); - oid | opcdeftype ------+------------ + ctid | opcdeftype +------+------------ (0 rows) -SELECT oid, pg_operator.oprleft +SELECT ctid, pg_operator.oprleft FROM pg_operator WHERE pg_operator.oprleft != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft); - oid | oprleft ------+--------- + ctid | oprleft +------+--------- (0 rows) -SELECT oid, pg_operator.oprright +SELECT ctid, pg_operator.oprright FROM pg_operator WHERE pg_operator.oprright != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright); - oid | oprright ------+---------- + ctid | oprright +------+---------- (0 rows) -SELECT oid, pg_operator.oprresult +SELECT ctid, pg_operator.oprresult FROM pg_operator WHERE pg_operator.oprresult != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult); - oid | oprresult ------+----------- + ctid | oprresult +------+----------- (0 rows) -SELECT oid, pg_operator.oprcom +SELECT ctid, pg_operator.oprcom FROM pg_operator WHERE pg_operator.oprcom != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom); - oid | oprcom ------+-------- + ctid | oprcom +------+-------- (0 rows) -SELECT oid, pg_operator.oprnegate +SELECT ctid, pg_operator.oprnegate FROM pg_operator WHERE pg_operator.oprnegate != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate); - oid | oprnegate ------+----------- + ctid | oprnegate +------+----------- (0 rows) -SELECT oid, pg_operator.oprlsortop +SELECT ctid, pg_operator.oprlsortop FROM pg_operator WHERE pg_operator.oprlsortop != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop); - oid | oprlsortop ------+------------ + ctid | oprlsortop +------+------------ (0 rows) -SELECT oid, pg_operator.oprrsortop +SELECT ctid, pg_operator.oprrsortop FROM pg_operator WHERE pg_operator.oprrsortop != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop); - oid | oprrsortop ------+------------ + ctid | oprrsortop +------+------------ (0 rows) -SELECT oid, pg_operator.oprcode +SELECT ctid, pg_operator.oprcode FROM pg_operator WHERE pg_operator.oprcode != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode); - oid | oprcode ------+--------- + ctid | oprcode +------+--------- (0 rows) -SELECT oid, pg_operator.oprrest +SELECT ctid, pg_operator.oprrest FROM pg_operator WHERE pg_operator.oprrest != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest); - oid | oprrest ------+--------- + ctid | oprrest +------+--------- (0 rows) -SELECT oid, pg_operator.oprjoin +SELECT ctid, pg_operator.oprjoin FROM pg_operator WHERE pg_operator.oprjoin != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin); - oid | oprjoin ------+--------- + ctid | oprjoin +------+--------- (0 rows) -SELECT oid, pg_proc.prolang +SELECT ctid, pg_proc.prolang FROM pg_proc WHERE pg_proc.prolang != 0 AND NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang); - oid | prolang ------+--------- + ctid | prolang +------+--------- (0 rows) -SELECT oid, pg_proc.prorettype +SELECT ctid, pg_proc.prorettype FROM pg_proc WHERE pg_proc.prorettype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype); - oid | prorettype ------+------------ + ctid | prorettype +------+------------ (0 rows) -SELECT oid, pg_rewrite.ev_class +SELECT ctid, pg_rewrite.ev_class FROM pg_rewrite WHERE pg_rewrite.ev_class != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class); - oid | ev_class ------+---------- + ctid | ev_class +------+---------- (0 rows) -SELECT oid, pg_statistic.starelid +SELECT ctid, pg_statistic.starelid FROM pg_statistic WHERE pg_statistic.starelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_statistic.starelid); - oid | starelid ------+---------- + ctid | starelid +------+---------- (0 rows) -SELECT oid, pg_statistic.staop1 +SELECT ctid, pg_statistic.staop1 FROM pg_statistic WHERE pg_statistic.staop1 != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop1); - oid | staop1 ------+-------- + ctid | staop1 +------+-------- (0 rows) -SELECT oid, pg_statistic.staop2 +SELECT ctid, pg_statistic.staop2 FROM pg_statistic WHERE pg_statistic.staop2 != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop2); - oid | staop2 ------+-------- + ctid | staop2 +------+-------- (0 rows) -SELECT oid, pg_statistic.staop3 +SELECT ctid, pg_statistic.staop3 FROM pg_statistic WHERE pg_statistic.staop3 != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop3); - oid | staop3 ------+-------- + ctid | staop3 +------+-------- (0 rows) -SELECT oid, pg_trigger.tgrelid +SELECT ctid, pg_trigger.tgrelid FROM pg_trigger WHERE pg_trigger.tgrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid); - oid | tgrelid ------+--------- + ctid | tgrelid +------+--------- (0 rows) -SELECT oid, pg_trigger.tgfoid +SELECT ctid, pg_trigger.tgfoid FROM pg_trigger WHERE pg_trigger.tgfoid != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid); - oid | tgfoid ------+-------- + ctid | tgfoid +------+-------- (0 rows) -SELECT oid, pg_type.typrelid +SELECT ctid, pg_type.typrelid FROM pg_type WHERE pg_type.typrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid); - oid | typrelid ------+---------- + ctid | typrelid +------+---------- (0 rows) -SELECT oid, pg_type.typelem +SELECT ctid, pg_type.typelem FROM pg_type WHERE pg_type.typelem != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem); - oid | typelem ------+--------- + ctid | typelem +------+--------- (0 rows) -SELECT oid, pg_type.typinput +SELECT ctid, pg_type.typinput FROM pg_type WHERE pg_type.typinput != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput); - oid | typinput ------+---------- + ctid | typinput +------+---------- (0 rows) -SELECT oid, pg_type.typoutput +SELECT ctid, pg_type.typoutput FROM pg_type WHERE pg_type.typoutput != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput); - oid | typoutput ------+----------- + ctid | typoutput +------+----------- (0 rows) -SELECT oid, pg_type.typreceive +SELECT ctid, pg_type.typreceive FROM pg_type WHERE pg_type.typreceive != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typreceive); - oid | typreceive ------+------------ + ctid | typreceive +------+------------ (0 rows) -SELECT oid, pg_type.typsend +SELECT ctid, pg_type.typsend FROM pg_type WHERE pg_type.typsend != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typsend); - oid | typsend ------+--------- + ctid | typsend +------+--------- (0 rows) diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 533655c52ea..25f9439afc3 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -480,8 +480,8 @@ WHERE p1.aggtransfn = p2.oid AND (p2.pronargs = 1 AND p1.aggbasetype = 0))); oid | aggname | oid | proname -------+---------+-----+------------- - 16959 | max | 768 | int4larger - 16973 | min | 769 | int4smaller + 10020 | max | 768 | int4larger + 10034 | min | 769 | int4smaller (2 rows) -- Cross-check finalfn (if present) against its entry in pg_proc. @@ -522,31 +522,20 @@ WHERE p1.oid != p2.oid AND -- **************** pg_amop **************** -- Look for illegal values in pg_amop fields -SELECT p1.oid +SELECT p1.amopclaid, p1.amopopr, p1.amopid FROM pg_amop as p1 WHERE p1.amopid = 0 OR p1.amopclaid = 0 OR p1.amopopr = 0 OR p1.amopstrategy <= 0; - oid ------ -(0 rows) - --- Look for duplicate pg_amop entries -SELECT p1.oid, p2.oid -FROM pg_amop AS p1, pg_amop AS p2 -WHERE p1.oid != p2.oid AND - p1.amopid = p2.amopid AND - p1.amopclaid = p2.amopclaid AND - p1.amopstrategy = p2.amopstrategy; - oid | oid ------+----- + amopclaid | amopopr | amopid +-----------+---------+-------- (0 rows) -- Cross-check amopstrategy index against parent AM -SELECT p1.oid, p2.oid, p2.amname +SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.amname FROM pg_amop AS p1, pg_am AS p2 WHERE p1.amopid = p2.oid AND p1.amopstrategy > p2.amstrategies; - oid | oid | amname ------+-----+-------- + amopclaid | amopopr | amopid | oid | amname +-----------+---------+--------+-----+-------- (0 rows) -- Detect missing pg_amop entries: should have as many strategy functions @@ -566,51 +555,40 @@ WHERE p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 -- operator yielding boolean. -- NOTE: for 7.1, add restriction that operator inputs are of same type. -- We used to have opclasses like "int24_ops" but these were broken. -SELECT p1.oid, p2.oid, p2.oprname +SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname FROM pg_amop AS p1, pg_operator AS p2 WHERE p1.amopopr = p2.oid AND (p2.oprkind != 'b' OR p2.oprresult != 16 OR p2.oprleft != p2.oprright); - oid | oid | oprname ------+-----+--------- + amopclaid | amopopr | amopid | oid | oprname +-----------+---------+--------+-----+--------- (0 rows) -- If opclass is for a specific type, operator inputs should be of that type -SELECT p1.oid, p2.oid, p2.oprname, p3.oid, p3.opcname +SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname, p3.oid, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND p3.opcdeftype != 0 AND (p3.opcdeftype != p2.oprleft OR p3.opcdeftype != p2.oprright); - oid | oid | oprname | oid | opcname ------+-----+---------+-----+--------- + amopclaid | amopopr | amopid | oid | oprname | oid | opcname +-----------+---------+--------+-----+---------+-----+--------- (0 rows) -- **************** pg_amproc **************** -- Look for illegal values in pg_amproc fields -SELECT p1.oid +SELECT p1.amid, p1.amopclaid, p1.amprocnum FROM pg_amproc as p1 WHERE p1.amid = 0 OR p1.amopclaid = 0 OR p1.amproc = 0 OR p1.amprocnum <= 0; - oid ------ -(0 rows) - --- Look for duplicate pg_amproc entries -SELECT p1.oid, p2.oid -FROM pg_amproc AS p1, pg_amproc AS p2 -WHERE p1.oid != p2.oid AND - p1.amid = p2.amid AND - p1.amopclaid = p2.amopclaid AND - p1.amprocnum = p2.amprocnum; - oid | oid ------+----- + amid | amopclaid | amprocnum +------+-----------+----------- (0 rows) -- Cross-check amprocnum index against parent AM -SELECT p1.oid, p2.oid, p2.amname +SELECT p1.amid, p1.amopclaid, p1.amprocnum, p2.oid, p2.amname FROM pg_amproc AS p1, pg_am AS p2 WHERE p1.amid = p2.oid AND p1.amprocnum > p2.amsupport; - oid | oid | amname ------+-----+-------- + amid | amopclaid | amprocnum | oid | amname +------+-----------+-----------+-----+-------- (0 rows) -- Detect missing pg_amproc entries: should have as many support functions @@ -631,14 +609,16 @@ WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 -- or different base data types. -- We can check that all the referenced instances of the same support -- routine number take the same number of parameters, but that's about it... -SELECT p1.oid, p2.oid, p2.proname, p3.oid, p4.oid, p4.proname +SELECT p1.amid, p1.amopclaid, p1.amprocnum, + p2.oid, p2.proname, + p3.amid, p3.amopclaid, p3.amprocnum, + p4.oid, p4.proname FROM pg_amproc AS p1, pg_proc AS p2, pg_amproc AS p3, pg_proc AS p4 -WHERE p1.oid != p3.oid AND - p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND +WHERE p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND p1.amproc = p2.oid AND p3.amproc = p4.oid AND (p2.proretset OR p4.proretset OR p2.pronargs != p4.pronargs); - oid | oid | proname | oid | oid | proname ------+-----+---------+-----+-----+--------- + amid | amopclaid | amprocnum | oid | proname | amid | amopclaid | amprocnum | oid | proname +------+-----------+-----------+-----+---------+------+-----------+-----------+-----+--------- (0 rows) -- Cross-check that each opclass that has any entries for a given AM diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index acf6aa47f06..a655f188096 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1279,7 +1279,7 @@ SELECT viewname, definition FROM pg_views ORDER BY viewname; pg_stat_user_tables | SELECT pg_stat_all_tables.relid, pg_stat_all_tables.relname, pg_stat_all_tables.seq_scan, pg_stat_all_tables.seq_tup_read, pg_stat_all_tables.idx_scan, pg_stat_all_tables.idx_tup_fetch, pg_stat_all_tables.n_tup_ins, pg_stat_all_tables.n_tup_upd, pg_stat_all_tables.n_tup_del FROM pg_stat_all_tables WHERE (pg_stat_all_tables.relname !~ '^pg_'::text); pg_statio_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, c.relname, i.relname AS indexrelname, (pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read, pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit FROM pg_class c, pg_class i, pg_index x WHERE (((c.relkind = 'r'::"char") AND (x.indrelid = c.oid)) AND (x.indexrelid = i.oid)); pg_statio_all_sequences | SELECT c.oid AS relid, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read, pg_stat_get_blocks_hit(c.oid) AS blks_hit FROM pg_class c WHERE (c.relkind = 'S'::"char"); - pg_statio_all_tables | SELECT c.oid AS relid, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))) AS idx_blks_read, sum(pg_stat_get_blocks_hit(i.indexrelid)) AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM (((pg_class c FULL JOIN pg_index i ON ((c.oid = i.indrelid))) FULL JOIN pg_class t ON ((c.reltoastrelid = t.oid))) FULL JOIN pg_class x ON ((c.reltoastidxid = x.oid))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, c.relname, t.oid, x.oid; + pg_statio_all_tables | SELECT c.oid AS relid, c.relname, (pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS heap_blks_read, pg_stat_get_blocks_hit(c.oid) AS heap_blks_hit, sum((pg_stat_get_blocks_fetched(i.indexrelid) - pg_stat_get_blocks_hit(i.indexrelid))) AS idx_blks_read, sum(pg_stat_get_blocks_hit(i.indexrelid)) AS idx_blks_hit, (pg_stat_get_blocks_fetched(t.oid) - pg_stat_get_blocks_hit(t.oid)) AS toast_blks_read, pg_stat_get_blocks_hit(t.oid) AS toast_blks_hit, (pg_stat_get_blocks_fetched(x.oid) - pg_stat_get_blocks_hit(x.oid)) AS tidx_blks_read, pg_stat_get_blocks_hit(x.oid) AS tidx_blks_hit FROM (((pg_class c FULL JOIN pg_index i ON ((c.oid = i.indrelid))) FULL JOIN pg_class t ON ((c.reltoastrelid = t.oid))) FULL JOIN pg_class x ON ((t.reltoastidxid = x.oid))) WHERE (c.relkind = 'r'::"char") GROUP BY c.oid, c.relname, t.oid, x.oid; pg_statio_sys_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE (pg_statio_all_indexes.relname ~ '^pg_'::text); pg_statio_sys_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE (pg_statio_all_sequences.relname ~ '^pg_'::text); pg_statio_sys_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE (pg_statio_all_tables.relname ~ '^pg_'::text); diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out index 4f388b0e456..4713768e851 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -127,31 +127,20 @@ WHERE (p1.relkind = 'i' AND p1.relam = 0) OR -- **************** pg_attribute **************** -- Look for illegal values in pg_attribute fields -SELECT p1.oid, p1.attrelid, p1.attname +SELECT p1.attrelid, p1.attname FROM pg_attribute as p1 WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR p1.attcacheoff != -1; - oid | attrelid | attname ------+----------+--------- -(0 rows) - --- Look for duplicate pg_attribute entries --- (This would not be necessary if the indexes on pg_attribute were UNIQUE?) -SELECT p1.oid, p1.attname, p2.oid, p2.attname -FROM pg_attribute AS p1, pg_attribute AS p2 -WHERE p1.oid != p2.oid AND - p1.attrelid = p2.attrelid AND - (p1.attname = p2.attname OR p1.attnum = p2.attnum); - oid | attname | oid | attname ------+---------+-----+--------- + attrelid | attname +----------+--------- (0 rows) -- Cross-check attnum against parent relation -SELECT p1.oid, p1.attname, p2.oid, p2.relname +SELECT p1.attrelid, p1.attname, p2.oid, p2.relname FROM pg_attribute AS p1, pg_class AS p2 WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts; - oid | attname | oid | relname ------+---------+-----+--------- + attrelid | attname | oid | relname +----------+---------+-----+--------- (0 rows) -- Detect missing pg_attribute entries: should have as many non-system @@ -165,13 +154,13 @@ WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2 (0 rows) -- Cross-check against pg_type entry -SELECT p1.oid, p1.attname, p2.oid, p2.typname +SELECT p1.attrelid, p1.attname, p2.oid, p2.typname FROM pg_attribute AS p1, pg_type AS p2 WHERE p1.atttypid = p2.oid AND (p1.attlen != p2.typlen OR p1.attalign != p2.typalign OR p1.attbyval != p2.typbyval); - oid | attname | oid | typname ------+---------+-----+--------- + attrelid | attname | oid | typname +----------+---------+-----+--------- (0 rows) diff --git a/src/test/regress/sql/oidjoins.sql b/src/test/regress/sql/oidjoins.sql index 34352128f44..191116318e8 100644 --- a/src/test/regress/sql/oidjoins.sql +++ b/src/test/regress/sql/oidjoins.sql @@ -1,223 +1,227 @@ -- -- This is created by pgsql/contrib/findoidjoins/make_oidjoin_check -- -SELECT oid, pg_aggregate.aggtransfn +SELECT ctid, pg_aggregate.aggtransfn FROM pg_aggregate WHERE pg_aggregate.aggtransfn != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggtransfn); -SELECT oid, pg_aggregate.aggfinalfn +SELECT ctid, pg_aggregate.aggfinalfn FROM pg_aggregate WHERE pg_aggregate.aggfinalfn != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_aggregate.aggfinalfn); -SELECT oid, pg_aggregate.aggbasetype +SELECT ctid, pg_aggregate.aggbasetype FROM pg_aggregate WHERE pg_aggregate.aggbasetype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggbasetype); -SELECT oid, pg_aggregate.aggtranstype +SELECT ctid, pg_aggregate.aggtranstype FROM pg_aggregate WHERE pg_aggregate.aggtranstype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggtranstype); -SELECT oid, pg_aggregate.aggfinaltype +SELECT ctid, pg_aggregate.aggfinaltype FROM pg_aggregate WHERE pg_aggregate.aggfinaltype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_aggregate.aggfinaltype); -SELECT oid, pg_am.amgettuple +SELECT ctid, pg_am.amgettuple FROM pg_am WHERE pg_am.amgettuple != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amgettuple); -SELECT oid, pg_am.aminsert +SELECT ctid, pg_am.aminsert FROM pg_am WHERE pg_am.aminsert != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.aminsert); -SELECT oid, pg_am.ambeginscan +SELECT ctid, pg_am.ambeginscan FROM pg_am WHERE pg_am.ambeginscan != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambeginscan); -SELECT oid, pg_am.amrescan +SELECT ctid, pg_am.amrescan FROM pg_am WHERE pg_am.amrescan != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrescan); -SELECT oid, pg_am.amendscan +SELECT ctid, pg_am.amendscan FROM pg_am WHERE pg_am.amendscan != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amendscan); -SELECT oid, pg_am.ammarkpos +SELECT ctid, pg_am.ammarkpos FROM pg_am WHERE pg_am.ammarkpos != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ammarkpos); -SELECT oid, pg_am.amrestrpos +SELECT ctid, pg_am.amrestrpos FROM pg_am WHERE pg_am.amrestrpos != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amrestrpos); -SELECT oid, pg_am.ambuild +SELECT ctid, pg_am.ambuild FROM pg_am WHERE pg_am.ambuild != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambuild); -SELECT oid, pg_am.ambulkdelete +SELECT ctid, pg_am.ambulkdelete FROM pg_am WHERE pg_am.ambulkdelete != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.ambulkdelete); -SELECT oid, pg_am.amcostestimate +SELECT ctid, pg_am.amcostestimate FROM pg_am WHERE pg_am.amcostestimate != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_am.amcostestimate); -SELECT oid, pg_amop.amopid +SELECT ctid, pg_amop.amopid FROM pg_amop WHERE pg_amop.amopid != 0 AND NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amop.amopid); -SELECT oid, pg_amop.amopclaid +SELECT ctid, pg_amop.amopclaid FROM pg_amop WHERE pg_amop.amopclaid != 0 AND NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amop.amopclaid); -SELECT oid, pg_amop.amopopr +SELECT ctid, pg_amop.amopopr FROM pg_amop WHERE pg_amop.amopopr != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_amop.amopopr); -SELECT oid, pg_amproc.amid +SELECT ctid, pg_amproc.amid FROM pg_amproc WHERE pg_amproc.amid != 0 AND NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_amproc.amid); -SELECT oid, pg_amproc.amopclaid +SELECT ctid, pg_amproc.amopclaid FROM pg_amproc WHERE pg_amproc.amopclaid != 0 AND NOT EXISTS(SELECT * FROM pg_opclass AS t1 WHERE t1.oid = pg_amproc.amopclaid); -SELECT oid, pg_amproc.amproc +SELECT ctid, pg_amproc.amproc FROM pg_amproc WHERE pg_amproc.amproc != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_amproc.amproc); -SELECT oid, pg_attribute.attrelid +SELECT ctid, pg_attribute.attrelid FROM pg_attribute WHERE pg_attribute.attrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_attribute.attrelid); -SELECT oid, pg_attribute.atttypid +SELECT ctid, pg_attribute.atttypid FROM pg_attribute WHERE pg_attribute.atttypid != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_attribute.atttypid); -SELECT oid, pg_class.reltype +SELECT ctid, pg_class.reltype FROM pg_class WHERE pg_class.reltype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_class.reltype); -SELECT oid, pg_class.relam +SELECT ctid, pg_class.relam FROM pg_class WHERE pg_class.relam != 0 AND NOT EXISTS(SELECT * FROM pg_am AS t1 WHERE t1.oid = pg_class.relam); -SELECT oid, pg_class.reltoastrelid +SELECT ctid, pg_class.reltoastrelid FROM pg_class WHERE pg_class.reltoastrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastrelid); -SELECT oid, pg_class.reltoastidxid +SELECT ctid, pg_class.reltoastidxid FROM pg_class WHERE pg_class.reltoastidxid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_class.reltoastidxid); -SELECT oid, pg_index.indexrelid +SELECT ctid, pg_description.classoid +FROM pg_description +WHERE pg_description.classoid != 0 AND + NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_description.classoid); +SELECT ctid, pg_index.indexrelid FROM pg_index WHERE pg_index.indexrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indexrelid); -SELECT oid, pg_index.indrelid +SELECT ctid, pg_index.indrelid FROM pg_index WHERE pg_index.indrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_index.indrelid); -SELECT oid, pg_opclass.opcdeftype +SELECT ctid, pg_opclass.opcdeftype FROM pg_opclass WHERE pg_opclass.opcdeftype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_opclass.opcdeftype); -SELECT oid, pg_operator.oprleft +SELECT ctid, pg_operator.oprleft FROM pg_operator WHERE pg_operator.oprleft != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprleft); -SELECT oid, pg_operator.oprright +SELECT ctid, pg_operator.oprright FROM pg_operator WHERE pg_operator.oprright != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprright); -SELECT oid, pg_operator.oprresult +SELECT ctid, pg_operator.oprresult FROM pg_operator WHERE pg_operator.oprresult != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_operator.oprresult); -SELECT oid, pg_operator.oprcom +SELECT ctid, pg_operator.oprcom FROM pg_operator WHERE pg_operator.oprcom != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprcom); -SELECT oid, pg_operator.oprnegate +SELECT ctid, pg_operator.oprnegate FROM pg_operator WHERE pg_operator.oprnegate != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprnegate); -SELECT oid, pg_operator.oprlsortop +SELECT ctid, pg_operator.oprlsortop FROM pg_operator WHERE pg_operator.oprlsortop != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprlsortop); -SELECT oid, pg_operator.oprrsortop +SELECT ctid, pg_operator.oprrsortop FROM pg_operator WHERE pg_operator.oprrsortop != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_operator.oprrsortop); -SELECT oid, pg_operator.oprcode +SELECT ctid, pg_operator.oprcode FROM pg_operator WHERE pg_operator.oprcode != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprcode); -SELECT oid, pg_operator.oprrest +SELECT ctid, pg_operator.oprrest FROM pg_operator WHERE pg_operator.oprrest != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprrest); -SELECT oid, pg_operator.oprjoin +SELECT ctid, pg_operator.oprjoin FROM pg_operator WHERE pg_operator.oprjoin != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_operator.oprjoin); -SELECT oid, pg_proc.prolang +SELECT ctid, pg_proc.prolang FROM pg_proc WHERE pg_proc.prolang != 0 AND NOT EXISTS(SELECT * FROM pg_language AS t1 WHERE t1.oid = pg_proc.prolang); -SELECT oid, pg_proc.prorettype +SELECT ctid, pg_proc.prorettype FROM pg_proc WHERE pg_proc.prorettype != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_proc.prorettype); -SELECT oid, pg_rewrite.ev_class +SELECT ctid, pg_rewrite.ev_class FROM pg_rewrite WHERE pg_rewrite.ev_class != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_rewrite.ev_class); -SELECT oid, pg_statistic.starelid +SELECT ctid, pg_statistic.starelid FROM pg_statistic WHERE pg_statistic.starelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_statistic.starelid); -SELECT oid, pg_statistic.staop1 +SELECT ctid, pg_statistic.staop1 FROM pg_statistic WHERE pg_statistic.staop1 != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop1); -SELECT oid, pg_statistic.staop2 +SELECT ctid, pg_statistic.staop2 FROM pg_statistic WHERE pg_statistic.staop2 != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop2); -SELECT oid, pg_statistic.staop3 +SELECT ctid, pg_statistic.staop3 FROM pg_statistic WHERE pg_statistic.staop3 != 0 AND NOT EXISTS(SELECT * FROM pg_operator AS t1 WHERE t1.oid = pg_statistic.staop3); -SELECT oid, pg_trigger.tgrelid +SELECT ctid, pg_trigger.tgrelid FROM pg_trigger WHERE pg_trigger.tgrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_trigger.tgrelid); -SELECT oid, pg_trigger.tgfoid +SELECT ctid, pg_trigger.tgfoid FROM pg_trigger WHERE pg_trigger.tgfoid != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_trigger.tgfoid); -SELECT oid, pg_type.typrelid +SELECT ctid, pg_type.typrelid FROM pg_type WHERE pg_type.typrelid != 0 AND NOT EXISTS(SELECT * FROM pg_class AS t1 WHERE t1.oid = pg_type.typrelid); -SELECT oid, pg_type.typelem +SELECT ctid, pg_type.typelem FROM pg_type WHERE pg_type.typelem != 0 AND NOT EXISTS(SELECT * FROM pg_type AS t1 WHERE t1.oid = pg_type.typelem); -SELECT oid, pg_type.typinput +SELECT ctid, pg_type.typinput FROM pg_type WHERE pg_type.typinput != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typinput); -SELECT oid, pg_type.typoutput +SELECT ctid, pg_type.typoutput FROM pg_type WHERE pg_type.typoutput != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typoutput); -SELECT oid, pg_type.typreceive +SELECT ctid, pg_type.typreceive FROM pg_type WHERE pg_type.typreceive != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typreceive); -SELECT oid, pg_type.typsend +SELECT ctid, pg_type.typsend FROM pg_type WHERE pg_type.typsend != 0 AND NOT EXISTS(SELECT * FROM pg_proc AS t1 WHERE t1.oid = pg_type.typsend); diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index 1cb5cd18c92..a1585923300 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -439,23 +439,14 @@ WHERE p1.oid != p2.oid AND -- Look for illegal values in pg_amop fields -SELECT p1.oid +SELECT p1.amopclaid, p1.amopopr, p1.amopid FROM pg_amop as p1 WHERE p1.amopid = 0 OR p1.amopclaid = 0 OR p1.amopopr = 0 OR p1.amopstrategy <= 0; --- Look for duplicate pg_amop entries - -SELECT p1.oid, p2.oid -FROM pg_amop AS p1, pg_amop AS p2 -WHERE p1.oid != p2.oid AND - p1.amopid = p2.amopid AND - p1.amopclaid = p2.amopclaid AND - p1.amopstrategy = p2.amopstrategy; - -- Cross-check amopstrategy index against parent AM -SELECT p1.oid, p2.oid, p2.amname +SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.amname FROM pg_amop AS p1, pg_am AS p2 WHERE p1.amopid = p2.oid AND p1.amopstrategy > p2.amstrategies; @@ -475,14 +466,14 @@ WHERE p1.amstrategies != (SELECT count(*) FROM pg_amop AS p3 -- NOTE: for 7.1, add restriction that operator inputs are of same type. -- We used to have opclasses like "int24_ops" but these were broken. -SELECT p1.oid, p2.oid, p2.oprname +SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname FROM pg_amop AS p1, pg_operator AS p2 WHERE p1.amopopr = p2.oid AND (p2.oprkind != 'b' OR p2.oprresult != 16 OR p2.oprleft != p2.oprright); -- If opclass is for a specific type, operator inputs should be of that type -SELECT p1.oid, p2.oid, p2.oprname, p3.oid, p3.opcname +SELECT p1.amopclaid, p1.amopopr, p1.amopid, p2.oid, p2.oprname, p3.oid, p3.opcname FROM pg_amop AS p1, pg_operator AS p2, pg_opclass AS p3 WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND p3.opcdeftype != 0 AND @@ -492,23 +483,14 @@ WHERE p1.amopopr = p2.oid AND p1.amopclaid = p3.oid AND -- Look for illegal values in pg_amproc fields -SELECT p1.oid +SELECT p1.amid, p1.amopclaid, p1.amprocnum FROM pg_amproc as p1 WHERE p1.amid = 0 OR p1.amopclaid = 0 OR p1.amproc = 0 OR p1.amprocnum <= 0; --- Look for duplicate pg_amproc entries - -SELECT p1.oid, p2.oid -FROM pg_amproc AS p1, pg_amproc AS p2 -WHERE p1.oid != p2.oid AND - p1.amid = p2.amid AND - p1.amopclaid = p2.amopclaid AND - p1.amprocnum = p2.amprocnum; - -- Cross-check amprocnum index against parent AM -SELECT p1.oid, p2.oid, p2.amname +SELECT p1.amid, p1.amopclaid, p1.amprocnum, p2.oid, p2.amname FROM pg_amproc AS p1, pg_am AS p2 WHERE p1.amid = p2.oid AND p1.amprocnum > p2.amsupport; @@ -529,10 +511,12 @@ WHERE p1.amsupport != (SELECT count(*) FROM pg_amproc AS p3 -- We can check that all the referenced instances of the same support -- routine number take the same number of parameters, but that's about it... -SELECT p1.oid, p2.oid, p2.proname, p3.oid, p4.oid, p4.proname +SELECT p1.amid, p1.amopclaid, p1.amprocnum, + p2.oid, p2.proname, + p3.amid, p3.amopclaid, p3.amprocnum, + p4.oid, p4.proname FROM pg_amproc AS p1, pg_proc AS p2, pg_amproc AS p3, pg_proc AS p4 -WHERE p1.oid != p3.oid AND - p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND +WHERE p1.amid = p3.amid AND p1.amprocnum = p3.amprocnum AND p1.amproc = p2.oid AND p3.amproc = p4.oid AND (p2.proretset OR p4.proretset OR p2.pronargs != p4.pronargs); diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql index dbd6d0af409..477696e3217 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -112,23 +112,14 @@ WHERE (p1.relkind = 'i' AND p1.relam = 0) OR -- Look for illegal values in pg_attribute fields -SELECT p1.oid, p1.attrelid, p1.attname +SELECT p1.attrelid, p1.attname FROM pg_attribute as p1 WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR p1.attcacheoff != -1; --- Look for duplicate pg_attribute entries --- (This would not be necessary if the indexes on pg_attribute were UNIQUE?) - -SELECT p1.oid, p1.attname, p2.oid, p2.attname -FROM pg_attribute AS p1, pg_attribute AS p2 -WHERE p1.oid != p2.oid AND - p1.attrelid = p2.attrelid AND - (p1.attname = p2.attname OR p1.attnum = p2.attnum); - -- Cross-check attnum against parent relation -SELECT p1.oid, p1.attname, p2.oid, p2.relname +SELECT p1.attrelid, p1.attname, p2.oid, p2.relname FROM pg_attribute AS p1, pg_class AS p2 WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts; @@ -142,7 +133,7 @@ WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2 -- Cross-check against pg_type entry -SELECT p1.oid, p1.attname, p2.oid, p2.typname +SELECT p1.attrelid, p1.attname, p2.oid, p2.typname FROM pg_attribute AS p1, pg_type AS p2 WHERE p1.atttypid = p2.oid AND (p1.attlen != p2.typlen OR -- cgit v1.2.3