PostgreSQL is the foundation for thousands and thousands of applications. The system has long proven its worth and is operating reliably. However, the question people often ask is: What is the best way to actually debug a database application?
Table of Contents
For a couple of years now, I have been using a simple method that greatly speeds up the debugging process of typical applications.
One of the core features of PostgreSQL, which has been around for decades already, is the idea of "inheritance". So what does that mean, exactly? Simply speaking, tables can inherit columns from each other. A child table will simply have all the columns of the parent table, plus its own additional ones.
But what does this mean in real life, and what does it have to do with debugging? Let us take a closer look:
|
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); |
The t_global table contains a sequence including a timestamp. Those two columns are passed on to t_product. What this means is that the sequence now also applies to the child table.
Here is 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'); |
We are now using the same parent table for the country table. What does it matter? What we have just produced is two things: First of all, there is ONE global sequence for all tables, which means that all IDs across the entirety of the systems are unique (we will need this later). Also: All tables have a timestamp that has an identical default value.
Let us now imagine for a second that we want to debug an application, and we want to know two things:
The structure we have just created makes answering these two questions really easy:
|
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) |
Remember, there is just one single sequence that feeds all the ID columns, so we can rely on the fact that the ID is unique all across the database. But there is more: In PostgreSQL, every table supports a virtual column called "tableoid". It shows the object ID of a table. The trick now is as follows: If we cast this object ID to a special data type (= regclass), it will give us a string representation of the table name. In other words, we can run a single SQL statement and it will give us all the IDs in the entire system, including the tablename containing this ID. Supported by the fact that everything in your entire application is unique, we can easily figure out where to find anything we might be looking for.
However, we might want to take a closer look at the second column: The timestamp. Note that all timestamps are the same. This is the case because all data has been written by the same transaction. At one glance, we can see in which order data has been inserted, and, at the same time, we can see if this has happened inside the same transaction. All the while, we still have full visibility of the order in which data has changed across ALL tables.
The method outlined in this post has served me well for many years, and has made my work of debugging the database side of things a lot easier. Therefore, I hope that this can help other people out there as well.
Do you need support for your PostgreSQL request? We will be happy to help you. Contact us directly.
Leave a Reply