BUG #5427: Using CASE in plpgsql causes 'ERROR: cache lookup failed'

Started by Mario Splivaloalmost 16 years ago3 messagesbugs
Jump to latest
#1Mario Splivalo
mario.splivalo@megafon.hr

The following bug has been logged online:

Bug reference: 5427
Logged by: Mario Splivalo
Email address: mario.splivalo@megafon.hr
PostgreSQL version: 8.4.2
Operating system: Ubuntu 9.04
Description: Using CASE in plpgsql causes 'ERROR: cache lookup
failed'
Details:

I have an enum-type, like this:

CREATE TYPE type_enum_service_type AS ENUM
('Banner', 'Ticker', 'Memo');

Then I have a table, like this:

CREATE TABLE services (
service_id integer NOT NULL,
service_type type_enum_service_type NOT NULL,
service_keyword character varying NOT NULL,
service_time_created timestamp with time zone NOT NULL DEFAULT now(),
);

And, I have a plpgsql function like this:

CREATE OR REPLACE FUNCTION service_something(a_service_id integer)
RETURNS void AS
$BODY$
DECLARE

BEGIN
CASE service_type FROM services WHERE service_id = a_service_id
WHEN 'Banner' THEN
RAISE NOTICE 'It is Banner!';
WHEN 'Ticker' THEN
RAISE NOTICE 'It is Ticker!';
WHEN 'Memo' THEN
RAISE NOTICE 'It is Memo!';
ELSE
RAISE EXCEPTION 'It is strange!';
END CASE;

RETURN;

END
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER
COST 100

Then I insert some data:

INSERT INTO services (1, 'Banner', 'kw-banner', now());
INSERT INTO services (2, 'Banner', 'kw-banner', now());
INSERT INTO services (2, 'Banner', 'kw-banner', now());

When I call 'service_something' function and provide nonexistent
service_id I get this error:

ERROR: cache lookup failed for type 37

When I repeat the query (SELECT service_something(1);) the error is like
this:
ERROR: cache lookup failed for type 0

Is this desired behavior so that first I need to check if service_id is
existent, or is this a bug? :)

Mike

P.S. PostgreSQL 8.4.2 on x86_64-unknown-linux-gnu, compiled by GCC
gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3, 64-bit

I have also tried it on Postgres 9.0alpha5revised, the behavior is the same.

#2Mario Splivalo
mario.splivalo@megafon.hr
In reply to: Mario Splivalo (#1)
Re: BUG #5427: Using CASE in plpgsql causes 'ERROR: cache lookup failed'

Mario Splivalo wrote:

Is this desired behavior so that first I need to check if service_id is
existent, or is this a bug? :)

Huh! As I was explained od pg-sql list by Tom Lane, this should not
happen, so it is a bug! :) I apologize for the inconvenience.

Mike

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Mario Splivalo (#2)
Re: BUG #5427: Using CASE in plpgsql causes 'ERROR: cache lookup failed'

Mario Splivalo <mario.splivalo@megafon.hr> writes:

Huh! As I was explained od pg-sql list by Tom Lane, this should not
happen, so it is a bug! :) I apologize for the inconvenience.

It's already fixed:
http://archives.postgresql.org/pgsql-committers/2010-04/msg00126.php

regards, tom lane