Date: Fri, 6 Dec 2024 16:15:20 +0100
From: Yoran Heling <contact@yorhel.nl>
To: pgsql-bugs@lists.postgresql.org
Subject: Dangling operator family after DROP TYPE
Message-ID: <Z1MVCOh1hprjK5Sf@gmai021>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="FtdORitYnoaFltIz"
Content-Disposition: inline
Archived-At: 
 <https://www.postgresql.org/message-id/Z1MVCOh1hprjK5Sf%40gmai021>
Precedence: bulk


--FtdORitYnoaFltIz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Hello list,

pg_upgrade was failing on one of my databases and, while digging a bit,
I found that the database in question contained a dangling operator
family for a type that didn't exist anymore.

I've attached a script to recreate this situation: creating a new type,
defining an operator class for it and then dropping the type causes the
implicitly created operator family to remain.

Attempting to drop this operator family results in an error. Attempting
to do a dump/restore results in a syntax error on restore.

The problematic database I found this on was running 15.10, but with the
attached script I can also reproduce it on 17.2.

Happy to provide more information as needed,

Yoran.

--FtdORitYnoaFltIz
Content-Type: application/sql
Content-Disposition: attachment; filename="t.sql"
Content-Transfer-Encoding: quoted-printable

-- Silly wrapper around a smallint.=0ACREATE TYPE t;=0A=0ACREATE FUNCTION t=
_in(cstring)    RETURNS t       AS 'int2in'    LANGUAGE internal IMMUTABLE =
STRICT PARALLEL SAFE;=0ACREATE FUNCTION t_out(t)         RETURNS cstring AS=
 'int2out'   LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;=0ACREATE FUN=
CTION t_recv(internal) RETURNS t       AS 'int2recv'  LANGUAGE internal IMM=
UTABLE STRICT PARALLEL SAFE;=0ACREATE FUNCTION t_send(t)        RETURNS byt=
ea   AS 'int2send'  LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;=0ACRE=
ATE FUNCTION t_cmp(t, t)      RETURNS int     AS 'btint2cmp' LANGUAGE inter=
nal IMMUTABLE STRICT PARALLEL SAFE;=0ACREATE FUNCTION t_lt(t, t)       RETU=
RNS boolean AS 'int2lt'    LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE=
;=0ACREATE FUNCTION t_le(t, t)       RETURNS boolean AS 'int2le'    LANGUAG=
E internal IMMUTABLE STRICT PARALLEL SAFE;=0ACREATE FUNCTION t_eq(t, t)    =
   RETURNS boolean AS 'int2eq'    LANGUAGE internal IMMUTABLE STRICT PARALL=
EL SAFE;=0ACREATE FUNCTION t_ge(t, t)       RETURNS boolean AS 'int2ge'    =
LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;=0ACREATE FUNCTION t_gt(t,=
 t)       RETURNS boolean AS 'int2gt'    LANGUAGE internal IMMUTABLE STRICT=
 PARALLEL SAFE;=0ACREATE FUNCTION t_ne(t, t)       RETURNS boolean AS 'int2=
ne'    LANGUAGE internal IMMUTABLE STRICT PARALLEL SAFE;=0A=0ACREATE TYPE t=
 (=0A    internallength =3D 2,=0A    input =3D t_in,=0A    output =3D t_out=
,=0A    receive =3D t_recv,=0A    send =3D t_send,=0A    alignment =3D int2=
,=0A    passedbyvalue=0A);=0A=0ACREATE OPERATOR <  (leftarg =3D t, rightarg=
 =3D t, procedure =3D t_lt, commutator =3D > , negator =3D >=3D, restrict =
=3D scalarltsel, join =3D scalarltjoinsel);=0ACREATE OPERATOR <=3D (leftarg=
 =3D t, rightarg =3D t, procedure =3D t_le, commutator =3D >=3D, negator =
=3D > , restrict =3D scalarlesel, join =3D scalarlejoinsel);=0ACREATE OPERA=
TOR =3D  (leftarg =3D t, rightarg =3D t, procedure =3D t_eq, commutator =3D=
 =3D , negator =3D <>, restrict =3D eqsel,       join =3D eqjoinsel, HASHES=
, MERGES);=0ACREATE OPERATOR <> (leftarg =3D t, rightarg =3D t, procedure =
=3D t_ne, commutator =3D <>, negator =3D =3D,  restrict =3D neqsel,      jo=
in =3D neqjoinsel);=0ACREATE OPERATOR >=3D (leftarg =3D t, rightarg =3D t, =
procedure =3D t_ge, commutator =3D <=3D, negator =3D < , restrict =3D scala=
rgesel, join =3D scalargejoinsel);=0ACREATE OPERATOR >  (leftarg =3D t, rig=
htarg =3D t, procedure =3D t_gt, commutator =3D < , negator =3D <=3D, restr=
ict =3D scalargtsel, join =3D scalargtjoinsel);=0A=0A-- Implicitly creates =
a t_btree_ops operator family.=0ACREATE OPERATOR CLASS t_btree_ops DEFAULT =
FOR TYPE t USING btree AS=0A    OPERATOR 1 <,=0A    OPERATOR 2 <=3D,=0A    =
OPERATOR 3 =3D,=0A    OPERATOR 4 >=3D,=0A    OPERATOR 5 >,=0A    FUNCTION 1=
 t_cmp(t, t),=0A    FUNCTION 4 btequalimage(oid);=0A=0A=0A-- All the formal=
ities done, now drop the type.=0ADROP TYPE t CASCADE;=0A=0A-- The operator =
family still exists and can't be dropped:=0ADROP OPERATOR FAMILY t_btree_op=
s USING btree;=0A-- ERROR:  cache lookup failed for type 9967078=0A
--FtdORitYnoaFltIz--