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.

One of our clients recently asked for a way to manage a stopwords table in PostgreSQL so that they could try to shorten the URLs for the titles of their web pages. In all honesty, I had no idea what a stopwords list even was at the time the question was asked. The client was previously a MySQL user, so they’d sent me a link to its documentation on them as an example. Once I saw that it was related to full-text search, I immediately started reading up on PostgreSQL’s abilities with that.

Following up on my previous tip, there’s a way you can make other extensions optional using the last tip’s method of finding another extension’s schema. A use case for this is my pg_jobmon extension.

First, you do the same check for the other extension’s schema

SELECT nspname INTO v_jobmon_schema FROM pg_namespace n, pg_extension e WHERE e.extname = 'pg_jobmon' AND e.extnamespace = n.oid;

Then, wherever you would optionally use your other extension, just check and see if the schema variable is null

I’ve been working on several extensions for PostgreSQL over the last few months. I was going to do one big blog post of some developer tips, but figured I’d just do this as an ongoing series as I come across something useful to share.

One of the first issues I came across was when my extension required another extension to work. I was designing my extension to be released to the public, so I couldn’t assume what schema someone may put those other extensions in. After some discussion with coworkers and at PGCon 2012 in the Royal Oak track, I managed to find a method to dynamically use any other extension’s schema.

Following up to my post last month about pg_jobmon’s logging capability, this post will discuss its ability to let you monitor your jobs’ statuses. Like last month, I will again be assuming you’ve installed pg_jobmon to the jobmon schema.

The Monitoring Function

The main function that’s used to monitor logged jobs is check_job_status(interval). It returns row output, so it can easily be used as a query for monitoring software (more on that later). The interval argument is used to tell the function how far back to go when checking the job logs. To help the function run faster, this should be not much greater than the longest interval between any job that is listed in job_check_config (see below). For example, if you’re monitoring a special job that is run weekly, and nothing you’re monitoring runs with any greater interval of time (bi-weekly, monthly, etc), then you should call

So it’s been a while since I released pg_extractor and made my first blog post. It’s had quite a few improvements in that time, and actually made version 1.0.0! (currently at 1.2.0) Many thanks to Joby Walker and Heather Sherman for the new features and bug fixes!

The the most significant and the one I’m most thankful for getting done is

--options_file
        Use a configuration file to list all the options you'd like to use. Each option is
        listed on its own line exactly as it would appear on the command line. This can be
        used in combination with command line options, but preference will be given to
        whichever is listed LAST on the command line. Also note that unlike other options
        here, there is NO equal sign between the option and the path to the options file.

The available options for this script just kept growing and growing and if you needed to use a significant amount of them, the command line got pretty crowded. This uses the Getopt::ArgvFile perl module, so please make sure you have that installed if you’re going to use this. It is an optional module, so the script will still work if you don’t have it and can’t use this option.