This Table ID is important because, even if the YSQL table or index name is displayed as "Table Name", and the YSQL database as "Keyspace", this is not sufficient to identify a table, an index or a partition. This is because the storage in DocDB is common to all APIs. It doesn't know the YSQL schema, or the relation type. And YCQL tables are also there.
From the screenshot above, identifying the YSQL table is easy because the "Table OID" is displayed. In this example, I see that the table
00004001000030008000000000004002 is the
categories table from the
yb_demo_northwind database, with the OID
yugabyte=# \c yb_demo_northwind psql (13.5, server 11.2-YB-18.104.22.168-b0) You are now connected to database "yb_demo_northwind" as user "yugabyte". select current_database(), schema, relname, relkind, am, owner, tablespace from ( -- pg_class -> table or index select relkind, relname, relnamespace, reltype, relowner, relam, reltablespace from pg_class where oid=16386 ) as c natural left join ( -- pg_namespace -> schema select oid relnamespace, nspname as schema from pg_namespace ) as n natural left join ( -- pg_authid -> owner select oid relowner, rolname as owner from pg_authid ) as o natural left join ( -- pg_am -> access method select oid relam, amname am from pg_am ) as a natural left join ( -- spcname pg_tablespace -> tablespace select oid reltablespace, spcname as tablespace from pg_tablespace ) as t; current_database | schema | relname | relkind | am | owner | tablespace -------------------+--------+------------+---------+----+----------+------------ yb_demo_northwind | public | categories | r | | yugabyte |
But you may also need to find the
table_id from a YSQL table. This is a UUID is built from the keyspace (which is the PostgreSQL database in the case of YSQL) and the table (with the PostgreSQL OID of the relation in the case of YSQL).
Let's decompose my
00004001000030008000000000004002 to understand how it is build.
00004001-0000-3000-8000-000000004002 ^^^^ -> PostgreSQL Database OID ^ -> UUID Version 3 = name based MD5 ^ -> UUID Variant = DCE 1.1, ISO/IEC ^^^^ -> PostgreSQL Relation OID
The code explains it is Repurpose old name-based UUID v3 to embed Postgres oids
This is how I get the
table_id from PostgreSQL for my
categories table, in my current database, found with the current search_path:
yb_demo_northwind=# select format('0000%s00003000800000000000%s' ,lpad(to_hex(oid::int), 4, '0') ,lpad(to_hex('categories'::regclass::oid::int),4,'0') ) table_id from pg_database where datname=current_database() table_id ---------------------------------- 00004001000030008000000000004002 (1 row)
I use this, for example, to get directly to the tablet definition as
This endpoint has can also find tablets with a
table_id instead of the
id but this works only for YCQL where there is no schema.
Knowing the Table ID is also useful for
yb-admin commands, prefixing UUID with
$ /home/opc/yugabyte-22.214.171.124/bin/yb-admin list_tablets tableid.00004001000030008000000000004002 Tablet-UUID Range Leader-IP Leader-UUID e4216871ffd644c3b616d37668400212 partition_key_start: "" partition_key_end: "UU" 10.0.0.61:9100 1e6aa7f085d146e8b29207483592116a 85f1061e6f10410888cbbf6418d7047c partition_key_start: "UU" partition_key_end: "\252\252" 10.0.0.63:9100 9eec605aede445e98ad61bf833dbf6cc 2884fe988f304591b2905e5272a571f9 partition_key_start: "\252\252" partition_key_end: "" 10.0.0.62:9100 4729e36c42934175817c580b52ea6a18
list_tablets can also work with YSQL table names by prefixing the keyspace with
]$ /home/opc/yugabyte-126.96.36.199/bin/yb-admin list_tablets ysql.yb_demo_northwind categories Tablet-UUID Range Leader-IP Leader-UUID e4216871ffd644c3b616d37668400212 partition_key_start: "" partition_key_end: "UU" 10.0.0.61:9100 1e6aa7f085d146e8b29207483592116a 85f1061e6f10410888cbbf6418d7047c partition_key_start: "UU" partition_key_end: "\252\252" 10.0.0.63:9100 9eec605aede445e98ad61bf833dbf6cc 2884fe988f304591b2905e5272a571f9 partition_key_start: "\252\252" partition_key_end: "" 10.0.0.62:9100 4729e36c42934175817c580b52ea6a18
However, this works only when there's only one table with this name. If I create another one in another schema:
yb_demo_northwind=# create schema franck; CREATE SCHEMA yb_demo_northwind=# create table franck.categories (a int); CREATE TABLE yb_demo_northwind=#
yb-admin command will now fail to identify the DocDB table by name:
$ /home/opc/yugabyte-188.8.131.52/bin/yb-admin -init_master_addrs=10.0.0.61:7100 list_tablets ysql.yb_demo_northwind categories Error running list_tablets: Invalid argument (yb/tools/yb-admin_client.cc:406): Namespace 'ysql.yb_demo_northwind' has multiple tables named 'categories', specify table id instead
The combination of keyspace and table names can identify a table only for YCQL. With YSQL there are multiple namespaces within a table, which are the PostgreSQL schemas. You need to use the Table ID, a UUID constructed from the PostgreSQL object identifier (OID) to identify a YSQL relation in DocDB. The queries above can be used for this purpose.
I did some maths with my 14 years old kid during the week-end, and I can now tell you that the function that maps YSQL tables to DocDB tables by name is non-injective. You need to map by UUID to get an injective function. It is still non-surjective if you consider all UUIDs in DocDB because it also holds tables for YCQL. But from an UUID Version 3 it is a bijection, and that's why you can use it in the URL and
yb-admin. Other tables, like YCQL or system ones, are UUID Version 4. This is easy to distinguish from the 13th digit in the hexadecimal representation of the Table ID