Cast to regrole on a literal string in a PL/pgSQL function

Hi,

I've noticed a rather odd behavior with a PL/pgSQL function whose 
definition includes a cast to regrole on a literal string, such as 
'a_role'::regrole.

The following function simply creates a role "a_role", asserts that the 
result of a cast to regrole on the string 'a_role' returns the same OID 
as the actual OID registered in pg_roles for "a_role", and finally drops 
the role. When this function is executed repeatedly, through separate 
transactions or not, but with the same connection, the assertion will 
unexpectedly fail after the first try, because 'a_role'::regrole keeps 
returning the OID that was given to the now-dropped role on the first 
try, instead of the actual OID of the newly created role.

I first observed this issue with a PostgreSQL 17.6 server on Debian - 
Debian 17.6-1.pgdg12+1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 
12.2.0-14+deb12u1) 12.2.0, 64-bit -, then reproduced it with PostgreSQL 
13 to 18 on Windows.

CREATE OR REPLACE FUNCTION regrole_cast_anomaly()
     RETURNS VOID
     LANGUAGE plpgsql
     AS $_$
     BEGIN
         -- with a table
         -- this test won't fail
         CREATE TABLE a_table () ;

         ASSERT 'a_table'::regclass = (
             SELECT pg_class.oid::regclass
                 FROM pg_catalog.pg_class
                 WHERE pg_class.relname = 'a_table'
         ), 'something''s wrong with ''a_table''::regclass' ;

         DROP TABLE a_table ;

         -- with a role
         -- this test is very likely to fail if the function
         -- is run more than once
         CREATE ROLE a_role ;

         RAISE NOTICE '[1] ''a_role''::regrole = %', 'a_role'::regrole ;
         RAISE NOTICE '[2] %', (
             SELECT format('actual OID = %s', pg_roles.oid)
                 FROM pg_catalog.pg_roles
                 WHERE pg_roles.rolname = 'a_role'
         ) ;

         ASSERT 'a_role'::regrole = (
             SELECT pg_roles.oid::regrole
                 FROM pg_catalog.pg_roles
                 WHERE pg_roles.rolname = 'a_role'
         ), 'something''s wrong with ''a_role''::regrole' ;

         DROP ROLE a_role ;
     END
     $_$ ;
SELECT regrole_cast_anomaly() ;
SELECT regrole_cast_anomaly() ; -- the assertion will fail

I couldn't find any warning about this in the documentation.

Thank you in advance for looking into it.

Regards,
Leslie Lemaire
Secrétariat général des ministères en charge de l'aménagement du 
territoire et de la transition écologique
Direction du numérique