Internal error with types changes and prepared statements

From https://github.com/sqlalchemy/sqlalchemy/issues/6842#issuecomment-925131836

The issue can be reproduced in psql with the following commands
(tested with PostgreSQL 12.8):

BEGIN;
CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
PREPARE stmt (an_enum[]) AS INSERT INTO foo (bar) VALUES ($1);
EXECUTE stmt ('{foo}');
ROLLBACK;

BEGIN;
CREATE TYPE an_enum AS ENUM ('foo', 'bar', 'baz');
CREATE TABLE foo(id integer, bar an_enum[]);
EXECUTE stmt ('{foo}');
ERROR:  cache lookup failed for type 8072147

\errverbose
ERROR:  XX000: cache lookup failed for type 8072147
LOCATION:  getBaseTypeAndTypmod, lsyscache.c:2355

Note: the issue is caused by Psycopg 3 use of prepared statements. Do
you suggest discarding prepared statements on a rollback on the
connection? I haven't seen documentation describing the interaction
between transactions and prepared statements.

Cheers

-- Daniele