Thread: BUG #18964: `ALTER DATABASE ... RESET ...` fails to reset extension parameters that no longer exist

The following bug has been logged on the website:

Bug reference:      18964
Logged by:          Mert Alev
Email address:      mert@futo.org
PostgreSQL version: 17.5
Operating system:   Debian 12
Description:

My project uses PostgreSQL (default 14, but later versions are supported as
well) with an extension where one of its parameters was removed in a later
version. I had applied a non-default value for this parameter using `ALTER
DATABASE immich SET vchordrq.prewarm_dim = '512,640,768,1024,1152,1536';`.
After upgrading the extension to a version that no longer includes this
parameter, admins reported that each connection emitted the following
warning:
```
2025-06-18 23:32:46.765 CEST [49] WARNING:  invalid configuration parameter
name "vchordrq.prewarm_dim"
2025-06-18 23:32:46.765 CEST [49] DETAIL:  "vchordrq" is a reserved prefix.
```
As a solution, I added a migration that removes the now-redundant parameter
with `ALTER DATABASE immich RESET vchordrq.prewarm_dim;`. However, while
this worked for our CI that uses Postgres 14 and for admins using 14, we
began getting reports from admins using 15, 16 and 17 that the migration
fails with the following error:
```
PostgresError: invalid configuration parameter name "vchordrq.prewarm_dim"
{
  severity_local: 'ERROR',
  severity: 'ERROR',
  code: '42602',
  detail: '"vchordrq" is a reserved prefix.',
  file: 'guc.c',
  line: '1153',
  routine: 'assignable_custom_variable_name'
}
```
While not a PG developer, I think this is likely due to the change in commit
[88103567c](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=88103567c)
that was part of the 15 release. It makes for a tricky situation where it
seems the options to remove this parameter are:
1. Write directly to `pg_catalog.pg_db_role_setting`, removing this
parameter from the `setconfig` array
2. Run `ALTER DATABASE immich RESET ALL;`, purging all database-level
parameter configuration along with it
3. Make a backup, then remove the relevant line that applies this parameter
in the backup file before restoring
There may be other approaches as well, but all of them are rather cumbersome
and error-prone. My solution was to write a transaction that first queries
the `setconfig` parameters, then runs `RESET ALL`, then re-applies all
parameters except `vchordrq.prewarm_dim` using the queried data.
It would be great if `RESET`ing the parameter still worked in this
situation, even if `SET`ing it does not.

Here is a fragile proof-of-concept patch that demonstrates roughly what I
had in mind.

-- 
nathan