Summary:
Currently, to get information about tablets (shards of a table or index) we have these two views:
- **yb_local_tablets** view gives information about tablets on the current node.
```
SELECT * FROM yb_local_tablets WHERE table_name = 'test_table';
```
| tablet_id | table_id | table_type | namespace_name | ysql_schema_name | table_name | partition_key_start | partition_key_end | state |
| 0ee55a2cbc2d4228bc886cd0539ee0e3 | 000034cb000030008000000000004000 | YSQL | yugabyte | public | test_table | \x8000 | \xaaaa | TABLET_DATA_READY |
| 0d2755a0722444649074eb33c1731814 | 000034cb000030008000000000004000 | YSQL | yugabyte | public | test_table | \xaaaa | \xd555 | TABLET_DATA_READY |
| da65e0afabe04c7996d788e282238766 | 000034cb000030008000000000004000 | YSQL | yugabyte | public | test_table | \xd555 | <null> | TABLET_DATA_READY |
| e09f981d148c4895a82e5dc66de446b0 | 000034cb000030008000000000004000 | YSQL | yugabyte | public | test_table | \x2aaa | \x5555 | TABLET_DATA_READY |
| ebeb23077f534fa0928aad93b37b9da9 | 000034cb000030008000000000004000 | YSQL | yugabyte | public | test_table | \x5555 | \x8000 | TABLET_DATA_READY |
| d1ba22b9f2ff412b8bd5cb13c2b2e28a | 000034cb000030008000000000004000 | YSQL | yugabyte | public | test_table | <null> | \x2aaa | TABLET_DATA_READY |
- **yb_table_properties** view gives information about the number of tablets a table has.
```
SELECT * FROM yb_table_properties ('test_table'::REGCLASS);
```
| num_tablets | num_hash_key_columns | is_colocated | tablegroup_oid | colocation_id |
| 6 | 1 | False | <null> | <null> |
As of today there is no cluster-wide tablet metadata view in YSQL, so this diff proposes yb_tablet_metadata view to expose tablet distribution and leadership information for all the tablets in a cluster.
The proposed yb_tablet_metadata view provides a YSQL accessible interface for fetching tablet distribution and leadership information across a YugabyteDB cluster. This view serves as the YSQL equivalent of the YCQL system.partitions table, offering visibility into tablet placement and replica roles.
###Examples:
- YSQL tables can be selected from either yb_tablet_metadata view or yb_get_tablet_metadata() function.
```
SELECT * FROM yb_tablet_metadata WHERE db_name = 'yugabyte' AND relname = 'test_table';
```
| tablet_id | oid | db_name | relname | start_hash_code | end_hash_code | leader | replicas |
| 3987b6a16bf94fbd92262744197350d7 | 16384 | yugabyte | test_table | 0 | 10922 | 127.0.0.2:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| dd50b59c7dcb493680093ffa5b195634 | 16384 | yugabyte | test_table | 10922 | 21845 | 127.0.0.1:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| bed5b3c3eee747e99622a4e21acf437a | 16384 | yugabyte | test_table | 21845 | 32768 | 127.0.0.3:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| da4bad5faa9f448f890cce57c775cd94 | 16384 | yugabyte | test_table | 32768 | 43690 | 127.0.0.2:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| 52176c704c614846bbd80f481678519e | 16384 | yugabyte | test_table | 43690 | 54613 | 127.0.0.1:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| ea252119fe774ba9bdc585504fae9398 | 16384 | yugabyte | test_table | 54613 | 65536 | 127.0.0.3:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
- YCQL tables can be selected from yb_get_tablet_metadata() function
```
SELECT * FROM yb_get_tablet_metadata() WHERE object_name = 'test_table_ycql';
```
| tablet_id | oid | namespace | object_name | type | start_hash_code | end_hash_code | leader | replicas |
| af91b4e74c044cba85a6027ec38b6d98 | 16385 | testks | test_table_cql | YCQL | 0 | 10922 | 127.0.0.1:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| 206393ede8a84596b7255425ee24f675 | 16385 | testks | test_table_cql | YCQL | 10922 | 21845 | 127.0.0.2:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| f56b570c4f1d445ab25b9c3080a7597f | 16385 | testks | test_table_cql | YCQL | 21845 | 32768 | 127.0.0.3:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| 92d9fef22e464ba6a09a29415fe69615 | 16385 | testks | test_table_cql | YCQL | 32768 | 43690 | 127.0.0.2:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| 564d3cb66b4e4e7daac3477829fd3aad | 16385 | testks | test_table_cql | YCQL | 43690 | 54613 | 127.0.0.3:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
| 2d6621b1222f47cda4111c69f7f6bbaa | 16385 | testks | test_table_cql | YCQL | 54613 | 65536 | 127.0.0.1:5433 | ['127.0.0.1:5433', '127.0.0.2:5433', '127.0.0.3:5433'] |
**Upgrade/Rollback safety:**
This diff is safe to upgrade and rollback
Jira: DB-17735
Test Plan:
./yb_build.sh --java-test TestPgRegressMisc#testPgRegressMiscIndependent
./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressRules#testPgRegressRules'
./yb_build.sh --java-test TestYsqlUpgrade
./yb_build.sh --cxx-test yql-test --gtest_filter YqlTest.TabletMetadataViewsWithYcqlAndYsql
./yb_build.sh --cxx-test pg_conn-test --gtest_filter PgConnTest.TabletMetadataConnectWithLeader;
./yb_build.sh --cxx-test pg_mini-test --gtest_filter PgMiniTest.TabletMetadataOidMatchesPgClass;
./yb_build.sh --cxx-test pg_mini-test --gtest_filter PgMiniTest.TabletMetadataCorrectnessWithHashPartitioning
Reviewers: asaha
Reviewed By: asaha
Subscribers: kannan, jason, yql
Differential Revision: https://phorge.dev.yugabyte.com/D45540
