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

cen <cen.is.imba@gmail.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

-- Need this cast, or the FK constraint definitions will be rejected:
CREATE FUNCTION varchar_to_uuid(VARCHAR) RETURNS uuid AS $$
SELECT uuid_in($1::cstring);
$$ LANGUAGE sql immutable;

CREATE CAST (VARCHAR AS UUID) WITH FUNCTION varchar_to_uuid(VARCHAR) AS IMPLICIT;

CREATE TABLE public.revisions
(
     id uuid NOT NULL,
     revisions_previous_id character varying COLLATE
pg_catalog."default", --oops, should have used uuid here
     revisions_next_id character varying COLLATE pg_catalog."default",
--same here..
     customer_notice character varying COLLATE pg_catalog."default",
     CONSTRAINT pk_revisions PRIMARY KEY (id),
     CONSTRAINT fk_revisions_next FOREIGN KEY (revisions_next_id)
         REFERENCES public.revisions (id) MATCH SIMPLE
         ON UPDATE CASCADE
         ON DELETE CASCADE,
     CONSTRAINT fk_revisions_previous FOREIGN KEY (revisions_previous_id)
         REFERENCES public.revisions (id) MATCH SIMPLE
         ON UPDATE CASCADE
         ON DELETE CASCADE
);

CREATE INDEX idx_fk_revisions_cart_revisions_next_id
     ON public.revisions USING btree
     (revisions_next_id COLLATE pg_catalog."default" ASC NULLS LAST)
;

CREATE INDEX idx_fk_revisions_cart_revisions_previous_id
     ON public.revisions USING btree
     (revisions_previous_id COLLATE pg_catalog."default" ASC NULLS LAST)
;

INSERT INTO public.revisions(
     id, revisions_previous_id, revisions_next_id, customer_notice)
     VALUES ('5c617ce7-688d-4bea-9d66-c0f0ebc635da', null, null, 'hi');

-- fails:
delete from revisions where id='5c617ce7-688d-4bea-9d66-c0f0ebc635da';