Обсуждение: ALTER DATABASE RESET with unexistent guc doesn't report an error

Dear Hackers,

I've found that ALTER DATABASE RESET with an unexistent guc does nothing without
error reporting.

ALTER DATABASE SET reports an error if guc doesn't exist:

> alter database mydb set myparam to 10;
ERROR:  unrecognized configuration parameter "myparam"

ALTER DATABASE RESET doesn't report an error at all:

> alter database mydb reset myparam;
ALTER DATABASE

I think it is a wrong behaviour. I believe, ALTER DATABASE RESET should report
an error in this case. I've also think that ALTER DATABASE RESET TABLESPACE does
nothing without any error reporting. I've prepared a simple patch to handle this
case (master branch). It adds a check for guc existence with error reporting.

With best regards,
Vitaly

Hi!

On Thu, 11 Sept 2025 at 13:35, Vitaly Davydov <v.davydov@postgrespro.ru> wrote:
> I've also think that ALTER DATABASE RESET TABLESPACE does
> nothing without any error reporting.

I can see that ALTER DATABASE RESET TABLESPACE indeed does not change
dattablespace.
Documentation also lacks any information about support of something
like this. [0]

This test case looks like just an oversight of 0844b3968985
I think we can remove "support" for ALTER DATABASE RESET TABLESPACE.

LGTM


[0] https://www.postgresql.org/docs/current/sql-alterdatabase.html


-- 
Best regards,
Kirill Reshke

On 2025-Sep-11, Kirill Reshke wrote:

> I think we can remove "support" for ALTER DATABASE RESET TABLESPACE.

What about ALTER USER RESET TABLESPACE?

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"Computing is too important to be left to men." (Karen Spärck Jones)

On Thu, 11 Sept 2025 at 19:27, Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2025-Sep-11, Kirill Reshke wrote:
>
> > I think we can remove "support" for ALTER DATABASE RESET TABLESPACE.
>
> What about ALTER USER RESET TABLESPACE?
>
> --
> Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
> "Computing is too important to be left to men." (Karen Spärck Jones)

Does this feature work?

```
reshke=# alter user u1 set tablespace to tb2;
ERROR:  unrecognized configuration parameter "tablespace"
```

Also this:

```
reshke=# alter table ss reset tablespace ;
ERROR:  syntax error at or near "tablespace"
LINE 1: alter table ss reset tablespace ;
                             ^
```

"tablespace" is tab-completed after 'alter table ss reset <TAB>' which
is a least strange


--
Best regards,
Kirill Reshke

=?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de> writes:
> On 2025-Sep-11, Kirill Reshke wrote:
>> I think we can remove "support" for ALTER DATABASE RESET TABLESPACE.

> What about ALTER USER RESET TABLESPACE?

Yeah, I think you're right.  The complaint is fundamentally that
these two cases behave differently:

regression=# ALTER DATABASE regression RESET bogus;
ERROR:  unrecognized configuration parameter "bogus"
regression=# ALTER DATABASE postgres RESET bogus;
ALTER DATABASE

the unobvious-to-the-user reason being that "regression" has a
pg_db_role_setting entry and "postgres" does not.  But there's
also no error for

regression=# ALTER USER postgres RESET bogus;
ALTER ROLE

and by the same logic there should be.  I think though that
the proposed patch addresses both cases.

Looking at the patch, the delta in database.out raises
another question:

 ALTER DATABASE regression_tbd RENAME TO regression_utf8;
 ALTER DATABASE regression_utf8 SET TABLESPACE regress_tblspace;
 ALTER DATABASE regression_utf8 RESET TABLESPACE;
+ERROR:  unrecognized configuration parameter "tablespace"
 ALTER DATABASE regression_utf8 CONNECTION_LIMIT 123;

The author of this bit of test script evidently thought that
ALTER ... RESET TABLESPACE is the inverse of ALTER ... SET TABLESPACE,
and what we are seeing is that it is not.  That may be a bug in
itself, but it's not what Vitaly is on about, IIUC.

            regards, tom lane

I wrote:
> Looking at the patch, the delta in database.out raises
> another question:
>  ALTER DATABASE regression_tbd RENAME TO regression_utf8;
>  ALTER DATABASE regression_utf8 SET TABLESPACE regress_tblspace;
>  ALTER DATABASE regression_utf8 RESET TABLESPACE;
> +ERROR:  unrecognized configuration parameter "tablespace"
>  ALTER DATABASE regression_utf8 CONNECTION_LIMIT 123;
> The author of this bit of test script evidently thought that
> ALTER ... RESET TABLESPACE is the inverse of ALTER ... SET TABLESPACE,
> and what we are seeing is that it is not.  That may be a bug in
> itself, but it's not what Vitaly is on about, IIUC.

That was in fact a test bug, now corrected at 4adb0380b.
I've substituted a more on-point test case, wordsmithed the
comment a little bit, and pushed it.

Thanks for the report and patch!

            regards, tom lane