From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mert@futo.org
Subject: BUG #18964: `ALTER DATABASE ... RESET ...` fails to reset extension parameters that no longer exist
Date: Fri, 20 Jun 2025 12:24:20 +0000
Message-ID: <18964-ba09dea8c98fccd6@postgresql.org> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mert@futo.org
Subject: Re: BUG #18964: `ALTER DATABASE ... RESET ...` fails to reset extension parameters that no longer exist
In-Reply-To: <18964-ba09dea8c98fccd6@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox