Re: ERROR: cache lookup failed for collation 0 on DELETE query after upgrading from 9.X to 12.3

cen <cen(dot)is(dot)imba(at)gmail(dot)com> writes:
> The bug appears when you specify a foreign key of type varchar to point
> to primary key of type uuid.

Ah-hah. For the record, attached is an actually-self-contained test
case.

The problem comes from this bit in RI_FKey_cascade_del, which evidently
was added as part of the nondeterministic-collations patch (5e1963fb7):

if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);

which is failing to guard against the possibility that pk_coll is
InvalidOid, i.e. the referenced column is non-collatable.
ri_GenerateQualCollation would correctly do nothing in that case,
but we don't get there because get_collation_isdeterministic has
already fallen over.

While the fix seems relatively straightforward --- probably we just
need to add an OidIsValid(pk_coll) clause here and in the similar tests
elsewhere in ri_triggers.c --- I'm still going to hold Peter's feet
to the fire about this code, because I consider the state of its
documentation to be absolutely unforgivable. There is NO comment
explaining why it'd be appropriate to do this for a nondeterministic
PK collation (and not otherwise). Nor has anything been done to fix the
multiple ways in which this addition falsified ri_GenerateQualCollation's
header comment.

regards, tom lane