Cached PL/PGSQL query plan failure - Mailing list pgsql-bugs

From Donald Fraser
Subject Cached PL/PGSQL query plan failure
Date
Msg-id 004c01c44a3c$fdc6bd90$1664a8c0@DEMOLITION
Whole thread Raw
List pgsql-bugs
PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC 2.96
OS: Redhat 7.2

Snippet of code which fails:

 squery := ''SELECT '' || scolname || '' AS s_data FROM '' || stblname || ''
WHERE id = '' || idrow  || '' LIMIT 1'';
 FOR data_rec IN EXECUTE squery LOOP
  IF (text(sdata) = text(data_rec.s_data)) OR (data_rec.s_data IS NULL AND
sdata IS NULL) THEN
   RETURN VOID;
  END IF;
 END LOOP;

Error message:
ERROR:  type of "data_rec.s_data" does not match that when preparing the plan

I get this message if I call the above code once per transaction and the
difference between the transactions is that the data type returned in the
dynamic query is not the same.
I would understand it better if I were calling this piece of code multiple
times with in the one transaction - but I am not.
To over come this problem I had to change the dynamic query as such:

 squery := ''SELECT text('' || scolname || '') AS s_data FROM '' || stblname ||
'' WHERE id = '' || idrow  || '' LIMIT 1'';
 FOR data_rec IN EXECUTE squery LOOP
  IF (text(sdata) = data_rec.s_data) OR (data_rec.s_data IS NULL AND sdata IS
NULL) THEN
   RETURN VOID;
  END IF;
 END LOOP;


Regards
Donald Fraser

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: build postgresql-7.4.2 fails: FATAL: semctl(35, 16, SETVAL, 536) failed: Inv,alid argument
Next
From: "PostgreSQL Bugs List"
Date:
Subject: BUG #1160: Postgres causing system to halt