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