Skip site navigation (1) Skip section navigation (2)

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

From: "Mario Splivalo" <mario(dot)splivalo(at)megafon(dot)hr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5427: Using CASE in plpgsql causes 'ERROR: cache lookup failed'
Date: 2010-04-16 09:56:53
Message-ID: 201004160956.o3G9urs3049822@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      5427
Logged by:          Mario Splivalo
Email address:      mario(dot)splivalo(at)megafon(dot)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.

Responses

pgsql-bugs by date

Next:From: Mario SplivaloDate: 2010-04-16 12:24:17
Subject: Re: BUG #5427: Using CASE in plpgsql causes 'ERROR: cache lookup failed'
Previous:From: Tom LaneDate: 2010-04-16 04:33:04
Subject: Re: Reset ACL to default for pg 8.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group