1-- 2-- TYPE_SANITY 3-- Sanity checks for common errors in making type-related system tables: 4-- pg_type, pg_class, pg_attribute, pg_range. 5-- 6-- None of the SELECTs here should ever find any matching entries, 7-- so the expected output is easy to maintain ;-). 8-- A test failure indicates someone messed up an entry in the system tables. 9-- 10-- NB: we assume the oidjoins test will have caught any dangling links, 11-- that is OID or REGPROC fields that are not zero and do not match some 12-- row in the linked-to table. However, if we want to enforce that a link 13-- field can't be 0, we have to check it here. 14-- **************** pg_type **************** 15-- Look for illegal values in pg_type fields. 16SELECT p1.oid, p1.typname 17FROM pg_type as p1 18WHERE p1.typnamespace = 0 OR 19 (p1.typlen <= 0 AND p1.typlen != -1 AND p1.typlen != -2) OR 20 (p1.typtype not in ('b', 'c', 'd', 'e', 'p', 'r')) OR 21 NOT p1.typisdefined OR 22 (p1.typalign not in ('c', 's', 'i', 'd')) OR 23 (p1.typstorage not in ('p', 'x', 'e', 'm')); 24 oid | typname 25-----+--------- 26(0 rows) 27 28-- Look for "pass by value" types that can't be passed by value. 29SELECT p1.oid, p1.typname 30FROM pg_type as p1 31WHERE p1.typbyval AND 32 (p1.typlen != 1 OR p1.typalign != 'c') AND 33 (p1.typlen != 2 OR p1.typalign != 's') AND 34 (p1.typlen != 4 OR p1.typalign != 'i') AND 35 (p1.typlen != 8 OR p1.typalign != 'd'); 36 oid | typname 37-----+--------- 38(0 rows) 39 40-- Look for "toastable" types that aren't varlena. 41SELECT p1.oid, p1.typname 42FROM pg_type as p1 43WHERE p1.typstorage != 'p' AND 44 (p1.typbyval OR p1.typlen != -1); 45 oid | typname 46-----+--------- 47(0 rows) 48 49-- Look for complex types that do not have a typrelid entry, 50-- or basic types that do. 51SELECT p1.oid, p1.typname 52FROM pg_type as p1 53WHERE (p1.typtype = 'c' AND p1.typrelid = 0) OR 54 (p1.typtype != 'c' AND p1.typrelid != 0); 55 oid | typname 56-----+--------- 57(0 rows) 58 59-- Look for types that should have an array type according to their typtype, 60-- but don't. We exclude composites here because we have not bothered to 61-- make array types corresponding to the system catalogs' rowtypes. 62-- NOTE: as of v10, this check finds pg_node_tree, pg_ndistinct, smgr. 63SELECT p1.oid, p1.typname 64FROM pg_type as p1 65WHERE p1.typtype not in ('c','d','p') AND p1.typname NOT LIKE E'\\_%' 66 AND NOT EXISTS 67 (SELECT 1 FROM pg_type as p2 68 WHERE p2.typname = ('_' || p1.typname)::name AND 69 p2.typelem = p1.oid and p1.typarray = p2.oid); 70 oid | typname 71------+----------------- 72 194 | pg_node_tree 73 3361 | pg_ndistinct 74 3402 | pg_dependencies 75 210 | smgr 76(4 rows) 77 78-- Make sure typarray points to a varlena array type of our own base 79SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype, 80 p2.typelem, p2.typlen 81FROM pg_type p1 LEFT JOIN pg_type p2 ON (p1.typarray = p2.oid) 82WHERE p1.typarray <> 0 AND 83 (p2.oid IS NULL OR p2.typelem <> p1.oid OR p2.typlen <> -1); 84 oid | basetype | arraytype | typelem | typlen 85-----+----------+-----------+---------+-------- 86(0 rows) 87 88-- Look for range types that do not have a pg_range entry 89SELECT p1.oid, p1.typname 90FROM pg_type as p1 91WHERE p1.typtype = 'r' AND 92 NOT EXISTS(SELECT 1 FROM pg_range r WHERE rngtypid = p1.oid); 93 oid | typname 94-----+--------- 95(0 rows) 96 97-- Look for range types whose typalign isn't sufficient 98SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign 99FROM pg_type as p1 100 LEFT JOIN pg_range as r ON rngtypid = p1.oid 101 LEFT JOIN pg_type as p2 ON rngsubtype = p2.oid 102WHERE p1.typtype = 'r' AND 103 (p1.typalign != (CASE WHEN p2.typalign = 'd' THEN 'd'::"char" 104 ELSE 'i'::"char" END) 105 OR p2.oid IS NULL); 106 oid | typname | typalign | typname | typalign 107-----+---------+----------+---------+---------- 108(0 rows) 109 110-- Text conversion routines must be provided. 111SELECT p1.oid, p1.typname 112FROM pg_type as p1 113WHERE (p1.typinput = 0 OR p1.typoutput = 0); 114 oid | typname 115-----+--------- 116(0 rows) 117 118-- Check for bogus typinput routines 119SELECT p1.oid, p1.typname, p2.oid, p2.proname 120FROM pg_type AS p1, pg_proc AS p2 121WHERE p1.typinput = p2.oid AND NOT 122 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'cstring'::regtype) OR 123 (p2.pronargs = 2 AND p2.proargtypes[0] = 'cstring'::regtype AND 124 p2.proargtypes[1] = 'oid'::regtype) OR 125 (p2.pronargs = 3 AND p2.proargtypes[0] = 'cstring'::regtype AND 126 p2.proargtypes[1] = 'oid'::regtype AND 127 p2.proargtypes[2] = 'int4'::regtype)); 128 oid | typname | oid | proname 129-----+---------+-----+--------- 130(0 rows) 131 132-- As of 8.0, this check finds refcursor, which is borrowing 133-- other types' I/O routines 134SELECT p1.oid, p1.typname, p2.oid, p2.proname 135FROM pg_type AS p1, pg_proc AS p2 136WHERE p1.typinput = p2.oid AND p1.typtype in ('b', 'p') AND NOT 137 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 138 (p2.prorettype = p1.oid AND NOT p2.proretset) 139ORDER BY 1; 140 oid | typname | oid | proname 141------+-----------+-----+--------- 142 1790 | refcursor | 46 | textin 143(1 row) 144 145-- Varlena array types will point to array_in 146-- Exception as of 8.1: int2vector and oidvector have their own I/O routines 147SELECT p1.oid, p1.typname, p2.oid, p2.proname 148FROM pg_type AS p1, pg_proc AS p2 149WHERE p1.typinput = p2.oid AND 150 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 151 (p2.oid = 'array_in'::regproc) 152ORDER BY 1; 153 oid | typname | oid | proname 154-----+------------+-----+-------------- 155 22 | int2vector | 40 | int2vectorin 156 30 | oidvector | 54 | oidvectorin 157(2 rows) 158 159-- typinput routines should not be volatile 160SELECT p1.oid, p1.typname, p2.oid, p2.proname 161FROM pg_type AS p1, pg_proc AS p2 162WHERE p1.typinput = p2.oid AND p2.provolatile NOT IN ('i', 's'); 163 oid | typname | oid | proname 164-----+---------+-----+--------- 165(0 rows) 166 167-- Composites, domains, enums, ranges should all use the same input routines 168SELECT DISTINCT typtype, typinput 169FROM pg_type AS p1 170WHERE p1.typtype not in ('b', 'p') 171ORDER BY 1; 172 typtype | typinput 173---------+----------- 174 c | record_in 175 d | domain_in 176 e | enum_in 177 r | range_in 178(4 rows) 179 180-- Check for bogus typoutput routines 181-- As of 8.0, this check finds refcursor, which is borrowing 182-- other types' I/O routines 183SELECT p1.oid, p1.typname, p2.oid, p2.proname 184FROM pg_type AS p1, pg_proc AS p2 185WHERE p1.typoutput = p2.oid AND p1.typtype in ('b', 'p') AND NOT 186 (p2.pronargs = 1 AND 187 (p2.proargtypes[0] = p1.oid OR 188 (p2.oid = 'array_out'::regproc AND 189 p1.typelem != 0 AND p1.typlen = -1))) 190ORDER BY 1; 191 oid | typname | oid | proname 192------+-----------+-----+--------- 193 1790 | refcursor | 47 | textout 194(1 row) 195 196SELECT p1.oid, p1.typname, p2.oid, p2.proname 197FROM pg_type AS p1, pg_proc AS p2 198WHERE p1.typoutput = p2.oid AND NOT 199 (p2.prorettype = 'cstring'::regtype AND NOT p2.proretset); 200 oid | typname | oid | proname 201-----+---------+-----+--------- 202(0 rows) 203 204-- typoutput routines should not be volatile 205SELECT p1.oid, p1.typname, p2.oid, p2.proname 206FROM pg_type AS p1, pg_proc AS p2 207WHERE p1.typoutput = p2.oid AND p2.provolatile NOT IN ('i', 's'); 208 oid | typname | oid | proname 209-----+---------+-----+--------- 210(0 rows) 211 212-- Composites, enums, ranges should all use the same output routines 213SELECT DISTINCT typtype, typoutput 214FROM pg_type AS p1 215WHERE p1.typtype not in ('b', 'd', 'p') 216ORDER BY 1; 217 typtype | typoutput 218---------+------------ 219 c | record_out 220 e | enum_out 221 r | range_out 222(3 rows) 223 224-- Domains should have same typoutput as their base types 225SELECT p1.oid, p1.typname, p2.oid, p2.typname 226FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid 227WHERE p1.typtype = 'd' AND p1.typoutput IS DISTINCT FROM p2.typoutput; 228 oid | typname | oid | typname 229-----+---------+-----+--------- 230(0 rows) 231 232-- Check for bogus typreceive routines 233SELECT p1.oid, p1.typname, p2.oid, p2.proname 234FROM pg_type AS p1, pg_proc AS p2 235WHERE p1.typreceive = p2.oid AND NOT 236 ((p2.pronargs = 1 AND p2.proargtypes[0] = 'internal'::regtype) OR 237 (p2.pronargs = 2 AND p2.proargtypes[0] = 'internal'::regtype AND 238 p2.proargtypes[1] = 'oid'::regtype) OR 239 (p2.pronargs = 3 AND p2.proargtypes[0] = 'internal'::regtype AND 240 p2.proargtypes[1] = 'oid'::regtype AND 241 p2.proargtypes[2] = 'int4'::regtype)); 242 oid | typname | oid | proname 243-----+---------+-----+--------- 244(0 rows) 245 246-- As of 7.4, this check finds refcursor, which is borrowing 247-- other types' I/O routines 248SELECT p1.oid, p1.typname, p2.oid, p2.proname 249FROM pg_type AS p1, pg_proc AS p2 250WHERE p1.typreceive = p2.oid AND p1.typtype in ('b', 'p') AND NOT 251 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 252 (p2.prorettype = p1.oid AND NOT p2.proretset) 253ORDER BY 1; 254 oid | typname | oid | proname 255------+-----------+------+---------- 256 1790 | refcursor | 2414 | textrecv 257(1 row) 258 259-- Varlena array types will point to array_recv 260-- Exception as of 8.1: int2vector and oidvector have their own I/O routines 261SELECT p1.oid, p1.typname, p2.oid, p2.proname 262FROM pg_type AS p1, pg_proc AS p2 263WHERE p1.typreceive = p2.oid AND 264 (p1.typelem != 0 AND p1.typlen < 0) AND NOT 265 (p2.oid = 'array_recv'::regproc) 266ORDER BY 1; 267 oid | typname | oid | proname 268-----+------------+------+---------------- 269 22 | int2vector | 2410 | int2vectorrecv 270 30 | oidvector | 2420 | oidvectorrecv 271(2 rows) 272 273-- Suspicious if typreceive doesn't take same number of args as typinput 274SELECT p1.oid, p1.typname, p2.oid, p2.proname, p3.oid, p3.proname 275FROM pg_type AS p1, pg_proc AS p2, pg_proc AS p3 276WHERE p1.typinput = p2.oid AND p1.typreceive = p3.oid AND 277 p2.pronargs != p3.pronargs; 278 oid | typname | oid | proname | oid | proname 279-----+---------+-----+---------+-----+--------- 280(0 rows) 281 282-- typreceive routines should not be volatile 283SELECT p1.oid, p1.typname, p2.oid, p2.proname 284FROM pg_type AS p1, pg_proc AS p2 285WHERE p1.typreceive = p2.oid AND p2.provolatile NOT IN ('i', 's'); 286 oid | typname | oid | proname 287-----+---------+-----+--------- 288(0 rows) 289 290-- Composites, domains, enums, ranges should all use the same receive routines 291SELECT DISTINCT typtype, typreceive 292FROM pg_type AS p1 293WHERE p1.typtype not in ('b', 'p') 294ORDER BY 1; 295 typtype | typreceive 296---------+------------- 297 c | record_recv 298 d | domain_recv 299 e | enum_recv 300 r | range_recv 301(4 rows) 302 303-- Check for bogus typsend routines 304-- As of 7.4, this check finds refcursor, which is borrowing 305-- other types' I/O routines 306SELECT p1.oid, p1.typname, p2.oid, p2.proname 307FROM pg_type AS p1, pg_proc AS p2 308WHERE p1.typsend = p2.oid AND p1.typtype in ('b', 'p') AND NOT 309 (p2.pronargs = 1 AND 310 (p2.proargtypes[0] = p1.oid OR 311 (p2.oid = 'array_send'::regproc AND 312 p1.typelem != 0 AND p1.typlen = -1))) 313ORDER BY 1; 314 oid | typname | oid | proname 315------+-----------+------+---------- 316 1790 | refcursor | 2415 | textsend 317(1 row) 318 319SELECT p1.oid, p1.typname, p2.oid, p2.proname 320FROM pg_type AS p1, pg_proc AS p2 321WHERE p1.typsend = p2.oid AND NOT 322 (p2.prorettype = 'bytea'::regtype AND NOT p2.proretset); 323 oid | typname | oid | proname 324-----+---------+-----+--------- 325(0 rows) 326 327-- typsend routines should not be volatile 328SELECT p1.oid, p1.typname, p2.oid, p2.proname 329FROM pg_type AS p1, pg_proc AS p2 330WHERE p1.typsend = p2.oid AND p2.provolatile NOT IN ('i', 's'); 331 oid | typname | oid | proname 332-----+---------+-----+--------- 333(0 rows) 334 335-- Composites, enums, ranges should all use the same send routines 336SELECT DISTINCT typtype, typsend 337FROM pg_type AS p1 338WHERE p1.typtype not in ('b', 'd', 'p') 339ORDER BY 1; 340 typtype | typsend 341---------+------------- 342 c | record_send 343 e | enum_send 344 r | range_send 345(3 rows) 346 347-- Domains should have same typsend as their base types 348SELECT p1.oid, p1.typname, p2.oid, p2.typname 349FROM pg_type AS p1 LEFT JOIN pg_type AS p2 ON p1.typbasetype = p2.oid 350WHERE p1.typtype = 'd' AND p1.typsend IS DISTINCT FROM p2.typsend; 351 oid | typname | oid | typname 352-----+---------+-----+--------- 353(0 rows) 354 355-- Check for bogus typmodin routines 356SELECT p1.oid, p1.typname, p2.oid, p2.proname 357FROM pg_type AS p1, pg_proc AS p2 358WHERE p1.typmodin = p2.oid AND NOT 359 (p2.pronargs = 1 AND 360 p2.proargtypes[0] = 'cstring[]'::regtype AND 361 p2.prorettype = 'int4'::regtype AND NOT p2.proretset); 362 oid | typname | oid | proname 363-----+---------+-----+--------- 364(0 rows) 365 366-- typmodin routines should not be volatile 367SELECT p1.oid, p1.typname, p2.oid, p2.proname 368FROM pg_type AS p1, pg_proc AS p2 369WHERE p1.typmodin = p2.oid AND p2.provolatile NOT IN ('i', 's'); 370 oid | typname | oid | proname 371-----+---------+-----+--------- 372(0 rows) 373 374-- Check for bogus typmodout routines 375SELECT p1.oid, p1.typname, p2.oid, p2.proname 376FROM pg_type AS p1, pg_proc AS p2 377WHERE p1.typmodout = p2.oid AND NOT 378 (p2.pronargs = 1 AND 379 p2.proargtypes[0] = 'int4'::regtype AND 380 p2.prorettype = 'cstring'::regtype AND NOT p2.proretset); 381 oid | typname | oid | proname 382-----+---------+-----+--------- 383(0 rows) 384 385-- typmodout routines should not be volatile 386SELECT p1.oid, p1.typname, p2.oid, p2.proname 387FROM pg_type AS p1, pg_proc AS p2 388WHERE p1.typmodout = p2.oid AND p2.provolatile NOT IN ('i', 's'); 389 oid | typname | oid | proname 390-----+---------+-----+--------- 391(0 rows) 392 393-- Array types should have same typmodin/out as their element types 394SELECT p1.oid, p1.typname, p2.oid, p2.typname 395FROM pg_type AS p1, pg_type AS p2 396WHERE p1.typelem = p2.oid AND NOT 397 (p1.typmodin = p2.typmodin AND p1.typmodout = p2.typmodout); 398 oid | typname | oid | typname 399-----+---------+-----+--------- 400(0 rows) 401 402-- Array types should have same typdelim as their element types 403SELECT p1.oid, p1.typname, p2.oid, p2.typname 404FROM pg_type AS p1, pg_type AS p2 405WHERE p1.typarray = p2.oid AND NOT (p1.typdelim = p2.typdelim); 406 oid | typname | oid | typname 407-----+---------+-----+--------- 408(0 rows) 409 410-- Look for array types whose typalign isn't sufficient 411SELECT p1.oid, p1.typname, p1.typalign, p2.typname, p2.typalign 412FROM pg_type AS p1, pg_type AS p2 413WHERE p1.typarray = p2.oid AND 414 p2.typalign != (CASE WHEN p1.typalign = 'd' THEN 'd'::"char" 415 ELSE 'i'::"char" END); 416 oid | typname | typalign | typname | typalign 417-----+---------+----------+---------+---------- 418(0 rows) 419 420-- Check for bogus typanalyze routines 421SELECT p1.oid, p1.typname, p2.oid, p2.proname 422FROM pg_type AS p1, pg_proc AS p2 423WHERE p1.typanalyze = p2.oid AND NOT 424 (p2.pronargs = 1 AND 425 p2.proargtypes[0] = 'internal'::regtype AND 426 p2.prorettype = 'bool'::regtype AND NOT p2.proretset); 427 oid | typname | oid | proname 428-----+---------+-----+--------- 429(0 rows) 430 431-- there does not seem to be a reason to care about volatility of typanalyze 432-- domains inherit their base type's typanalyze 433SELECT d.oid, d.typname, d.typanalyze, t.oid, t.typname, t.typanalyze 434FROM pg_type d JOIN pg_type t ON d.typbasetype = t.oid 435WHERE d.typanalyze != t.typanalyze; 436 oid | typname | typanalyze | oid | typname | typanalyze 437-----+---------+------------+-----+---------+------------ 438(0 rows) 439 440-- range_typanalyze should be used for all and only range types 441-- (but exclude domains, which we checked above) 442SELECT t.oid, t.typname, t.typanalyze 443FROM pg_type t LEFT JOIN pg_range r on t.oid = r.rngtypid 444WHERE t.typbasetype = 0 AND 445 (t.typanalyze = 'range_typanalyze'::regproc) != (r.rngtypid IS NOT NULL); 446 oid | typname | typanalyze 447-----+---------+------------ 448(0 rows) 449 450-- array_typanalyze should be used for all and only array types 451-- (but exclude domains, which we checked above) 452-- As of 9.2 this finds int2vector and oidvector, which are weird anyway 453SELECT t.oid, t.typname, t.typanalyze 454FROM pg_type t 455WHERE t.typbasetype = 0 AND 456 (t.typanalyze = 'array_typanalyze'::regproc) != 457 (typelem != 0 AND typlen < 0) 458ORDER BY 1; 459 oid | typname | typanalyze 460-----+------------+------------ 461 22 | int2vector | - 462 30 | oidvector | - 463(2 rows) 464 465-- **************** pg_class **************** 466-- Look for illegal values in pg_class fields 467SELECT p1.oid, p1.relname 468FROM pg_class as p1 469WHERE relkind NOT IN ('r', 'i', 'S', 't', 'v', 'm', 'c', 'f', 'p') OR 470 relpersistence NOT IN ('p', 'u', 't') OR 471 relreplident NOT IN ('d', 'n', 'f', 'i'); 472 oid | relname 473-----+--------- 474(0 rows) 475 476-- Indexes should have an access method, others not. 477SELECT p1.oid, p1.relname 478FROM pg_class as p1 479WHERE (p1.relkind = 'i' AND p1.relam = 0) OR 480 (p1.relkind != 'i' AND p1.relam != 0); 481 oid | relname 482-----+--------- 483(0 rows) 484 485-- **************** pg_attribute **************** 486-- Look for illegal values in pg_attribute fields 487SELECT p1.attrelid, p1.attname 488FROM pg_attribute as p1 489WHERE p1.attrelid = 0 OR p1.atttypid = 0 OR p1.attnum = 0 OR 490 p1.attcacheoff != -1 OR p1.attinhcount < 0 OR 491 (p1.attinhcount = 0 AND NOT p1.attislocal); 492 attrelid | attname 493----------+--------- 494(0 rows) 495 496-- Cross-check attnum against parent relation 497SELECT p1.attrelid, p1.attname, p2.oid, p2.relname 498FROM pg_attribute AS p1, pg_class AS p2 499WHERE p1.attrelid = p2.oid AND p1.attnum > p2.relnatts; 500 attrelid | attname | oid | relname 501----------+---------+-----+--------- 502(0 rows) 503 504-- Detect missing pg_attribute entries: should have as many non-system 505-- attributes as parent relation expects 506SELECT p1.oid, p1.relname 507FROM pg_class AS p1 508WHERE p1.relnatts != (SELECT count(*) FROM pg_attribute AS p2 509 WHERE p2.attrelid = p1.oid AND p2.attnum > 0); 510 oid | relname 511-----+--------- 512(0 rows) 513 514-- Cross-check against pg_type entry 515-- NOTE: we allow attstorage to be 'plain' even when typstorage is not; 516-- this is mainly for toast tables. 517SELECT p1.attrelid, p1.attname, p2.oid, p2.typname 518FROM pg_attribute AS p1, pg_type AS p2 519WHERE p1.atttypid = p2.oid AND 520 (p1.attlen != p2.typlen OR 521 p1.attalign != p2.typalign OR 522 p1.attbyval != p2.typbyval OR 523 (p1.attstorage != p2.typstorage AND p1.attstorage != 'p')); 524 attrelid | attname | oid | typname 525----------+---------+-----+--------- 526(0 rows) 527 528-- **************** pg_range **************** 529-- Look for illegal values in pg_range fields. 530SELECT p1.rngtypid, p1.rngsubtype 531FROM pg_range as p1 532WHERE p1.rngtypid = 0 OR p1.rngsubtype = 0 OR p1.rngsubopc = 0; 533 rngtypid | rngsubtype 534----------+------------ 535(0 rows) 536 537-- rngcollation should be specified iff subtype is collatable 538SELECT p1.rngtypid, p1.rngsubtype, p1.rngcollation, t.typcollation 539FROM pg_range p1 JOIN pg_type t ON t.oid = p1.rngsubtype 540WHERE (rngcollation = 0) != (typcollation = 0); 541 rngtypid | rngsubtype | rngcollation | typcollation 542----------+------------+--------------+-------------- 543(0 rows) 544 545-- opclass had better be a btree opclass accepting the subtype. 546-- We must allow anyarray matches, cf opr_sanity's binary_coercible() 547SELECT p1.rngtypid, p1.rngsubtype, o.opcmethod, o.opcname 548FROM pg_range p1 JOIN pg_opclass o ON o.oid = p1.rngsubopc 549WHERE o.opcmethod != 403 OR 550 ((o.opcintype != p1.rngsubtype) AND NOT 551 (o.opcintype = 'pg_catalog.anyarray'::regtype AND 552 EXISTS(select 1 from pg_catalog.pg_type where 553 oid = p1.rngsubtype and typelem != 0 and typlen = -1))); 554 rngtypid | rngsubtype | opcmethod | opcname 555----------+------------+-----------+--------- 556(0 rows) 557 558-- canonical function, if any, had better match the range type 559SELECT p1.rngtypid, p1.rngsubtype, p.proname 560FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngcanonical 561WHERE pronargs != 1 OR proargtypes[0] != rngtypid OR prorettype != rngtypid; 562 rngtypid | rngsubtype | proname 563----------+------------+--------- 564(0 rows) 565 566-- subdiff function, if any, had better match the subtype 567SELECT p1.rngtypid, p1.rngsubtype, p.proname 568FROM pg_range p1 JOIN pg_proc p ON p.oid = p1.rngsubdiff 569WHERE pronargs != 2 570 OR proargtypes[0] != rngsubtype OR proargtypes[1] != rngsubtype 571 OR prorettype != 'pg_catalog.float8'::regtype; 572 rngtypid | rngsubtype | proname 573----------+------------+--------- 574(0 rows) 575 576