Keith's Ramblings…

PostgreSQL Extension Developer Tips – Part 1

without comments

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.

I’ve been working on a per-table replication extension that requires both dblink and another extension I’ve blogged about called pg_jobmon. I can’t control where someone may install dblink and I didn’t set any requirements for pg_jobmon’s schema either. The first step is to grab what schema these extensions were installed to and save that for use later.

Now, I could just use those variables and dynamically write EXECUTE statements where those schema names are needed for the rest of the function. I found this to be quite a pain when using pg_jobmon frequently to log different steps of a function’s execution. Also, using a lot of dynamic EXECUTE statements isn’t that great for efficiency. So, just for the running of this function, I change the search path, first saving the original one so I can restore it later.

Note I added the extension’s own schema in there as well to make calling its own functions easier too. Then after the function body, I restore the search path to what it was originally

So, it’s still using EXECUTE statements, but at most it’s two instead of potentially many more. And it makes calling the functions in other schemas much easier since I can just use SELECT or PERFORM statements most of the time.

One issue I ran into with this is during exceptions. The exception block resets the path, so if you have to use any of the other extensions’ functions in the exception block you either have to write execute statements using the schema variables or reset the search path again the same way.

Now, if you look at the documentation for set_config, if you set the last parameter to ‘true’ instead of false, it should only apply the change to the current session. I was running into some weird edge cases if/when the function failed that would not reset the path properly and potentially cause other issues. So I just went with ‘false’ and manually resetting the path to ensure it doesn’t interfere with other queries in the session.

Of course this can be applied to function authoring in general for PostgreSQL. But extension development is where I’ve come across it the most so far since this is most likely where you’re going to be sharing code that cannot assume the destination’s environment.

Written by Keith

October 7th, 2012 at 12:57 pm

Posted in PostgreSQL

Tagged with , ,