🐘OID <-> 🚀table_id
Tags: yugabytedb, distributed, sql, database
Published: 2022-05-16 09:25:32

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/01975smrxdgj01nor79u.png)

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

```sql
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 |

```

## 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.

```sql
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](https://github.com/yugabyte/yugabyte-db/blob/71d527c69554e5380788b3b20b20aa2b35d23bb2/src/yb/common/entity_ids.cc#L50) 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:

```sql
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 `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.`:
```bash
$ /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
```

The `list_tablets` can also work with YSQL table names by prefixing the keyspace with `ysql.`:
```bash
]$ /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
```

However, this works only when there's only one table with this name. If I create another one in another schema:
```sql
yb_demo_northwind=# create schema franck;
CREATE SCHEMA
yb_demo_northwind=# create table franck.categories (a int);
CREATE TABLE
yb_demo_northwind=#
```
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
```

## 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](https://en.wikipedia.org/wiki/Injective_function). You need to map by UUID to get an injective function. It is still [non-surjective](https://en.wikipedia.org/wiki/Surjective_function) if you consider all UUIDs in DocDB because it also holds tables for YCQL. But from an UUID Version 3 it is a [bijection](https://en.wikipedia.org/wiki/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