crdb_internal

# LogicTest: local local-opt fakedist fakedist-opt fakedist-metadata

query error database "crdb_internal" does not exist
ALTER DATABASE crdb_internal RENAME TO not_crdb_internal

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

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

query TT
SHOW TABLES FROM crdb_internal WITH COMMENT
----
backward_dependencies      NULL
builtin_functions          NULL
cluster_queries            NULL
cluster_sessions           NULL
cluster_settings           NULL
create_statements          NULL
feature_usage              NULL
forward_dependencies       NULL
gossip_alerts              NULL
gossip_liveness            NULL
gossip_network             NULL
gossip_nodes               NULL
index_columns              NULL
jobs                       NULL
kv_node_status             NULL
kv_store_status            NULL
leases                     NULL
node_build_info            NULL
node_metrics               NULL
node_queries               NULL
node_runtime_info          NULL
node_sessions              NULL
node_statement_statistics  NULL
partitions                 NULL
ranges                     NULL
ranges_no_leases           NULL
schema_changes             NULL
session_trace              NULL
session_variables          NULL
table_columns              NULL
table_indexes              NULL
tables                     NULL
zones                      NULL

statement ok
CREATE DATABASE testdb; CREATE TABLE testdb.foo(x INT)

query TIT
SELECT t.name, t.version, t.state FROM crdb_internal.tables AS t JOIN system.namespace AS n ON (n.id = t.parent_id and n.name = 'testdb');
----
foo 1 PUBLIC

# Ensure there is a lease taken on foo.
query I
SELECT * FROM testdb.foo
----

# Check the lease.
query T
SELECT l.name FROM crdb_internal.leases AS l JOIN system.namespace AS n ON (n.id = l.table_id and n.name = 'foo');
----
foo

# We merely check the column list for schema_changes.
query IITTITTT colnames
SELECT * FROM crdb_internal.schema_changes
----
table_id parent_id name type target_id target_name state direction

query IITTITRTTTTTT colnames
SELECT * FROM crdb_internal.tables WHERE NAME = 'namespace'
----
table_id  parent_id  name       database_name  version  mod_time                         mod_time_logical  format_version            state   sc_lease_node_id  sc_lease_expiration_time  drop_time  audit_mode
2         1          namespace  system         1        1970-01-01 00:00:00 +0000 +0000  0E-10             InterleavedFormatVersion  PUBLIC  NULL              NULL                      NULL       DISABLED

# Verify that table names are not double escaped.

statement ok
CREATE TABLE testdb." ""\'" (i int)

query T
SELECT NAME from crdb_internal.tables WHERE DATABASE_NAME = 'testdb'
----
foo
"\'

query TT colnames
SELECT field, value FROM crdb_internal.node_build_info WHERE field ILIKE 'name'
----
field value
Name  CockroachDB

query T rowsort
SELECT field FROM crdb_internal.node_build_info
----
Name
Build
ClusterID
Organization
Version
Channel


# The validity of the rows in this table are tested elsewhere; we merely assert the columns.
query ITTTTTTTTTTRTTI colnames
SELECT * FROM crdb_internal.jobs WHERE false
----
job_id  job_type  description  user_name  descriptor_ids  status  running_status  created  started  finished  modified  fraction_completed  high_water_timestamp  error  coordinator_id

query IITTITTT colnames
SELECT * FROM crdb_internal.schema_changes WHERE table_id < 0
----
table_id  parent_id  name  type  target_id  target_name  state  direction

query IITITB colnames
SELECT * FROM crdb_internal.leases WHERE node_id < 0
----
node_id  table_id  name  parent_id  expiration  deleted

query ITTTTIIITFFFFFFFFFFFF colnames
SELECT * FROM crdb_internal.node_statement_statistics WHERE node_id < 0
----
node_id  application_name  flags  key  anonymized  count  first_attempt_count  max_retries  last_error  rows_avg  rows_var  parse_lat_avg  parse_lat_var  plan_lat_avg  plan_lat_var  run_lat_avg  run_lat_var  service_lat_avg  service_lat_var  overhead_lat_avg  overhead_lat_var

query IITTTTTTT colnames
SELECT * FROM crdb_internal.session_trace WHERE span_idx < 0
----
span_idx  message_idx  timestamp  duration  operation  loc  tag  message age

query TTTT colnames
SELECT * FROM crdb_internal.cluster_settings WHERE variable = ''
----
variable  value  type  description

query TI colnames
SELECT * FROM crdb_internal.feature_usage WHERE feature_name = ''
----
feature_name  usage_count

query TT colnames
SELECT * FROM crdb_internal.session_variables WHERE variable = ''
----
variable                       value

query TITTTTTBT colnames
SELECT * FROM crdb_internal.node_queries WHERE node_id < 0
----
query_id  node_id  user_name  start  query  client_address  application_name  distributed  phase

query TITTTTTBT colnames
SELECT * FROM crdb_internal.cluster_queries WHERE node_id < 0
----
query_id  node_id  user_name  start  query  client_address  application_name  distributed  phase

query ITTTTTTTTTTT colnames
SELECT * FROM crdb_internal.node_sessions WHERE node_id < 0
----
node_id  session_id  user_name  client_address  application_name  active_queries  last_active_query  session_start  oldest_query_start  kv_txn  alloc_bytes  max_alloc_bytes

query ITTTTTTTTTTT colnames
SELECT * FROM crdb_internal.cluster_sessions WHERE node_id < 0
----
node_id  session_id  user_name  client_address  application_name  active_queries  last_active_query  session_start  oldest_query_start  kv_txn  alloc_bytes  max_alloc_bytes

query TTTT colnames
SELECT * FROM crdb_internal.builtin_functions WHERE function = ''
----
function  signature  category  details

query ITTITTTTTTT colnames
SELECT * FROM crdb_internal.create_statements WHERE database_name = ''
----
database_id  database_name  schema_name  descriptor_id  descriptor_type  descriptor_name  create_statement  state  create_nofks  alter_statements  validate_statements

query ITITTBTB colnames
SELECT * FROM crdb_internal.table_columns WHERE descriptor_name = ''
----
descriptor_id  descriptor_name  column_id  column_name  column_type  nullable  default_expr  hidden

query ITITTB colnames
SELECT * FROM crdb_internal.table_indexes WHERE descriptor_name = ''
----
descriptor_id  descriptor_name  index_id  index_name  index_type  is_unique

query ITITTITT colnames
SELECT * FROM crdb_internal.index_columns WHERE descriptor_name = ''
----
descriptor_id  descriptor_name  index_id  index_name  column_type  column_id  column_name  column_direction

query ITIIITITT colnames
SELECT * FROM crdb_internal.backward_dependencies WHERE descriptor_name = ''
----
descriptor_id  descriptor_name  index_id  column_id  dependson_id  dependson_type  dependson_index_id  dependson_name  dependson_details

query ITIITITT colnames
SELECT * FROM crdb_internal.forward_dependencies WHERE descriptor_name = ''
----
descriptor_id  descriptor_name  index_id  dependedonby_id  dependedonby_type  dependedonby_index_id  dependedonby_name  dependedonby_details

query ITTTTT colnames
SELECT * FROM crdb_internal.zones WHERE false
----
zone_id  zone_name cli_specifier  config_yaml  config_sql  config_protobuf

statement ok
INSERT INTO system.zones (id, config) VALUES
  (18, (SELECT config_protobuf FROM crdb_internal.zones WHERE zone_id = 0)),
  (53, (SELECT config_protobuf FROM crdb_internal.zones WHERE zone_id = 0)),
  (54, (SELECT config_protobuf FROM crdb_internal.zones WHERE zone_id = 0))

query IT
SELECT zone_id, cli_specifier FROM crdb_internal.zones ORDER BY 1
----
0   .default
1   system
15  system.jobs
16  .meta
17  .system
18  .timeseries
22  .liveness
53  testdb
54  testdb.foo

query T
SELECT quote_literal(config_yaml) FROM crdb_internal.zones WHERE zone_id = 0
----
e'range_min_bytes: 16777216\nrange_max_bytes: 67108864\ngc:\n  ttlseconds: 90000\nnum_replicas: 3\nconstraints: []\nlease_preferences: []\n'

query T
SELECT config_sql FROM crdb_internal.zones WHERE zone_id = 0
----
ALTER RANGE default CONFIGURE ZONE USING
  range_min_bytes = 16777216,
  range_max_bytes = 67108864,
  gc.ttlseconds = 90000,
  num_replicas = 3,
  constraints = '[]',
  lease_preferences = '[]'

query error pq: foo
SELECT crdb_internal.force_error('', 'foo')

query error pgcode FOOYAA pq: foo
SELECT crdb_internal.force_error('FOOYAA', 'foo')

query I
select crdb_internal.force_retry(interval '0s')
----
0

query error pq: crdb_internal.set_vmodule\(\): syntax error: expect comma-separated list of filename=N
select crdb_internal.set_vmodule('not anything reasonable')

query I
select crdb_internal.set_vmodule('doesntexist=2,butitsok=4')
----
0

query I
select crdb_internal.set_vmodule('')
----
0

query T
select crdb_internal.node_executable_version()
----
2.1-3

query ITTT colnames
select node_id, component, field, regexp_replace(regexp_replace(value, '^\d+$', '<port>'), e':\\d+', ':<port>') as value from crdb_internal.node_runtime_info
----
node_id  component  field   value
1        DB         URL     postgresql://root@127.0.0.1:<port>?sslcert=test_certs%2Fclient.root.crt&sslkey=test_certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=test_certs%2Fca.crt
1        DB         Scheme  postgresql
1        DB         User    root
1        DB         Host    127.0.0.1
1        DB         Port    <port>
1        DB         URI     /?sslcert=test_certs%2Fclient.root.crt&sslkey=test_certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=test_certs%2Fca.crt
1        UI         URL     https://127.0.0.1:<port>
1        UI         Scheme  https
1        UI         User    ·
1        UI         Host    127.0.0.1
1        UI         Port    <port>
1        UI         URI     /

query ITTTTT colnames
SELECT node_id, network, regexp_replace(address, '\d+$', '<port>') as address, attrs, locality, regexp_replace(server_version, '^\d+\.\d+(-\d+)?$', '<server_version>') as server_version FROM crdb_internal.gossip_nodes WHERE node_id = 1
----
node_id  network  address           attrs  locality            server_version
1        tcp      127.0.0.1:<port>  []     {"region": "test"}  <server_version>

query IITBB colnames
SELECT node_id, epoch, regexp_replace(expiration, '^\d+\.\d+,\d+$', '<timestamp>') as expiration, draining, decommissioning FROM crdb_internal.gossip_liveness WHERE node_id = 1
----
node_id  epoch  expiration    draining  decommissioning
1        1      <timestamp>   false     false

query ITTTTTT colnames
SELECT node_id, network, regexp_replace(address, '\d+$', '<port>') as address, attrs, locality, regexp_replace(server_version, '^\d+\.\d+(-\d+)?$', '<server_version>') as server_version, regexp_replace(go_version, '^go.+$', '<go_version>') as go_version
FROM crdb_internal.kv_node_status WHERE node_id = 1
----
node_id  network  address           attrs  locality            server_version    go_version
1        tcp      127.0.0.1:<port>  []     {"region": "test"}  <server_version>  <go_version>

query IITI colnames
SELECT node_id, store_id, attrs, used
FROM crdb_internal.kv_store_status WHERE node_id = 1
----
node_id  store_id  attrs  used
1        1         []     0

statement ok
CREATE TABLE foo (a INT PRIMARY KEY); INSERT INTO foo VALUES(1)

# Make sure that the cluster id isn't unset.
query B
select crdb_internal.cluster_id() != '00000000-0000-0000-0000-000000000000' FROM foo
----
true

# Check that privileged builtins are only allowed for 'root'
user testuser

query error insufficient privilege
select crdb_internal.force_retry(interval '0s')

query error insufficient privilege
select crdb_internal.force_panic('foo')

query error insufficient privilege
select crdb_internal.force_log_fatal('foo')

query error insufficient privilege
select crdb_internal.set_vmodule('')

query error pq: only superusers are allowed to access the node runtime information
select * from crdb_internal.node_runtime_info

query error pq: only superusers are allowed to read crdb_internal.ranges
select * from crdb_internal.ranges

query error pq: only superusers are allowed to read crdb_internal.gossip_nodes
select * from crdb_internal.gossip_nodes

query error pq: only superusers are allowed to read crdb_internal.gossip_liveness
select * from crdb_internal.gossip_liveness

query error pq: only superusers are allowed to read crdb_internal.node_metrics
select * from crdb_internal.node_metrics

query error pq: only superusers are allowed to read crdb_internal.kv_node_status
select * from crdb_internal.kv_node_status

query error pq: only superusers are allowed to read crdb_internal.kv_store_status
select * from crdb_internal.kv_store_status

query error pq: only superusers are allowed to read crdb_internal.gossip_alerts
select * from crdb_internal.gossip_alerts

# Anyone can see the executable version.
query T
select crdb_internal.node_executable_version()
----
2.1-3