1.0 Introduction

Catalog table is a set of special tables in PostgreSQL for storing metadata information of the database. These tables record the definition, structure, access rights and other important information of database objects. In your PostgreSQL journey, you may not have a chance to explore all of them, but just be aware that they are there, and most of time, they should have everything you need to know about your database instance.

There is also a concept of catalog views, which provides a higher level of abstraction for easier viewing and understanding of database structures and objects returned by catalog tables. We normally use catalog views to access catalog information rather than accessing catalog tables directly. Today I will show you how to customize catalog views by going through PostgreSQL source code (Version 15). This would also be a good exercise for anyone wishing to learn more about PostgreSQL development.

2.0 Catalog View Example

We will use pg_stat_ssl as example, and we will be adding 2 more columns (not_before and not_after) to its output

From:

postgres=# select * from pg_stat_ssl;
 pid  | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn
------+-----+---------+--------+------+-----------+---------------+-----------
 7448 | t   |   xxx   |   xxx  | xxx  |    xxx    |     xxx       |    xxx

To:

postgres=# select * from pg_stat_ssl;
 pid  | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn | not_before | not_after
------+-----+---------+--------+------+-----------+---------------+-----------+------------+----------
 7448 | t   |   xxx   |   xxx  |  xxx |    xxx    |      xxx      |    xxx    |    xxx     |    xxx    

3.0 Approach

3.1 Find the schema definition of pg_stat_ssl:

This can be done using global search function of your code editor, like Eclipse。 search for the pg_stat_ssl keyword, we should find it at src/backend/catalog/system_view.sql. Let’s add 2 more outputs called not_before and not_after:

CREATE VIEW pg_stat_ssl AS
    SELECT
            S.pid,
            S.ssl,
            S.sslversion AS version,
            S.sslcipher AS cipher,
            S.sslbits AS bits,
            S.ssl_client_dn AS client_dn,
            S.ssl_client_serial AS client_serial,
            S.ssl_issuer_dn AS issuer_dn,
            S.ssl_not_before AS not_before,
            S.ssl_not_after AS not_after
    FROM pg_stat_get_activity(NULL) AS S
    WHERE S.client_port IS NOT NULL;

3.2 Trace the source of values

The schema suggests that the values come from a SQL function called pg_stat_get_activity. Let’s find this implementation using global search again, which should turn out to be at src/backend/utils/adt/pgstatfuncs.c.

Our main goal here is to make this function return 33 columns instead of 31:

3.3 add not_before and not_after values

We want to add the 2 values right after issuer_dn value, so we find the index of issuer_dn and add the 2 values after it (both data and null arrays):

and push all other values 2 index up

3.4 find the source of values

We just told pg_stat_get_activity the variables that contain not_before and not_after values, but we never give them any values. So we once again have to look at our neighbor, issuer_dn and find where it got it values. We should find that the values are assigned at these file locations when a psql connects:

• src/backend/utils/activity/backend_status.c
• src/backend/libpq/be-secure-openssl.c

3.5 update pg_proc.dat file

so far, we have modified pg_stat_get_activity function to output 2 more columns, but PostgreSQL itself does not know about it yet. It still expects 31 values instead of 33. So we need to tell it by modifying src/include/catalog/pg_proc.dat. We will output the values in cstring data type for simplicity.

• proallargtypes: data types of all outputs
• proargmodes: indicates input i or output o
• proargnames: names of arguments

3.6 test

We now should have everything in place. We shall do a clean build of PostgreSQL (make clean; make). When we connect to this PostgreSQL instance with SSL enabled, we should see the output of new values:

Now, we will need to run the regression tests and fix the test failures as a result of this change.

4.0 Conclusion

This is the general procedure to modify catalog views, in which I believe would be a good exercise to demonstrate PostgreSQL source structure and learn more about catalog data. I hope you would feel the same as well.

Cary Huang

Cary is a Senior Software Developer in HighGo Software Canada with 8 years of industrial experience developing innovative software solutions in C/C++ in the field of smart grid & metering prior to joining HighGo. He holds a bachelor degree in Electrical Engineering from University of British Columnbia (UBC) in Vancouver in 2012 and has extensive hands-on experience in technologies such as: Advanced Networking, Network & Data security, Smart Metering Innovations, deployment management with Docker, Software Engineering Lifecycle, scalability, authentication, cryptography, PostgreSQL & non-relational database, web services, firewalls, embedded systems, RTOS, ARM, PKI, Cisco equipment, functional and Architecture Design.