Feedback-ID: ia2694551:Fastmail
Date: Tue, 14 Nov 2023 18:20:06 +0100
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
Message-ID: <202311141720.u7gf2homkhst@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <PR3PR04MB73409EB3F2695DA61418F784D6B2A@PR3PR04MB7340.eurprd04.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202311141720.u7gf2homkhst%40alvherre.pgsql>
Precedence: bulk

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)