My first PostgreSQL patch/hack

After almost two years of lobbying at work to promote PostgreSQL instead of MySQL, after two years of tears against poor DB typing, lack of advanced SQL features, traps in what seemed like basic needs (select * from myview where a=5, if myview is not «simple», triggers a full scan on the view result)… we are finally starting to deploy some PostgreSQL databases.

I wrote my dear sysadmin friends documentations, argumentations. Among my «it’s simpler» arguments, I promoted application_name as a way to quickly know who is hurting that bad your SQL server, along with the so simple ps faux to know who is doing what on your server… That’s the simplest monitoring possible, but when you’re in a hurry at 3AM, it can help you… Sadly, the application_name is not displayed in the ps output. I was looking for a tiny thing to patch PostgreSQL in order to discover its code a bit, it seemed like a good opportunity.

And let’s share also how I did the patch to highlight how easy patching PostgreSQL is (the patch has been written together with this blog entry)

Well, this is quite simple, the activity is added to a buffer containing the fixed portion of the proc title. To prevent useless memory allocations, the same buffer is reused at each call.
Our application_name can change during one connexion, we will need it in this function to dynamically append it before the activity when available.
But where is application_name available ?
~/projects/postgresql$ ack-grep --type cc application_name

This gives us more results, but there is an obvious one :src/include/utils/guc.h
228:extern char *application_name;

It’s a simple per process global variable…

2) Patching…

So our patch is very straigh-forward now, even for C beginners.
~/projects/postgresql$ $EDITOR src/backend/utils/misc/ps_status.c

The main part we will change is around here :strlcpy(ps_buffer + ps_buffer_fixed_size, application_name,
ps_buffer_size - ps_buffer_fixed_size);
ps_buffer_dynamic_len = strlen(application_name);

3) Testing

Compiling and testing PostgreSQL is simple, and can be done along another running instance.
~/projects/postgresql$ ./configure --prefix $HOME/bin/psql-master --enable-debug
~/projects/postgresql$ make -j 6
~/projects/postgresql$ make -j 3 install

Et voilà…

Now that your PostgreSQL has been installed in ~/bin/psql-master, let’s create an instance and start it.~/projects/postgresql$ cd ~/bin/psql-master/bin
~/bin/psql-master/bin$ ./initdb ../data
…

If you have a PostgreSQL running on port 5432, you’ll have to change the port in the ../data/postgresql.conf file. I changed it to 5435.
And now…
~/bin/psql-master/bin$ ./postgres -D ../data
LOG: database system was shut down at 2013-06-17 23:51:29 CEST
LOG: database system is ready to accept connections
LOG: autovacuum launcher started

But it’s not ready to be submitted yet… Maybe people are using the current ps output and awk/cut-ing it to extract data. Better not crash these by adding a configuration option maybe.

4) Back to code…

First, you must commit your current patch. Git is your friend, use it
~/projects/postgresql$ git add src/backend/utils/misc/ps_status.c
~/projects/postgresql$ git commit -m "Display the application_name in proctitle before the current activity"
[application-name-in-proctitle 28a6b94] Display the application_name in proctitle before the current activity
1 file changed, 17 insertions(+), 2 deletions(-)

Now, we can look at adding a new configuration option.
We know that a configuration option exists to disable update_process_title entirely. We can just look at how it works, and copy it.

And we have our configuration setting, allowing us to switch between application_name in the process title and the current behaviour.

5) Conclusion

In less than one hour, with no PostgreSQL code experience, it’s really easy to hack a simple patch like this one.
After having nightmares through the MySQL code, it’s a real pleasure to hack on PostgreSQL code : it’s simple, commented, logical…