4

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.)

Improve this question
3
  • Can you run alter user bass set kludge.param TO 'foo';?
    – user1822
    Commented May 2, 2018 at 10:30
  • @a_horse_with_no_name, unfortunately, no. ERROR: permission denied to set parameter "kludge.param". This is a particularly good guess though, since the account we get does indeed have the "create role" attribute, which should be the one that gets checked when trying to alter user. (As a particularly interesting behaviour, trying to alter user all not only fails, it also causes the connection to drop with "SSL SYSCALL error: EOF detected".)
    – Bass
    Commented May 2, 2018 at 12:08
  • (After further investigation, it seems like that command crashes the entire DB: all pids in pg_stat_activity change at once.)
    – Bass
    Commented May 2, 2018 at 12:22

2 Answers 2

Reset to default
1

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.

Improve this answer
1

Setting PGOPTIONS when connecting to the database to set the value of kludge.param might be a workaround for you, if you have control of where the DB connections are made from.

For example, setting PGOPTIONS to -c kludge.param=default_value does seem to work with RDS; SELECT current_setting(kludge.param) returns default_value in the session.

see 19.1.4 in https://www.postgresql.org/docs/current/config-setting.html

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.