github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/pg_catalog (about) 1 # LogicTest: local 2 3 statement ok 4 SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false 5 6 # Verify pg_catalog database handles mutation statements correctly. 7 8 query error database "pg_catalog" does not exist 9 ALTER DATABASE pg_catalog RENAME TO not_pg_catalog 10 11 statement error schema cannot be modified: "pg_catalog" 12 CREATE TABLE pg_catalog.t (x INT) 13 14 query error database "pg_catalog" does not exist 15 DROP DATABASE pg_catalog 16 17 query TTT 18 SHOW TABLES FROM pg_catalog 19 ---- 20 pg_catalog pg_aggregate table 21 pg_catalog pg_am table 22 pg_catalog pg_attrdef table 23 pg_catalog pg_attribute table 24 pg_catalog pg_auth_members table 25 pg_catalog pg_authid table 26 pg_catalog pg_available_extensions table 27 pg_catalog pg_cast table 28 pg_catalog pg_class table 29 pg_catalog pg_collation table 30 pg_catalog pg_constraint table 31 pg_catalog pg_conversion table 32 pg_catalog pg_database table 33 pg_catalog pg_default_acl table 34 pg_catalog pg_depend table 35 pg_catalog pg_description table 36 pg_catalog pg_enum table 37 pg_catalog pg_event_trigger table 38 pg_catalog pg_extension table 39 pg_catalog pg_foreign_data_wrapper table 40 pg_catalog pg_foreign_server table 41 pg_catalog pg_foreign_table table 42 pg_catalog pg_index table 43 pg_catalog pg_indexes table 44 pg_catalog pg_inherits table 45 pg_catalog pg_language table 46 pg_catalog pg_locks table 47 pg_catalog pg_matviews table 48 pg_catalog pg_namespace table 49 pg_catalog pg_operator table 50 pg_catalog pg_prepared_statements table 51 pg_catalog pg_prepared_xacts table 52 pg_catalog pg_proc table 53 pg_catalog pg_range table 54 pg_catalog pg_rewrite table 55 pg_catalog pg_roles table 56 pg_catalog pg_seclabel table 57 pg_catalog pg_seclabels table 58 pg_catalog pg_sequence table 59 pg_catalog pg_settings table 60 pg_catalog pg_shdepend table 61 pg_catalog pg_shdescription table 62 pg_catalog pg_shseclabel table 63 pg_catalog pg_stat_activity table 64 pg_catalog pg_tables table 65 pg_catalog pg_tablespace table 66 pg_catalog pg_trigger table 67 pg_catalog pg_type table 68 pg_catalog pg_user table 69 pg_catalog pg_user_mapping table 70 pg_catalog pg_views table 71 72 # Verify "pg_catalog" is a regular db name 73 74 statement ok 75 CREATE DATABASE other_db 76 77 statement ok 78 ALTER DATABASE other_db RENAME TO pg_catalog 79 80 statement error database "pg_catalog" already exists 81 CREATE DATABASE pg_catalog 82 83 statement ok 84 DROP DATABASE pg_catalog 85 86 # the following query checks that the planDataSource instantiated from 87 # a virtual table in the FROM clause is properly deallocated even when 88 # query preparation causes an error. #9853 89 query error unknown function 90 SELECT * FROM pg_catalog.pg_class c WHERE nonexistent_function() 91 92 # Verify pg_catalog handles reflection correctly. 93 94 query TTT 95 SHOW TABLES FROM test.pg_catalog 96 ---- 97 pg_catalog pg_aggregate table 98 pg_catalog pg_am table 99 pg_catalog pg_attrdef table 100 pg_catalog pg_attribute table 101 pg_catalog pg_auth_members table 102 pg_catalog pg_authid table 103 pg_catalog pg_available_extensions table 104 pg_catalog pg_cast table 105 pg_catalog pg_class table 106 pg_catalog pg_collation table 107 pg_catalog pg_constraint table 108 pg_catalog pg_conversion table 109 pg_catalog pg_database table 110 pg_catalog pg_default_acl table 111 pg_catalog pg_depend table 112 pg_catalog pg_description table 113 pg_catalog pg_enum table 114 pg_catalog pg_event_trigger table 115 pg_catalog pg_extension table 116 pg_catalog pg_foreign_data_wrapper table 117 pg_catalog pg_foreign_server table 118 pg_catalog pg_foreign_table table 119 pg_catalog pg_index table 120 pg_catalog pg_indexes table 121 pg_catalog pg_inherits table 122 pg_catalog pg_language table 123 pg_catalog pg_locks table 124 pg_catalog pg_matviews table 125 pg_catalog pg_namespace table 126 pg_catalog pg_operator table 127 pg_catalog pg_prepared_statements table 128 pg_catalog pg_prepared_xacts table 129 pg_catalog pg_proc table 130 pg_catalog pg_range table 131 pg_catalog pg_rewrite table 132 pg_catalog pg_roles table 133 pg_catalog pg_seclabel table 134 pg_catalog pg_seclabels table 135 pg_catalog pg_sequence table 136 pg_catalog pg_settings table 137 pg_catalog pg_shdepend table 138 pg_catalog pg_shdescription table 139 pg_catalog pg_shseclabel table 140 pg_catalog pg_stat_activity table 141 pg_catalog pg_tables table 142 pg_catalog pg_tablespace table 143 pg_catalog pg_trigger table 144 pg_catalog pg_type table 145 pg_catalog pg_user table 146 pg_catalog pg_user_mapping table 147 pg_catalog pg_views table 148 149 query TT colnames 150 SHOW CREATE TABLE pg_catalog.pg_namespace 151 ---- 152 table_name create_statement 153 pg_catalog.pg_namespace CREATE TABLE pg_namespace ( 154 oid OID NULL, 155 nspname NAME NOT NULL, 156 nspowner OID NULL, 157 nspacl STRING[] NULL 158 ) 159 160 query TTBTTTB colnames 161 SHOW COLUMNS FROM pg_catalog.pg_namespace 162 ---- 163 column_name data_type is_nullable column_default generation_expression indices is_hidden 164 oid OID true NULL · {} false 165 nspname NAME false NULL · {} false 166 nspowner OID true NULL · {} false 167 nspacl STRING[] true NULL · {} false 168 169 query TTBITTBB colnames 170 SHOW INDEXES FROM pg_catalog.pg_namespace 171 ---- 172 table_name index_name non_unique seq_in_index column_name direction storing implicit 173 174 query TTTTB colnames 175 SHOW CONSTRAINTS FROM pg_catalog.pg_namespace 176 ---- 177 table_name constraint_name constraint_type details validated 178 179 query TTTTT colnames 180 SHOW GRANTS ON pg_catalog.pg_namespace 181 ---- 182 database_name schema_name table_name grantee privilege_type 183 test pg_catalog pg_namespace public SELECT 184 185 186 # Verify selecting from pg_catalog. 187 188 statement ok 189 CREATE DATABASE constraint_db 190 191 statement ok 192 CREATE TABLE constraint_db.t1 ( 193 p FLOAT PRIMARY KEY, 194 a INT UNIQUE, 195 b INT, 196 c INT DEFAULT 12, 197 d VARCHAR(5), 198 e BIT(5), 199 f DECIMAL(10,7), 200 UNIQUE INDEX index_key(b, c) 201 ) 202 203 statement ok 204 CREATE TABLE constraint_db.t2 ( 205 t1_ID INT, 206 CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_db.t1(a), 207 INDEX (t1_ID) 208 ) 209 210 statement ok 211 CREATE TABLE constraint_db.t3 ( 212 a INT, 213 b INT CHECK (b > 11), 214 c STRING DEFAULT 'FOO', 215 CONSTRAINT fk FOREIGN KEY (a, b) REFERENCES constraint_db.t1(b, c), 216 INDEX (a, b DESC) STORING (c) 217 ) 218 219 statement ok 220 CREATE VIEW constraint_db.v1 AS SELECT p,a,b,c FROM constraint_db.t1 221 222 ## pg_catalog.pg_namespace 223 224 query OTOT colnames 225 SELECT * FROM pg_catalog.pg_namespace 226 ---- 227 oid nspname nspowner nspacl 228 3604332469 crdb_internal NULL NULL 229 3672231114 information_schema NULL NULL 230 2508829085 pg_catalog NULL NULL 231 1841002695 pg_extension NULL NULL 232 3426283741 public NULL NULL 233 234 ## pg_catalog.pg_database 235 236 query OTOITTBB colnames 237 SELECT oid, datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn 238 FROM pg_catalog.pg_database 239 ORDER BY oid 240 ---- 241 oid datname datdba encoding datcollate datctype datistemplate datallowconn 242 1 system NULL 6 en_US.utf8 en_US.utf8 false true 243 50 defaultdb NULL 6 en_US.utf8 en_US.utf8 false true 244 51 postgres NULL 6 en_US.utf8 en_US.utf8 false true 245 52 test NULL 6 en_US.utf8 en_US.utf8 false true 246 54 constraint_db NULL 6 en_US.utf8 en_US.utf8 false true 247 248 query OTIOIIOT colnames 249 SELECT oid, datname, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl 250 FROM pg_catalog.pg_database 251 ORDER BY oid 252 ---- 253 oid datname datconnlimit datlastsysoid datfrozenxid datminmxid dattablespace datacl 254 1 system -1 0 NULL NULL 0 NULL 255 50 defaultdb -1 0 NULL NULL 0 NULL 256 51 postgres -1 0 NULL NULL 0 NULL 257 52 test -1 0 NULL NULL 0 NULL 258 54 constraint_db -1 0 NULL NULL 0 NULL 259 260 user testuser 261 262 # Should be globally visible 263 query OTIOIIOT colnames 264 SELECT oid, datname, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl 265 FROM pg_catalog.pg_database 266 ORDER BY oid LIMIT 1 267 ---- 268 oid datname datconnlimit datlastsysoid datfrozenxid datminmxid dattablespace datacl 269 1 system -1 0 NULL NULL 0 NULL 270 271 user root 272 273 ## pg_catalog.pg_tables 274 275 statement ok 276 SET DATABASE = constraint_db 277 278 query TTTTBBBB colnames 279 SELECT * FROM constraint_db.pg_catalog.pg_tables WHERE schemaname = 'public' 280 ---- 281 schemaname tablename tableowner tablespace hasindexes hasrules hastriggers rowsecurity 282 public t1 NULL NULL true false false false 283 public t2 NULL NULL true false false false 284 public t3 NULL NULL true false false false 285 286 query TB colnames 287 SELECT tablename, hasindexes FROM pg_catalog.pg_tables WHERE schemaname = 'information_schema' AND tablename LIKE '%table%' 288 ---- 289 tablename hasindexes 290 role_table_grants false 291 table_constraints false 292 table_privileges false 293 tables false 294 295 ## pg_catalog.pg_tablespace 296 297 query OTOTT colnames 298 SELECT oid, spcname, spcowner, spcacl, spcoptions FROM pg_tablespace 299 ---- 300 oid spcname spcowner spcacl spcoptions 301 0 pg_default NULL NULL NULL 302 303 ## pg_catalog.pg_views 304 305 query TTTT colnames 306 SELECT * FROM pg_catalog.pg_views 307 ---- 308 schemaname viewname viewowner definition 309 public v1 NULL SELECT p, a, b, c FROM constraint_db.public.t1 310 311 ## pg_catalog.pg_class 312 313 query OTOOOOOOO colnames 314 SELECT c.oid, relname, relnamespace, reltype, reloftype, relowner, relam, relfilenode, reltablespace 315 FROM pg_catalog.pg_class c 316 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 317 WHERE n.nspname = 'public' 318 ---- 319 oid relname relnamespace reltype reloftype relowner relam relfilenode reltablespace 320 55 t1 2332901747 0 0 NULL 2631952481 0 0 321 450499963 primary 2332901747 0 0 NULL 2631952481 0 0 322 450499960 t1_a_key 2332901747 0 0 NULL 2631952481 0 0 323 450499961 index_key 2332901747 0 0 NULL 2631952481 0 0 324 56 t2 2332901747 0 0 NULL 2631952481 0 0 325 2315049508 primary 2332901747 0 0 NULL 2631952481 0 0 326 2315049511 t2_t1_id_idx 2332901747 0 0 NULL 2631952481 0 0 327 57 t3 2332901747 0 0 NULL 2631952481 0 0 328 969972501 primary 2332901747 0 0 NULL 2631952481 0 0 329 969972502 t3_a_b_idx 2332901747 0 0 NULL 2631952481 0 0 330 58 v1 2332901747 0 0 NULL 0 0 0 331 332 query TIRIOBBT colnames 333 SELECT relname, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence 334 FROM pg_catalog.pg_class c 335 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 336 WHERE n.nspname = 'public' 337 ---- 338 relname relpages reltuples relallvisible reltoastrelid relhasindex relisshared relpersistence 339 t1 NULL NULL 0 0 true false p 340 primary NULL NULL 0 0 false false p 341 t1_a_key NULL NULL 0 0 false false p 342 index_key NULL NULL 0 0 false false p 343 t2 NULL NULL 0 0 true false p 344 primary NULL NULL 0 0 false false p 345 t2_t1_id_idx NULL NULL 0 0 false false p 346 t3 NULL NULL 0 0 true false p 347 primary NULL NULL 0 0 false false p 348 t3_a_b_idx NULL NULL 0 0 false false p 349 v1 NULL NULL 0 0 false false p 350 351 query TBTIIBB colnames 352 SELECT relname, relistemp, relkind, relnatts, relchecks, relhasoids, relhaspkey 353 FROM pg_catalog.pg_class c 354 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 355 WHERE n.nspname = 'public' 356 ---- 357 relname relistemp relkind relnatts relchecks relhasoids relhaspkey 358 t1 false r 7 0 false true 359 primary false i 1 0 false false 360 t1_a_key false i 1 0 false false 361 index_key false i 2 0 false false 362 t2 false r 2 0 false true 363 primary false i 1 0 false false 364 t2_t1_id_idx false i 1 0 false false 365 t3 false r 4 1 false true 366 primary false i 1 0 false false 367 t3_a_b_idx false i 2 0 false false 368 v1 false v 4 0 false false 369 370 query TBBBITT colnames 371 SELECT relname, relhasrules, relhastriggers, relhassubclass, relfrozenxid, relacl, reloptions 372 FROM pg_catalog.pg_class c 373 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 374 WHERE n.nspname = 'public' 375 ---- 376 relname relhasrules relhastriggers relhassubclass relfrozenxid relacl reloptions 377 t1 false false false 0 NULL NULL 378 primary false false false 0 NULL NULL 379 t1_a_key false false false 0 NULL NULL 380 index_key false false false 0 NULL NULL 381 t2 false false false 0 NULL NULL 382 primary false false false 0 NULL NULL 383 t2_t1_id_idx false false false 0 NULL NULL 384 t3 false false false 0 NULL NULL 385 primary false false false 0 NULL NULL 386 t3_a_b_idx false false false 0 NULL NULL 387 v1 false false false 0 NULL NULL 388 389 ## pg_catalog.pg_attribute 390 391 query OTTOIIIII colnames 392 SELECT attrelid, c.relname, attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff 393 FROM pg_catalog.pg_attribute a 394 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 395 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 396 WHERE n.nspname = 'public' 397 ---- 398 attrelid relname attname atttypid attstattarget attlen attnum attndims attcacheoff 399 55 t1 p 701 0 8 1 0 -1 400 55 t1 a 20 0 8 2 0 -1 401 55 t1 b 20 0 8 3 0 -1 402 55 t1 c 20 0 8 4 0 -1 403 55 t1 d 1043 0 -1 5 0 -1 404 55 t1 e 1560 0 -1 6 0 -1 405 55 t1 f 1700 0 -1 7 0 -1 406 450499963 primary p 701 0 8 1 0 -1 407 450499960 t1_a_key a 20 0 8 2 0 -1 408 450499961 index_key b 20 0 8 3 0 -1 409 450499961 index_key c 20 0 8 4 0 -1 410 56 t2 t1_id 20 0 8 1 0 -1 411 56 t2 rowid 20 0 8 2 0 -1 412 2315049508 primary rowid 20 0 8 2 0 -1 413 2315049511 t2_t1_id_idx t1_id 20 0 8 1 0 -1 414 57 t3 a 20 0 8 1 0 -1 415 57 t3 b 20 0 8 2 0 -1 416 57 t3 c 25 0 -1 3 0 -1 417 57 t3 rowid 20 0 8 4 0 -1 418 969972501 primary rowid 20 0 8 4 0 -1 419 969972502 t3_a_b_idx a 20 0 8 1 0 -1 420 969972502 t3_a_b_idx b 20 0 8 2 0 -1 421 58 v1 p 701 0 8 1 0 -1 422 58 v1 a 20 0 8 2 0 -1 423 58 v1 b 20 0 8 3 0 -1 424 58 v1 c 20 0 8 4 0 -1 425 426 query TTIBTTBB colnames 427 SELECT c.relname, attname, atttypmod, attbyval, attstorage, attalign, attnotnull, atthasdef 428 FROM pg_catalog.pg_attribute a 429 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 430 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 431 WHERE n.nspname = 'public' 432 ---- 433 relname attname atttypmod attbyval attstorage attalign attnotnull atthasdef 434 t1 p -1 NULL NULL NULL true false 435 t1 a -1 NULL NULL NULL false false 436 t1 b -1 NULL NULL NULL false false 437 t1 c -1 NULL NULL NULL false true 438 t1 d 9 NULL NULL NULL false false 439 t1 e 5 NULL NULL NULL false false 440 t1 f 655371 NULL NULL NULL false false 441 primary p -1 NULL NULL NULL true false 442 t1_a_key a -1 NULL NULL NULL false false 443 index_key b -1 NULL NULL NULL false false 444 index_key c -1 NULL NULL NULL false true 445 t2 t1_id -1 NULL NULL NULL false false 446 t2 rowid -1 NULL NULL NULL true true 447 primary rowid -1 NULL NULL NULL true true 448 t2_t1_id_idx t1_id -1 NULL NULL NULL false false 449 t3 a -1 NULL NULL NULL false false 450 t3 b -1 NULL NULL NULL false false 451 t3 c -1 NULL NULL NULL false true 452 t3 rowid -1 NULL NULL NULL true true 453 primary rowid -1 NULL NULL NULL true true 454 t3_a_b_idx a -1 NULL NULL NULL false false 455 t3_a_b_idx b -1 NULL NULL NULL false false 456 v1 p -1 NULL NULL NULL true false 457 v1 a -1 NULL NULL NULL true false 458 v1 b -1 NULL NULL NULL true false 459 v1 c -1 NULL NULL NULL true false 460 461 query TTBBITTT colnames 462 SELECT c.relname, attname, attisdropped, attislocal, attinhcount, attacl, attoptions, attfdwoptions 463 FROM pg_catalog.pg_attribute a 464 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 465 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 466 WHERE n.nspname = 'public' 467 ---- 468 relname attname attisdropped attislocal attinhcount attacl attoptions attfdwoptions 469 t1 p false true 0 NULL NULL NULL 470 t1 a false true 0 NULL NULL NULL 471 t1 b false true 0 NULL NULL NULL 472 t1 c false true 0 NULL NULL NULL 473 t1 d false true 0 NULL NULL NULL 474 t1 e false true 0 NULL NULL NULL 475 t1 f false true 0 NULL NULL NULL 476 primary p false true 0 NULL NULL NULL 477 t1_a_key a false true 0 NULL NULL NULL 478 index_key b false true 0 NULL NULL NULL 479 index_key c false true 0 NULL NULL NULL 480 t2 t1_id false true 0 NULL NULL NULL 481 t2 rowid false true 0 NULL NULL NULL 482 primary rowid false true 0 NULL NULL NULL 483 t2_t1_id_idx t1_id false true 0 NULL NULL NULL 484 t3 a false true 0 NULL NULL NULL 485 t3 b false true 0 NULL NULL NULL 486 t3 c false true 0 NULL NULL NULL 487 t3 rowid false true 0 NULL NULL NULL 488 primary rowid false true 0 NULL NULL NULL 489 t3_a_b_idx a false true 0 NULL NULL NULL 490 t3_a_b_idx b false true 0 NULL NULL NULL 491 v1 p false true 0 NULL NULL NULL 492 v1 a false true 0 NULL NULL NULL 493 v1 b false true 0 NULL NULL NULL 494 v1 c false true 0 NULL NULL NULL 495 496 # Check relkind codes. 497 statement ok 498 CREATE DATABASE relkinds 499 500 statement ok 501 SET DATABASE = relkinds 502 503 statement ok 504 CREATE TABLE tbl_test (k int primary key, v int) 505 506 statement ok 507 CREATE INDEX tbl_test_v_idx ON tbl_test (v) 508 509 statement ok 510 CREATE VIEW view_test AS SELECT k, v FROM tbl_test ORDER BY v 511 512 statement ok 513 CREATE SEQUENCE seq_test 514 515 query TT 516 SELECT relname, relkind 517 FROM pg_catalog.pg_class c 518 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 519 WHERE n.nspname = 'public' 520 ORDER BY relname 521 ---- 522 primary i 523 seq_test S 524 tbl_test r 525 tbl_test_v_idx i 526 view_test v 527 528 statement ok 529 DROP DATABASE relkinds 530 531 statement ok 532 SET DATABASE = constraint_db 533 534 # Select all columns with collations. 535 query TTTOT colnames 536 SELECT c.relname, attname, t.typname, attcollation, k.collname 537 FROM pg_catalog.pg_attribute a 538 JOIN pg_catalog.pg_class c ON a.attrelid = c.oid 539 JOIN pg_catalog.pg_type t ON a.atttypid = t.oid 540 JOIN pg_catalog.pg_collation k ON a.attcollation = k.oid 541 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 542 WHERE n.nspname = 'public' 543 ---- 544 relname attname typname attcollation collname 545 t1 d varchar 3903121477 en-US 546 t3 c text 3903121477 en-US 547 548 549 ## pg_catalog.pg_am 550 551 query OTIIBBBBBBBBBBBOOOOOOOOOOOOOOOOOT colnames 552 SELECT * 553 FROM pg_catalog.pg_am 554 ---- 555 oid amname amstrategies amsupport amcanorder amcanorderbyop amcanbackward amcanunique amcanmulticol amoptionalkey amsearcharray amsearchnulls amstorage amclusterable ampredlocks amkeytype aminsert ambeginscan amgettuple amgetbitmap amrescan amendscan ammarkpos amrestrpos ambuild ambuildempty ambulkdelete amvacuumcleanup amcanreturn amcostestimate amoptions amhandler amtype 556 2631952481 prefix 0 0 true false true true true true true true false false false 0 NULL NULL 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL i 557 4004609370 inverted 0 0 false false false false false false false true false false false 0 NULL NULL 0 0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL i 558 559 ## pg_catalog.pg_attrdef 560 561 query OTOITT colnames 562 SELECT ad.oid, c.relname, adrelid, adnum, adbin, adsrc 563 FROM pg_catalog.pg_attrdef ad 564 JOIN pg_catalog.pg_class c ON ad.adrelid = c.oid 565 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 566 WHERE n.nspname = 'public' 567 ---- 568 oid relname adrelid adnum adbin adsrc 569 1666782879 t1 55 4 12 12 570 841178406 t2 56 2 unique_rowid() unique_rowid() 571 2186255414 t3 57 3 'FOO'::STRING 'FOO'::STRING 572 2186255409 t3 57 4 unique_rowid() unique_rowid() 573 574 ## pg_catalog.pg_indexes 575 576 query OTTTT colnames 577 SELECT crdb_oid, schemaname, tablename, indexname, tablespace 578 FROM pg_catalog.pg_indexes 579 WHERE schemaname = 'public' 580 ---- 581 crdb_oid schemaname tablename indexname tablespace 582 450499963 public t1 primary NULL 583 450499960 public t1 t1_a_key NULL 584 450499961 public t1 index_key NULL 585 2315049508 public t2 primary NULL 586 2315049511 public t2 t2_t1_id_idx NULL 587 969972501 public t3 primary NULL 588 969972502 public t3 t3_a_b_idx NULL 589 590 query OTTT colnames 591 SELECT crdb_oid, tablename, indexname, indexdef 592 FROM pg_catalog.pg_indexes 593 WHERE schemaname = 'public' 594 ---- 595 crdb_oid tablename indexname indexdef 596 450499963 t1 primary CREATE UNIQUE INDEX "primary" ON constraint_db.public.t1 USING btree (p ASC) 597 450499960 t1 t1_a_key CREATE UNIQUE INDEX t1_a_key ON constraint_db.public.t1 USING btree (a ASC) 598 450499961 t1 index_key CREATE UNIQUE INDEX index_key ON constraint_db.public.t1 USING btree (b ASC, c ASC) 599 2315049508 t2 primary CREATE UNIQUE INDEX "primary" ON constraint_db.public.t2 USING btree (rowid ASC) 600 2315049511 t2 t2_t1_id_idx CREATE INDEX t2_t1_id_idx ON constraint_db.public.t2 USING btree (t1_id ASC) 601 969972501 t3 primary CREATE UNIQUE INDEX "primary" ON constraint_db.public.t3 USING btree (rowid ASC) 602 969972502 t3 t3_a_b_idx CREATE INDEX t3_a_b_idx ON constraint_db.public.t3 USING btree (a ASC, b DESC) STORING (c) 603 604 ## pg_catalog.pg_index 605 606 query OOIBBB colnames 607 SELECT indexrelid, indrelid, indnatts, indisunique, indisprimary, indisexclusion 608 FROM pg_catalog.pg_index 609 WHERE indnatts = 2 610 ---- 611 indexrelid indrelid indnatts indisunique indisprimary indisexclusion 612 450499961 55 2 true false false 613 969972502 57 2 false false false 614 615 query OBBBBB colnames 616 SELECT indexrelid, indimmediate, indisclustered, indisvalid, indcheckxmin, indisready 617 FROM pg_catalog.pg_index 618 WHERE indnatts = 2 619 ---- 620 indexrelid indimmediate indisclustered indisvalid indcheckxmin indisready 621 450499961 true false true false false 622 969972502 false false true false false 623 624 query OOBBTTTTTT colnames 625 SELECT indexrelid, indrelid, indislive, indisreplident, indkey, indcollation, indclass, indoption, indexprs, indpred 626 FROM pg_catalog.pg_index 627 WHERE indnatts = 2 628 ---- 629 indexrelid indrelid indislive indisreplident indkey indcollation indclass indoption indexprs indpred 630 450499961 55 true false 3 4 0 0 0 0 2 2 NULL NULL 631 969972502 57 true false 1 2 0 0 0 0 2 1 NULL NULL 632 633 statement ok 634 SET DATABASE = system 635 636 query OOIBBBBBBBBBBTTTTTT colnames 637 SELECT * 638 FROM pg_catalog.pg_index 639 ORDER BY indexrelid 640 ---- 641 indexrelid indrelid indnatts indisunique indisprimary indisexclusion indimmediate indisclustered indisvalid indcheckxmin indisready indislive indisreplident indkey indcollation indclass indoption indexprs indpred 642 144368028 32 1 true true false true false true false false true false 1 0 0 2 NULL NULL 643 543291288 23 1 false false false false false true false false true false 1 3903121477 0 2 NULL NULL 644 543291289 23 1 false false false false false true false false true false 2 3903121477 0 2 NULL NULL 645 543291291 23 2 true true false true false true false false true false 1 2 3903121477 3903121477 0 0 2 2 NULL NULL 646 803027558 26 3 true true false true false true false false true false 1 2 3 0 0 3903121477 0 0 0 2 2 2 NULL NULL 647 1062763829 25 4 true true false true false true false false true false 1 2 3 4 0 0 3903121477 3903121477 0 0 0 0 2 2 2 2 NULL NULL 648 1276104432 12 2 true true false true false true false false true false 1 6 0 0 0 0 2 2 NULL NULL 649 1322500096 28 1 true true false true false true false false true false 1 0 0 2 NULL NULL 650 1489445036 35 2 false false false false false true false false true false 2 1 0 0 0 0 2 2 NULL NULL 651 1489445039 35 1 true true false true false true false false true false 1 0 0 2 NULL NULL 652 1582236367 3 1 true true false true false true false false true false 1 0 0 2 NULL NULL 653 1628632028 19 1 false false false false false true false false true false 5 0 0 2 NULL NULL 654 1628632029 19 1 false false false false false true false false true false 4 0 0 2 NULL NULL 655 1628632031 19 1 true true false true false true false false true false 1 0 0 2 NULL NULL 656 1841972634 6 1 true true false true false true false false true false 1 3903121477 0 2 NULL NULL 657 2101708905 5 1 true true false true false true false false true false 1 0 0 2 NULL NULL 658 2148104569 21 2 true true false true false true false false true false 1 2 3903121477 3903121477 0 0 2 2 NULL NULL 659 2361445172 8 1 true true false true false true false false true false 1 0 0 2 NULL NULL 660 2407840836 24 3 true true false true false true false false true false 1 2 3 0 0 0 0 0 0 2 2 2 NULL NULL 661 2621181440 15 2 false false false false false true false false true false 2 3 3903121477 0 0 0 2 2 NULL NULL 662 2621181441 15 2 false false false false false true false false true false 6 7 3903121477 0 0 0 2 2 NULL NULL 663 2621181443 15 1 true true false true false true false false true false 1 0 0 2 NULL NULL 664 2667577107 31 1 true true false true false true false false true false 1 0 0 2 NULL NULL 665 2834522046 34 1 true true false true false true false false true false 1 0 0 2 NULL NULL 666 2927313374 2 2 true true false true false true false false true false 1 2 0 3903121477 0 0 2 2 NULL NULL 667 3094258317 33 2 true true false true false true false false true false 1 2 3903121477 3903121477 0 0 2 2 NULL NULL 668 3353994584 36 1 true true false true false true false false true false 1 0 0 2 NULL NULL 669 3446785912 4 1 true true false true false true false false true false 1 3903121477 0 2 NULL NULL 670 3493181576 20 2 true true false true false true false false true false 1 2 0 0 0 0 2 2 NULL NULL 671 3706522183 11 4 true true false true false true false false true false 1 2 4 3 0 0 0 0 0 0 0 0 2 2 2 2 NULL NULL 672 3752917847 27 2 true true false true false true false false true false 1 2 0 0 0 0 2 2 NULL NULL 673 3966258450 14 1 true true false true false true false false true false 1 3903121477 0 2 NULL NULL 674 4012654114 30 3 true true false true false true false false true false 1 2 3 0 0 3903121477 0 0 0 2 2 2 NULL NULL 675 4225994721 13 2 true true false true false true false false true false 1 7 0 0 0 0 2 2 NULL NULL 676 677 # From #26504 678 query OOI colnames 679 SELECT indexrelid, 680 (information_schema._pg_expandarray(indclass)).x AS operator_argument_type_oid, 681 (information_schema._pg_expandarray(indclass)).n AS operator_argument_position 682 FROM pg_index 683 ORDER BY indexrelid, operator_argument_position 684 ---- 685 indexrelid operator_argument_type_oid operator_argument_position 686 144368028 0 1 687 543291288 0 1 688 543291289 0 1 689 543291291 0 1 690 543291291 0 2 691 803027558 0 1 692 803027558 0 2 693 803027558 0 3 694 1062763829 0 1 695 1062763829 0 2 696 1062763829 0 3 697 1062763829 0 4 698 1276104432 0 1 699 1276104432 0 2 700 1322500096 0 1 701 1489445036 0 1 702 1489445036 0 2 703 1489445039 0 1 704 1582236367 0 1 705 1628632028 0 1 706 1628632029 0 1 707 1628632031 0 1 708 1841972634 0 1 709 2101708905 0 1 710 2148104569 0 1 711 2148104569 0 2 712 2361445172 0 1 713 2407840836 0 1 714 2407840836 0 2 715 2407840836 0 3 716 2621181440 0 1 717 2621181440 0 2 718 2621181441 0 1 719 2621181441 0 2 720 2621181443 0 1 721 2667577107 0 1 722 2834522046 0 1 723 2927313374 0 1 724 2927313374 0 2 725 3094258317 0 1 726 3094258317 0 2 727 3353994584 0 1 728 3446785912 0 1 729 3493181576 0 1 730 3493181576 0 2 731 3706522183 0 1 732 3706522183 0 2 733 3706522183 0 3 734 3706522183 0 4 735 3752917847 0 1 736 3752917847 0 2 737 3966258450 0 1 738 4012654114 0 1 739 4012654114 0 2 740 4012654114 0 3 741 4225994721 0 1 742 4225994721 0 2 743 744 ## pg_catalog.pg_collation 745 746 statement ok 747 SET DATABASE = constraint_db 748 749 query OTOOITT colnames 750 SELECT * FROM pg_collation 751 WHERE collname='en-US' 752 ---- 753 oid collname collnamespace collowner collencoding collcollate collctype 754 3903121477 en-US 1307062959 NULL 6 NULL NULL 755 756 user testuser 757 758 # Should be globally visible 759 query OT colnames 760 SELECT oid, collname FROM pg_collation 761 WHERE collname='en-US' 762 ---- 763 oid collname 764 3903121477 en-US 765 766 user root 767 768 ## pg_catalog.pg_constraint 769 ## 770 ## These order of this virtual table is non-deterministic, so all queries must 771 ## explicitly add an ORDER BY clause. 772 773 query OTOT colnames 774 SELECT con.oid, conname, connamespace, contype 775 FROM pg_catalog.pg_constraint con 776 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 777 WHERE n.nspname = 'public' 778 ORDER BY con.oid 779 ---- 780 oid conname connamespace contype 781 2143281868 fk 2332901747 f 782 2792001267 check_b 2332901747 c 783 3572320190 primary 2332901747 p 784 4089604113 fk 2332901747 f 785 4243354484 t1_a_key 2332901747 u 786 4243354485 index_key 2332901747 u 787 788 query TTBBBOOO colnames 789 SELECT conname, contype, condeferrable, condeferred, convalidated, conrelid, contypid, conindid 790 FROM pg_catalog.pg_constraint con 791 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 792 WHERE n.nspname = 'public' 793 ORDER BY con.oid 794 ---- 795 conname contype condeferrable condeferred convalidated conrelid contypid conindid 796 fk f false false true 57 0 450499961 797 check_b c false false true 57 0 0 798 primary p false false true 55 0 450499963 799 fk f false false true 56 0 450499960 800 t1_a_key u false false true 55 0 450499960 801 index_key u false false true 55 0 450499961 802 803 query T 804 SELECT conname 805 FROM pg_catalog.pg_constraint con 806 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 807 WHERE n.nspname = 'public' AND contype NOT IN ('c', 'f', 'p', 'u') 808 ORDER BY con.oid 809 ---- 810 811 query TOTTT colnames 812 SELECT conname, confrelid, confupdtype, confdeltype, confmatchtype 813 FROM pg_catalog.pg_constraint con 814 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 815 WHERE n.nspname = 'public' AND contype IN ('c', 'p', 'u') 816 ORDER BY con.oid 817 ---- 818 conname confrelid confupdtype confdeltype confmatchtype 819 check_b 0 NULL NULL NULL 820 primary 0 NULL NULL NULL 821 t1_a_key 0 NULL NULL NULL 822 index_key 0 NULL NULL NULL 823 824 query TOTTT colnames 825 SELECT conname, confrelid, confupdtype, confdeltype, confmatchtype 826 FROM pg_catalog.pg_constraint con 827 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 828 WHERE n.nspname = 'public' AND contype = 'f' 829 ORDER BY con.oid 830 ---- 831 conname confrelid confupdtype confdeltype confmatchtype 832 fk 55 a a s 833 fk 55 a a s 834 835 query TBIBT colnames 836 SELECT conname, conislocal, coninhcount, connoinherit, conkey 837 FROM pg_catalog.pg_constraint con 838 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 839 WHERE n.nspname = 'public' 840 ORDER BY con.oid 841 ---- 842 conname conislocal coninhcount connoinherit conkey 843 fk true 0 true {1,2} 844 check_b true 0 true {2} 845 primary true 0 true {1} 846 fk true 0 true {1} 847 t1_a_key true 0 true {2} 848 index_key true 0 true {3,4} 849 850 query TTTTTTTT colnames 851 SELECT conname, confkey, conpfeqop, conppeqop, conffeqop, conexclop, conbin, consrc 852 FROM pg_catalog.pg_constraint con 853 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 854 WHERE n.nspname = 'public' AND contype IN ('c', 'p', 'u') 855 ORDER BY con.oid 856 ---- 857 conname confkey conpfeqop conppeqop conffeqop conexclop conbin consrc 858 check_b NULL NULL NULL NULL NULL (b > 11:::INT8) (b > 11:::INT8) 859 primary NULL NULL NULL NULL NULL NULL NULL 860 t1_a_key NULL NULL NULL NULL NULL NULL NULL 861 index_key NULL NULL NULL NULL NULL NULL NULL 862 863 query TTTTTTTT colnames 864 SELECT conname, confkey, conpfeqop, conppeqop, conffeqop, conexclop, conbin, consrc 865 FROM pg_catalog.pg_constraint con 866 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 867 WHERE n.nspname = 'public' AND contype = 'f' 868 ORDER BY con.oid 869 ---- 870 conname confkey conpfeqop conppeqop conffeqop conexclop conbin consrc 871 fk {3,4} NULL NULL NULL NULL NULL NULL 872 fk {2} NULL NULL NULL NULL NULL NULL 873 874 ## pg_catalog.pg_depend 875 876 query OOIOOIT colnames 877 SELECT classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype 878 FROM pg_catalog.pg_depend 879 ORDER BY objid 880 ---- 881 classid objid objsubid refclassid refobjid refobjsubid deptype 882 4294967223 2143281868 0 4294967225 450499961 0 n 883 4294967223 4089604113 0 4294967225 450499960 0 n 884 885 # All entries in pg_depend are dependency links from the pg_constraint system 886 # table to the pg_class system table. 887 888 query OOTT colnames 889 SELECT DISTINCT classid, refclassid, cla.relname AS tablename, refcla.relname AS reftablename 890 FROM pg_catalog.pg_depend 891 JOIN pg_class cla ON classid=cla.oid 892 JOIN pg_class refcla ON refclassid=refcla.oid 893 ---- 894 classid refclassid tablename reftablename 895 4294967223 4294967225 pg_constraint pg_class 896 897 # All entries in pg_depend are foreign key constraints that reference an index 898 # in pg_class. 899 900 query TT colnames 901 SELECT relname, relkind 902 FROM pg_depend 903 JOIN pg_class ON refobjid=pg_class.oid 904 ORDER BY relname 905 ---- 906 relname relkind 907 index_key i 908 t1_a_key i 909 910 911 # All entries are pg_depend are linked to a foreign key constraint whose 912 # supporting index is the referenced object id. 913 914 query T colnames 915 SELECT DISTINCT pg_constraint.contype 916 FROM pg_depend 917 JOIN pg_constraint ON objid=pg_constraint.oid AND refobjid=pg_constraint.conindid 918 ---- 919 contype 920 f 921 922 ## pg_catalog.pg_enum 923 statement ok 924 SET experimental_enable_enums=true; 925 CREATE TYPE newtype1 AS ENUM ('v1', 'v2'); 926 CREATE TYPE newtype2 AS ENUM ('v3', 'v4') 927 928 query OORT colnames 929 SELECT * FROM pg_enum 930 ---- 931 oid enumtypid enumsortorder enumlabel 932 1043025669 100063 0 v1 933 1043025861 100063 1 v2 934 1881906619 100065 0 v3 935 1881906555 100065 1 v4 936 937 ## pg_catalog.pg_type 938 939 query OTOOIBT colnames 940 SELECT oid, typname, typnamespace, typowner, typlen, typbyval, typtype 941 FROM pg_catalog.pg_type 942 ORDER BY oid 943 ---- 944 oid typname typnamespace typowner typlen typbyval typtype 945 16 bool 1307062959 NULL 1 true b 946 17 bytea 1307062959 NULL -1 false b 947 18 char 1307062959 NULL 1 true b 948 19 name 1307062959 NULL -1 false b 949 20 int8 1307062959 NULL 8 true b 950 21 int2 1307062959 NULL 2 true b 951 22 int2vector 1307062959 NULL -1 false b 952 23 int4 1307062959 NULL 4 true b 953 24 regproc 1307062959 NULL 8 true b 954 25 text 1307062959 NULL -1 false b 955 26 oid 1307062959 NULL 8 true b 956 30 oidvector 1307062959 NULL -1 false b 957 700 float4 1307062959 NULL 4 true b 958 701 float8 1307062959 NULL 8 true b 959 705 unknown 1307062959 NULL 0 true b 960 869 inet 1307062959 NULL 24 true b 961 1000 _bool 1307062959 NULL -1 false b 962 1001 _bytea 1307062959 NULL -1 false b 963 1002 _char 1307062959 NULL -1 false b 964 1003 _name 1307062959 NULL -1 false b 965 1005 _int2 1307062959 NULL -1 false b 966 1006 _int2vector 1307062959 NULL -1 false b 967 1007 _int4 1307062959 NULL -1 false b 968 1008 _regproc 1307062959 NULL -1 false b 969 1009 _text 1307062959 NULL -1 false b 970 1013 _oidvector 1307062959 NULL -1 false b 971 1014 _bpchar 1307062959 NULL -1 false b 972 1015 _varchar 1307062959 NULL -1 false b 973 1016 _int8 1307062959 NULL -1 false b 974 1021 _float4 1307062959 NULL -1 false b 975 1022 _float8 1307062959 NULL -1 false b 976 1028 _oid 1307062959 NULL -1 false b 977 1041 _inet 1307062959 NULL -1 false b 978 1042 bpchar 1307062959 NULL -1 false b 979 1043 varchar 1307062959 NULL -1 false b 980 1082 date 1307062959 NULL 16 true b 981 1083 time 1307062959 NULL 8 true b 982 1114 timestamp 1307062959 NULL 24 true b 983 1115 _timestamp 1307062959 NULL -1 false b 984 1182 _date 1307062959 NULL -1 false b 985 1183 _time 1307062959 NULL -1 false b 986 1184 timestamptz 1307062959 NULL 24 true b 987 1185 _timestamptz 1307062959 NULL -1 false b 988 1186 interval 1307062959 NULL 24 true b 989 1187 _interval 1307062959 NULL -1 false b 990 1231 _numeric 1307062959 NULL -1 false b 991 1266 timetz 1307062959 NULL 16 true b 992 1270 _timetz 1307062959 NULL -1 false b 993 1560 bit 1307062959 NULL -1 false b 994 1561 _bit 1307062959 NULL -1 false b 995 1562 varbit 1307062959 NULL -1 false b 996 1563 _varbit 1307062959 NULL -1 false b 997 1700 numeric 1307062959 NULL -1 false b 998 2202 regprocedure 1307062959 NULL 8 true b 999 2205 regclass 1307062959 NULL 8 true b 1000 2206 regtype 1307062959 NULL 8 true b 1001 2207 _regprocedure 1307062959 NULL -1 false b 1002 2210 _regclass 1307062959 NULL -1 false b 1003 2211 _regtype 1307062959 NULL -1 false b 1004 2249 record 1307062959 NULL 0 true p 1005 2277 anyarray 1307062959 NULL -1 false p 1006 2283 anyelement 1307062959 NULL -1 false p 1007 2287 _record 1307062959 NULL -1 false b 1008 2950 uuid 1307062959 NULL 16 true b 1009 2951 _uuid 1307062959 NULL -1 false b 1010 3802 jsonb 1307062959 NULL -1 false b 1011 3807 _jsonb 1307062959 NULL -1 false b 1012 4089 regnamespace 1307062959 NULL 8 true b 1013 4090 _regnamespace 1307062959 NULL -1 false b 1014 90000 geometry 1307062959 NULL -1 false b 1015 90001 _geometry 1307062959 NULL -1 false b 1016 90002 geography 1307062959 NULL -1 false b 1017 90003 _geography 1307062959 NULL -1 false b 1018 100063 newtype1 1307062959 NULL -1 false e 1019 100064 _newtype1 1307062959 NULL -1 false b 1020 100065 newtype2 1307062959 NULL -1 false e 1021 100066 _newtype2 1307062959 NULL -1 false b 1022 1023 query OTTBBTOOO colnames 1024 SELECT oid, typname, typcategory, typispreferred, typisdefined, typdelim, typrelid, typelem, typarray 1025 FROM pg_catalog.pg_type 1026 ORDER BY oid 1027 ---- 1028 oid typname typcategory typispreferred typisdefined typdelim typrelid typelem typarray 1029 16 bool B false true , 0 0 1000 1030 17 bytea U false true , 0 0 1001 1031 18 char S false true , 0 0 1002 1032 19 name S false true , 0 0 1003 1033 20 int8 N false true , 0 0 1016 1034 21 int2 N false true , 0 0 1005 1035 22 int2vector A false true , 0 21 1006 1036 23 int4 N false true , 0 0 1007 1037 24 regproc N false true , 0 0 1008 1038 25 text S false true , 0 0 1009 1039 26 oid N false true , 0 0 1028 1040 30 oidvector A false true , 0 26 1013 1041 700 float4 N false true , 0 0 1021 1042 701 float8 N false true , 0 0 1022 1043 705 unknown X false true , 0 0 0 1044 869 inet I false true , 0 0 1041 1045 1000 _bool A false true , 0 16 0 1046 1001 _bytea A false true , 0 17 0 1047 1002 _char A false true , 0 18 0 1048 1003 _name A false true , 0 19 0 1049 1005 _int2 A false true , 0 21 0 1050 1006 _int2vector A false true , 0 22 0 1051 1007 _int4 A false true , 0 23 0 1052 1008 _regproc A false true , 0 24 0 1053 1009 _text A false true , 0 25 0 1054 1013 _oidvector A false true , 0 30 0 1055 1014 _bpchar A false true , 0 1042 0 1056 1015 _varchar A false true , 0 1043 0 1057 1016 _int8 A false true , 0 20 0 1058 1021 _float4 A false true , 0 700 0 1059 1022 _float8 A false true , 0 701 0 1060 1028 _oid A false true , 0 26 0 1061 1041 _inet A false true , 0 869 0 1062 1042 bpchar S false true , 0 0 1014 1063 1043 varchar S false true , 0 0 1015 1064 1082 date D false true , 0 0 1182 1065 1083 time D false true , 0 0 1183 1066 1114 timestamp D false true , 0 0 1115 1067 1115 _timestamp A false true , 0 1114 0 1068 1182 _date A false true , 0 1082 0 1069 1183 _time A false true , 0 1083 0 1070 1184 timestamptz D false true , 0 0 1185 1071 1185 _timestamptz A false true , 0 1184 0 1072 1186 interval T false true , 0 0 1187 1073 1187 _interval A false true , 0 1186 0 1074 1231 _numeric A false true , 0 1700 0 1075 1266 timetz D false true , 0 0 1270 1076 1270 _timetz A false true , 0 1266 0 1077 1560 bit V false true , 0 0 1561 1078 1561 _bit A false true , 0 1560 0 1079 1562 varbit V false true , 0 0 1563 1080 1563 _varbit A false true , 0 1562 0 1081 1700 numeric N false true , 0 0 1231 1082 2202 regprocedure N false true , 0 0 2207 1083 2205 regclass N false true , 0 0 2210 1084 2206 regtype N false true , 0 0 2211 1085 2207 _regprocedure A false true , 0 2202 0 1086 2210 _regclass A false true , 0 2205 0 1087 2211 _regtype A false true , 0 2206 0 1088 2249 record P false true , 0 0 2287 1089 2277 anyarray P false true , 0 0 0 1090 2283 anyelement P false true , 0 0 2277 1091 2287 _record A false true , 0 2249 0 1092 2950 uuid U false true , 0 0 2951 1093 2951 _uuid A false true , 0 2950 0 1094 3802 jsonb U false true , 0 0 3807 1095 3807 _jsonb A false true , 0 3802 0 1096 4089 regnamespace N false true , 0 0 4090 1097 4090 _regnamespace A false true , 0 4089 0 1098 90000 geometry U false true , 0 0 90001 1099 90001 _geometry A false true , 0 90000 0 1100 90002 geography U false true , 0 0 90003 1101 90003 _geography A false true , 0 90002 0 1102 100063 newtype1 E false true , 0 0 100064 1103 100064 _newtype1 A false true , 0 100063 0 1104 100065 newtype2 E false true , 0 0 100066 1105 100066 _newtype2 A false true , 0 100065 0 1106 1107 query OTOOOOOOO colnames 1108 SELECT oid, typname, typinput, typoutput, typreceive, typsend, typmodin, typmodout, typanalyze 1109 FROM pg_catalog.pg_type 1110 ORDER BY oid 1111 ---- 1112 oid typname typinput typoutput typreceive typsend typmodin typmodout typanalyze 1113 16 bool boolin boolout boolrecv boolsend 0 0 0 1114 17 bytea byteain byteaout bytearecv byteasend 0 0 0 1115 18 char charin charout charrecv charsend 0 0 0 1116 19 name namein nameout namerecv namesend 0 0 0 1117 20 int8 int8in int8out int8recv int8send 0 0 0 1118 21 int2 int2in int2out int2recv int2send 0 0 0 1119 22 int2vector int2vectorin int2vectorout int2vectorrecv int2vectorsend 0 0 0 1120 23 int4 int4in int4out int4recv int4send 0 0 0 1121 24 regproc regprocin regprocout regprocrecv regprocsend 0 0 0 1122 25 text textin textout textrecv textsend 0 0 0 1123 26 oid oidin oidout oidrecv oidsend 0 0 0 1124 30 oidvector oidvectorin oidvectorout oidvectorrecv oidvectorsend 0 0 0 1125 700 float4 float4in float4out float4recv float4send 0 0 0 1126 701 float8 float8in float8out float8recv float8send 0 0 0 1127 705 unknown unknownin unknownout unknownrecv unknownsend 0 0 0 1128 869 inet inetin inetout inetrecv inetsend 0 0 0 1129 1000 _bool array_in array_out array_recv array_send 0 0 0 1130 1001 _bytea array_in array_out array_recv array_send 0 0 0 1131 1002 _char array_in array_out array_recv array_send 0 0 0 1132 1003 _name array_in array_out array_recv array_send 0 0 0 1133 1005 _int2 array_in array_out array_recv array_send 0 0 0 1134 1006 _int2vector array_in array_out array_recv array_send 0 0 0 1135 1007 _int4 array_in array_out array_recv array_send 0 0 0 1136 1008 _regproc array_in array_out array_recv array_send 0 0 0 1137 1009 _text array_in array_out array_recv array_send 0 0 0 1138 1013 _oidvector array_in array_out array_recv array_send 0 0 0 1139 1014 _bpchar array_in array_out array_recv array_send 0 0 0 1140 1015 _varchar array_in array_out array_recv array_send 0 0 0 1141 1016 _int8 array_in array_out array_recv array_send 0 0 0 1142 1021 _float4 array_in array_out array_recv array_send 0 0 0 1143 1022 _float8 array_in array_out array_recv array_send 0 0 0 1144 1028 _oid array_in array_out array_recv array_send 0 0 0 1145 1041 _inet array_in array_out array_recv array_send 0 0 0 1146 1042 bpchar bpcharin bpcharout bpcharrecv bpcharsend 0 0 0 1147 1043 varchar varcharin varcharout varcharrecv varcharsend 0 0 0 1148 1082 date date_in date_out date_recv date_send 0 0 0 1149 1083 time time_in time_out time_recv time_send 0 0 0 1150 1114 timestamp timestamp_in timestamp_out timestamp_recv timestamp_send 0 0 0 1151 1115 _timestamp array_in array_out array_recv array_send 0 0 0 1152 1182 _date array_in array_out array_recv array_send 0 0 0 1153 1183 _time array_in array_out array_recv array_send 0 0 0 1154 1184 timestamptz timestamptz_in timestamptz_out timestamptz_recv timestamptz_send 0 0 0 1155 1185 _timestamptz array_in array_out array_recv array_send 0 0 0 1156 1186 interval interval_in interval_out interval_recv interval_send 0 0 0 1157 1187 _interval array_in array_out array_recv array_send 0 0 0 1158 1231 _numeric array_in array_out array_recv array_send 0 0 0 1159 1266 timetz timetz_in timetz_out timetz_recv timetz_send 0 0 0 1160 1270 _timetz array_in array_out array_recv array_send 0 0 0 1161 1560 bit bit_in bit_out bit_recv bit_send 0 0 0 1162 1561 _bit array_in array_out array_recv array_send 0 0 0 1163 1562 varbit varbit_in varbit_out varbit_recv varbit_send 0 0 0 1164 1563 _varbit array_in array_out array_recv array_send 0 0 0 1165 1700 numeric numeric_in numeric_out numeric_recv numeric_send 0 0 0 1166 2202 regprocedure regprocedurein regprocedureout regprocedurerecv regproceduresend 0 0 0 1167 2205 regclass regclassin regclassout regclassrecv regclasssend 0 0 0 1168 2206 regtype regtypein regtypeout regtyperecv regtypesend 0 0 0 1169 2207 _regprocedure array_in array_out array_recv array_send 0 0 0 1170 2210 _regclass array_in array_out array_recv array_send 0 0 0 1171 2211 _regtype array_in array_out array_recv array_send 0 0 0 1172 2249 record record_in record_out record_recv record_send 0 0 0 1173 2277 anyarray anyarray_in anyarray_out anyarray_recv anyarray_send 0 0 0 1174 2283 anyelement anyelement_in anyelement_out anyelement_recv anyelement_send 0 0 0 1175 2287 _record array_in array_out array_recv array_send 0 0 0 1176 2950 uuid uuid_in uuid_out uuid_recv uuid_send 0 0 0 1177 2951 _uuid array_in array_out array_recv array_send 0 0 0 1178 3802 jsonb jsonb_in jsonb_out jsonb_recv jsonb_send 0 0 0 1179 3807 _jsonb array_in array_out array_recv array_send 0 0 0 1180 4089 regnamespace regnamespacein regnamespaceout regnamespacerecv regnamespacesend 0 0 0 1181 4090 _regnamespace array_in array_out array_recv array_send 0 0 0 1182 90000 geometry geometry_in geometry_out geometry_recv geometry_send 0 0 0 1183 90001 _geometry array_in array_out array_recv array_send 0 0 0 1184 90002 geography geography_in geography_out geography_recv geography_send 0 0 0 1185 90003 _geography array_in array_out array_recv array_send 0 0 0 1186 100063 newtype1 enum_in enum_out enum_recv enum_send 0 0 0 1187 100064 _newtype1 array_in array_out array_recv array_send 0 0 0 1188 100065 newtype2 enum_in enum_out enum_recv enum_send 0 0 0 1189 100066 _newtype2 array_in array_out array_recv array_send 0 0 0 1190 1191 query OTTTBOI colnames 1192 SELECT oid, typname, typalign, typstorage, typnotnull, typbasetype, typtypmod 1193 FROM pg_catalog.pg_type 1194 ORDER BY oid 1195 ---- 1196 oid typname typalign typstorage typnotnull typbasetype typtypmod 1197 16 bool NULL NULL false 0 -1 1198 17 bytea NULL NULL false 0 -1 1199 18 char NULL NULL false 0 -1 1200 19 name NULL NULL false 0 -1 1201 20 int8 NULL NULL false 0 -1 1202 21 int2 NULL NULL false 0 -1 1203 22 int2vector NULL NULL false 0 -1 1204 23 int4 NULL NULL false 0 -1 1205 24 regproc NULL NULL false 0 -1 1206 25 text NULL NULL false 0 -1 1207 26 oid NULL NULL false 0 -1 1208 30 oidvector NULL NULL false 0 -1 1209 700 float4 NULL NULL false 0 -1 1210 701 float8 NULL NULL false 0 -1 1211 705 unknown NULL NULL false 0 -1 1212 869 inet NULL NULL false 0 -1 1213 1000 _bool NULL NULL false 0 -1 1214 1001 _bytea NULL NULL false 0 -1 1215 1002 _char NULL NULL false 0 -1 1216 1003 _name NULL NULL false 0 -1 1217 1005 _int2 NULL NULL false 0 -1 1218 1006 _int2vector NULL NULL false 0 -1 1219 1007 _int4 NULL NULL false 0 -1 1220 1008 _regproc NULL NULL false 0 -1 1221 1009 _text NULL NULL false 0 -1 1222 1013 _oidvector NULL NULL false 0 -1 1223 1014 _bpchar NULL NULL false 0 -1 1224 1015 _varchar NULL NULL false 0 -1 1225 1016 _int8 NULL NULL false 0 -1 1226 1021 _float4 NULL NULL false 0 -1 1227 1022 _float8 NULL NULL false 0 -1 1228 1028 _oid NULL NULL false 0 -1 1229 1041 _inet NULL NULL false 0 -1 1230 1042 bpchar NULL NULL false 0 -1 1231 1043 varchar NULL NULL false 0 -1 1232 1082 date NULL NULL false 0 -1 1233 1083 time NULL NULL false 0 -1 1234 1114 timestamp NULL NULL false 0 -1 1235 1115 _timestamp NULL NULL false 0 -1 1236 1182 _date NULL NULL false 0 -1 1237 1183 _time NULL NULL false 0 -1 1238 1184 timestamptz NULL NULL false 0 -1 1239 1185 _timestamptz NULL NULL false 0 -1 1240 1186 interval NULL NULL false 0 -1 1241 1187 _interval NULL NULL false 0 -1 1242 1231 _numeric NULL NULL false 0 -1 1243 1266 timetz NULL NULL false 0 -1 1244 1270 _timetz NULL NULL false 0 -1 1245 1560 bit NULL NULL false 0 -1 1246 1561 _bit NULL NULL false 0 -1 1247 1562 varbit NULL NULL false 0 -1 1248 1563 _varbit NULL NULL false 0 -1 1249 1700 numeric NULL NULL false 0 -1 1250 2202 regprocedure NULL NULL false 0 -1 1251 2205 regclass NULL NULL false 0 -1 1252 2206 regtype NULL NULL false 0 -1 1253 2207 _regprocedure NULL NULL false 0 -1 1254 2210 _regclass NULL NULL false 0 -1 1255 2211 _regtype NULL NULL false 0 -1 1256 2249 record NULL NULL false 0 -1 1257 2277 anyarray NULL NULL false 0 -1 1258 2283 anyelement NULL NULL false 0 -1 1259 2287 _record NULL NULL false 0 -1 1260 2950 uuid NULL NULL false 0 -1 1261 2951 _uuid NULL NULL false 0 -1 1262 3802 jsonb NULL NULL false 0 -1 1263 3807 _jsonb NULL NULL false 0 -1 1264 4089 regnamespace NULL NULL false 0 -1 1265 4090 _regnamespace NULL NULL false 0 -1 1266 90000 geometry NULL NULL false 0 -1 1267 90001 _geometry NULL NULL false 0 -1 1268 90002 geography NULL NULL false 0 -1 1269 90003 _geography NULL NULL false 0 -1 1270 100063 newtype1 NULL NULL false 0 -1 1271 100064 _newtype1 NULL NULL false 0 -1 1272 100065 newtype2 NULL NULL false 0 -1 1273 100066 _newtype2 NULL NULL false 0 -1 1274 1275 query OTIOTTT colnames 1276 SELECT oid, typname, typndims, typcollation, typdefaultbin, typdefault, typacl 1277 FROM pg_catalog.pg_type 1278 ORDER BY oid 1279 ---- 1280 oid typname typndims typcollation typdefaultbin typdefault typacl 1281 16 bool 0 0 NULL NULL NULL 1282 17 bytea 0 0 NULL NULL NULL 1283 18 char 0 3903121477 NULL NULL NULL 1284 19 name 0 3903121477 NULL NULL NULL 1285 20 int8 0 0 NULL NULL NULL 1286 21 int2 0 0 NULL NULL NULL 1287 22 int2vector 0 0 NULL NULL NULL 1288 23 int4 0 0 NULL NULL NULL 1289 24 regproc 0 0 NULL NULL NULL 1290 25 text 0 3903121477 NULL NULL NULL 1291 26 oid 0 0 NULL NULL NULL 1292 30 oidvector 0 0 NULL NULL NULL 1293 700 float4 0 0 NULL NULL NULL 1294 701 float8 0 0 NULL NULL NULL 1295 705 unknown 0 0 NULL NULL NULL 1296 869 inet 0 0 NULL NULL NULL 1297 1000 _bool 0 0 NULL NULL NULL 1298 1001 _bytea 0 0 NULL NULL NULL 1299 1002 _char 0 3903121477 NULL NULL NULL 1300 1003 _name 0 3903121477 NULL NULL NULL 1301 1005 _int2 0 0 NULL NULL NULL 1302 1006 _int2vector 0 0 NULL NULL NULL 1303 1007 _int4 0 0 NULL NULL NULL 1304 1008 _regproc 0 0 NULL NULL NULL 1305 1009 _text 0 3903121477 NULL NULL NULL 1306 1013 _oidvector 0 0 NULL NULL NULL 1307 1014 _bpchar 0 3903121477 NULL NULL NULL 1308 1015 _varchar 0 3903121477 NULL NULL NULL 1309 1016 _int8 0 0 NULL NULL NULL 1310 1021 _float4 0 0 NULL NULL NULL 1311 1022 _float8 0 0 NULL NULL NULL 1312 1028 _oid 0 0 NULL NULL NULL 1313 1041 _inet 0 0 NULL NULL NULL 1314 1042 bpchar 0 3903121477 NULL NULL NULL 1315 1043 varchar 0 3903121477 NULL NULL NULL 1316 1082 date 0 0 NULL NULL NULL 1317 1083 time 0 0 NULL NULL NULL 1318 1114 timestamp 0 0 NULL NULL NULL 1319 1115 _timestamp 0 0 NULL NULL NULL 1320 1182 _date 0 0 NULL NULL NULL 1321 1183 _time 0 0 NULL NULL NULL 1322 1184 timestamptz 0 0 NULL NULL NULL 1323 1185 _timestamptz 0 0 NULL NULL NULL 1324 1186 interval 0 0 NULL NULL NULL 1325 1187 _interval 0 0 NULL NULL NULL 1326 1231 _numeric 0 0 NULL NULL NULL 1327 1266 timetz 0 0 NULL NULL NULL 1328 1270 _timetz 0 0 NULL NULL NULL 1329 1560 bit 0 0 NULL NULL NULL 1330 1561 _bit 0 0 NULL NULL NULL 1331 1562 varbit 0 0 NULL NULL NULL 1332 1563 _varbit 0 0 NULL NULL NULL 1333 1700 numeric 0 0 NULL NULL NULL 1334 2202 regprocedure 0 0 NULL NULL NULL 1335 2205 regclass 0 0 NULL NULL NULL 1336 2206 regtype 0 0 NULL NULL NULL 1337 2207 _regprocedure 0 0 NULL NULL NULL 1338 2210 _regclass 0 0 NULL NULL NULL 1339 2211 _regtype 0 0 NULL NULL NULL 1340 2249 record 0 0 NULL NULL NULL 1341 2277 anyarray 0 3903121477 NULL NULL NULL 1342 2283 anyelement 0 0 NULL NULL NULL 1343 2287 _record 0 0 NULL NULL NULL 1344 2950 uuid 0 0 NULL NULL NULL 1345 2951 _uuid 0 0 NULL NULL NULL 1346 3802 jsonb 0 0 NULL NULL NULL 1347 3807 _jsonb 0 0 NULL NULL NULL 1348 4089 regnamespace 0 0 NULL NULL NULL 1349 4090 _regnamespace 0 0 NULL NULL NULL 1350 90000 geometry 0 0 NULL NULL NULL 1351 90001 _geometry 0 0 NULL NULL NULL 1352 90002 geography 0 0 NULL NULL NULL 1353 90003 _geography 0 0 NULL NULL NULL 1354 100063 newtype1 0 0 NULL NULL NULL 1355 100064 _newtype1 0 0 NULL NULL NULL 1356 100065 newtype2 0 0 NULL NULL NULL 1357 100066 _newtype2 0 0 NULL NULL NULL 1358 1359 user testuser 1360 1361 # Should be globally visible 1362 query OTOIBT colnames 1363 SELECT oid, typname, typowner, typlen, typbyval, typtype 1364 FROM pg_catalog.pg_type WHERE typname = 'uuid' 1365 ORDER BY oid 1366 ---- 1367 oid typname typowner typlen typbyval typtype 1368 2950 uuid NULL 16 true b 1369 1370 user root 1371 1372 ## pg_catalog.pg_proc 1373 1374 query TOOOTTO colnames 1375 SELECT proname, pronamespace, proowner, prolang, procost, prorows, provariadic 1376 FROM pg_catalog.pg_proc 1377 WHERE proname='substring' 1378 ---- 1379 proname pronamespace proowner prolang procost prorows provariadic 1380 substring 1307062959 NULL 0 NULL NULL 0 1381 substring 1307062959 NULL 0 NULL NULL 0 1382 substring 1307062959 NULL 0 NULL NULL 0 1383 substring 1307062959 NULL 0 NULL NULL 0 1384 substring 1307062959 NULL 0 NULL NULL 0 1385 substring 1307062959 NULL 0 NULL NULL 0 1386 substring 1307062959 NULL 0 NULL NULL 0 1387 substring 1307062959 NULL 0 NULL NULL 0 1388 1389 query TTBBBB colnames 1390 SELECT proname, protransform, proisagg, proiswindow, prosecdef, proleakproof 1391 FROM pg_catalog.pg_proc 1392 WHERE proname='substring' 1393 ---- 1394 proname protransform proisagg proiswindow prosecdef proleakproof 1395 substring NULL false false false false 1396 substring NULL false false false false 1397 substring NULL false false false false 1398 substring NULL false false false false 1399 substring NULL false false false false 1400 substring NULL false false false false 1401 substring NULL false false false false 1402 substring NULL false false false false 1403 1404 query TBBTT colnames 1405 SELECT proname, proisstrict, proretset, provolatile, proparallel 1406 FROM pg_catalog.pg_proc 1407 WHERE proname='substring' 1408 ---- 1409 proname proisstrict proretset provolatile proparallel 1410 substring false false i NULL 1411 substring false false i NULL 1412 substring false false i NULL 1413 substring false false i NULL 1414 substring false false i NULL 1415 substring false false i NULL 1416 substring false false i NULL 1417 substring false false i NULL 1418 1419 query TIIOTTTT colnames 1420 SELECT proname, pronargs, pronargdefaults, prorettype, proargtypes, proallargtypes, proargmodes, proargdefaults 1421 FROM pg_catalog.pg_proc 1422 WHERE proname='substring' 1423 ---- 1424 proname pronargs pronargdefaults prorettype proargtypes proallargtypes proargmodes proargdefaults 1425 substring 2 0 25 25 20 NULL NULL NULL 1426 substring 3 0 25 25 20 20 NULL NULL NULL 1427 substring 2 0 25 25 25 NULL NULL NULL 1428 substring 3 0 25 25 25 25 NULL NULL NULL 1429 substring 2 0 1562 1562 20 NULL NULL NULL 1430 substring 3 0 1562 1562 20 20 NULL NULL NULL 1431 substring 2 0 17 17 20 NULL NULL NULL 1432 substring 3 0 17 17 20 20 NULL NULL NULL 1433 1434 query TTTTTT colnames 1435 SELECT proname, protrftypes, prosrc, probin, proconfig, proacl 1436 FROM pg_catalog.pg_proc 1437 WHERE proname='substring' 1438 ---- 1439 proname protrftypes prosrc probin proconfig proacl 1440 substring NULL substring NULL NULL NULL 1441 substring NULL substring NULL NULL NULL 1442 substring NULL substring NULL NULL NULL 1443 substring NULL substring NULL NULL NULL 1444 substring NULL substring NULL NULL NULL 1445 substring NULL substring NULL NULL NULL 1446 substring NULL substring NULL NULL NULL 1447 substring NULL substring NULL NULL NULL 1448 1449 query TOIOTT colnames 1450 SELECT proname, provariadic, pronargs, prorettype, proargtypes, proargmodes 1451 FROM pg_catalog.pg_proc 1452 WHERE proname='least' 1453 ---- 1454 proname provariadic pronargs prorettype proargtypes proargmodes 1455 least 2283 1 2283 2283 {v} 1456 1457 query TOIOTT colnames 1458 SELECT proname, provariadic, pronargs, prorettype, proargtypes, proargmodes 1459 FROM pg_catalog.pg_proc 1460 WHERE proname='json_extract_path' 1461 ---- 1462 proname provariadic pronargs prorettype proargtypes proargmodes 1463 json_extract_path 25 2 3802 3802 25 {i,v} 1464 1465 user testuser 1466 1467 # Should be globally visible 1468 query TOIOTT colnames 1469 SELECT proname, provariadic, pronargs, prorettype, proargtypes, proargmodes 1470 FROM pg_catalog.pg_proc 1471 WHERE proname='json_extract_path' 1472 ---- 1473 proname provariadic pronargs prorettype proargtypes proargmodes 1474 json_extract_path 25 2 3802 3802 25 {i,v} 1475 1476 user root 1477 1478 ## pg_catalog.pg_range 1479 query IIIIII colnames 1480 SELECT * from pg_catalog.pg_range 1481 ---- 1482 rngtypid rngsubtype rngcollation rngsubopc rngcanonical rngsubdiff 1483 1484 ## pg_catalog.pg_roles 1485 1486 query OTBBBBBBB colnames 1487 SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolreplication 1488 FROM pg_catalog.pg_roles 1489 ORDER BY rolname 1490 ---- 1491 oid rolname rolsuper rolinherit rolcreaterole rolcreatedb rolcatupdate rolcanlogin rolreplication 1492 2310524507 admin true true true true false true false 1493 1546506610 root true false true true false true false 1494 2264919399 testuser false false false false false true false 1495 1496 query OTITTBT colnames 1497 SELECT oid, rolname, rolconnlimit, rolpassword, rolvaliduntil, rolbypassrls, rolconfig 1498 FROM pg_catalog.pg_roles 1499 ORDER BY rolname 1500 ---- 1501 oid rolname rolconnlimit rolpassword rolvaliduntil rolbypassrls rolconfig 1502 2310524507 admin -1 ******** NULL false NULL 1503 1546506610 root -1 ******** NULL false NULL 1504 2264919399 testuser -1 ******** NULL false NULL 1505 1506 ## pg_catalog.pg_auth_members 1507 1508 query OOOB colnames 1509 SELECT roleid, member, grantor, admin_option 1510 FROM pg_catalog.pg_auth_members 1511 ---- 1512 roleid member grantor admin_option 1513 2310524507 1546506610 NULL true 1514 1515 ## pg_catalog.pg_user 1516 1517 query TOBBBBTTA colnames 1518 SELECT usename, usesysid, usecreatedb, usesuper, userepl, usebypassrls, passwd, valuntil, useconfig 1519 FROM pg_catalog.pg_user 1520 ORDER BY usename 1521 ---- 1522 usename usesysid usecreatedb usesuper userepl usebypassrls passwd valuntil useconfig 1523 root 1546506610 true true false false ******** NULL NULL 1524 testuser 2264919399 false false false false ******** NULL NULL 1525 1526 ## pg_catalog.pg_description 1527 1528 query OOIT colnames 1529 SELECT objoid, classoid, objsubid, regexp_replace(description, e'\n.*', '') AS description 1530 FROM pg_catalog.pg_description 1531 ---- 1532 objoid classoid objsubid description 1533 4294967294 4294967225 0 backward inter-descriptor dependencies starting from tables accessible by current user in current database (KV scan) 1534 4294967292 4294967225 0 built-in functions (RAM/static) 1535 4294967291 4294967225 0 running queries visible by current user (cluster RPC; expensive!) 1536 4294967289 4294967225 0 running sessions visible to current user (cluster RPC; expensive!) 1537 4294967288 4294967225 0 cluster settings (RAM) 1538 4294967290 4294967225 0 running user transactions visible by the current user (cluster RPC; expensive!) 1539 4294967287 4294967225 0 CREATE and ALTER statements for all tables accessible by current user in current database (KV scan) 1540 4294967286 4294967225 0 CREATE statements for all user defined types accessible by the current user in current database (KV scan) 1541 4294967285 4294967225 0 telemetry counters (RAM; local node only) 1542 4294967284 4294967225 0 forward inter-descriptor dependencies starting from tables accessible by current user in current database (KV scan) 1543 4294967282 4294967225 0 locally known gossiped health alerts (RAM; local node only) 1544 4294967281 4294967225 0 locally known gossiped node liveness (RAM; local node only) 1545 4294967280 4294967225 0 locally known edges in the gossip network (RAM; local node only) 1546 4294967283 4294967225 0 locally known gossiped node details (RAM; local node only) 1547 4294967279 4294967225 0 index columns for all indexes accessible by current user in current database (KV scan) 1548 4294967278 4294967225 0 decoded job metadata from system.jobs (KV scan) 1549 4294967277 4294967225 0 node details across the entire cluster (cluster RPC; expensive!) 1550 4294967276 4294967225 0 store details and status (cluster RPC; expensive!) 1551 4294967275 4294967225 0 acquired table leases (RAM; local node only) 1552 4294967293 4294967225 0 detailed identification strings (RAM, local node only) 1553 4294967271 4294967225 0 current values for metrics (RAM; local node only) 1554 4294967274 4294967225 0 running queries visible by current user (RAM; local node only) 1555 4294967266 4294967225 0 server parameters, useful to construct connection URLs (RAM, local node only) 1556 4294967272 4294967225 0 running sessions visible by current user (RAM; local node only) 1557 4294967262 4294967225 0 statement statistics (in-memory, not durable; local node only). This table is wiped periodically (by default, at least every two hours) 1558 4294967273 4294967225 0 running user transactions visible by the current user (RAM; local node only) 1559 4294967258 4294967225 0 per-application transaction statistics (in-memory, not durable; local node only). This table is wiped periodically (by default, at least every two hours) 1560 4294967270 4294967225 0 defined partitions for all tables/indexes accessible by the current user in the current database (KV scan) 1561 4294967269 4294967225 0 comments for predefined virtual tables (RAM/static) 1562 4294967268 4294967225 0 range metadata without leaseholder details (KV join; expensive!) 1563 4294967265 4294967225 0 ongoing schema changes, across all descriptors accessible by current user (KV scan; expensive!) 1564 4294967264 4294967225 0 session trace accumulated so far (RAM) 1565 4294967263 4294967225 0 session variables (RAM) 1566 4294967261 4294967225 0 details for all columns accessible by current user in current database (KV scan) 1567 4294967260 4294967225 0 indexes accessible by current user in current database (KV scan) 1568 4294967259 4294967225 0 table descriptors accessible by current user, including non-public and virtual (KV scan; expensive!) 1569 4294967257 4294967225 0 decoded zone configurations from system.zones (KV scan) 1570 4294967255 4294967225 0 roles for which the current user has admin option 1571 4294967254 4294967225 0 roles available to the current user 1572 4294967253 4294967225 0 check constraints 1573 4294967252 4294967225 0 column privilege grants (incomplete) 1574 4294967251 4294967225 0 table and view columns (incomplete) 1575 4294967250 4294967225 0 columns usage by constraints 1576 4294967249 4294967225 0 roles for the current user 1577 4294967248 4294967225 0 column usage by indexes and key constraints 1578 4294967247 4294967225 0 built-in function parameters (empty - introspection not yet supported) 1579 4294967246 4294967225 0 foreign key constraints 1580 4294967245 4294967225 0 privileges granted on table or views (incomplete; see also information_schema.table_privileges; may contain excess users or roles) 1581 4294967244 4294967225 0 built-in functions (empty - introspection not yet supported) 1582 4294967242 4294967225 0 schema privileges (incomplete; may contain excess users or roles) 1583 4294967243 4294967225 0 database schemas (may contain schemata without permission) 1584 4294967241 4294967225 0 sequences 1585 4294967240 4294967225 0 index metadata and statistics (incomplete) 1586 4294967239 4294967225 0 table constraints 1587 4294967238 4294967225 0 privileges granted on table or views (incomplete; may contain excess users or roles) 1588 4294967237 4294967225 0 tables and views 1589 4294967235 4294967225 0 grantable privileges (incomplete) 1590 4294967236 4294967225 0 views (incomplete) 1591 4294967233 4294967225 0 aggregated built-in functions (incomplete) 1592 4294967232 4294967225 0 index access methods (incomplete) 1593 4294967231 4294967225 0 column default values 1594 4294967230 4294967225 0 table columns (incomplete - see also information_schema.columns) 1595 4294967228 4294967225 0 role membership 1596 4294967229 4294967225 0 authorization identifiers - differs from postgres as we do not display passwords, 1597 4294967227 4294967225 0 available extensions 1598 4294967226 4294967225 0 casts (empty - needs filling out) 1599 4294967225 4294967225 0 tables and relation-like objects (incomplete - see also information_schema.tables/sequences/views) 1600 4294967224 4294967225 0 available collations (incomplete) 1601 4294967223 4294967225 0 table constraints (incomplete - see also information_schema.table_constraints) 1602 4294967222 4294967225 0 encoding conversions (empty - unimplemented) 1603 4294967221 4294967225 0 available databases (incomplete) 1604 4294967220 4294967225 0 default ACLs (empty - unimplemented) 1605 4294967219 4294967225 0 dependency relationships (incomplete) 1606 4294967218 4294967225 0 object comments 1607 4294967216 4294967225 0 enum types and labels (empty - feature does not exist) 1608 4294967215 4294967225 0 event triggers (empty - feature does not exist) 1609 4294967214 4294967225 0 installed extensions (empty - feature does not exist) 1610 4294967213 4294967225 0 foreign data wrappers (empty - feature does not exist) 1611 4294967212 4294967225 0 foreign servers (empty - feature does not exist) 1612 4294967211 4294967225 0 foreign tables (empty - feature does not exist) 1613 4294967210 4294967225 0 indexes (incomplete) 1614 4294967209 4294967225 0 index creation statements 1615 4294967208 4294967225 0 table inheritance hierarchy (empty - feature does not exist) 1616 4294967207 4294967225 0 available languages (empty - feature does not exist) 1617 4294967206 4294967225 0 locks held by active processes (empty - feature does not exist) 1618 4294967205 4294967225 0 available materialized views (empty - feature does not exist) 1619 4294967204 4294967225 0 available namespaces (incomplete; namespaces and databases are congruent in CockroachDB) 1620 4294967203 4294967225 0 operators (incomplete) 1621 4294967202 4294967225 0 prepared statements 1622 4294967201 4294967225 0 prepared transactions (empty - feature does not exist) 1623 4294967200 4294967225 0 built-in functions (incomplete) 1624 4294967199 4294967225 0 range types (empty - feature does not exist) 1625 4294967198 4294967225 0 rewrite rules (empty - feature does not exist) 1626 4294967197 4294967225 0 database roles 1627 4294967184 4294967225 0 security labels (empty - feature does not exist) 1628 4294967196 4294967225 0 security labels (empty) 1629 4294967195 4294967225 0 sequences (see also information_schema.sequences) 1630 4294967194 4294967225 0 session variables (incomplete) 1631 4294967193 4294967225 0 shared dependencies (empty - not implemented) 1632 4294967217 4294967225 0 shared object comments 1633 4294967183 4294967225 0 shared security labels (empty - feature not supported) 1634 4294967185 4294967225 0 backend access statistics (empty - monitoring works differently in CockroachDB) 1635 4294967190 4294967225 0 tables summary (see also information_schema.tables, pg_catalog.pg_class) 1636 4294967189 4294967225 0 available tablespaces (incomplete; concept inapplicable to CockroachDB) 1637 4294967188 4294967225 0 triggers (empty - feature does not exist) 1638 4294967187 4294967225 0 scalar types (incomplete) 1639 4294967192 4294967225 0 database users 1640 4294967191 4294967225 0 local to remote user mapping (empty - feature does not exist) 1641 4294967186 4294967225 0 view definitions (incomplete - see also information_schema.views) 1642 4294967181 4294967225 0 Shows all defined geography columns. Matches PostGIS' geography_columns functionality. 1643 4294967180 4294967225 0 Shows all defined geometry columns. Matches PostGIS' geometry_columns functionality. 1644 4294967179 4294967225 0 Shows all defined Spatial Reference Identifiers (SRIDs). Matches PostGIS' spatial_ref_sys table. 1645 1646 ## pg_catalog.pg_shdescription 1647 1648 query OOT colnames 1649 SELECT objoid, classoid, description FROM pg_catalog.pg_shdescription 1650 ---- 1651 objoid classoid description 1652 1653 ## pg_catalog.pg_event_trigger 1654 1655 query TTOOTT colnames 1656 SELECT * FROM pg_catalog.pg_event_trigger 1657 ---- 1658 evtname evtevent evtowner evtfoid evtenabled evttags 1659 1660 ## pg_catalog.pg_extension 1661 query OTOOBTTT colnames 1662 SELECT * FROM pg_catalog.pg_extension 1663 ---- 1664 oid extname extowner extnamespace extrelocatable extversion extconfig extcondition 1665 1666 ## pg_catalog.pg_stat_activity 1667 1668 query OTIOTTTTITTTTTTTIIT colnames 1669 SELECT * FROM pg_catalog.pg_stat_activity 1670 ---- 1671 datid datname pid usesysid usename application_name client_addr client_hostname client_port backend_start xact_start query_start state_change wait_event_type wait_event state backend_xid backend_xmin query 1672 1673 query TTBTTTB colnames 1674 SHOW COLUMNS FROM pg_catalog.pg_stat_activity 1675 ---- 1676 column_name data_type is_nullable column_default generation_expression indices is_hidden 1677 datid OID true NULL · {} false 1678 datname NAME true NULL · {} false 1679 pid INT8 true NULL · {} false 1680 usesysid OID true NULL · {} false 1681 usename NAME true NULL · {} false 1682 application_name STRING true NULL · {} false 1683 client_addr INET true NULL · {} false 1684 client_hostname STRING true NULL · {} false 1685 client_port INT8 true NULL · {} false 1686 backend_start TIMESTAMPTZ true NULL · {} false 1687 xact_start TIMESTAMPTZ true NULL · {} false 1688 query_start TIMESTAMPTZ true NULL · {} false 1689 state_change TIMESTAMPTZ true NULL · {} false 1690 wait_event_type STRING true NULL · {} false 1691 wait_event STRING true NULL · {} false 1692 state STRING true NULL · {} false 1693 backend_xid INT8 true NULL · {} false 1694 backend_xmin INT8 true NULL · {} false 1695 query STRING true NULL · {} false 1696 1697 1698 ## pg_catalog.pg_settings 1699 1700 statement ok 1701 SET DATABASE = test 1702 1703 # We filter here because 'optimizer' will be different depending on which 1704 # configuration this logic test is running in, and session ID will vary. 1705 query TTTTTT colnames 1706 SELECT 1707 name, setting, category, short_desc, extra_desc, vartype 1708 FROM 1709 pg_catalog.pg_settings 1710 WHERE 1711 name != 'optimizer' AND name != 'crdb_version' AND name != 'session_id' 1712 ---- 1713 name setting category short_desc extra_desc vartype 1714 application_name · NULL NULL NULL string 1715 bytea_output hex NULL NULL NULL string 1716 client_encoding UTF8 NULL NULL NULL string 1717 client_min_messages notice NULL NULL NULL string 1718 database test NULL NULL NULL string 1719 datestyle ISO, MDY NULL NULL NULL string 1720 default_int_size 8 NULL NULL NULL string 1721 default_tablespace · NULL NULL NULL string 1722 default_transaction_isolation serializable NULL NULL NULL string 1723 default_transaction_priority normal NULL NULL NULL string 1724 default_transaction_read_only off NULL NULL NULL string 1725 distsql off NULL NULL NULL string 1726 enable_experimental_alter_column_type_general off NULL NULL NULL string 1727 enable_implicit_select_for_update on NULL NULL NULL string 1728 enable_insert_fast_path on NULL NULL NULL string 1729 enable_zigzag_join on NULL NULL NULL string 1730 experimental_distsql_planning off NULL NULL NULL string 1731 experimental_enable_enums on NULL NULL NULL string 1732 experimental_enable_hash_sharded_indexes off NULL NULL NULL string 1733 experimental_enable_temp_tables off NULL NULL NULL string 1734 experimental_optimizer_foreign_key_cascades on NULL NULL NULL string 1735 experimental_partial_indexes off NULL NULL NULL string 1736 extra_float_digits 0 NULL NULL NULL string 1737 force_savepoint_restart off NULL NULL NULL string 1738 foreign_key_cascades_limit 10000 NULL NULL NULL string 1739 idle_in_transaction_session_timeout 0 NULL NULL NULL string 1740 integer_datetimes on NULL NULL NULL string 1741 intervalstyle postgres NULL NULL NULL string 1742 locality region=test,dc=dc1 NULL NULL NULL string 1743 lock_timeout 0 NULL NULL NULL string 1744 max_identifier_length 128 NULL NULL NULL string 1745 max_index_keys 32 NULL NULL NULL string 1746 node_id 1 NULL NULL NULL string 1747 optimizer_foreign_keys on NULL NULL NULL string 1748 optimizer_use_histograms on NULL NULL NULL string 1749 optimizer_use_multicol_stats on NULL NULL NULL string 1750 reorder_joins_limit 4 NULL NULL NULL string 1751 require_explicit_primary_keys off NULL NULL NULL string 1752 results_buffer_size 16384 NULL NULL NULL string 1753 row_security off NULL NULL NULL string 1754 search_path public NULL NULL NULL string 1755 serial_normalization rowid NULL NULL NULL string 1756 server_encoding UTF8 NULL NULL NULL string 1757 server_version 9.5.0 NULL NULL NULL string 1758 server_version_num 90500 NULL NULL NULL string 1759 session_user root NULL NULL NULL string 1760 sql_safe_updates off NULL NULL NULL string 1761 standard_conforming_strings on NULL NULL NULL string 1762 statement_timeout 0 NULL NULL NULL string 1763 synchronize_seqscans on NULL NULL NULL string 1764 timezone UTC NULL NULL NULL string 1765 tracing off NULL NULL NULL string 1766 transaction_isolation serializable NULL NULL NULL string 1767 transaction_priority normal NULL NULL NULL string 1768 transaction_read_only off NULL NULL NULL string 1769 transaction_status NoTxn NULL NULL NULL string 1770 vectorize on NULL NULL NULL string 1771 vectorize_row_count_threshold 0 NULL NULL NULL string 1772 1773 query TTTTTTT colnames 1774 SELECT 1775 name, setting, unit, context, enumvals, boot_val, reset_val 1776 FROM 1777 pg_catalog.pg_settings 1778 WHERE 1779 name != 'optimizer' AND name != 'crdb_version' AND name != 'session_id' 1780 ---- 1781 name setting unit context enumvals boot_val reset_val 1782 application_name · NULL user NULL · · 1783 bytea_output hex NULL user NULL hex hex 1784 client_encoding UTF8 NULL user NULL UTF8 UTF8 1785 client_min_messages notice NULL user NULL notice notice 1786 database test NULL user NULL · test 1787 datestyle ISO, MDY NULL user NULL ISO, MDY ISO, MDY 1788 default_int_size 8 NULL user NULL 8 8 1789 default_tablespace · NULL user NULL · · 1790 default_transaction_isolation serializable NULL user NULL default default 1791 default_transaction_priority normal NULL user NULL normal normal 1792 default_transaction_read_only off NULL user NULL off off 1793 distsql off NULL user NULL off off 1794 enable_experimental_alter_column_type_general off NULL user NULL off off 1795 enable_implicit_select_for_update on NULL user NULL on on 1796 enable_insert_fast_path on NULL user NULL on on 1797 enable_zigzag_join on NULL user NULL on on 1798 experimental_distsql_planning off NULL user NULL off off 1799 experimental_enable_enums on NULL user NULL off off 1800 experimental_enable_hash_sharded_indexes off NULL user NULL off off 1801 experimental_enable_temp_tables off NULL user NULL off off 1802 experimental_optimizer_foreign_key_cascades on NULL user NULL on on 1803 experimental_partial_indexes off NULL user NULL off off 1804 extra_float_digits 0 NULL user NULL 0 2 1805 force_savepoint_restart off NULL user NULL off off 1806 foreign_key_cascades_limit 10000 NULL user NULL 10000 10000 1807 idle_in_transaction_session_timeout 0 NULL user NULL 0 0 1808 integer_datetimes on NULL user NULL on on 1809 intervalstyle postgres NULL user NULL postgres postgres 1810 locality region=test,dc=dc1 NULL user NULL region=test,dc=dc1 region=test,dc=dc1 1811 lock_timeout 0 NULL user NULL 0 0 1812 max_identifier_length 128 NULL user NULL 128 128 1813 max_index_keys 32 NULL user NULL 32 32 1814 node_id 1 NULL user NULL 1 1 1815 optimizer_foreign_keys on NULL user NULL on on 1816 optimizer_use_histograms on NULL user NULL on on 1817 optimizer_use_multicol_stats on NULL user NULL on on 1818 reorder_joins_limit 4 NULL user NULL 4 4 1819 require_explicit_primary_keys off NULL user NULL off off 1820 results_buffer_size 16384 NULL user NULL 16384 16384 1821 row_security off NULL user NULL off off 1822 search_path public NULL user NULL public public 1823 serial_normalization rowid NULL user NULL rowid rowid 1824 server_encoding UTF8 NULL user NULL UTF8 UTF8 1825 server_version 9.5.0 NULL user NULL 9.5.0 9.5.0 1826 server_version_num 90500 NULL user NULL 90500 90500 1827 session_user root NULL user NULL root root 1828 sql_safe_updates off NULL user NULL off off 1829 standard_conforming_strings on NULL user NULL on on 1830 statement_timeout 0 NULL user NULL 0 0 1831 synchronize_seqscans on NULL user NULL on on 1832 timezone UTC NULL user NULL UTC UTC 1833 tracing off NULL user NULL off off 1834 transaction_isolation serializable NULL user NULL serializable serializable 1835 transaction_priority normal NULL user NULL normal normal 1836 transaction_read_only off NULL user NULL off off 1837 transaction_status NoTxn NULL user NULL NoTxn NoTxn 1838 vectorize on NULL user NULL on on 1839 vectorize_row_count_threshold 0 NULL user NULL 0 0 1840 1841 query TTTTTT colnames 1842 SELECT name, source, min_val, max_val, sourcefile, sourceline FROM pg_catalog.pg_settings 1843 ---- 1844 name source min_val max_val sourcefile sourceline 1845 application_name NULL NULL NULL NULL NULL 1846 bytea_output NULL NULL NULL NULL NULL 1847 client_encoding NULL NULL NULL NULL NULL 1848 client_min_messages NULL NULL NULL NULL NULL 1849 crdb_version NULL NULL NULL NULL NULL 1850 database NULL NULL NULL NULL NULL 1851 datestyle NULL NULL NULL NULL NULL 1852 default_int_size NULL NULL NULL NULL NULL 1853 default_tablespace NULL NULL NULL NULL NULL 1854 default_transaction_isolation NULL NULL NULL NULL NULL 1855 default_transaction_priority NULL NULL NULL NULL NULL 1856 default_transaction_read_only NULL NULL NULL NULL NULL 1857 distsql NULL NULL NULL NULL NULL 1858 enable_experimental_alter_column_type_general NULL NULL NULL NULL NULL 1859 enable_implicit_select_for_update NULL NULL NULL NULL NULL 1860 enable_insert_fast_path NULL NULL NULL NULL NULL 1861 enable_zigzag_join NULL NULL NULL NULL NULL 1862 experimental_distsql_planning NULL NULL NULL NULL NULL 1863 experimental_enable_enums NULL NULL NULL NULL NULL 1864 experimental_enable_hash_sharded_indexes NULL NULL NULL NULL NULL 1865 experimental_enable_temp_tables NULL NULL NULL NULL NULL 1866 experimental_optimizer_foreign_key_cascades NULL NULL NULL NULL NULL 1867 experimental_partial_indexes NULL NULL NULL NULL NULL 1868 extra_float_digits NULL NULL NULL NULL NULL 1869 force_savepoint_restart NULL NULL NULL NULL NULL 1870 foreign_key_cascades_limit NULL NULL NULL NULL NULL 1871 idle_in_transaction_session_timeout NULL NULL NULL NULL NULL 1872 integer_datetimes NULL NULL NULL NULL NULL 1873 intervalstyle NULL NULL NULL NULL NULL 1874 locality NULL NULL NULL NULL NULL 1875 lock_timeout NULL NULL NULL NULL NULL 1876 max_identifier_length NULL NULL NULL NULL NULL 1877 max_index_keys NULL NULL NULL NULL NULL 1878 node_id NULL NULL NULL NULL NULL 1879 optimizer NULL NULL NULL NULL NULL 1880 optimizer_foreign_keys NULL NULL NULL NULL NULL 1881 optimizer_use_histograms NULL NULL NULL NULL NULL 1882 optimizer_use_multicol_stats NULL NULL NULL NULL NULL 1883 reorder_joins_limit NULL NULL NULL NULL NULL 1884 require_explicit_primary_keys NULL NULL NULL NULL NULL 1885 results_buffer_size NULL NULL NULL NULL NULL 1886 row_security NULL NULL NULL NULL NULL 1887 search_path NULL NULL NULL NULL NULL 1888 serial_normalization NULL NULL NULL NULL NULL 1889 server_encoding NULL NULL NULL NULL NULL 1890 server_version NULL NULL NULL NULL NULL 1891 server_version_num NULL NULL NULL NULL NULL 1892 session_id NULL NULL NULL NULL NULL 1893 session_user NULL NULL NULL NULL NULL 1894 sql_safe_updates NULL NULL NULL NULL NULL 1895 standard_conforming_strings NULL NULL NULL NULL NULL 1896 statement_timeout NULL NULL NULL NULL NULL 1897 synchronize_seqscans NULL NULL NULL NULL NULL 1898 timezone NULL NULL NULL NULL NULL 1899 tracing NULL NULL NULL NULL NULL 1900 transaction_isolation NULL NULL NULL NULL NULL 1901 transaction_priority NULL NULL NULL NULL NULL 1902 transaction_read_only NULL NULL NULL NULL NULL 1903 transaction_status NULL NULL NULL NULL NULL 1904 vectorize NULL NULL NULL NULL NULL 1905 vectorize_row_count_threshold NULL NULL NULL NULL NULL 1906 1907 # pg_catalog.pg_sequence 1908 1909 statement ok 1910 CREATE DATABASE seq 1911 1912 query OOIIIIIB 1913 SELECT * FROM pg_catalog.pg_sequence 1914 ---- 1915 1916 statement ok 1917 CREATE SEQUENCE foo 1918 1919 statement ok 1920 CREATE SEQUENCE bar MAXVALUE 10 MINVALUE 5 START 6 INCREMENT 2 1921 1922 query OOIIIIIB colnames 1923 SELECT * FROM pg_catalog.pg_sequence 1924 ---- 1925 seqrelid seqtypid seqstart seqincrement seqmax seqmin seqcache seqcycle 1926 68 20 1 1 9223372036854775807 1 1 false 1927 69 20 6 2 10 5 1 false 1928 1929 statement ok 1930 DROP DATABASE seq 1931 1932 statement ok 1933 SET database = constraint_db 1934 1935 # Verify sequences can't be seen from another database. 1936 query OOIIIIIB 1937 SELECT * FROM pg_catalog.pg_sequence 1938 ---- 1939 1940 ## pg_catalog.pg_operator 1941 1942 query OTOOTBBOOOOOOOO colnames 1943 SELECT * FROM pg_catalog.pg_operator where oprname='+' and oprleft='float8'::regtype 1944 ---- 1945 oid oprname oprnamespace oprowner oprkind oprcanmerge oprcanhash oprleft oprright oprresult oprcom oprnegate oprcode oprrest oprjoin 1946 74817020 + 1307062959 NULL b false false 701 701 701 NULL NULL NULL NULL NULL 1947 1948 # Verify proper functionality of system information functions. 1949 1950 query TT 1951 SELECT pg_catalog.pg_get_expr('1', 0), pg_catalog.pg_get_expr('1', 0::OID) 1952 ---- 1953 1 1 1954 1955 query T 1956 SELECT pg_catalog.pg_get_expr('1', 0, true) 1957 ---- 1958 1 1959 1960 statement ok 1961 SET DATABASE = constraint_db 1962 1963 query OTT 1964 SELECT def.oid, c.relname, pg_catalog.pg_get_expr(def.adbin, def.adrelid) 1965 FROM pg_catalog.pg_attrdef def 1966 JOIN pg_catalog.pg_class c ON def.adrelid = c.oid 1967 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 1968 WHERE n.nspname = 'public' 1969 ---- 1970 1666782879 t1 12 1971 841178406 t2 unique_rowid() 1972 2186255414 t3 'FOO'::STRING 1973 2186255409 t3 unique_rowid() 1974 1975 # Verify that a set database shows tables from that database for a non-root 1976 # user, when that user has permissions. 1977 1978 statement ok 1979 GRANT ALL ON constraint_db.* TO testuser 1980 1981 user testuser 1982 1983 statement ok 1984 SET DATABASE = 'constraint_db' 1985 1986 query I 1987 SELECT count(*) FROM pg_catalog.pg_tables WHERE schemaname='public' 1988 ---- 1989 3 1990 1991 user root 1992 1993 # Verify that an unset database shows tables across databases. 1994 # But only those items visible to this user are reported. 1995 # (Tests below show that root sees more). 1996 1997 statement ok 1998 SET DATABASE = '' 1999 2000 query error cannot access virtual schema in anonymous database 2001 SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' ORDER BY 1 2002 2003 query error cannot access virtual schema in anonymous database 2004 SELECT viewname FROM pg_catalog.pg_views WHERE schemaname='public' ORDER BY 1 2005 2006 query error cannot access virtual schema in anonymous database 2007 SELECT relname FROM pg_catalog.pg_class c 2008 JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid 2009 WHERE nspname='public' 2010 2011 query error cannot access virtual schema in anonymous database 2012 SELECT conname FROM pg_catalog.pg_constraint con 2013 JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid 2014 WHERE n.nspname = 'public' 2015 2016 query error cannot access virtual schema in anonymous database 2017 SELECT count(*) FROM pg_catalog.pg_depend 2018 2019 query error cannot access virtual schema in anonymous database 2020 select 'upper'::REGPROC; 2021 2022 statement ok 2023 SET DATABASE = test 2024 2025 ## #13567 2026 ## regproc columns display as text but can still be joined against oid columns 2027 query OTO 2028 SELECT p.oid, p.proname, t.typinput 2029 FROM pg_proc p 2030 JOIN pg_type t ON t.typinput = p.oid 2031 WHERE t.typname = '_int4' 2032 ---- 2033 780513238 array_in array_in 2034 2035 ## #16285 2036 ## int2vectors should be 0-indexed 2037 query I 2038 SELECT count(*) FROM pg_catalog.pg_index WHERE indkey[0] IS NULL; 2039 ---- 2040 0 2041 2042 ## Ensure no two builtins have the same oid. 2043 query I 2044 SELECT c FROM (SELECT oid, count(*) as c FROM pg_catalog.pg_proc GROUP BY oid) WHERE c > 1 2045 ---- 2046 2047 ## Ensure that unnest works with oid wrapper arrays 2048 2049 query O 2050 SELECT unnest((SELECT proargtypes FROM pg_proc WHERE proname='split_part')); 2051 ---- 2052 25 2053 25 2054 20 2055 2056 subtest pg_catalog.pg_prepare_statement 2057 2058 statement ok 2059 CREATE TABLE types(a timestamptz, b integer) 2060 2061 statement ok 2062 PREPARE test_insert_statement (integer, timestamptz) AS INSERT INTO types VALUES ($2, $1) 2063 2064 statement ok 2065 PREPARE test_select_statement AS SELECT * FROM types 2066 2067 query TTTB 2068 select name, statement, parameter_types, from_sql from pg_prepared_statements ORDER BY 1 2069 ---- 2070 test_insert_statement PREPARE test_insert_statement (int, timestamptz) AS INSERT INTO types VALUES ($2, $1) {bigint,"'timestamp with time zone'"} true 2071 test_select_statement PREPARE test_select_statement AS SELECT * FROM types {} true 2072 2073 statement ok 2074 DROP TABLE types 2075 2076 ## TODO(masha): #16769 2077 #statement ok 2078 #CREATE TABLE types(a int8, b int2); 2079 2080 #query I 2081 #SELECT attname, atttypid, typname FROM pg_attribute a JOIN pg_type t ON a.atttypid=t.oid WHERE attrelid = 'types'::REGCLASS; 2082 #attname atttypid typname 2083 #a 20 int8 2084 #b 20 int2 2085 2086 subtest pg_catalog.pg_seclabel 2087 2088 query OOOTT colnames 2089 SELECT objoid, classoid, objsubid, provider, label FROM pg_catalog.pg_seclabel 2090 ---- 2091 objoid classoid objsubid provider label 2092 2093 subtest pg_catalog.pg_shseclabel 2094 2095 query OOTT colnames 2096 SELECT objoid, classoid, provider, label FROM pg_catalog.pg_shseclabel 2097 ---- 2098 objoid classoid provider label 2099 2100 subtest pg_catalog.pg_aggregate 2101 2102 query O colnames 2103 SELECT oid::REGPROC FROM pg_proc WHERE proisagg = true EXCEPT SELECT aggfnoid FROM pg_aggregate 2104 ---- 2105 oid 2106 2107 # Check whether correct operator's oid is set for max and bool_or. 2108 query OTO colnames 2109 SELECT c.oid, c.oprname, a.aggsortop FROM pg_aggregate a 2110 JOIN pg_proc b ON a.aggfnoid = b.oid::REGPROC 2111 JOIN pg_operator c ON c.oprname = '>' AND b.proargtypes[0] = c.oprleft AND b.proargtypes[0] = c.oprright 2112 WHERE (b.proname = 'max' OR b.proname = 'bool_or') AND c.oid = a.aggsortop; 2113 ---- 2114 oid oprname aggsortop 2115 1224236426 > 1224236426 2116 3636536082 > 3636536082 2117 3636536082 > 3636536082 2118 2948286002 > 2948286002 2119 3234851498 > 3234851498 2120 2318307066 > 2318307066 2121 1737252658 > 1737252658 2122 1737252658 > 1737252658 2123 1383827510 > 1383827510 2124 2105536758 > 2105536758 2125 1928531314 > 1928531314 2126 3421685890 > 3421685890 2127 883535762 > 883535762 2128 530358714 > 530358714 2129 3802002898 > 3802002898 2130 1737252658 > 1737252658 2131 1737252658 > 1737252658 2132 1064453514 > 1064453514 2133 1778355034 > 1778355034 2134 256681770 > 256681770 2135 2139039570 > 2139039570 2136 3457382662 > 3457382662 2137 1385359122 > 1385359122 2138 2139 # Check whether correct operator's oid is set for min, bool_and and every. 2140 query OTO colnames 2141 SELECT c.oid, c.oprname, a.aggsortop FROM pg_aggregate a 2142 JOIN pg_proc b ON a.aggfnoid = b.oid::REGPROC 2143 JOIN pg_operator c ON c.oprname = '<' AND b.proargtypes[0] = c.oprleft AND b.proargtypes[0] = c.oprright 2144 WHERE (b.proname = 'min' OR b.proname = 'bool_and' OR b.proname = 'every') AND c.oid = a.aggsortop; 2145 ---- 2146 oid oprname aggsortop 2147 3859576864 < 3859576864 2148 2134593616 < 2134593616 2149 2134593616 < 2134593616 2150 2134593616 < 2134593616 2151 1446343536 < 1446343536 2152 2457977576 < 2457977576 2153 2790955336 < 2790955336 2154 235310192 < 235310192 2155 235310192 < 235310192 2156 2011297100 < 2011297100 2157 2104629996 < 2104629996 2158 3942776496 < 3942776496 2159 4132205728 < 4132205728 2160 3676560592 < 3676560592 2161 1494969736 < 1494969736 2162 3842027408 < 3842027408 2163 235310192 < 235310192 2164 235310192 < 235310192 2165 2300570720 < 2300570720 2166 3675947880 < 3675947880 2167 426663592 < 426663592 2168 2699108304 < 2699108304 2169 2897050084 < 2897050084 2170 1579888144 < 1579888144 2171 2172 subtest collated_string_type 2173 2174 statement ok 2175 CREATE TABLE coltab (a STRING COLLATE en) 2176 2177 query OT 2178 SELECT typ.oid, typ.typname FROM pg_attribute att JOIN pg_type typ ON atttypid=typ.oid WHERE attrelid='coltab'::regclass AND attname='a' 2179 ---- 2180 25 text 2181 2182 subtest 31545 2183 2184 # Test an index of 2 referencing an index of 2. 2185 statement ok 2186 CREATE TABLE a ( 2187 id_a_1 INT UNIQUE, 2188 id_a_2 INT, 2189 PRIMARY KEY (id_a_1, id_a_2) 2190 ) 2191 2192 statement ok 2193 CREATE TABLE b ( 2194 id_b_1 INT, 2195 id_b_2 INT, 2196 PRIMARY KEY (id_b_1, id_b_2), 2197 CONSTRAINT my_fkey FOREIGN KEY (id_b_1, id_b_2) REFERENCES a (id_a_1, id_a_2) 2198 ) 2199 2200 query TT colnames 2201 SELECT conkey, confkey FROM pg_catalog.pg_constraint WHERE conname = 'my_fkey' 2202 ---- 2203 conkey confkey 2204 {1,2} {1,2} 2205 2206 # Test an index of 3 referencing an index of 2. 2207 statement ok 2208 DROP TABLE b; 2209 CREATE TABLE b ( 2210 id_b_1 INT, 2211 id_b_2 INT, 2212 id_b_3 INT, 2213 PRIMARY KEY (id_b_1, id_b_2, id_b_3), 2214 CONSTRAINT my_fkey FOREIGN KEY (id_b_1, id_b_2) REFERENCES a (id_a_1, id_a_2) 2215 ) 2216 2217 query TT colnames 2218 SELECT conkey, confkey FROM pg_catalog.pg_constraint WHERE conname = 'my_fkey' 2219 ---- 2220 conkey confkey 2221 {1,2} {1,2} 2222 2223 # Test an index of 3 referencing an index of 1. 2224 statement ok 2225 DROP TABLE b; 2226 CREATE TABLE b ( 2227 id_b_1 INT, 2228 id_b_2 INT, 2229 id_b_3 INT, 2230 PRIMARY KEY (id_b_1, id_b_2, id_b_3), 2231 CONSTRAINT my_fkey FOREIGN KEY (id_b_1) REFERENCES a (id_a_1) 2232 ) 2233 2234 query TT colnames 2235 SELECT conkey, confkey FROM pg_catalog.pg_constraint WHERE conname = 'my_fkey' 2236 ---- 2237 conkey confkey 2238 {1} {1} 2239 2240 subtest regression_34856 2241 2242 statement ok 2243 CREATE DATABASE d34856 2244 2245 statement ok 2246 CREATE TABLE d34856.t(x INT); 2247 CREATE VIEW d34856.v AS SELECT x FROM d34856.t; 2248 CREATE SEQUENCE d34856.s 2249 2250 # Check that only tables show up in pg_tables. 2251 query T 2252 SELECT tablename FROM d34856.pg_catalog.pg_tables WHERE schemaname = 'public' 2253 ---- 2254 t 2255 2256 statement ok 2257 DROP DATABASE d34856 CASCADE 2258 2259 subtest regression_34862 2260 2261 statement ok 2262 CREATE DATABASE d34862; SET database=d34862 2263 2264 statement ok 2265 CREATE TABLE t(x INT UNIQUE); 2266 CREATE TABLE u( 2267 a INT REFERENCES t(x) ON DELETE NO ACTION, 2268 b INT REFERENCES t(x) ON DELETE RESTRICT, 2269 c INT REFERENCES t(x) ON DELETE SET NULL, 2270 d INT DEFAULT 123 REFERENCES t(x) ON DELETE SET DEFAULT, 2271 e INT REFERENCES t(x) ON DELETE CASCADE, 2272 f INT REFERENCES t(x) ON UPDATE NO ACTION, 2273 g INT REFERENCES t(x) ON UPDATE RESTRICT, 2274 h INT REFERENCES t(x) ON UPDATE SET NULL, 2275 i INT DEFAULT 123 REFERENCES t(x) ON UPDATE SET DEFAULT, 2276 j INT REFERENCES t(x) ON UPDATE CASCADE, 2277 k INT REFERENCES t(x) ON DELETE RESTRICT ON UPDATE SET NULL 2278 ); 2279 2280 query TTT 2281 SELECT conname, confupdtype, confdeltype FROM pg_constraint ORDER BY conname 2282 ---- 2283 fk_a_ref_t a a 2284 fk_b_ref_t a r 2285 fk_c_ref_t a n 2286 fk_d_ref_t a d 2287 fk_e_ref_t a c 2288 fk_f_ref_t a a 2289 fk_g_ref_t r a 2290 fk_h_ref_t n a 2291 fk_i_ref_t d a 2292 fk_j_ref_t c a 2293 fk_k_ref_t n r 2294 t_x_key NULL NULL 2295 2296 statement ok 2297 DROP TABLE u; DROP TABLE t 2298 2299 statement ok 2300 CREATE TABLE v(x INT, y INT, UNIQUE (x,y)) 2301 2302 statement ok 2303 CREATE TABLE w( 2304 a INT, b INT, c INT, d INT, 2305 FOREIGN KEY (a,b) REFERENCES v(x,y) MATCH FULL, 2306 FOREIGN KEY (c,d) REFERENCES v(x,y) MATCH SIMPLE 2307 ); 2308 2309 query TT 2310 SELECT conname, confmatchtype FROM pg_constraint ORDER BY conname 2311 ---- 2312 fk_a_ref_v f 2313 fk_c_ref_v s 2314 v_x_y_key NULL 2315 2316 statement ok 2317 DROP DATABASE d34862 CASCADE; SET database=test 2318 2319 subtest regression_35108 2320 2321 query T 2322 SELECT pg_catalog.current_setting('statement_timeout') 2323 ---- 2324 0 2325 2326 query T 2327 SELECT pg_catalog.current_setting('statement_timeout', false) 2328 ---- 2329 0 2330 2331 # check returns null on unsupported session var. 2332 query T 2333 SELECT IFNULL(pg_catalog.current_setting('woo', true), 'OK') 2334 ---- 2335 OK 2336 2337 # check error on nonexistent session var. 2338 query error unrecognized configuration parameter 2339 SELECT pg_catalog.current_setting('woo', false) 2340 2341 # check error on unsupported session var. 2342 query error configuration setting.*not supported 2343 SELECT pg_catalog.current_setting('vacuum_cost_delay', false) 2344 2345 query T 2346 SHOW application_name 2347 ---- 2348 · 2349 2350 query T 2351 SELECT pg_catalog.set_config('application_name', 'woo', false) 2352 ---- 2353 woo 2354 2355 query T 2356 SHOW application_name 2357 ---- 2358 woo 2359 2360 query error transaction-scoped settings are not supported 2361 SELECT pg_catalog.set_config('application_name', 'woo', true) 2362 2363 query error unrecognized configuration parameter 2364 SELECT pg_catalog.set_config('woo', 'woo', false) 2365 2366 query error configuration setting.*not supported 2367 SELECT pg_catalog.set_config('vacuum_cost_delay', '0', false) 2368 2369 subtest regression_46450 2370 2371 statement ok 2372 CREATE TABLE regression_46450 (id UUID PRIMARY KEY, json JSONB) 2373 2374 statement ok 2375 CREATE INDEX regression_46450_idx ON regression_46450 USING gin(json) 2376 2377 query TTTTTT 2378 select * from pg_indexes where indexname = 'regression_46450_idx' 2379 ---- 2380 1962521914 public regression_46450 regression_46450_idx NULL CREATE INDEX regression_46450_idx ON test.public.regression_46450 USING gin (json ASC) 2381 2382 # Make sure that selecting from vtables with indexes in other dbs properly 2383 # hides descriptors that should be hidden. 2384 2385 statement ok 2386 CREATE TABLE hidden_in_vtable_index_test(a int) 2387 2388 let $testid 2389 SELECT oid FROM pg_class WHERE relname='hidden_in_vtable_index_test' 2390 2391 statement ok 2392 CREATE DATABASE other_db; SET DATABASE = other_db 2393 2394 query O 2395 SELECT oid FROM pg_class WHERE oid=$testid 2396 ---- 2397 2398 subtest geospatial 2399 2400 statement ok 2401 SET DATABASE = test 2402 2403 statement ok 2404 CREATE TABLE geospatial_table ( 2405 id UUID PRIMARY KEY, 2406 a geography(geometry, 4326), 2407 b geometry(point, 4326), 2408 INVERTED INDEX idxa (a) 2409 ) 2410 2411 statement ok 2412 CREATE INVERTED INDEX idxb ON geospatial_table (b) 2413 2414 query TT colnames 2415 SELECT indexname, indexdef 2416 FROM pg_catalog.pg_indexes 2417 WHERE tablename = 'geospatial_table' 2418 ---- 2419 indexname indexdef 2420 primary CREATE UNIQUE INDEX "primary" ON test.public.geospatial_table USING btree (id ASC) 2421 idxa CREATE INDEX idxa ON test.public.geospatial_table USING gin (a ASC) 2422 idxb CREATE INDEX idxb ON test.public.geospatial_table USING gin (b ASC) 2423 2424 subtest regression_46799 2425 statement ok 2426 CREATE TABLE t(x INT DEFAULT 1, y INT DEFAULT 1); 2427 2428 query I 2429 SELECT adnum FROM pg_attrdef WHERE adrelid = 89 2430 ---- 2431 1 2432 2 2433 3 2434 2435 statement ok 2436 ALTER TABLE t DROP COLUMN y; 2437 ALTER TABLE t ADD COLUMN y INT DEFAULT 1; 2438 2439 # Make sure after adding and dropping the same column, the adnum for the re-added column increases. 2440 query I 2441 select adnum from pg_attrdef WHERE adrelid = 89 2442 ---- 2443 1 2444 3 2445 4 2446 2447 # Check virtual table lookup joins. 2448 statement ok 2449 CREATE TABLE jt (a INT PRIMARY KEY); INSERT INTO jt VALUES(1); INSERT INTO jt VALUES('jt'::regclass::int) 2450 2451 query ITT 2452 SELECT a, oid, relname FROM jt INNER LOOKUP JOIN pg_class ON a::oid=oid 2453 ---- 2454 90 90 jt 2455 2456 query ITT 2457 SELECT a, oid, relname FROM jt LEFT OUTER LOOKUP JOIN pg_class ON a::oid=oid 2458 ---- 2459 1 NULL NULL 2460 90 90 jt 2461 2462 subtest regression_49207 2463 statement ok 2464 CREATE ROLE role_test_login with LOGIN; 2465 CREATE ROLE role_test_nologin; 2466 2467 query B 2468 SELECT rolcanlogin FROM pg_roles WHERE rolname = 'role_test_login'; 2469 ---- 2470 true 2471 2472 query B 2473 SELECT rolcanlogin FROM pg_roles WHERE rolname = 'role_test_nologin'; 2474 ---- 2475 false