Postgresql – Postgres “session variables” kludge on AWS RDS

awsparameterpostgresql

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:

  1. Does anything I'm saying make any sense at all?
  2. Is there an easier way?
  3. 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.)

Best Answer

AWS Support confirmed that it is, indeed, impossible to set defaults for custom session variables on RDS PostgreSQL.

The next best solution was to trap SQLSTATE 42704 (undefined_object) errors in all the trigger functions with

EXCEPTION WHEN undefined_object THEN SET kludge.param TO 'default_value';

followed by replicated trigger code, so that the trigger gets properly executed even in the error case.

Related Question