From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Bernd Lentes <bernd.lentes@helmholtz-muenchen.de>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Update "usename" in pg_user
Date: Tue, 14 Nov 2023 18:20:06 +0100
Message-ID: <202311141720.u7gf2homkhst@alvherre.pgsql> (raw)
In-Reply-To: <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>
On 2023-Nov-14, Bernd Lentes wrote:
> It didn't work. I got this message (unfortunately in german):
> postgres=# update pg_user set usename = 'usename@helmholtz-munich.de' where usename = 'dorota.germann';
> FEHLER: kann Sicht »pg_shadow« nicht aktualisieren
> DETAIL: Sichten, die nicht aus einer einzigen Tabelle oder Sicht lesen, sind nicht automatisch aktualisierbar.
> HINT: Um Aktualisieren der Sicht zu ermöglichen, richten Sie einen INSTEAD OF UPDATE Trigger oder eine ON UPDATE DO INSTEAD Regel ohne Bedingung ein.
This error says that you're trying to update a view (Sicht), and
suggesting to update the underlying table instead. So you would update
pg_authid, which is the table that the pg_user and pg_shadow views are
based on (and the column would be "rolname", not "usename"). Also, you
probably wanted the SET clause as
SET usename = usename || '@helmholtz-munich.de'
otherwise all users would end up with the same username (or actually got
an error that the second user would get a duplicate name).
However, using UPDATE (or any DML) on system catalogs is not a great
move. Using \gexec as already suggested by Laurenz is probably your
best bet.
BTW, you can use
SET lc_messages to "C";
before the UPDATE to get these error messages in English.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"The Postgresql hackers have what I call a "NASA space shot" mentality.
Quite refreshing in a world of "weekend drag racer" developers."
(Scott Marlowe)
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-admin@postgresql.org
Cc: alvherre@alvh.no-ip.org, bernd.lentes@helmholtz-muenchen.de, pgsql-admin@lists.postgresql.org
Subject: Re: Update "usename" in pg_user
In-Reply-To: <202311141720.u7gf2homkhst@alvherre.pgsql>
* 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