PostgreSQL Extension Developer Tips – Part 3

My venture into PostgreSQL extension development was the first time I’d actually had to worry about the contents of a Makefile. The PostgreSQL Extension Network’s How To gave enough of an example that I was able to just copy-n-paste it and it worked. The fact that my extensions have no code to actually compile has a lot to do with that. The Makefile PGXN provides assumes that all your SQL code is in a single file. At first that wasn’t a big deal. But once my extensions started getting over several thousand lines combined with many separate functions, maintenance started becoming more of a pain. So I started learning a bit more about Makefiles, specifically the part that made the extension sql file that gets installed.

sql/$(EXTENSION)--$(EXTVERSION).sql: sql/$(EXTENSION).sql
    cp $< $@

Basically, that just copies your extension sql file (ex. pg_jobmon.sql) that resides in the /sql folder to the specially formatted file required by postgres (ex. pg_jobmon–0.1.2.sql). So, it works great if all your sql is in a single file. I wanted to be able to have each of my functions in their own file. And maybe other folders and files for things like tables and types. Looking through the gnu make docs I found the variable I needed to do this and how to use it

sql/$(EXTENSION)--$(EXTVERSION).sql: sql/tables/*.sql sql/functions/*.sql
	cat $^ > $@

The $^ variable represents all the prerequisites (part after the colon) for the make rule that you list on the previous line. In my case I have two folders, tables & functions, that contain .sql files. The $@ variable represents the target of the rule (part before the colon). For extensions, this is the specially formatted file they require. Using the cat command and the appropriate redirect, this command just takes all the files in the given folders and dumps them into a single file. One thing I had to be careful of was that the functions required that the tables be made first in the resulting SQL file if the install was going to run properly. The cat operation is done in the order that the prerequisites are listed, so the tables folder was placed before the functions folder.

Since my extensions are all SQL based, doing this has made my extension development tremendously easier to maintain. To see real examples, take a look at these extensions: pg_jobmon or mimeo.

And another tip that concerns the Makefile was something I just came across recently. At OmniTI, we’ve developed our own operating system called OmniOS. To make things easier for us internally, I started learning how to create packages for it, specifically for my PostgreSQL extensions so they’re easier for anyone to install. During the learning process I came across the fact that the PGXN Makefile I copied from makes some assumptions about the environment it’s being built on, specifically that it expects the version of grep to just automatically be gnu grep. This isn’t the case in OmniOS, and may not be the case for many other environments. So one of my coworkers showed me how to fix the Makefile to be a little more platform independent by adding this line

GREP ?= grep

This allows the build environment to set the grep command that is used during the build process, or if it doesn’t set it, defaults to “grep”. OmniOS does have gnu grep available, it’s just called ggrep instead.

So in addition to making extension development easier, I’ve learned my first lesson in making a saner build environment.