Inheritance as a debugging aid
PostgreSQL's table inheritance lets a child table pick up every column of its parent and add its own on top. The feature has been part of the system for decades, but it also turns out to be a practical foundation for tracing what an application does to the database.
Consider a parent table that carries a sequence and a timestamp:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE t_global ( id serial, tstamp timestamptz DEFAULT now() ); CREATE TABLE t_product ( name text, price numeric ) INHERITS (t_global); INSERT INTO t_product (name, price) VALUES ('Shoes', 113.98), ('Sausage', 4.58); |
Because t_product inherits from t_global, the sequence and its default apply to the child table as well. The same parent can back a second table:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE t_country ( country_name text ) INHERITS (t_global); INSERT INTO t_country (country_name) VALUES ('Austria'), ('Germany'), ('Japan'); |
Two consequences follow. A single global sequence feeds all of these tables, so IDs are unique across the whole system. And every table shares the same default timestamp.
Locating an ID and reading a transaction
With that structure in place, two debugging questions become cheap to answer:
- Which table holds the ID I am looking for?
- Which operations ran inside the same transaction?
|
1 2 3 4 5 6 7 8 9 |
test=# SELECT tableoid::regclass, * FROM t_global; tableoid | id | tstamp -----------+----+------------------------------- t_product | 1 | 2025-01-24 12:02:07.295524+01 t_product | 2 | 2025-01-24 12:02:07.295524+01 t_country | 3 | 2025-01-24 12:02:07.295524+01 t_country | 4 | 2025-01-24 12:02:07.295524+01 t_country | 5 | 2025-01-24 12:02:07.295524+01 (5 rows) |
The uniqueness of the shared sequence is what makes the lookup safe. PostgreSQL also exposes a virtual column named tableoid on every table, which holds the object ID of the table. Cast it to regclass and you get the table name as a string, so a single SQL statement returns every ID in the system along with the table it lives in.
The timestamp column answers the second question. Identical timestamps mean the rows were written by the same transaction, which makes the insertion order across all tables visible at a glance — and shows whether the writes were part of one transaction.
