Cleanly setting the role when doing DDL from inside a PLPgSQL function

Question

A bit of background first : I have an application that is deployed to many sites, each with its own DB.

Several users can use the DB. I have created a appdbusers ROLE that I granted to each user. The DB and all the tables are owned by appdbusers.

At the application start, an upgradedb() function does the required DDL to add/modify/delete relations, tuples, constraints, etc. to match the application's requirements.

Before calling upgradedb() the application does SET ROLE appdbusers and afterwards RESET ROLE. This way, the first user launching the new version upgrades the DB.

Now I have a rather big PLPgSQL function in which I drop 2 tables and recreate them ; these should be defined as TEMP tables but for different reasons they can not. I could consider them as regular tables and do the DDL magic in upgradedb() but I prefer to DROP and reCREATE them on each run.

I would like to include the SET ROLE appdbusers at the start of the function and the RESET ROLE at the end but if the function aborts for any reason (including user interruption), the RESET ROLE will never happen.

I haven't seen a TRY...FINALLY construct in PLPgSQL and I have read (here) that :

functions that need to trap exceptions are considerably more expensive, so it's best to avoid exceptions.

So is there a way to automatically reset the role when the function exits ? Or is there a better way to handle the DDL logic ?

Answer 1

Use SET LOCAL ROLE.

The documentation says:

The effects of SET LOCAL last only till the end of the current transaction, whether committed or not.

Unless you start an explicit transaction before calling the function, that should do what you want.