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
