Renaming a table & keeping connections to existing partitions in YugabyteDB

Question

[Question posted by a user on YugabyteDB Community Slack]

Does renaming the table, existing partitions attached to that table remain as it is after renaming?

Answer 1

Yes.

yugabyte=# \dt
                 List of relations
 Schema |         Name          | Type  |  Owner
--------+-----------------------+-------+----------
 public | order_changes         | table | yugabyte
 public | order_changes_2019_02 | table | yugabyte
 public | order_changes_2019_03 | table | yugabyte
 public | order_changes_2020_11 | table | yugabyte
 public | order_changes_2020_12 | table | yugabyte
 public | order_changes_2021_01 | table | yugabyte
 public | people                | table | yugabyte
 public | people1               | table | yugabyte
 public | user_audit            | table | yugabyte
 public | user_credentials      | table | yugabyte
 public | user_profile          | table | yugabyte
 public | user_svc_account      | table | yugabyte
(12 rows)
yugabyte=# alter table order_changes RENAME TO oc;
ALTER TABLE
yugabyte=# \dS+ oc
                                      Table "public.oc"
   Column    | Type | Collation | Nullable | Default | Storage  | Stats target | Description
-------------+------+-----------+----------+---------+----------+--------------+-------------
 change_date | date |           |          |         | plain    |              |
 type        | text |           |          |         | extended |              |
 description | text |           |          |         | extended |              |
Partition key: RANGE (change_date)
Partitions: order_changes_2019_02 FOR VALUES FROM ('2019-02-01') TO ('2019-03-01'),
            order_changes_2019_03 FOR VALUES FROM ('2019-03-01') TO ('2019-04-01'),
            order_changes_2020_11 FOR VALUES FROM ('2020-11-01') TO ('2020-12-01'),
            order_changes_2020_12 FOR VALUES FROM ('2020-12-01') TO ('2021-01-01'),
            order_changes_2021_01 FOR VALUES FROM ('2021-01-01') TO ('2021-02-01')

Postgres and therefore YugabyteDB doesn’t actually use the names of an object, it uses the OID (object ID) of an object.

That means that you can rename it, without actually causing any harm, because it’s simply a name in the catalog with the object identified by its OID.

This has other side effects as well: if you create a table, and perform a certain SQL like ‘select count(*) from table’, drop it, and then create a table with the same name, and perform the exact same SQL, you will get two records in pg_stat_statements with identical SQL text. This seems weird from the perspective of databases where the SQL area is shared. In postgres, only pg_stat_statements is shared, there is no SQL cache.

pg_stat_statements does not store the SQL text, it stores the query tree (an internal representation of the SQL), and symbolizes the tree, which makes to appear like SQL again. The query tree uses the OID, and therefore for pg_stat_statements the above two identical SQL texts are different query trees, because the OIDs of the tables are different.