[YSQL][Query Diagnostics] Collect schema information #22146
Assignees
Labels
area/ysql
Yugabyte SQL (YSQL)
kind/enhancement
This is an enhancement of an existing feature
priority/medium
Medium priority issue
Comments
IshanChhangani
added
area/ysql
yugabyte-ci
added
kind/enhancement
IshanChhangani
changed the title
11 tasks
IshanChhangani
added a commit
that referenced
this issue
Nov 12, 2024
Summary: - This diff adds schema details to the query diagnostics bundle, within the file named `schema_details.txt`. - We output only the schema of the tables mentioned within the bundled query. - We reuse the queries within DescribeOneTableDetails() function of describe.c to extract schema information. - For executing SQL queries within postgres backend we use SPI framework. - Executing SQL queries requires us to have a database connection, thus the background worker is registered with `BGWORKER_BACKEND_DATABASE_CONNECTION` bgw_flag and start_time is changed to `BgWorkerStart_RecoveryFinished`. Sample output of schema_details.txt: ``` "Table Name: test_table" - Table information: |Table Name |Table Groupname |Colocated | +------------------+----------------+----------+ |public.test_table | |false | - Columns: |Column |Type |Collation |Nullable |Default |Storage |Stats Target |Description | +------------+-------------------------+----------+---------+------------------+---------+-------------+------------+ |id |integer | |not null | |plain | | | |name |character varying(100) | |not null | |extended | | | |description |text | | | |extended | | | |updated_at |timestamp with time zone | | |CURRENT_TIMESTAMP |plain | | | |is_active |boolean | | |true |plain | | | |price |numeric(10,2) | | | |main | | | - Indexes: |Name |Index Definition |Constraint Definition | +------------------------------+----------------------------------------------------------------------------------------------------------+-------------------------+ |test_table_pkey |CREATE UNIQUE INDEX test_table_pkey ON ONLY test_table USING lsm (id HASH) |PRIMARY KEY (id) | |test_table_id_description_key |CREATE UNIQUE INDEX test_table_id_description_key ON ONLY test_table USING lsm (id HASH, description ASC) |UNIQUE (id, description) | |idx_test_table_active |CREATE INDEX idx_test_table_active ON ONLY test_table USING lsm (is_active HASH) WHERE is_active = true | | |idx_test_table_name |CREATE INDEX idx_test_table_name ON ONLY test_table USING lsm (name HASH) | | - Check constraints: |Name |Constraint Definition | +---------------------+----------------------------+ |check_price_positive |CHECK (price >= 0::numeric) | - Policies: |Name |Type |Applicable Roles |USING Expression |With CHECK Expression |Applicable Command | +------------+-----------+-----------------+-------------------+----------------------+-------------------+ |test_policy |Permissive |ALL ROLES |(is_active = true) | |SELECT | - Statistics: |OID |TABLE |Namespace |Statistics Name |Columns |N-Dinstinct Enabled |Functional Dependencies Enabled | +------+-----------+----------+----------------+------------+--------------------+--------------------------------+ |16403 |test_table |public |test_statistics |name, price |No |Yes | - Rules: |Name |Rule Definition | +----------+----------------------------------------------------------------------------------------------------------------------------------------+ |test_rule |CREATE RULE test_rule AS ON DELETE TO test_table DO INSTEAD UPDATE test_table SET is_active = false WHERE test_table.id = old.id | ============================================================ "Table Name: foreign_test_table" - Table information: |Table Name |Table Groupname |Colocated | +--------------------------+----------------+----------+ |public.foreign_test_table | |false | - Columns: |Column |Type |Collation |Nullable |Default |Storage |Stats Target |Description | +-------+-----------------------+----------+---------+--------+---------+-------------+------------+ |id |integer | | | |plain | | | |name |character varying(100) | | | |extended | | | - Foreign table information: |Server Name |FDW Options | +---------------+-----------------------------------------------------+ |foreign_server |schema_name 'public', table_name 'remote_test_table' | ============================================================ ``` - As highlighted in worker_spi.c, it is important to be in a transactional state before calling `SPI_execute()` or `get_rel_name` from a background worker. Without this we hit an assertion failure `Assert(IsTransactionState());` within SearchCatCacheInternal() in catcache.c - Further `pg_plan_query` expects to have an `ActiveSnapshotSet()` thus we `PushActiveSnapshot()` before calling `SPI_execute()`. - Also, we haven't included queries to gather data on `inherited tables` as they are not yet supported by YB. Jira: DB-11074 Test Plan: ./yb_build.sh --java-test TestYbQueryDiagnostics#checkSchemaDetails Reviewers: asaha, telgersma Reviewed By: telgersma Subscribers: telgersma, yql Differential Revision: https://phorge.dev.yugabyte.com/D36469
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Jira Link: DB-11074
Description
This issue involves implementing a recursive mechanism to collect schema information for tables directly affected by a query and all tables dependent on them. This functionality aims to provide data like that obtained by running describe on a table.
\d table_nameIssue Type
kind/enhancement
Warning: Please confirm that this issue does not contain any sensitive information
The text was updated successfully, but these errors were encountered: