# Catalog reference¶

SQream DB contains a schema called sqream_catalog that contains information about your database’s objects - tables, columns, views, permissions, and more.

Some additional catalog tables are used primarily for internal introspection, which could change across SQream DB versions.

## Types of data exposed by sqream_catalog¶

Database objects

Object

Table

Clustering keys

clustering_keys

Columns

columns, external_table_columns

Databases

databases

Permissions

table_permissions, database_permissions, schema_permissions, permission_types, udf_permissions

Roles

roles, roles_memeberships

Schemas

schemas

Sequences

identity_key

Tables

tables, external_tables

Views

views

UDFs

user_defined_functions

The catalog contains a few more tables which contain storage details for internal use

Storage objects

Object

Table

Extents

extents

Chunks

chunks

Delete predicates

delete_predicates

## Tables in the catalog¶

### clustering_keys¶

Explicit clustering keys for tables.

When more than one clustering key is defined, each key is listed in a separate row.

Column

Description

database_name

Name of the database containing the table

table_id

ID of the table containing the column

schema_name

Name of the schema containing the table

table_name

Name of the table containing the column

clustering_key

Name of the column that is a clustering key for this table

### columns¶

Column objects for standard tables

Column

Description

database_name

Name of the database containing the table

schema_name

Name of the schema containing the table

table_id

ID of the table containing the column

table_name

Name of the table containing the column

column_id

Ordinal of the column in the table (begins at 0)

column_name

Name of the column

type_name

Data type of the column

column_size

The maximum length in bytes.

has_default

NULL if the column has no default value. 1 if the default is a fixed value, or 2 if the default is an Identity (sequence)

default_value

Default value for the column

compression_strategy

User-overridden compression strategy

created

Timestamp when the column was created

altered

Timestamp when the column was last altered

### external_tables¶

external_tables identifies external tables in the database.

For TABLES see tables

Column

Description

database_name

Name of the database containing the table

table_id

Database-unique ID for the table

schema_name

Name of the schema containing the table

table_name

Name of the table

format

Identifies the foreign data wrapper used.

0 for csv_fdw, 1 for parquet_fdw, 2 for orc_fdw.

created

Identifies the clause used to create the table

### external_table_columns¶

Column objects for external tables

### databases¶

Column

Description

database_Id

Unique ID of the database

database_name

Name of the database

default_disk_chunk_size

Internal use

default_process_chunk_size

Internal use

rechunk_size

Internal use

storage_subchunk_size

Internal use

compression_chunk_size_threshold

Internal use

### database_permissions¶

database_permissions identifies all permissions granted to databases.

There is one row for each combination of role (grantee) and permission granted to a database.

Column

Description

database_name

Name of the database the permission applies to

role_id

ID of the role granted permissions (grantee)

permission_type

Identifies the permission type

### permission_types¶

permission_types Identifies the permission names that exist in the database.

Column

Description

permission_type_id

ID of the permission type

name

Name of the permission type

### roles¶

roles identifies the roles in the database.

Column

Description

role_id

Database-unique ID of the role

name

Name of the role

superuser

Identifies if this role is a superuser. 1 for superuser or 0 otherwise.

login

Identifies if this role can be used to log in to SQream DB. 1 for yes or 0 otherwise.

has_password

Identifies if this role has a password. 1 for yes or 0 otherwise.

can_create_function

Identifies if this role can create UDFs. 1 for yes, 0 otherwise.

### roles_memberships¶

roles_memberships identifies the role memberships in the database.

Column

Description

role_id

Role ID

member_role_id

ID of the parent role from which this role will inherit

inherit

Identifies if permissions are inherited. 1 for yes or 0 otherwise.

### savedqueries¶

savedqueries identifies the saved_queries in the database.

Column

Description

name

Saved query name

num_parameters

Number of parameters to be replaced at run-time

### schemas¶

schemas identifies all the database’s schemas.

Column

Description

schema_id

Unique ID of the schema

schema_name

Name of the schema

schema_owner

Name of the role who owns this schema

rechunker_ignore

Internal use

### schema_permissions¶

schema_permissions identifies all permissions granted to schemas.

There is one row for each combination of role (grantee) and permission granted to a schema.

Column

Description

database_name

Name of the database containing the schema

schema_id

ID of the schema the permission applies to

role_id

ID of the role granted permissions (grantee)

permission_type

Identifies the permission type

### tables¶

tables identifies proper SQream tables in the database.

For EXTERNAL TABLES see external_tables

Column

Description

database_name

Name of the database containing the table

table_id

Database-unique ID for the table

schema_name

Name of the schema containing the table

table_name

Name of the table

row_count_valid

Identifies if the row_count can be used

row_count

Number of rows in the table

rechunker_ignore

Internal use

### table_permissions¶

table_permissions identifies all permissions granted to tables.

There is one row for each combination of role (grantee) and permission granted to a table.

Column

Description

database_name

Name of the database containing the table

table_id

ID of the table the permission applies to

role_id

ID of the role granted permissions (grantee)

permission_type

Identifies the permission type

### user_defined_functions¶

user_defined_functions identifies UDFs in the database.

Column

Description

database_name

Name of the database containing the view

function_id

Database-unique ID for the UDF

function_name

Name of the UDF

### views¶

views identifies views in the database.

Column

Description

view_id

Database-unique ID for the view

view_schema

Name of the schema containing the view

view_name

Name of the view

view_data

Internal use

view_query_text

Identifies the AS clause used to create the view

There are additional tables in the catalog that can be used for performance monitoring and inspection.

The definition for these tables is provided below could change across SQream DB versions.

### extents¶

extents identifies storage extents.

Each storage extents can contain several chunks.

Note

This is an internal table designed for low-level performance troubleshooting.

Column

Description

database_name

Name of the databse containing the extent

table_id

ID of the table containing the extent

column_id

ID of the column containing the extent

extent_id

ID for the extent

size

Extent size in megabytes

path

Full path to the extent on the file system

### chunk_columns¶

chunk_columns lists chunk information by column.

Column

Description

database_name

Name of the databse containing the extent

table_id

ID of the table containing the extent

column_id

ID of the column containing the extent

chunk_id

ID for the chunk

extent_id

ID for the extent

compressed_size

Actual chunk size in bytes

uncompressed_size

Uncompressed chunk size in bytes

compression_type

Actual compression scheme for this chunk

long_min

Minimum numeric value in this chunk (if exists)

long_max

Maximum numeric value in this chunk (if exists)

string_min

Minimum text value in this chunk (if exists)

string_max

Maximum text value in this chunk (if exists)

offset_in_file

Internal use

Note

This is an internal table designed for low-level performance troubleshooting.

### chunks¶

chunks identifies storage chunks.

Note

This is an internal table designed for low-level performance troubleshooting.

Column

Description

database_name

Name of the databse containing the chunk

table_id

ID of the table containing the chunk

column_id

ID of the column containing the chunk

rows_num

Amount of rows contained in the chunk

deletion_status

When data is deleted from the table, it is first deleted logically. This value identifies how much data is deleted from the chunk. 0 for no data, 1 for some data, 2 to specify the entire chunk is deleted.

### delete_predicates¶

delete_predicates identifies the existing delete predicates that have not been cleaned up.

Each DELETE command may result in several entries in this table.

Note

This is an internal table designed for low-level performance troubleshooting.

Column

Description

database_name

Name of the databse containing the predicate

table_id

ID of the table containing the predicate

max_chunk_id

Internal use. Placeholder marker for the highest chunk_id logged during the DELETE operation.

delete_predicate

Identifies the DELETE predicate

## Examples¶

### List all tables in the database¶

master=> SELECT * FROM sqream_catalog.tables;
database_name | table_id | schema_name | table_name     | row_count_valid | row_count | rechunker_ignore
--------------+----------+-------------+----------------+-----------------+-----------+-----------------
master        |        1 | public      | nba            | true            |       457 |                0
master        |       12 | public      | cool_dates     | true            |         5 |                0
master        |       13 | public      | cool_numbers   | true            |         9 |                0
master        |       27 | public      | jabberwocky    | true            |         8 |                0


### List all schemas in the database¶

master=> SELECT * FROM sqream_catalog.schemas;
schema_id | schema_name   | schema_owner | rechunker_ignore
----------+---------------+--------------+-----------------
0 | public        | sqream       | false
1 | secret_schema | mjordan      | false


### List columns and their types for a specific table¶

SELECT column_name, type_name
FROM sqream_catalog.columns
WHERE table_name='cool_animals';


### List delete predicates¶

SELECT  t.table_name, d.*  FROM
sqream_catalog.delete_predicates AS d
INNER JOIN sqream_catalog.tables AS t
ON d.table_id=t.table_id;


### List Saved queries¶

SELECT * FROM sqream_catalog.savedqueries;