DEV Community

Franck Pachot for YugabyteDB

Posted on

🐘OID <-> 🚀table_id

When you look at YugabyteDB web console, or some yb-admin output, you will see your YSQL tables identified with a table_id or uuid. Here is an example:
Image description

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.

Table OID

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 16386:

yugabyte=# \c yb_demo_northwind

psql (13.5, server 11.2-YB-2.13.1.0-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 |

Enter fullscreen mode Exit fullscreen mode

UUID

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 table_id above: 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

Enter fullscreen mode Exit fullscreen mode

The code explains it is Repurpose old name-based UUID v3 to embed Postgres oids

OID

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)
Enter fullscreen mode Exit fullscreen mode

I use this, for example, to get directly to the tablet definition as http://yb-tmaster-0:7000/table?id=00004001000030008000000000004002

This endpoint has can also find tablets with a keyspace_name and table_id instead of the id but this works only for YCQL where there is no schema.

yb-admin

Knowing the Table ID is also useful for yb-admin commands, prefixing UUID with tableid.:

$ /home/opc/yugabyte-2.13.1.0/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
Enter fullscreen mode Exit fullscreen mode

The list_tablets can also work with YSQL table names by prefixing the keyspace with ysql.:

]$ /home/opc/yugabyte-2.13.1.0/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
Enter fullscreen mode Exit fullscreen mode

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=#
Enter fullscreen mode Exit fullscreen mode

The same yb-admin command will now fail to identify the DocDB table by name:

$ /home/opc/yugabyte-2.13.1.0/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
Enter fullscreen mode Exit fullscreen mode

In short

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

Discussion (0)