PostgreSQL Extension Developer Tips - Part 4

So my latest release of mimeo (v0.10.0) taught me a lesson to look up a new feature to make sure of when it was added and that it would be compatible for the versions I’m trying to support. That feature was the new GET STACKED DIAGNOSTICS command that allows the capture and display of more details in the exception block to help make debugging problems easier. However that was introduced in 9.2 and I’m trying to stay compatible with 9.1. I tried wrapping the call in an IF statement that checked the current postgres version, but apparently that command gets evaluated when the function is installed, not on execution. So I’ve had to remove what I thought was a nice additional feature for now.

The reason this post is an extension developer tip was the way I handled releasing a fix for this. If you’re upgrading an extension and you are several released versions behind, when you run the ALTER EXTENSION … UPDATE TO … command, all of the updates between your version and the target version are run in sequence. However, due to the issue described above, the 0.9.3 -> 0.10.0 update script would fail if you’re not running postgres 9.2. This means anyone updating from 0.9.3 and earlier to 0.10.0 and later would never be able to run the intermediary update script, and hence never upgrade. The key to allowing a bypass of this version update is in the way the update scripts are named.

mimeo--0.9.3--0.10.0.sql

The name you give the file must be the version you’re upgrading from and the version it is upgrading you to. The trick to bypass the 0.10.0 update is to just create an update script like this

mimeo--0.9.3--0.10.1.sql

Seeing this file, the extension update planner will see that it is a shorter update path and choose it instead of running the intermediate update step from 0.9.3 to 0.10.0. The important thing you have to remember is to include ALL updates that occurred between the given versions in the bypass script.

There’s a handy function that can show you the update path that an extension update (or downgrade) can take so you can be sure things will work as expected

pg_extension_update_paths('extension_name')

This will show you every single update and downgrade path possible, and if a valid path exists, all the steps along that path. This can be quite spammy for extensions with a long update history. Luckily this function returns a record set, so you can filter with a WHERE condition. Here’s an example with mimeo 0.9.2 installed and the path that would be taken without the bypass script.

keith=# select * from pg_extension_update_paths('mimeo') where source = '0.9.2' and path is not null;
 source | target |             path             
--------+--------+------------------------------
 0.9.2  | 0.9.3  | 0.9.2--0.9.3
 0.9.2  | 0.10.0 | 0.9.2--0.9.3--0.10.0
 0.9.2  | 0.10.1 | 0.9.2--0.9.3--0.10.0--0.10.1

With the bypass script available, the update paths turn into this.

keith=# select * from pg_extension_update_paths('mimeo') where source = '0.9.2' and path is not null;
 source | target |         path         
--------+--------+----------------------
 0.9.2  | 0.9.3  | 0.9.2--0.9.3
 0.9.2  | 0.10.0 | 0.9.2--0.9.3--0.10.0
 0.9.2  | 0.10.1 | 0.9.2--0.9.3--0.10.1

So now when I give the update command

ALTER EXTENSION mimeo UPDATE TO '0.10.1';

it will skip right over the 0.10.0 update script. This will allow PostgreSQL versions older than 9.2 to update without any issues. So, my apologies to anyone that grabbed my extension right away after my blog post and ran into issues. At least this gave me an opportunity for another tip!