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

Found a repro after some trial and error.

The bug appears when you specify a foreign key of type varchar to point 
to primary key of type uuid. This is obviously a developer error for 
specifying the wrong type

but somehow this used to work in 9.X but fails with internal error on 12.3.


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
)

TABLESPACE pg_default;

CREATE INDEX idx_fk_revisions_cart_revisions_next_id
     ON public.revisions USING btree
     (revisions_next_id COLLATE pg_catalog."default" ASC NULLS LAST)  
--no collate needed here..
     TABLESPACE pg_default;

CREATE INDEX idx_fk_revisions_cart_revisions_previous_id
     ON public.revisions USING btree
     (revisions_previous_id COLLATE pg_catalog."default" ASC NULLS LAST) 
--and here also..
     TABLESPACE pg_default;


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

delete from revisions where id='5c617ce7-688d-4bea-9d66-c0f0ebc635da' -- 
produces error



I will probably be able to fix our db simply by changing the fk columns 
to uuid and redefine the indexes.

I am leaving it to the postgres team to evaluate this bug further 
whether it works as expected or whether it is a regression and should be 
fixed.


Best regards, cen


On 14. 07. 20 16:11, Tom Lane wrote:
> cen <cen.is.imba@gmail.com> writes:
>> we are upgrading to 12.3 from 9.X and encountered an error with delete
>> statements.
>> 2020-07-14 15:26:20.728 CEST [67736] ERROR:  cache lookup failed for
>> collation 0
> Please supply a self-contained example.  (Mostly, I'm not interested
> in trying to guess at your table schema.)
>
>             regards, tom lane