Materialize/test/sqllogictest/cockroach/pg_catalog.slt

# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved.
# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.
#
# This file is derived from the logic test suite in CockroachDB. The
# original file was retrieved on June 10, 2019 from:
#
#     https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/pg_catalog
#
# The original source code is subject to the terms of the Apache
# 2.0 license, a copy of which can be found in the LICENSE file at the
# root of this repository.

mode cockroach

# We are not currently trying to be this PostgreSQL compatible. Perhaps someday.
halt

statement ok
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = false

# Verify pg_catalog database handles mutation statements correctly.

query error database "pg_catalog" does not exist
ALTER DATABASE pg_catalog RENAME TO not_pg_catalog

statement error schema cannot be modified: "pg_catalog"
CREATE TABLE pg_catalog.t (x INT)

query error database "pg_catalog" does not exist
DROP DATABASE pg_catalog

query T
SHOW TABLES FROM pg_catalog
----
pg_am
pg_attrdef
pg_attribute
pg_auth_members
pg_class
pg_collation
pg_constraint
pg_database
pg_depend
pg_description
pg_enum
pg_extension
pg_foreign_data_wrapper
pg_foreign_server
pg_foreign_table
pg_index
pg_indexes
pg_inherits
pg_language
pg_namespace
pg_operator
pg_proc
pg_range
pg_rewrite
pg_roles
pg_seclabel
pg_sequence
pg_settings
pg_shdescription
pg_shseclabel
pg_stat_activity
pg_tables
pg_tablespace
pg_trigger
pg_type
pg_user
pg_user_mapping
pg_views

# Verify "pg_catalog" is a regular db name

statement ok
CREATE DATABASE other_db

statement ok
ALTER DATABASE other_db RENAME TO pg_catalog

statement error database "pg_catalog" already exists
CREATE DATABASE pg_catalog

statement ok
DROP DATABASE pg_catalog

# the following query checks that the planDataSource instantiated from
# a virtual table in the FROM clause is properly deallocated even when
# query preparation causes an error. database-issues#2980
query error unknown function
SELECT * FROM pg_catalog.pg_class c WHERE nonexistent_function()

# Verify pg_catalog handles reflection correctly.

query T
SHOW TABLES FROM test.pg_catalog
----
pg_am
pg_attrdef
pg_attribute
pg_auth_members
pg_class
pg_collation
pg_constraint
pg_database
pg_depend
pg_description
pg_enum
pg_extension
pg_foreign_data_wrapper
pg_foreign_server
pg_foreign_table
pg_index
pg_indexes
pg_inherits
pg_language
pg_namespace
pg_operator
pg_proc
pg_range
pg_rewrite
pg_roles
pg_seclabel
pg_sequence
pg_settings
pg_shdescription
pg_shseclabel
pg_stat_activity
pg_tables
pg_tablespace
pg_trigger
pg_type
pg_user
pg_user_mapping
pg_views

query TT colnames
SHOW CREATE TABLE pg_catalog.pg_namespace
----
table_name               create_statement
pg_catalog.pg_namespace  CREATE TABLE pg_namespace (
                         oid OID NULL,
                         nspname NAME NOT NULL,
                         nspowner OID NULL,
                         nspacl STRING[] NULL
)

query TTBTTTB colnames
SHOW COLUMNS FROM pg_catalog.pg_namespace
----
column_name  data_type  is_nullable  column_default  generation_expression  indices  is_hidden
oid          OID        true         NULL            ·                      {}       false
nspname      NAME       false        NULL            ·                      {}       false
nspowner     OID        true         NULL            ·                      {}       false
nspacl       STRING[]   true         NULL            ·                      {}       false

query TTBITTBB colnames
SHOW INDEXES ON pg_catalog.pg_namespace
----
table_name  index_name  non_unique  seq_in_index  column_name  direction  storing  implicit

query TTTTB colnames
SHOW CONSTRAINTS FROM pg_catalog.pg_namespace
----
table_name  constraint_name  constraint_type  details  validated

query TTTTT colnames
SHOW GRANTS ON pg_catalog.pg_namespace
----
database_name  schema_name  table_name    grantee  privilege_type
test           pg_catalog   pg_namespace  public   SELECT


# Verify selecting from pg_catalog.

statement ok
CREATE DATABASE constraint_db

statement ok
CREATE TABLE constraint_db.t1 (
  p FLOAT PRIMARY KEY,
  a INT UNIQUE,
  b INT,
  c INT DEFAULT 12,
  d VARCHAR(5),
  e BIT(5),
  f DECIMAL(10,7),
  UNIQUE INDEX index_key(b, c)
)

statement ok
CREATE TABLE constraint_db.t2 (
    t1_ID INT,
    CONSTRAINT fk FOREIGN KEY (t1_ID) REFERENCES constraint_db.t1(a),
    INDEX (t1_ID)
)

statement ok
CREATE TABLE constraint_db.t3 (
    a INT,
    b INT CHECK (b > 11),
    c STRING DEFAULT 'FOO',
    CONSTRAINT fk FOREIGN KEY (a, b) REFERENCES constraint_db.t1(b, c),
    INDEX (a, b DESC) STORING (c)
)

statement ok
CREATE VIEW constraint_db.v1 AS SELECT p,a,b,c FROM constraint_db.t1

## pg_catalog.pg_namespace

query OTOT colnames
SELECT * FROM pg_catalog.pg_namespace
----
oid         nspname             nspowner  nspacl
3604332469  crdb_internal       NULL      NULL
3672231114  information_schema  NULL      NULL
2508829085  pg_catalog          NULL      NULL
3426283741  public              NULL      NULL

## pg_catalog.pg_database

query OTOITTBB colnames
SELECT oid, datname, datdba, encoding, datcollate, datctype, datistemplate, datallowconn
FROM pg_catalog.pg_database
ORDER BY oid
----
oid  datname        datdba  encoding  datcollate  datctype    datistemplate  datallowconn
1    system         NULL    6         en_US.utf8  en_US.utf8  false          true
50   materialize      NULL    6         en_US.utf8  en_US.utf8  false          true
51   postgres       NULL    6         en_US.utf8  en_US.utf8  false          true
52   test           NULL    6         en_US.utf8  en_US.utf8  false          true
54   constraint_db  NULL    6         en_US.utf8  en_US.utf8  false          true

query OTIOIIOT colnames
SELECT oid, datname, datconnlimit, datlastsysoid, datfrozenxid, datminmxid, dattablespace, datacl
FROM pg_catalog.pg_database
ORDER BY oid
----
oid  datname        datconnlimit  datlastsysoid  datfrozenxid  datminmxid  dattablespace  datacl
1    system         -1            NULL           NULL          NULL        0              NULL
50   materialize      -1            NULL           NULL          NULL        0              NULL
51   postgres       -1            NULL           NULL          NULL        0              NULL
52   test           -1            NULL           NULL          NULL        0              NULL
54   constraint_db  -1            NULL           NULL          NULL        0              NULL

## pg_catalog.pg_tables

statement ok
SET DATABASE = constraint_db

query TTTTBBBB colnames
SELECT * FROM constraint_db.pg_catalog.pg_tables WHERE schemaname = 'public'
----
schemaname  tablename  tableowner  tablespace  hasindexes  hasrules  hastriggers  rowsecurity
public      t1         NULL        NULL        true        false     false        false
public      t2         NULL        NULL        true        false     false        false
public      t3         NULL        NULL        true        false     false        false

query TB colnames
SELECT tablename, hasindexes FROM pg_catalog.pg_tables WHERE schemaname = 'information_schema' AND tablename LIKE '%table%'
----
tablename          hasindexes
role_table_grants  false
table_constraints  false
table_privileges   false
tables             false

## pg_catalog.pg_tablespace

query OTOTT colnames
SELECT oid, spcname, spcowner, spcacl, spcoptions FROM pg_tablespace
----
oid  spcname     spcowner  spcacl  spcoptions
0    pg_default  NULL      NULL    NULL

## pg_catalog.pg_views

query TTTT colnames
SELECT * FROM pg_catalog.pg_views
----
schemaname  viewname  viewowner  definition
public      v1        NULL       SELECT p, a, b, c FROM constraint_db.public.t1

## pg_catalog.pg_class

query OTOOOOOO colnames
SELECT c.oid, relname, relnamespace, reltype, relowner, relam, relfilenode, reltablespace
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
oid         relname       relnamespace  reltype  relowner  relam  relfilenode  reltablespace
55          t1            2332901747    0        NULL      NULL   0            0
450499963   primary       2332901747    0        NULL      NULL   0            0
450499960   t1_a_key      2332901747    0        NULL      NULL   0            0
450499961   index_key     2332901747    0        NULL      NULL   0            0
56          t2            2332901747    0        NULL      NULL   0            0
2315049508  primary       2332901747    0        NULL      NULL   0            0
2315049511  t2_t1_id_idx  2332901747    0        NULL      NULL   0            0
57          t3            2332901747    0        NULL      NULL   0            0
969972501   primary       2332901747    0        NULL      NULL   0            0
969972502   t3_a_b_idx    2332901747    0        NULL      NULL   0            0
58          v1            2332901747    0        NULL      NULL   0            0

query TIRIOBBT colnames
SELECT relname, relpages, reltuples, relallvisible, reltoastrelid, relhasindex, relisshared, relpersistence
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
relname       relpages  reltuples  relallvisible  reltoastrelid  relhasindex  relisshared  relpersistence
t1            NULL      NULL       0              0              true         false        p
primary       NULL      NULL       0              0              false        false        p
t1_a_key      NULL      NULL       0              0              false        false        p
index_key     NULL      NULL       0              0              false        false        p
t2            NULL      NULL       0              0              true         false        p
primary       NULL      NULL       0              0              false        false        p
t2_t1_id_idx  NULL      NULL       0              0              false        false        p
t3            NULL      NULL       0              0              true         false        p
primary       NULL      NULL       0              0              false        false        p
t3_a_b_idx    NULL      NULL       0              0              false        false        p
v1            NULL      NULL       0              0              false        false        p

query TBTIIBB colnames
SELECT relname, relistemp, relkind, relnatts, relchecks, relhasoids, relhaspkey
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
relname       relistemp  relkind  relnatts  relchecks  relhasoids  relhaspkey
t1            false      r        7         0          false       true
primary       false      i        1         0          false       false
t1_a_key      false      i        1         0          false       false
index_key     false      i        2         0          false       false
t2            false      r        2         0          false       true
primary       false      i        1         0          false       false
t2_t1_id_idx  false      i        1         0          false       false
t3            false      r        4         1          false       true
primary       false      i        1         0          false       false
t3_a_b_idx    false      i        2         0          false       false
v1            false      v        4         0          false       false

query TBBBITT colnames
SELECT relname, relhasrules, relhastriggers, relhassubclass, relfrozenxid, relacl, reloptions
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
relname       relhasrules  relhastriggers  relhassubclass  relfrozenxid  relacl  reloptions
t1            false        false           false           0             NULL    NULL
primary       false        false           false           0             NULL    NULL
t1_a_key      false        false           false           0             NULL    NULL
index_key     false        false           false           0             NULL    NULL
t2            false        false           false           0             NULL    NULL
primary       false        false           false           0             NULL    NULL
t2_t1_id_idx  false        false           false           0             NULL    NULL
t3            false        false           false           0             NULL    NULL
primary       false        false           false           0             NULL    NULL
t3_a_b_idx    false        false           false           0             NULL    NULL
v1            false        false           false           0             NULL    NULL

## pg_catalog.pg_attribute

query OTTOIIIII colnames
SELECT attrelid, c.relname, attname, atttypid, attstattarget, attlen, attnum, attndims, attcacheoff
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
attrelid    relname       attname  atttypid  attstattarget  attlen  attnum  attndims  attcacheoff
55          t1            p        701       0              8       1       0         -1
55          t1            a        20        0              8       2       0         -1
55          t1            b        20        0              8       3       0         -1
55          t1            c        20        0              8       4       0         -1
55          t1            d        1043      0              -1      5       0         -1
55          t1            e        1560      0              -1      6       0         -1
55          t1            f        1700      0              -1      7       0         -1
450499963   primary       p        701       0              8       1       0         -1
450499960   t1_a_key      a        20        0              8       2       0         -1
450499961   index_key     b        20        0              8       3       0         -1
450499961   index_key     c        20        0              8       4       0         -1
56          t2            t1_id    20        0              8       1       0         -1
56          t2            rowid    20        0              8       2       0         -1
2315049508  primary       rowid    20        0              8       2       0         -1
2315049511  t2_t1_id_idx  t1_id    20        0              8       1       0         -1
57          t3            a        20        0              8       1       0         -1
57          t3            b        20        0              8       2       0         -1
57          t3            c        25        0              -1      3       0         -1
57          t3            rowid    20        0              8       4       0         -1
969972501   primary       rowid    20        0              8       4       0         -1
969972502   t3_a_b_idx    a        20        0              8       1       0         -1
969972502   t3_a_b_idx    b        20        0              8       2       0         -1
58          v1            p        701       0              8       1       0         -1
58          v1            a        20        0              8       2       0         -1
58          v1            b        20        0              8       3       0         -1
58          v1            c        20        0              8       4       0         -1

query TTIBTTBB colnames
SELECT c.relname, attname, atttypmod, attbyval, attstorage, attalign, attnotnull, atthasdef
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
relname       attname  atttypmod  attbyval  attstorage  attalign  attnotnull  atthasdef
t1            p        -1         NULL      NULL        NULL      true        false
t1            a        -1         NULL      NULL        NULL      false       false
t1            b        -1         NULL      NULL        NULL      false       false
t1            c        -1         NULL      NULL        NULL      false       true
t1            d        9          NULL      NULL        NULL      false       false
t1            e        5          NULL      NULL        NULL      false       false
t1            f        655371     NULL      NULL        NULL      false       false
primary       p        -1         NULL      NULL        NULL      true        false
t1_a_key      a        -1         NULL      NULL        NULL      false       false
index_key     b        -1         NULL      NULL        NULL      false       false
index_key     c        -1         NULL      NULL        NULL      false       true
t2            t1_id    -1         NULL      NULL        NULL      false       false
t2            rowid    -1         NULL      NULL        NULL      true        true
primary       rowid    -1         NULL      NULL        NULL      true        true
t2_t1_id_idx  t1_id    -1         NULL      NULL        NULL      false       false
t3            a        -1         NULL      NULL        NULL      false       false
t3            b        -1         NULL      NULL        NULL      false       false
t3            c        -1         NULL      NULL        NULL      false       true
t3            rowid    -1         NULL      NULL        NULL      true        true
primary       rowid    -1         NULL      NULL        NULL      true        true
t3_a_b_idx    a        -1         NULL      NULL        NULL      false       false
t3_a_b_idx    b        -1         NULL      NULL        NULL      false       false
v1            p        -1         NULL      NULL        NULL      true        false
v1            a        -1         NULL      NULL        NULL      true        false
v1            b        -1         NULL      NULL        NULL      true        false
v1            c        -1         NULL      NULL        NULL      true        false

query TTBBITTT colnames
SELECT c.relname, attname, attisdropped, attislocal, attinhcount, attacl, attoptions, attfdwoptions
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
relname       attname  attisdropped  attislocal  attinhcount  attacl  attoptions  attfdwoptions
t1            p        false         true        0            NULL    NULL        NULL
t1            a        false         true        0            NULL    NULL        NULL
t1            b        false         true        0            NULL    NULL        NULL
t1            c        false         true        0            NULL    NULL        NULL
t1            d        false         true        0            NULL    NULL        NULL
t1            e        false         true        0            NULL    NULL        NULL
t1            f        false         true        0            NULL    NULL        NULL
primary       p        false         true        0            NULL    NULL        NULL
t1_a_key      a        false         true        0            NULL    NULL        NULL
index_key     b        false         true        0            NULL    NULL        NULL
index_key     c        false         true        0            NULL    NULL        NULL
t2            t1_id    false         true        0            NULL    NULL        NULL
t2            rowid    false         true        0            NULL    NULL        NULL
primary       rowid    false         true        0            NULL    NULL        NULL
t2_t1_id_idx  t1_id    false         true        0            NULL    NULL        NULL
t3            a        false         true        0            NULL    NULL        NULL
t3            b        false         true        0            NULL    NULL        NULL
t3            c        false         true        0            NULL    NULL        NULL
t3            rowid    false         true        0            NULL    NULL        NULL
primary       rowid    false         true        0            NULL    NULL        NULL
t3_a_b_idx    a        false         true        0            NULL    NULL        NULL
t3_a_b_idx    b        false         true        0            NULL    NULL        NULL
v1            p        false         true        0            NULL    NULL        NULL
v1            a        false         true        0            NULL    NULL        NULL
v1            b        false         true        0            NULL    NULL        NULL
v1            c        false         true        0            NULL    NULL        NULL

# Check relkind codes.
statement ok
CREATE DATABASE relkinds

statement ok
SET DATABASE = relkinds

statement ok
CREATE TABLE tbl_test (k int primary key, v int)

statement ok
CREATE INDEX tbl_test_v_idx ON tbl_test (v)

statement ok
CREATE VIEW view_test AS SELECT k, v FROM tbl_test ORDER BY v

statement ok
CREATE SEQUENCE seq_test

query TT
SELECT relname, relkind
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
ORDER BY relname
----
primary         i
seq_test        S
tbl_test        r
tbl_test_v_idx  i
view_test       v

statement ok
DROP DATABASE relkinds

statement ok
SET DATABASE = constraint_db

# Select all columns with collations.
query TTTOT colnames
SELECT c.relname, attname, t.typname, attcollation, k.collname
FROM pg_catalog.pg_attribute a
JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
JOIN pg_catalog.pg_collation k ON a.attcollation = k.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
relname  attname  typname  attcollation  collname
t1       d        varchar  3903121477    en-US
t3       c        text     3903121477    en-US


## pg_catalog.pg_am

query OTIIBBBBBBBBBBBOOOOOOOOOOOOOOOOOT colnames
SELECT *
FROM pg_catalog.pg_am
----
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
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

## pg_catalog.pg_attrdef

query OTOITT colnames
SELECT ad.oid, c.relname, adrelid, adnum, adbin, adsrc
FROM pg_catalog.pg_attrdef ad
JOIN pg_catalog.pg_class c ON ad.adrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
oid         relname  adrelid  adnum  adbin           adsrc
1666782879  t1       55       4      12:::INT8       12:::INT8
841178406   t2       56       2      unique_rowid()  unique_rowid()
2186255414  t3       57       3      'FOO':::STRING  'FOO':::STRING
2186255409  t3       57       4      unique_rowid()  unique_rowid()

## pg_catalog.pg_indexes

query OTTTT colnames
SELECT crdb_oid, schemaname, tablename, indexname, tablespace
FROM pg_catalog.pg_indexes
WHERE schemaname = 'public'
----
crdb_oid    schemaname  tablename  indexname     tablespace
450499963   public      t1         primary       NULL
450499960   public      t1         t1_a_key      NULL
450499961   public      t1         index_key     NULL
2315049508  public      t2         primary       NULL
2315049511  public      t2         t2_t1_id_idx  NULL
969972501   public      t3         primary       NULL
969972502   public      t3         t3_a_b_idx    NULL

query OTTT colnames
SELECT crdb_oid, tablename, indexname, indexdef
FROM pg_catalog.pg_indexes
WHERE schemaname = 'public'
----
crdb_oid    tablename  indexname     indexdef
450499963   t1         primary       CREATE UNIQUE INDEX "primary" ON constraint_db.public.t1 (p ASC)
450499960   t1         t1_a_key      CREATE UNIQUE INDEX t1_a_key ON constraint_db.public.t1 (a ASC)
450499961   t1         index_key     CREATE UNIQUE INDEX index_key ON constraint_db.public.t1 (b ASC, c ASC)
2315049508  t2         primary       CREATE UNIQUE INDEX "primary" ON constraint_db.public.t2 (rowid ASC)
2315049511  t2         t2_t1_id_idx  CREATE INDEX t2_t1_id_idx ON constraint_db.public.t2 (t1_id ASC)
969972501   t3         primary       CREATE UNIQUE INDEX "primary" ON constraint_db.public.t3 (rowid ASC)
969972502   t3         t3_a_b_idx    CREATE INDEX t3_a_b_idx ON constraint_db.public.t3 (a ASC, b DESC) STORING (c)

## pg_catalog.pg_index

query OOIBBB colnames
SELECT indexrelid, indrelid, indnatts, indisunique, indisprimary, indisexclusion
FROM pg_catalog.pg_index
WHERE indnatts = 2
----
indexrelid  indrelid  indnatts  indisunique  indisprimary  indisexclusion
450499961   55        2         true         false         false
969972502   57        2         false        false         false

query OBBBBB colnames
SELECT indexrelid, indimmediate, indisclustered, indisvalid, indcheckxmin, indisready
FROM pg_catalog.pg_index
WHERE indnatts = 2
----
indexrelid  indimmediate  indisclustered  indisvalid  indcheckxmin  indisready
450499961   true          false           true        false         false
969972502   false         false           true        false         false

query OOBBTTTTTT colnames
SELECT indexrelid, indrelid, indislive, indisreplident, indkey, indcollation, indclass, indoption, indexprs, indpred
FROM pg_catalog.pg_index
WHERE indnatts = 2
----
indexrelid  indrelid  indislive  indisreplident  indkey  indcollation  indclass  indoption  indexprs  indpred
450499961   55        true       false           3 4     0 0           0 0       0 0        NULL      NULL
969972502   57        true       false           1 2     0 0           0 0       0 0        NULL      NULL

statement ok
SET DATABASE = system

query OOIBBBBBBBBBBTTTTTT colnames
SELECT *
FROM pg_catalog.pg_index
ORDER BY indexrelid
----
indexrelid  indrelid  indnatts  indisunique  indisprimary  indisexclusion  indimmediate  indisclustered  indisvalid  indcheckxmin  indisready  indislive  indisreplident  indkey   indcollation           indclass  indoption  indexprs  indpred
543291288   23        1         false        false         false           false         false           true        false         false       true       false           1        3903121477             0         0          NULL      NULL
543291289   23        1         false        false         false           false         false           true        false         false       true       false           2        3903121477             0         0          NULL      NULL
543291291   23        2         true         true          false           true          false           true        false         false       true       false           1 2      3903121477 3903121477  0 0       0 0        NULL      NULL
1276104432  12        2         true         true          false           true          false           true        false         false       true       false           1 6      0 0                    0 0       0 0        NULL      NULL
1582236367  3         1         true         true          false           true          false           true        false         false       true       false           1        0                      0         0          NULL      NULL
1628632028  19        1         false        false         false           false         false           true        false         false       true       false           5        0                      0         0          NULL      NULL
1628632029  19        1         false        false         false           false         false           true        false         false       true       false           4        0                      0         0          NULL      NULL
1628632031  19        1         true         true          false           true          false           true        false         false       true       false           1        0                      0         0          NULL      NULL
1841972634  6         1         true         true          false           true          false           true        false         false       true       false           1        3903121477             0         0          NULL      NULL
2101708905  5         1         true         true          false           true          false           true        false         false       true       false           1        0                      0         0          NULL      NULL
2148104569  21        2         true         true          false           true          false           true        false         false       true       false           1 2      3903121477 3903121477  0 0       0 0        NULL      NULL
2407840836  24        3         true         true          false           true          false           true        false         false       true       false           1 2 3    0 0 0                  0 0 0     0 0 0      NULL      NULL
2621181440  15        2         false        false         false           false         false           true        false         false       true       false           2 3      3903121477 0           0 0       0 0        NULL      NULL
2621181443  15        1         true         true          false           true          false           true        false         false       true       false           1        0                      0         0          NULL      NULL
2927313374  2         2         true         true          false           true          false           true        false         false       true       false           1 2      0 3903121477           0 0       0 0        NULL      NULL
3446785912  4         1         true         true          false           true          false           true        false         false       true       false           1        3903121477             0         0          NULL      NULL
3493181576  20        2         true         true          false           true          false           true        false         false       true       false           1 2      0 0                    0 0       0 0        NULL      NULL
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   0 0 0 0    NULL      NULL
3966258450  14        1         true         true          false           true          false           true        false         false       true       false           1        3903121477             0         0          NULL      NULL
4225994721  13        2         true         true          false           true          false           true        false         false       true       false           1 7      0 0                    0 0       0 0        NULL      NULL

# From materialize#26504
query OOI colnames
SELECT indexrelid,
       (information_schema._pg_expandarray(indclass)).x AS operator_argument_type_oid,
       (information_schema._pg_expandarray(indclass)).n AS operator_argument_position
FROM pg_index
ORDER BY indexrelid, operator_argument_position
----
indexrelid  operator_argument_type_oid  operator_argument_position
543291288   0                           1
543291289   0                           1
543291291   0                           1
543291291   0                           2
1276104432  0                           1
1276104432  0                           2
1582236367  0                           1
1628632028  0                           1
1628632029  0                           1
1628632031  0                           1
1841972634  0                           1
2101708905  0                           1
2148104569  0                           1
2148104569  0                           2
2407840836  0                           1
2407840836  0                           2
2407840836  0                           3
2621181440  0                           1
2621181440  0                           2
2621181443  0                           1
2927313374  0                           1
2927313374  0                           2
3446785912  0                           1
3493181576  0                           1
3493181576  0                           2
3706522183  0                           1
3706522183  0                           2
3706522183  0                           3
3706522183  0                           4
3966258450  0                           1
4225994721  0                           1
4225994721  0                           2

## pg_catalog.pg_collation

statement ok
SET DATABASE = constraint_db

query OTOOITT colnames
SELECT * FROM pg_collation
WHERE collname='en-US'
----
oid         collname  collnamespace  collowner  collencoding  collcollate  collctype
3903121477  en-US     2332901747     NULL       6             NULL         NULL

## pg_catalog.pg_constraint
##
## These order of this virtual table is non-deterministic, so all queries must
## explicitly add an ORDER BY clause.

query OTOT colnames
SELECT con.oid, conname, connamespace, contype
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public'
ORDER BY con.oid
----
oid         conname    connamespace  contype
178791267   fk         2332901747    f
3236224800  check_b    2332901747    c
3318155331  fk         2332901747    f
3572320190  primary    2332901747    p
4243354484  t1_a_key   2332901747    u
4243354485  index_key  2332901747    u

query TTBBBOOO colnames
SELECT conname, contype, condeferrable, condeferred, convalidated, conrelid, contypid, conindid
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public'
ORDER BY con.oid
----
conname    contype  condeferrable  condeferred  convalidated  conrelid  contypid  conindid
fk         f        false          false        true          57        0         450499961
check_b    c        false          false        true          57        0         0
fk         f        false          false        true          56        0         450499960
primary    p        false          false        true          55        0         450499963
t1_a_key   u        false          false        true          55        0         450499960
index_key  u        false          false        true          55        0         450499961

query T
SELECT conname
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public' AND contype NOT IN ('c', 'f', 'p', 'u')
ORDER BY con.oid
----

query TOTTT colnames
SELECT conname, confrelid, confupdtype, confdeltype, confmatchtype
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public' AND contype IN ('c', 'p', 'u')
ORDER BY con.oid
----
conname    confrelid  confupdtype  confdeltype  confmatchtype
check_b    0          NULL         NULL         NULL
primary    0          NULL         NULL         NULL
t1_a_key   0          NULL         NULL         NULL
index_key  0          NULL         NULL         NULL

query TOTTT colnames
SELECT conname, confrelid, confupdtype, confdeltype, confmatchtype
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public' AND contype = 'f'
ORDER BY con.oid
----
conname  confrelid  confupdtype  confdeltype  confmatchtype
fk       55         a            a            s
fk       55         a            a            s

query TBIBT colnames
SELECT conname, conislocal, coninhcount, connoinherit, conkey
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public'
ORDER BY con.oid
----
conname    conislocal  coninhcount  connoinherit  conkey
fk         true        0            true          {1,2}
check_b    true        0            true          {2}
fk         true        0            true          {1}
primary    true        0            true          {1}
t1_a_key   true        0            true          {2}
index_key  true        0            true          {3,4}

query TTTTTTTT colnames
SELECT conname, confkey, conpfeqop, conppeqop, conffeqop, conexclop, conbin, consrc
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public' AND contype IN ('c', 'p', 'u')
ORDER BY con.oid
----
conname    confkey  conpfeqop  conppeqop  conffeqop  conexclop  conbin  consrc
check_b    NULL     NULL       NULL       NULL       NULL       b > 11  b > 11
primary    NULL     NULL       NULL       NULL       NULL       NULL    NULL
t1_a_key   NULL     NULL       NULL       NULL       NULL       NULL    NULL
index_key  NULL     NULL       NULL       NULL       NULL       NULL    NULL

query TTTTTTTT colnames
SELECT conname, confkey, conpfeqop, conppeqop, conffeqop, conexclop, conbin, consrc
FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public' AND contype = 'f'
ORDER BY con.oid
----
conname  confkey  conpfeqop  conppeqop  conffeqop  conexclop  conbin  consrc
fk       {3,4}    NULL       NULL       NULL       NULL       NULL    NULL
fk       {2}      NULL       NULL       NULL       NULL       NULL    NULL

## pg_catalog.pg_depend

query OOIOOIT colnames
SELECT classid, objid, objsubid, refclassid, refobjid, refobjsubid, deptype
FROM pg_catalog.pg_depend
ORDER BY objid
----
classid     objid       objsubid  refclassid  refobjid   refobjsubid  deptype
4294967232  178791267   0         4294967234  450499961  0            n
4294967232  3318155331  0         4294967234  450499960  0            n

# All entries in pg_depend are dependency links from the pg_constraint system
# table to the pg_class system table.

query OOTT colnames
SELECT DISTINCT classid, refclassid, cla.relname AS tablename, refcla.relname AS reftablename
FROM pg_catalog.pg_depend
JOIN pg_class cla ON classid=cla.oid
JOIN pg_class refcla ON refclassid=refcla.oid
----
classid     refclassid  tablename      reftablename
4294967232  4294967234  pg_constraint  pg_class

# All entries in pg_depend are foreign key constraints that reference an index
# in pg_class.

query TT colnames
SELECT relname, relkind
FROM pg_depend
JOIN pg_class ON refobjid=pg_class.oid
ORDER BY relname
----
relname    relkind
index_key  i
t1_a_key   i


# All entries are pg_depend are linked to a foreign key constraint whose
# supporting index is the referenced object id.

query T colnames
SELECT DISTINCT pg_constraint.contype
FROM pg_depend
JOIN pg_constraint ON objid=pg_constraint.oid AND refobjid=pg_constraint.conindid
----
contype
f

## pg_catalog.pg_type

query OTOOIBT colnames
SELECT oid, typname, typnamespace, typowner, typlen, typbyval, typtype
FROM pg_catalog.pg_type
ORDER BY oid
----
oid   typname        typnamespace  typowner  typlen  typbyval  typtype
16    bool           1307062959    NULL      1       true      b
17    bytea          1307062959    NULL      -1      false     b
18    char           1307062959    NULL      -1      false     b
19    name           1307062959    NULL      -1      false     b
20    int8           1307062959    NULL      8       true      b
21    int2           1307062959    NULL      8       true      b
22    int2vector     1307062959    NULL      -1      false     b
23    int4           1307062959    NULL      8       true      b
24    regproc        1307062959    NULL      8       true      b
25    text           1307062959    NULL      -1      false     b
26    oid            1307062959    NULL      8       true      b
30    oidvector      1307062959    NULL      -1      false     b
700   float4         1307062959    NULL      8       true      b
701   float8         1307062959    NULL      8       true      b
705   unknown        1307062959    NULL      0       true      b
869   inet           1307062959    NULL      24      true      b
1000  _bool          1307062959    NULL      -1      false     b
1001  _bytea         1307062959    NULL      -1      false     b
1002  _char          1307062959    NULL      -1      false     b
1003  _name          1307062959    NULL      -1      false     b
1005  _int2          1307062959    NULL      -1      false     b
1006  _int2vector    1307062959    NULL      -1      false     b
1007  _int4          1307062959    NULL      -1      false     b
1008  _regproc       1307062959    NULL      -1      false     b
1009  _text          1307062959    NULL      -1      false     b
1013  _oidvector     1307062959    NULL      -1      false     b
1014  _bpchar        1307062959    NULL      -1      false     b
1015  _varchar       1307062959    NULL      -1      false     b
1016  _int8          1307062959    NULL      -1      false     b
1021  _float4        1307062959    NULL      -1      false     b
1022  _float8        1307062959    NULL      -1      false     b
1028  _oid           1307062959    NULL      -1      false     b
1041  _inet          1307062959    NULL      -1      false     b
1042  bpchar         1307062959    NULL      -1      false     b
1043  varchar        1307062959    NULL      -1      false     b
1082  date           1307062959    NULL      16      true      b
1083  time           1307062959    NULL      8       true      b
1114  timestamp      1307062959    NULL      24      true      b
1115  _timestamp     1307062959    NULL      -1      false     b
1182  _date          1307062959    NULL      -1      false     b
1183  _time          1307062959    NULL      -1      false     b
1184  timestamptz    1307062959    NULL      24      true      b
1185  _timestamptz   1307062959    NULL      -1      false     b
1186  interval       1307062959    NULL      24      true      b
1187  _interval      1307062959    NULL      -1      false     b
1231  _numeric       1307062959    NULL      -1      false     b
1560  bit            1307062959    NULL      -1      false     b
1561  _bit           1307062959    NULL      -1      false     b
1562  varbit         1307062959    NULL      -1      false     b
1563  _varbit        1307062959    NULL      -1      false     b
1700  numeric        1307062959    NULL      -1      false     b
2202  regprocedure   1307062959    NULL      8       true      b
2205  regclass       1307062959    NULL      8       true      b
2206  regtype        1307062959    NULL      8       true      b
2207  _regprocedure  1307062959    NULL      -1      false     b
2210  _regclass      1307062959    NULL      -1      false     b
2211  _regtype       1307062959    NULL      -1      false     b
2249  record         1307062959    NULL      0       true      p
2277  anyarray       1307062959    NULL      -1      false     p
2283  anyelement     1307062959    NULL      -1      false     p
2287  _record        1307062959    NULL      -1      false     b
2950  uuid           1307062959    NULL      16      true      b
2951  _uuid          1307062959    NULL      -1      false     b
3802  jsonb          1307062959    NULL      -1      false     b
3807  _jsonb         1307062959    NULL      -1      false     b
4089  regnamespace   1307062959    NULL      8       true      b
4090  _regnamespace  1307062959    NULL      -1      false     b

query OTTBBTOOO colnames
SELECT oid, typname, typcategory, typispreferred, typisdefined, typdelim, typrelid, typelem, typarray
FROM pg_catalog.pg_type
ORDER BY oid
----
oid   typname        typcategory  typispreferred  typisdefined  typdelim  typrelid  typelem  typarray
16    bool           B            false           true          ,         0         0        1000
17    bytea          U            false           true          ,         0         0        1001
18    char           S            false           true          ,         0         0        1002
19    name           S            false           true          ,         0         0        1003
20    int8           N            false           true          ,         0         0        1016
21    int2           N            false           true          ,         0         0        1005
22    int2vector     A            false           true          ,         0         21       1006
23    int4           N            false           true          ,         0         0        1007
24    regproc        N            false           true          ,         0         0        1008
25    text           S            false           true          ,         0         0        1009
26    oid            N            false           true          ,         0         0        1028
30    oidvector      A            false           true          ,         0         26       1013
700   float4         N            false           true          ,         0         0        1021
701   float8         N            false           true          ,         0         0        1022
705   unknown        X            false           true          ,         0         0        0
869   inet           I            false           true          ,         0         0        1041
1000  _bool          A            false           true          ,         0         16       0
1001  _bytea         A            false           true          ,         0         17       0
1002  _char          A            false           true          ,         0         18       0
1003  _name          A            false           true          ,         0         19       0
1005  _int2          A            false           true          ,         0         21       0
1006  _int2vector    A            false           true          ,         0         22       0
1007  _int4          A            false           true          ,         0         23       0
1008  _regproc       A            false           true          ,         0         24       0
1009  _text          A            false           true          ,         0         25       0
1013  _oidvector     A            false           true          ,         0         30       0
1014  _bpchar        A            false           true          ,         0         1042     0
1015  _varchar       A            false           true          ,         0         1043     0
1016  _int8          A            false           true          ,         0         20       0
1021  _float4        A            false           true          ,         0         700      0
1022  _float8        A            false           true          ,         0         701      0
1028  _oid           A            false           true          ,         0         26       0
1041  _inet          A            false           true          ,         0         869      0
1042  bpchar         S            false           true          ,         0         0        1014
1043  varchar        S            false           true          ,         0         0        1015
1082  date           D            false           true          ,         0         0        1182
1083  time           D            false           true          ,         0         0        1183
1114  timestamp      D            false           true          ,         0         0        1115
1115  _timestamp     A            false           true          ,         0         1114     0
1182  _date          A            false           true          ,         0         1082     0
1183  _time          A            false           true          ,         0         1083     0
1184  timestamptz    D            false           true          ,         0         0        1185
1185  _timestamptz   A            false           true          ,         0         1184     0
1186  interval       T            false           true          ,         0         0        1187
1187  _interval      A            false           true          ,         0         1186     0
1231  _numeric       A            false           true          ,         0         1700     0
1560  bit            V            false           true          ,         0         0        1561
1561  _bit           A            false           true          ,         0         1560     0
1562  varbit         V            false           true          ,         0         0        1563
1563  _varbit        A            false           true          ,         0         1562     0
1700  numeric        N            false           true          ,         0         0        1231
2202  regprocedure   N            false           true          ,         0         0        2207
2205  regclass       N            false           true          ,         0         0        2210
2206  regtype        N            false           true          ,         0         0        2211
2207  _regprocedure  A            false           true          ,         0         2202     0
2210  _regclass      A            false           true          ,         0         2205     0
2211  _regtype       A            false           true          ,         0         2206     0
2249  record         P            false           true          ,         0         0        2287
2277  anyarray       P            false           true          ,         0         0        0
2283  anyelement     P            false           true          ,         0         0        2277
2287  _record        A            false           true          ,         0         2249     0
2950  uuid           U            false           true          ,         0         0        2951
2951  _uuid          A            false           true          ,         0         2950     0
3802  jsonb          U            false           true          ,         0         0        3807
3807  _jsonb         A            false           true          ,         0         3802     0
4089  regnamespace   N            false           true          ,         0         0        4090
4090  _regnamespace  A            false           true          ,         0         4089     0

query OTOOOOOOO colnames
SELECT oid, typname, typinput, typoutput, typreceive, typsend, typmodin, typmodout, typanalyze
FROM pg_catalog.pg_type
ORDER BY oid
----
oid   typname        typinput        typoutput        typreceive        typsend           typmodin  typmodout  typanalyze
16    bool           boolin          boolout          boolrecv          boolsend          0         0          0
17    bytea          byteain         byteaout         bytearecv         byteasend         0         0          0
18    char           charin          charout          charrecv          charsend          0         0          0
19    name           namein          nameout          namerecv          namesend          0         0          0
20    int8           int8in          int8out          int8recv          int8send          0         0          0
21    int2           int2in          int2out          int2recv          int2send          0         0          0
22    int2vector     int2vectorin    int2vectorout    int2vectorrecv    int2vectorsend    0         0          0
23    int4           int4in          int4out          int4recv          int4send          0         0          0
24    regproc        regprocin       regprocout       regprocrecv       regprocsend       0         0          0
25    text           textin          textout          textrecv          textsend          0         0          0
26    oid            oidin           oidout           oidrecv           oidsend           0         0          0
30    oidvector      oidvectorin     oidvectorout     oidvectorrecv     oidvectorsend     0         0          0
700   float4         float4in        float4out        float4recv        float4send        0         0          0
701   float8         float8in        float8out        float8recv        float8send        0         0          0
705   unknown        unknownin       unknownout       unknownrecv       unknownsend       0         0          0
869   inet           inetin          inetout          inetrecv          inetsend          0         0          0
1000  _bool          array_in        array_out        array_recv        array_send        0         0          0
1001  _bytea         array_in        array_out        array_recv        array_send        0         0          0
1002  _char          array_in        array_out        array_recv        array_send        0         0          0
1003  _name          array_in        array_out        array_recv        array_send        0         0          0
1005  _int2          array_in        array_out        array_recv        array_send        0         0          0
1006  _int2vector    array_in        array_out        array_recv        array_send        0         0          0
1007  _int4          array_in        array_out        array_recv        array_send        0         0          0
1008  _regproc       array_in        array_out        array_recv        array_send        0         0          0
1009  _text          array_in        array_out        array_recv        array_send        0         0          0
1013  _oidvector     array_in        array_out        array_recv        array_send        0         0          0
1014  _bpchar        array_in        array_out        array_recv        array_send        0         0          0
1015  _varchar       array_in        array_out        array_recv        array_send        0         0          0
1016  _int8          array_in        array_out        array_recv        array_send        0         0          0
1021  _float4        array_in        array_out        array_recv        array_send        0         0          0
1022  _float8        array_in        array_out        array_recv        array_send        0         0          0
1028  _oid           array_in        array_out        array_recv        array_send        0         0          0
1041  _inet          array_in        array_out        array_recv        array_send        0         0          0
1042  bpchar         bpcharin        bpcharout        bpcharrecv        bpcharsend        0         0          0
1043  varchar        varcharin       varcharout       varcharrecv       varcharsend       0         0          0
1082  date           date_in         date_out         date_recv         date_send         0         0          0
1083  time           time_in         time_out         time_recv         time_send         0         0          0
1114  timestamp      timestamp_in    timestamp_out    timestamp_recv    timestamp_send    0         0          0
1115  _timestamp     array_in        array_out        array_recv        array_send        0         0          0
1182  _date          array_in        array_out        array_recv        array_send        0         0          0
1183  _time          array_in        array_out        array_recv        array_send        0         0          0
1184  timestamptz    timestamptz_in  timestamptz_out  timestamptz_recv  timestamptz_send  0         0          0
1185  _timestamptz   array_in        array_out        array_recv        array_send        0         0          0
1186  interval       interval_in     interval_out     interval_recv     interval_send     0         0          0
1187  _interval      array_in        array_out        array_recv        array_send        0         0          0
1231  _numeric       array_in        array_out        array_recv        array_send        0         0          0
1560  bit            bit_in          bit_out          bit_recv          bit_send          0         0          0
1561  _bit           array_in        array_out        array_recv        array_send        0         0          0
1562  varbit         varbit_in       varbit_out       varbit_recv       varbit_send       0         0          0
1563  _varbit        array_in        array_out        array_recv        array_send        0         0          0
1700  numeric        numeric_in      numeric_out      numeric_recv      numeric_send      0         0          0
2202  regprocedure   regprocedurein  regprocedureout  regprocedurerecv  regproceduresend  0         0          0
2205  regclass       regclassin      regclassout      regclassrecv      regclasssend      0         0          0
2206  regtype        regtypein       regtypeout       regtyperecv       regtypesend       0         0          0
2207  _regprocedure  array_in        array_out        array_recv        array_send        0         0          0
2210  _regclass      array_in        array_out        array_recv        array_send        0         0          0
2211  _regtype       array_in        array_out        array_recv        array_send        0         0          0
2249  record         record_in       record_out       record_recv       record_send       0         0          0
2277  anyarray       anyarray_in     anyarray_out     anyarray_recv     anyarray_send     0         0          0
2283  anyelement     anyelement_in   anyelement_out   anyelement_recv   anyelement_send   0         0          0
2287  _record        array_in        array_out        array_recv        array_send        0         0          0
2950  uuid           uuid_in         uuid_out         uuid_recv         uuid_send         0         0          0
2951  _uuid          array_in        array_out        array_recv        array_send        0         0          0
3802  jsonb          jsonb_in        jsonb_out        jsonb_recv        jsonb_send        0         0          0
3807  _jsonb         array_in        array_out        array_recv        array_send        0         0          0
4089  regnamespace   regnamespacein  regnamespaceout  regnamespacerecv  regnamespacesend  0         0          0
4090  _regnamespace  array_in        array_out        array_recv        array_send        0         0          0

query OTTTBOI colnames
SELECT oid, typname, typalign, typstorage, typnotnull, typbasetype, typtypmod
FROM pg_catalog.pg_type
ORDER BY oid
----
oid   typname        typalign  typstorage  typnotnull  typbasetype  typtypmod
16    bool           NULL      NULL        false       0            -1
17    bytea          NULL      NULL        false       0            -1
18    char           NULL      NULL        false       0            -1
19    name           NULL      NULL        false       0            -1
20    int8           NULL      NULL        false       0            -1
21    int2           NULL      NULL        false       0            -1
22    int2vector     NULL      NULL        false       0            -1
23    int4           NULL      NULL        false       0            -1
24    regproc        NULL      NULL        false       0            -1
25    text           NULL      NULL        false       0            -1
26    oid            NULL      NULL        false       0            -1
30    oidvector      NULL      NULL        false       0            -1
700   float4         NULL      NULL        false       0            -1
701   float8         NULL      NULL        false       0            -1
705   unknown        NULL      NULL        false       0            -1
869   inet           NULL      NULL        false       0            -1
1000  _bool          NULL      NULL        false       0            -1
1001  _bytea         NULL      NULL        false       0            -1
1002  _char          NULL      NULL        false       0            -1
1003  _name          NULL      NULL        false       0            -1
1005  _int2          NULL      NULL        false       0            -1
1006  _int2vector    NULL      NULL        false       0            -1
1007  _int4          NULL      NULL        false       0            -1
1008  _regproc       NULL      NULL        false       0            -1
1009  _text          NULL      NULL        false       0            -1
1013  _oidvector     NULL      NULL        false       0            -1
1014  _bpchar        NULL      NULL        false       0            -1
1015  _varchar       NULL      NULL        false       0            -1
1016  _int8          NULL      NULL        false       0            -1
1021  _float4        NULL      NULL        false       0            -1
1022  _float8        NULL      NULL        false       0            -1
1028  _oid           NULL      NULL        false       0            -1
1041  _inet          NULL      NULL        false       0            -1
1042  bpchar         NULL      NULL        false       0            -1
1043  varchar        NULL      NULL        false       0            -1
1082  date           NULL      NULL        false       0            -1
1083  time           NULL      NULL        false       0            -1
1114  timestamp      NULL      NULL        false       0            -1
1115  _timestamp     NULL      NULL        false       0            -1
1182  _date          NULL      NULL        false       0            -1
1183  _time          NULL      NULL        false       0            -1
1184  timestamptz    NULL      NULL        false       0            -1
1185  _timestamptz   NULL      NULL        false       0            -1
1186  interval       NULL      NULL        false       0            -1
1187  _interval      NULL      NULL        false       0            -1
1231  _numeric       NULL      NULL        false       0            -1
1560  bit            NULL      NULL        false       0            -1
1561  _bit           NULL      NULL        false       0            -1
1562  varbit         NULL      NULL        false       0            -1
1563  _varbit        NULL      NULL        false       0            -1
1700  numeric        NULL      NULL        false       0            -1
2202  regprocedure   NULL      NULL        false       0            -1
2205  regclass       NULL      NULL        false       0            -1
2206  regtype        NULL      NULL        false       0            -1
2207  _regprocedure  NULL      NULL        false       0            -1
2210  _regclass      NULL      NULL        false       0            -1
2211  _regtype       NULL      NULL        false       0            -1
2249  record         NULL      NULL        false       0            -1
2277  anyarray       NULL      NULL        false       0            -1
2283  anyelement     NULL      NULL        false       0            -1
2287  _record        NULL      NULL        false       0            -1
2950  uuid           NULL      NULL        false       0            -1
2951  _uuid          NULL      NULL        false       0            -1
3802  jsonb          NULL      NULL        false       0            -1
3807  _jsonb         NULL      NULL        false       0            -1
4089  regnamespace   NULL      NULL        false       0            -1
4090  _regnamespace  NULL      NULL        false       0            -1

query OTIOTTT colnames
SELECT oid, typname, typndims, typcollation, typdefaultbin, typdefault, typacl
FROM pg_catalog.pg_type
ORDER BY oid
----
oid   typname        typndims  typcollation  typdefaultbin  typdefault  typacl
16    bool           0         0             NULL           NULL        NULL
17    bytea          0         0             NULL           NULL        NULL
18    char           0         3903121477    NULL           NULL        NULL
19    name           0         3903121477    NULL           NULL        NULL
20    int8           0         0             NULL           NULL        NULL
21    int2           0         0             NULL           NULL        NULL
22    int2vector     0         0             NULL           NULL        NULL
23    int4           0         0             NULL           NULL        NULL
24    regproc        0         0             NULL           NULL        NULL
25    text           0         3903121477    NULL           NULL        NULL
26    oid            0         0             NULL           NULL        NULL
30    oidvector      0         0             NULL           NULL        NULL
700   float4         0         0             NULL           NULL        NULL
701   float8         0         0             NULL           NULL        NULL
705   unknown        0         0             NULL           NULL        NULL
869   inet           0         0             NULL           NULL        NULL
1000  _bool          0         0             NULL           NULL        NULL
1001  _bytea         0         0             NULL           NULL        NULL
1002  _char          0         3903121477    NULL           NULL        NULL
1003  _name          0         3903121477    NULL           NULL        NULL
1005  _int2          0         0             NULL           NULL        NULL
1006  _int2vector    0         0             NULL           NULL        NULL
1007  _int4          0         0             NULL           NULL        NULL
1008  _regproc       0         0             NULL           NULL        NULL
1009  _text          0         3903121477    NULL           NULL        NULL
1013  _oidvector     0         0             NULL           NULL        NULL
1014  _bpchar        0         3903121477    NULL           NULL        NULL
1015  _varchar       0         3903121477    NULL           NULL        NULL
1016  _int8          0         0             NULL           NULL        NULL
1021  _float4        0         0             NULL           NULL        NULL
1022  _float8        0         0             NULL           NULL        NULL
1028  _oid           0         0             NULL           NULL        NULL
1041  _inet          0         0             NULL           NULL        NULL
1042  bpchar         0         3903121477    NULL           NULL        NULL
1043  varchar        0         3903121477    NULL           NULL        NULL
1082  date           0         0             NULL           NULL        NULL
1083  time           0         0             NULL           NULL        NULL
1114  timestamp      0         0             NULL           NULL        NULL
1115  _timestamp     0         0             NULL           NULL        NULL
1182  _date          0         0             NULL           NULL        NULL
1183  _time          0         0             NULL           NULL        NULL
1184  timestamptz    0         0             NULL           NULL        NULL
1185  _timestamptz   0         0             NULL           NULL        NULL
1186  interval       0         0             NULL           NULL        NULL
1187  _interval      0         0             NULL           NULL        NULL
1231  _numeric       0         0             NULL           NULL        NULL
1560  bit            0         0             NULL           NULL        NULL
1561  _bit           0         0             NULL           NULL        NULL
1562  varbit         0         0             NULL           NULL        NULL
1563  _varbit        0         0             NULL           NULL        NULL
1700  numeric        0         0             NULL           NULL        NULL
2202  regprocedure   0         0             NULL           NULL        NULL
2205  regclass       0         0             NULL           NULL        NULL
2206  regtype        0         0             NULL           NULL        NULL
2207  _regprocedure  0         0             NULL           NULL        NULL
2210  _regclass      0         0             NULL           NULL        NULL
2211  _regtype       0         0             NULL           NULL        NULL
2249  record         0         0             NULL           NULL        NULL
2277  anyarray       0         3903121477    NULL           NULL        NULL
2283  anyelement     0         0             NULL           NULL        NULL
2287  _record        0         0             NULL           NULL        NULL
2950  uuid           0         0             NULL           NULL        NULL
2951  _uuid          0         0             NULL           NULL        NULL
3802  jsonb          0         0             NULL           NULL        NULL
3807  _jsonb         0         0             NULL           NULL        NULL
4089  regnamespace   0         0             NULL           NULL        NULL
4090  _regnamespace  0         0             NULL           NULL        NULL

## pg_catalog.pg_proc

query TOOOTTO colnames
SELECT proname, pronamespace, proowner, prolang, procost, prorows, provariadic
FROM pg_catalog.pg_proc
WHERE proname='substring'
----
proname    pronamespace  proowner  prolang  procost  prorows  provariadic
substring  1307062959    NULL      0        NULL     NULL     0
substring  1307062959    NULL      0        NULL     NULL     0
substring  1307062959    NULL      0        NULL     NULL     0
substring  1307062959    NULL      0        NULL     NULL     0

query TTBBBB colnames
SELECT proname, protransform, proisagg, proiswindow, prosecdef, proleakproof
FROM pg_catalog.pg_proc
WHERE proname='substring'
----
proname    protransform  proisagg  proiswindow  prosecdef  proleakproof
substring  NULL          false     false        false      true
substring  NULL          false     false        false      true
substring  NULL          false     false        false      true
substring  NULL          false     false        false      true

query TBBTT colnames
SELECT proname, proisstrict, proretset, provolatile, proparallel
FROM pg_catalog.pg_proc
WHERE proname='substring'
----
proname    proisstrict  proretset  provolatile  proparallel
substring  false        false      NULL         NULL
substring  false        false      NULL         NULL
substring  false        false      NULL         NULL
substring  false        false      NULL         NULL

query TIIOTTTT colnames
SELECT proname, pronargs, pronargdefaults, prorettype, proargtypes, proallargtypes, proargmodes, proargdefaults
FROM pg_catalog.pg_proc
WHERE proname='substring'
----
proname    pronargs  pronargdefaults  prorettype  proargtypes  proallargtypes  proargmodes  proargdefaults
substring  2         0                25          25 20        NULL            NULL         NULL
substring  3         0                25          25 20 20     NULL            NULL         NULL
substring  2         0                25          25 25        NULL            NULL         NULL
substring  3         0                25          25 25 25     NULL            NULL         NULL

query TTTTTT colnames
SELECT proname, protrftypes, prosrc, probin, proconfig, proacl
FROM pg_catalog.pg_proc
WHERE proname='substring'
----
proname    protrftypes  prosrc     probin  proconfig  proacl
substring  NULL         substring  NULL    NULL       NULL
substring  NULL         substring  NULL    NULL       NULL
substring  NULL         substring  NULL    NULL       NULL
substring  NULL         substring  NULL    NULL       NULL

query TOIOTT colnames
SELECT proname, provariadic, pronargs, prorettype, proargtypes, proargmodes
FROM pg_catalog.pg_proc
WHERE proname='least'
----
proname  provariadic  pronargs  prorettype  proargtypes  proargmodes
least    2283         1         2283        2283         {v}

query TOIOTT colnames
SELECT proname, provariadic, pronargs, prorettype, proargtypes, proargmodes
FROM pg_catalog.pg_proc
WHERE proname='json_extract_path'
----
proname            provariadic  pronargs  prorettype  proargtypes  proargmodes
json_extract_path  25           2         3802        3802 25      {i,v}

## pg_catalog.pg_range
query IIIIII colnames
SELECT * from pg_catalog.pg_range
----
rngtypid  rngsubtype  rngcollation  rngsubopc  rngcanonical  rngsubdiff

## pg_catalog.pg_roles

query OTBBBBBBB colnames
SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolreplication
FROM pg_catalog.pg_roles
ORDER BY rolname
----
oid         rolname   rolsuper  rolinherit  rolcreaterole  rolcreatedb  rolcatupdate  rolcanlogin  rolreplication
2310524507  admin     true      true        true           true         false         false        false
1546506610  root      true      false       true           true         false         true         false
2264919399  testuser  false     false       false          false        false         true         false

query OTITTBT colnames
SELECT oid, rolname, rolconnlimit, rolpassword, rolvaliduntil, rolbypassrls, rolconfig
FROM pg_catalog.pg_roles
ORDER BY rolname
----
oid         rolname   rolconnlimit  rolpassword  rolvaliduntil  rolbypassrls  rolconfig
2310524507  admin     -1            ********     NULL           false         NULL
1546506610  root      -1            ********     NULL           false         NULL
2264919399  testuser  -1            ********     NULL           false         NULL

## pg_catalog.pg_auth_members

query OOOB colnames
SELECT roleid, member, grantor, admin_option
FROM pg_catalog.pg_auth_members
----
roleid      member      grantor  admin_option
2310524507  1546506610  NULL     true

## pg_catalog.pg_user

query TOBBBBTTA colnames
SELECT usename, usesysid, usecreatedb, usesuper, userepl, usebypassrls, passwd, valuntil, useconfig
FROM pg_catalog.pg_user
ORDER BY usename
----
usename   usesysid    usecreatedb  usesuper  userepl  usebypassrls  passwd    valuntil  useconfig
root      1546506610  true         true      false    false         ********  NULL      NULL
testuser  2264919399  false        false     false    false         ********  NULL      NULL

## pg_catalog.pg_description

query OOIT colnames
SELECT objoid, classoid, objsubid, regexp_replace(description, e'\n.*', '') AS description
  FROM pg_catalog.pg_description
----
objoid      classoid    objsubid  description
4294967294  4294967234  0         backward inter-descriptor dependencies starting from tables accessible by current user in current database (KV scan)
4294967292  4294967234  0         built-in functions (RAM/static)
4294967291  4294967234  0         running queries visible by current user (cluster RPC; expensive!)
4294967290  4294967234  0         running sessions visible to current user (cluster RPC; expensive!)
4294967289  4294967234  0         cluster settings (RAM)
4294967288  4294967234  0         CREATE and ALTER statements for all tables accessible by current user in current database (KV scan)
4294967287  4294967234  0         telemetry counters (RAM; local node only)
4294967286  4294967234  0         forward inter-descriptor dependencies starting from tables accessible by current user in current database (KV scan)
4294967284  4294967234  0         locally known gossiped health alerts (RAM; local node only)
4294967283  4294967234  0         locally known gossiped node liveness (RAM; local node only)
4294967282  4294967234  0         locally known edges in the gossip network (RAM; local node only)
4294967285  4294967234  0         locally known gossiped node details (RAM; local node only)
4294967281  4294967234  0         index columns for all indexes accessible by current user in current database (KV scan)
4294967280  4294967234  0         decoded job metadata from system.jobs (KV scan)
4294967279  4294967234  0         node details across the entire cluster (cluster RPC; expensive!)
4294967278  4294967234  0         store details and status (cluster RPC; expensive!)
4294967277  4294967234  0         acquired table leases (RAM; local node only)
4294967293  4294967234  0         detailed identification strings (RAM, local node only)
4294967274  4294967234  0         current values for metrics (RAM; local node only)
4294967276  4294967234  0         running queries visible by current user (RAM; local node only)
4294967269  4294967234  0         server parameters, useful to construct connection URLs (RAM, local node only)
4294967275  4294967234  0         running sessions visible by current user (RAM; local node only)
4294967265  4294967234  0         statement statistics (RAM; local node only)
4294967273  4294967234  0         defined partitions for all tables/indexes accessible by the current user in the current database (KV scan)
4294967272  4294967234  0         comments for predefined virtual tables (RAM/static)
4294967271  4294967234  0         range metadata without leaseholder details (KV join; expensive!)
4294967268  4294967234  0         ongoing schema changes, across all descriptors accessible by current user (KV scan; expensive!)
4294967267  4294967234  0         session trace accumulated so far (RAM)
4294967266  4294967234  0         session variables (RAM)
4294967264  4294967234  0         details for all columns accessible by current user in current database (KV scan)
4294967263  4294967234  0         indexes accessible by current user in current database (KV scan)
4294967262  4294967234  0         table descriptors accessible by current user, including non-public and virtual (KV scan; expensive!)
4294967261  4294967234  0         decoded zone configurations from system.zones (KV scan)
4294967259  4294967234  0         roles for which the current user has admin option
4294967258  4294967234  0         roles available to the current user
4294967257  4294967234  0         column privilege grants (incomplete)
4294967256  4294967234  0         table and view columns (incomplete)
4294967255  4294967234  0         columns usage by constraints
4294967254  4294967234  0         roles for the current user
4294967253  4294967234  0         column usage by indexes and key constraints
4294967252  4294967234  0         built-in function parameters (empty - introspection not yet supported)
4294967251  4294967234  0         foreign key constraints
4294967250  4294967234  0         privileges granted on table or views (incomplete; see also information_schema.table_privileges; may contain excess users or roles)
4294967249  4294967234  0         built-in functions (empty - introspection not yet supported)
4294967247  4294967234  0         schema privileges (incomplete; may contain excess users or roles)
4294967248  4294967234  0         database schemas (may contain schemata without permission)
4294967246  4294967234  0         sequences
4294967245  4294967234  0         index metadata and statistics (incomplete)
4294967244  4294967234  0         table constraints
4294967243  4294967234  0         privileges granted on table or views (incomplete; may contain excess users or roles)
4294967242  4294967234  0         tables and views
4294967240  4294967234  0         grantable privileges (incomplete)
4294967241  4294967234  0         views (incomplete)
4294967238  4294967234  0         index access methods (incomplete)
4294967237  4294967234  0         column default values
4294967236  4294967234  0         table columns (incomplete - see also information_schema.columns)
4294967235  4294967234  0         role membership
4294967234  4294967234  0         tables and relation-like objects (incomplete - see also information_schema.tables/sequences/views)
4294967233  4294967234  0         available collations (incomplete)
4294967232  4294967234  0         table constraints (incomplete - see also information_schema.table_constraints)
4294967231  4294967234  0         available databases (incomplete)
4294967230  4294967234  0         dependency relationships (incomplete)
4294967229  4294967234  0         object comments
4294967227  4294967234  0         enum types and labels (empty - feature does not exist)
4294967226  4294967234  0         installed extensions (empty - feature does not exist)
4294967225  4294967234  0         foreign data wrappers (empty - feature does not exist)
4294967224  4294967234  0         foreign servers (empty - feature does not exist)
4294967223  4294967234  0         foreign tables (empty  - feature does not exist)
4294967222  4294967234  0         indexes (incomplete)
4294967221  4294967234  0         index creation statements
4294967220  4294967234  0         table inheritance hierarchy (empty - feature does not exist)
4294967219  4294967234  0         available languages (empty - feature does not exist)
4294967218  4294967234  0         available namespaces (incomplete; namespaces and databases are congruent in CockroachDB)
4294967217  4294967234  0         operators (incomplete)
4294967216  4294967234  0         built-in functions (incomplete)
4294967215  4294967234  0         range types (empty - feature does not exist)
4294967214  4294967234  0         rewrite rules (empty - feature does not exist)
4294967213  4294967234  0         database roles
4294967202  4294967234  0         security labels (empty - feature does not exist)
4294967212  4294967234  0         sequences (see also information_schema.sequences)
4294967211  4294967234  0         session variables (incomplete)
4294967228  4294967234  0         shared object comments
4294967201  4294967234  0         shared security labels (empty - feature not supported)
4294967203  4294967234  0         backend access statistics (empty - monitoring works differently in CockroachDB)
4294967208  4294967234  0         tables summary (see also information_schema.tables, pg_catalog.pg_class)
4294967207  4294967234  0         available tablespaces (incomplete; concept inapplicable to CockroachDB)
4294967206  4294967234  0         triggers (empty - feature does not exist)
4294967205  4294967234  0         scalar types (incomplete)
4294967210  4294967234  0         database users
4294967209  4294967234  0         local to remote user mapping (empty - feature does not exist)
4294967204  4294967234  0         view definitions (incomplete - see also information_schema.views)

## pg_catalog.pg_shdescription

query OOT colnames
SELECT objoid, classoid, description FROM pg_catalog.pg_shdescription
----
objoid  classoid  description

## pg_catalog.pg_enum

query OORT colnames
SELECT * FROM pg_catalog.pg_enum
----
oid  enumtypid  enumsortorder  enumlabel

## pg_catalog.pg_extension
query OTOOBTTT colnames
SELECT * FROM pg_catalog.pg_extension
----
oid  extname  extowner  extnamespace  extrelocatable  extversion  extconfig  extcondition

## pg_catalog.pg_stat_activity

query OTIOTTTTITTTTTTTIIT colnames
SELECT * FROM pg_catalog.pg_stat_activity
----
datid  datname  pid  usesysid  username  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

query TTBTTTB colnames
SHOW COLUMNS FROM pg_catalog.pg_stat_activity
----
column_name       data_type    is_nullable  column_default  generation_expression  indices  is_hidden
datid             OID          true         NULL            ·                      {}       false
datname           NAME         true         NULL            ·                      {}       false
pid               INT8         true         NULL            ·                      {}       false
usesysid          OID          true         NULL            ·                      {}       false
username          NAME         true         NULL            ·                      {}       false
application_name  STRING       true         NULL            ·                      {}       false
client_addr       INET         true         NULL            ·                      {}       false
client_hostname   STRING       true         NULL            ·                      {}       false
client_port       INT8         true         NULL            ·                      {}       false
backend_start     TIMESTAMPTZ  true         NULL            ·                      {}       false
xact_start        TIMESTAMPTZ  true         NULL            ·                      {}       false
query_start       TIMESTAMPTZ  true         NULL            ·                      {}       false
state_change      TIMESTAMPTZ  true         NULL            ·                      {}       false
wait_event_type   STRING       true         NULL            ·                      {}       false
wait_event        STRING       true         NULL            ·                      {}       false
state             STRING       true         NULL            ·                      {}       false
backend_xid       INT8         true         NULL            ·                      {}       false
backend_xmin      INT8         true         NULL            ·                      {}       false
query             STRING       true         NULL            ·                      {}       false


## pg_catalog.pg_settings

statement ok
SET DATABASE = test

# We filter here because 'optimizer' will be different depending on which
# configuration this logic test is running in.
query TTTTTT colnames
SELECT
  name, setting, category, short_desc, extra_desc, vartype
FROM
  pg_catalog.pg_settings
WHERE
  name != 'optimizer' AND name != 'crdb_version'
----
name                                 setting       category  short_desc  extra_desc  vartype
application_name                     ·             NULL      NULL        NULL        string
bytea_output                         hex           NULL      NULL        NULL        string
client_encoding                      UTF8          NULL      NULL        NULL        string
client_min_messages                  notice        NULL      NULL        NULL        string
database                             test          NULL      NULL        NULL        string
datestyle                            ISO, MDY      NULL      NULL        NULL        string
default_int_size                     8             NULL      NULL        NULL        string
default_tablespace                   ·             NULL      NULL        NULL        string
default_transaction_isolation        serializable  NULL      NULL        NULL        string
default_transaction_read_only        off           NULL      NULL        NULL        string
distsql                              off           NULL      NULL        NULL        string
experimental_enable_zigzag_join      on            NULL      NULL        NULL        string
experimental_force_split_at          off           NULL      NULL        NULL        string
experimental_serial_normalization    rowid         NULL      NULL        NULL        string
experimental_vectorize               off           NULL      NULL        NULL        string
extra_float_digits                   0             NULL      NULL        NULL        string
force_savepoint_restart              off           NULL      NULL        NULL        string
idle_in_transaction_session_timeout  0             NULL      NULL        NULL        string
integer_datetimes                    on            NULL      NULL        NULL        string
intervalstyle                        postgres      NULL      NULL        NULL        string
lock_timeout                         0             NULL      NULL        NULL        string
max_index_keys                       32            NULL      NULL        NULL        string
node_id                              1             NULL      NULL        NULL        string
reorder_joins_limit                  4             NULL      NULL        NULL        string
results_buffer_size                  16384         NULL      NULL        NULL        string
row_security                         off           NULL      NULL        NULL        string
search_path                          public        NULL      NULL        NULL        string
server_encoding                      UTF8          NULL      NULL        NULL        string
server_version                       9.5.0         NULL      NULL        NULL        string
server_version_num                   90500         NULL      NULL        NULL        string
session_user                         root          NULL      NULL        NULL        string
sql_safe_updates                     off           NULL      NULL        NULL        string
standard_conforming_strings          on            NULL      NULL        NULL        string
statement_timeout                    0             NULL      NULL        NULL        string
synchronize_seqscans                 on            NULL      NULL        NULL        string
timezone                             UTC           NULL      NULL        NULL        string
tracing                              off           NULL      NULL        NULL        string
transaction_isolation                serializable  NULL      NULL        NULL        string
transaction_priority                 normal        NULL      NULL        NULL        string
transaction_read_only                off           NULL      NULL        NULL        string
transaction_status                   NoTxn         NULL      NULL        NULL        string

query TTTTTTT colnames
SELECT
  name, setting, unit, context, enumvals, boot_val, reset_val
FROM
  pg_catalog.pg_settings
WHERE
  name != 'optimizer' AND name != 'crdb_version'
----
name                                 setting       unit  context  enumvals  boot_val      reset_val
application_name                     ·             NULL  user     NULL      ·             ·
bytea_output                         hex           NULL  user     NULL      hex           hex
client_encoding                      UTF8          NULL  user     NULL      UTF8          UTF8
client_min_messages                  notice        NULL  user     NULL      notice        notice
database                             test          NULL  user     NULL      ·             test
datestyle                            ISO, MDY      NULL  user     NULL      ISO, MDY      ISO, MDY
default_int_size                     8             NULL  user     NULL      8             8
default_tablespace                   ·             NULL  user     NULL      ·             ·
default_transaction_isolation        serializable  NULL  user     NULL      default       default
default_transaction_read_only        off           NULL  user     NULL      off           off
distsql                              off           NULL  user     NULL      off           off
experimental_enable_zigzag_join      on            NULL  user     NULL      on            on
experimental_force_split_at          off           NULL  user     NULL      off           off
experimental_serial_normalization    rowid         NULL  user     NULL      rowid         rowid
experimental_vectorize               off           NULL  user     NULL      off           off
extra_float_digits                   0             NULL  user     NULL      0             2
force_savepoint_restart              off           NULL  user     NULL      off           off
idle_in_transaction_session_timeout  0             NULL  user     NULL      0             0
integer_datetimes                    on            NULL  user     NULL      on            on
intervalstyle                        postgres      NULL  user     NULL      postgres      postgres
lock_timeout                         0             NULL  user     NULL      0             0
max_index_keys                       32            NULL  user     NULL      32            32
node_id                              1             NULL  user     NULL      1             1
reorder_joins_limit                  4             NULL  user     NULL      4             4
results_buffer_size                  16384         NULL  user     NULL      16384         16384
row_security                         off           NULL  user     NULL      off           off
search_path                          public        NULL  user     NULL      public        public
server_encoding                      UTF8          NULL  user     NULL      UTF8          UTF8
server_version                       9.5.0         NULL  user     NULL      9.5.0         9.5.0
server_version_num                   90500         NULL  user     NULL      90500         90500
session_user                         root          NULL  user     NULL      root          root
sql_safe_updates                     off           NULL  user     NULL      off           off
standard_conforming_strings          on            NULL  user     NULL      on            on
statement_timeout                    0             NULL  user     NULL      0             0
synchronize_seqscans                 on            NULL  user     NULL      on            on
timezone                             UTC           NULL  user     NULL      UTC           UTC
tracing                              off           NULL  user     NULL      off           off
transaction_isolation                serializable  NULL  user     NULL      serializable  serializable
transaction_priority                 normal        NULL  user     NULL      normal        normal
transaction_read_only                off           NULL  user     NULL      off           off
transaction_status                   NoTxn         NULL  user     NULL      NoTxn         NoTxn

query TTTTTT colnames
SELECT name, source, min_val, max_val, sourcefile, sourceline FROM pg_catalog.pg_settings
----
name                                 source  min_val  max_val  sourcefile  sourceline
application_name                     NULL    NULL     NULL     NULL        NULL
bytea_output                         NULL    NULL     NULL     NULL        NULL
client_encoding                      NULL    NULL     NULL     NULL        NULL
client_min_messages                  NULL    NULL     NULL     NULL        NULL
crdb_version                         NULL    NULL     NULL     NULL        NULL
database                             NULL    NULL     NULL     NULL        NULL
datestyle                            NULL    NULL     NULL     NULL        NULL
default_int_size                     NULL    NULL     NULL     NULL        NULL
default_tablespace                   NULL    NULL     NULL     NULL        NULL
default_transaction_isolation        NULL    NULL     NULL     NULL        NULL
default_transaction_read_only        NULL    NULL     NULL     NULL        NULL
distsql                              NULL    NULL     NULL     NULL        NULL
experimental_enable_zigzag_join      NULL    NULL     NULL     NULL        NULL
experimental_force_split_at          NULL    NULL     NULL     NULL        NULL
experimental_serial_normalization    NULL    NULL     NULL     NULL        NULL
experimental_vectorize               NULL    NULL     NULL     NULL        NULL
extra_float_digits                   NULL    NULL     NULL     NULL        NULL
force_savepoint_restart              NULL    NULL     NULL     NULL        NULL
idle_in_transaction_session_timeout  NULL    NULL     NULL     NULL        NULL
integer_datetimes                    NULL    NULL     NULL     NULL        NULL
intervalstyle                        NULL    NULL     NULL     NULL        NULL
lock_timeout                         NULL    NULL     NULL     NULL        NULL
max_index_keys                       NULL    NULL     NULL     NULL        NULL
node_id                              NULL    NULL     NULL     NULL        NULL
optimizer                            NULL    NULL     NULL     NULL        NULL
reorder_joins_limit                  NULL    NULL     NULL     NULL        NULL
results_buffer_size                  NULL    NULL     NULL     NULL        NULL
row_security                         NULL    NULL     NULL     NULL        NULL
search_path                          NULL    NULL     NULL     NULL        NULL
server_encoding                      NULL    NULL     NULL     NULL        NULL
server_version                       NULL    NULL     NULL     NULL        NULL
server_version_num                   NULL    NULL     NULL     NULL        NULL
session_user                         NULL    NULL     NULL     NULL        NULL
sql_safe_updates                     NULL    NULL     NULL     NULL        NULL
standard_conforming_strings          NULL    NULL     NULL     NULL        NULL
statement_timeout                    NULL    NULL     NULL     NULL        NULL
synchronize_seqscans                 NULL    NULL     NULL     NULL        NULL
timezone                             NULL    NULL     NULL     NULL        NULL
tracing                              NULL    NULL     NULL     NULL        NULL
transaction_isolation                NULL    NULL     NULL     NULL        NULL
transaction_priority                 NULL    NULL     NULL     NULL        NULL
transaction_read_only                NULL    NULL     NULL     NULL        NULL
transaction_status                   NULL    NULL     NULL     NULL        NULL

# pg_catalog.pg_sequence

statement ok
CREATE DATABASE seq

query OOIIIIIB
SELECT * FROM pg_catalog.pg_sequence
----

statement ok
CREATE SEQUENCE foo

statement ok
CREATE SEQUENCE bar MAXVALUE 10 MINVALUE 5 START 6 INCREMENT 2

query OOIIIIIB colnames
SELECT * FROM pg_catalog.pg_sequence
----
seqrelid  seqtypid  seqstart  seqincrement  seqmax               seqmin  seqcache  seqcycle
64        20        1         1             9223372036854775807  1       1         false
65        20        6         2             10                   5       1         false

statement ok
DROP DATABASE seq

statement ok
SET database = constraint_db

# Verify sequences can't be seen from another database.
query OOIIIIIB
SELECT * FROM pg_catalog.pg_sequence
----

## pg_catalog.pg_operator

query OTOOTBBOOOOOOOO colnames
SELECT * FROM pg_catalog.pg_operator where oprname='+' and oprleft='float8'::regtype
----
oid       oprname  oprnamespace  oprowner  oprkind  oprcanmerge  oprcanhash  oprleft  oprright  oprresult  oprcom  oprnegate  oprcode  oprrest  oprjoin
74817020  +        1307062959    NULL      b        false        false       701      701       701        NULL    NULL       NULL     NULL     NULL

# Verify proper functionality of system information functions.

query TT
SELECT pg_catalog.pg_get_expr('1', 0), pg_catalog.pg_get_expr('1', 0::OID)
----
1  1

query T
SELECT pg_catalog.pg_get_expr('1', 0, true)
----
1

statement ok
SET DATABASE = constraint_db

query OTT
SELECT def.oid, c.relname, pg_catalog.pg_get_expr(def.adbin, def.adrelid)
FROM pg_catalog.pg_attrdef def
JOIN pg_catalog.pg_class c ON def.adrelid = c.oid
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE n.nspname = 'public'
----
1666782879  t1  12:::INT8
841178406   t2  unique_rowid()
2186255414  t3  'FOO':::STRING
2186255409  t3  unique_rowid()

# Verify that a set database shows tables from that database for a non-root
# user, when that user has permissions.

statement ok
GRANT ALL ON constraint_db.* TO testuser

user testuser

statement ok
SET DATABASE = 'constraint_db'

query I
SELECT count(*) FROM pg_catalog.pg_tables WHERE schemaname='public'
----
3

user root

# Verify that an unset database shows tables across databases.
# But only those items visible to this user are reported.
# (Tests below show that root sees more).

statement ok
SET DATABASE = ''

query error cannot access virtual schema in anonymous database
SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname='public' ORDER BY 1

query error cannot access virtual schema in anonymous database
SELECT viewname FROM pg_catalog.pg_views WHERE schemaname='public' ORDER BY 1

query error cannot access virtual schema in anonymous database
SELECT relname FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE nspname='public'

query error cannot access virtual schema in anonymous database
SELECT conname FROM pg_catalog.pg_constraint con
JOIN pg_catalog.pg_namespace n ON con.connamespace = n.oid
WHERE n.nspname = 'public'

query error cannot access virtual schema in anonymous database
SELECT count(*) FROM pg_catalog.pg_depend

query error cannot access virtual schema in anonymous database
select 'upper'::REGPROC;

query error cannot access virtual schema in anonymous database
select 'system.namespace'::regclass

statement ok
SET DATABASE = test

## materialize#13567
## regproc columns display as text but can still be joined against oid columns
query OTO
SELECT p.oid, p.proname, t.typinput
FROM pg_proc p
JOIN pg_type t ON t.typinput = p.oid
WHERE t.typname = '_int4'
----
780513238  array_in  array_in

## materialize#16285
## int2vectors should be 0-indexed
query I
SELECT count(*) FROM pg_catalog.pg_index WHERE indkey[0] IS NULL;
----
0

## Ensure no two builtins have the same oid.
query I
SELECT c FROM (SELECT oid, count(*) as c FROM pg_catalog.pg_proc GROUP BY oid) WHERE c > 1
----

## Ensure that unnest works with oid wrapper arrays

query O
SELECT unnest((SELECT proargtypes FROM pg_proc WHERE proname='split_part'));
----
25
25
20

## TODO(masha): cockroach#16769
#statement ok
#CREATE TABLE types(a int8, b int2);

#query I
#SELECT attname, atttypid, typname FROM pg_attribute a JOIN pg_type t ON a.atttypid=t.oid WHERE attrelid = 'types'::REGCLASS;
#attname   atttypid  typname
#a         20        int8
#b         20        int2

subtest pg_catalog.pg_seclabel

query OOOTT colnames
SELECT objoid, classoid, objsubid, provider, label FROM pg_catalog.pg_seclabel
----
objoid  classoid  objsubid  provider  label

subtest pg_catalog.pg_shseclabel

query OOTT colnames
SELECT objoid, classoid, provider, label FROM pg_catalog.pg_shseclabel
----
objoid  classoid  provider  label

subtest collated_string_type

statement ok
CREATE TABLE coltab (a STRING COLLATE en)

query OT
SELECT typ.oid, typ.typname FROM pg_attribute att JOIN pg_type typ ON atttypid=typ.oid WHERE attrelid='coltab'::regclass AND attname='a'
----
25 text

subtest 31545

# Test an index of 2 referencing an index of 2.
statement ok
CREATE TABLE a (
  id_a_1 INT UNIQUE,
  id_a_2 INT,
  PRIMARY KEY (id_a_1, id_a_2)
)

statement ok
CREATE TABLE b (
  id_b_1 INT,
  id_b_2 INT,
  PRIMARY KEY (id_b_1, id_b_2),
  CONSTRAINT my_fkey FOREIGN KEY (id_b_1, id_b_2) REFERENCES a (id_a_1, id_a_2)
)

query TT colnames
SELECT conkey, confkey FROM pg_catalog.pg_constraint WHERE conname = 'my_fkey'
----
conkey  confkey
{1,2}   {1,2}

# Test an index of 3 referencing an index of 2.
statement ok
DROP TABLE b;
CREATE TABLE b (
  id_b_1 INT,
  id_b_2 INT,
  id_b_3 INT,
  PRIMARY KEY (id_b_1, id_b_2, id_b_3),
  CONSTRAINT my_fkey FOREIGN KEY (id_b_1, id_b_2) REFERENCES a (id_a_1, id_a_2)
)

query TT colnames
SELECT conkey, confkey FROM pg_catalog.pg_constraint WHERE conname = 'my_fkey'
----
conkey  confkey
{1,2}   {1,2}

# Test an index of 3 referencing an index of 1.
statement ok
DROP TABLE b;
CREATE TABLE b (
  id_b_1 INT,
  id_b_2 INT,
  id_b_3 INT,
  PRIMARY KEY (id_b_1, id_b_2, id_b_3),
  CONSTRAINT my_fkey FOREIGN KEY (id_b_1) REFERENCES a (id_a_1)
)

query TT colnames
SELECT conkey, confkey FROM pg_catalog.pg_constraint WHERE conname = 'my_fkey'
----
conkey  confkey
{1}     {1}

subtest regression_34856

statement ok
CREATE DATABASE d34856

statement ok
CREATE TABLE d34856.t(x INT);
  CREATE VIEW d34856.v AS SELECT x FROM d34856.t;
  CREATE SEQUENCE d34856.s

# Check that only tables show up in pg_tables.
query T
SELECT tablename FROM d34856.pg_catalog.pg_tables WHERE schemaname = 'public'
----
t

statement ok
DROP DATABASE d34856 CASCADE

subtest regression_34862

statement ok
CREATE DATABASE d34862; SET database=d34862

statement ok
CREATE TABLE t(x INT UNIQUE);
  CREATE TABLE u(
    a INT REFERENCES t(x) ON DELETE NO ACTION,
    b INT REFERENCES t(x) ON DELETE RESTRICT,
    c INT REFERENCES t(x) ON DELETE SET NULL,
    d INT DEFAULT 123 REFERENCES t(x) ON DELETE SET DEFAULT,
    e INT REFERENCES t(x) ON DELETE CASCADE,
    f INT REFERENCES t(x) ON UPDATE NO ACTION,
    g INT REFERENCES t(x) ON UPDATE RESTRICT,
    h INT REFERENCES t(x) ON UPDATE SET NULL,
    i INT DEFAULT 123 REFERENCES t(x) ON UPDATE SET DEFAULT,
    j INT REFERENCES t(x) ON UPDATE CASCADE,
    k INT REFERENCES t(x) ON DELETE RESTRICT ON UPDATE SET NULL
  );

query TTT
SELECT conname, confupdtype, confdeltype FROM pg_constraint ORDER BY conname
----
fk_a_ref_t  a     a
fk_b_ref_t  a     r
fk_c_ref_t  a     n
fk_d_ref_t  a     d
fk_e_ref_t  a     c
fk_f_ref_t  a     a
fk_g_ref_t  r     a
fk_h_ref_t  n     a
fk_i_ref_t  d     a
fk_j_ref_t  c     a
fk_k_ref_t  n     r
t_x_key     NULL  NULL

statement ok
DROP TABLE u; DROP TABLE t

statement ok
CREATE TABLE v(x INT, y INT, UNIQUE (x,y))

statement ok
CREATE TABLE w(
  a INT, b INT, c INT, d INT,
  FOREIGN KEY (a,b) REFERENCES v(x,y) MATCH FULL,
  FOREIGN KEY (c,d) REFERENCES v(x,y) MATCH SIMPLE
  );

query TT
SELECT conname, confmatchtype FROM pg_constraint ORDER BY conname
----
fk_a_ref_v  f
fk_c_ref_v  s
v_x_y_key   NULL

statement ok
DROP DATABASE d34862 CASCADE; SET database=test

subtest regression_35108

query T
SELECT pg_catalog.current_setting('statement_timeout')
----
0

query T
SELECT pg_catalog.current_setting('statement_timeout', false)
----
0

# check returns null on unsupported session var.
query T
SELECT IFNULL(pg_catalog.current_setting('woo', true), 'OK')
----
OK

# check error on nonexistent session var.
query error unrecognized configuration parameter
SELECT pg_catalog.current_setting('woo', false)

# check error on unsupported session var.
query error configuration setting.*not supported
SELECT pg_catalog.current_setting('vacuum_cost_delay', false)

query T
SHOW application_name
----
·

query T
SELECT pg_catalog.set_config('application_name', 'woo', false)
----
woo

query T
SHOW application_name
----
woo

query error transaction-scoped settings are not supported
SELECT  pg_catalog.set_config('application_name', 'woo', true)

query error unrecognized configuration parameter
SELECT  pg_catalog.set_config('woo', 'woo', false)

query error configuration setting.*not supported
SELECT  pg_catalog.set_config('vacuum_cost_delay', '0', false)