I am trying to migrate a PostgreSQL database to AWS RDS.
The problem I ran into was that the database in question makes extensive use of the
SET kludge.variable TO 'foo' hack, that is, it uses the customized options syntax for storing runtime session data. (In this case, the DB has a slew of triggers, which seem to be basically reinventing the concept of roles. Rewriting the software isn't really an option at this point, so please bear with me here.)
The main problem with migrating that DB to the AWS cloud is that I couldn't find a way to set a sensible default for the custom param. An RDS database doesn't give you Superuser access required for
ALTER SYSTEM SET kludge.param TO 'foo', nor do you get to edit
postgresql.conf. (It's possible to edit existing DB parameters via RDS's DB parameter group mechanism, but adding new ones won't work. I tried.)
In addition, trying to get the
current_setting(kludge.param) doesn't return anything nice if the option isn't set; instead it causes the statement to fail with an SQL error. Here's what I get when I try to update a table without setting the param in the session:
ERROR: unrecognized configuration parameter "kludge.param" CONTEXT: PL/pgSQL function update_modifier() line 1 at assignment
Furthermore, I couldn't find a way to check for the existence of the parameter without producing the same error.
My current idea is to edit all the trigger functions (although there are many triggers, there aren't ridiculously many trigger functions) to trap error 42P02 undefined_parameter with a
WHEN clause, and set a default value for the custom parameter in the error handler.
Given that my only goal seems very simple (inserts and updates work sensibly, with or without setting any custom parameters) and my solution seems to fall in the suspicious category of "clever hacks for enabling other clever hacks", I wanted to check with you guys first:
- Does anything I'm saying make any sense at all?
- Is there an easier way?
- If there isn't, is this approach likely to solve my problem?
Thanks for your time in advance.
(We're running PostgreSQL server version 9.5.10.)