[BUGS] BUG #14659: "ERROR: could not open relation with OID" when creatingtable from SELECT with large row - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      14659
Logged by:          Kwan Lim
Email address:      kwan.lim@gmail.com
PostgreSQL version: 9.5.6
Operating system:   Windows 10
Description:

We have a UDF that creates a temporary table from a SELECT statement then
returns the result and drops the table.

For certain cases where it appears that there are large strings in some
columns the UDF fails with: "ERROR: could not open relation with OID"

I have prepared a SQL script that demonstrates the problem:

------- BEGIN SQL SCRIPT ----------
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.6
-- Dumped by pg_dump version 9.5.6

-- Started on 2017-05-18 10:08:03

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = exclaim, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- 1. Create table

CREATE TABLE bbb (   foo integer,   bar integer,   baz character varying(25),   ram character varying,   pop character
varying,  lor character varying,   tor numeric
 
);

-- 2. Populate table with data
INSERT INTO bbb VALUES (100, 123456, NULL, 'aaaa@aaaaaaaaT.aa
,aaaa@aaaaaaaaT.aa', 'LL', 'LL', 1.00000000000000000000);
INSERT INTO bbb VALUES (101, 123457, NULL, 'aaaa@aaaaaaaaT.aa
,aaaa@aaaaaaaaT.aa', 'OUPKJUULOJOZP JOOK UPJOP
OLKUJUK PLUOOO ZUUK $5.75, 9UL JPOZOK LJJJ $7.99
OLPZPJJJJ UUPZUK JJJ ZJUPJ $4.97 OPLOJJ POZJJZ $5.75, 2 PJK OJOOJPP $2.20 JL
2 ULP JPOZOK OOPU $5.59 OJP UL ,4 OLOKLPUOP $9.94 Z 4
LPPUK JLPKJOP JPOZJO ZJJP $9.59 JJUUOLOP OLZOOOLUPJ $6.39, KUZL POKL $9.50
OLUJ PJLPO''P JLLLOJPO OJOOJP PLZKJ $3.78
JPLPJUUU OLOPOZL OJPPO $4.24, LZOP UJOUOL PZPZO $4.59
JLP POLPP ZJJ PLULK $3.99
KOOOUUOJOPP JLPKJO PLULK $3.99, PJOPLPUOOP KJLPLP PLULK $3.99 2 PPLLJU UKJ
OLKU $2.49 JL, UUPUULOK KJUKUJO LPJLPP $20.99
JPJLP ZLUZJ LUZJLJPPUJP $96.37 Z 2LLLL JJLOOZU $3.45, JLZ UJL POZPKPJLO
$2.502.5 KOZ JJJP $5.49 Z 2, ZPPL JOP JOPPJPLKUPJ $3.49 LLUPLOUK ZUOJJPJPPJ
$4.09 LLP PLZKJ $3.78 PUPLKJL PLZKJ $6.89 UJPKJZO $6.89 LUKUP LLLZ KUUUP
$6.89,JOOJZ OZPPLPK $2.63
OUOKJK JLPUUK $8.00 , JZPPL KJZOUZ OUUK PLUPL $3.44
KOPO PPLPKJ $4.09, LLUUOJ POKL $4.09 LUU OZPOOPJ JUOZP 2.29
LLZ UJLZJP $2.06ZJJPLLUJ OUU $2.29, OUUZJ OUU $2.29, 
ZOPKJPPJPPJUPJ $5.96, JUUPJPP $5.64, PJL LLJP', 'OUPKJUULOJOZP JOOK UPJOP
OLKUJUK PLUOOO ZUUK $5.75, 9UL JPOZOK LJJJ $7.99
OLPZPJJJJ UUPZUK JJJ ZJUPJ $4.97 OPLOJJ POZJJZ $5.75, 2 PJK OJOOJPP $2.20 JL
2 ULP JPOZOK OOPU $5.59 OJP UL ,4 OLOKLPUOP $9.94 Z 4
LPPUK JLPKJOP JPOZJO ZJJP $9.59 JJUUOLOP OLZOOOLUPJ $6.39, KUZL POKL $9.50
OLUJ PJLPO''P JLLLOJPO OJOOJP PLZKJ $3.78
JPLPJUUU OLOPOZL OJPPO $4.24, LZOP UJOUOL PZPZO $4.59
JLP POLPP ZJJ PLULK $3.99
KOOOUUOJOPP JLPKJO PLULK $3.99, PJOPLPUOOP KJLPLP PLULK $3.99 2 PPLLJU UKJ
OLKU $2.49 JL, UUPUULOK KJUKUJO LPJLPP $20.99
JPJLP ZLUZJ LUZJLJPPUJP $96.37 Z 2LLLL JJLOOZU $3.45, JLZ UJL POZPKPJLO
$2.502.5 KOZ JJJP $5.49 Z 2, ZPPL JOP JOPPJPLKUPJ $3.49 LLUPLOUK ZUOJJPJPPJ
$4.09 LLP PLZKJ $3.78 PUPLKJL PLZKJ $6.89 UJPKJZO $6.89 LUKUP LLLZ KUUUP
$6.89,JOOJZ OZPPLPK $2.63
OUOKJK JLPUUK $8.00 , JZPPL KJZOUZ OUUK PLUPL $3.44
KOPO PPLPKJ $4.09, LLUUOJ POKL $4.09 LUU OZPOOPJ JUOZP 2.29
LLZ UJLZJP $2.06ZJJPLLUJ OUU $2.29, OUUZJ OUU $2.29, 
ZOPKJPPJPPJUPJ $5.96, JUUPJPP $5.64, PJL LLJP', 1.00000000000000000000);


-- 3. Create UDF to return row from table using a temp table
CREATE OR REPLACE FUNCTION testbbb(   IN pid integer) RETURNS TABLE( foo integer,   bar integer,   baz character
varying,  ram character varying,   pop character varying,   lor character varying,   tor numeric) AS
 
$BODY$
BEGINDROP TABLE IF EXISTS TempReport;CREATE TEMPORARY TABLE TempReport AS    SELECT A.foo, A.bar, A.baz, A.ram, A.pop,
A.lor,A.tor    FROM bbb A    WHERE A.bar=pid;     RETURN QUERY    SELECT T.foo, T.bar, T.baz, T.ram, T.pop, T.lor,
T.tor   FROM TempReport T;DROP TABLE IF EXISTS TempReport;
 
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100 ROWS 1000;

-- 4. Call to UDF that succeeds select * from testbbb(123456); 
-- 5. Call to UDF that fails with:
-- ERROR: could not open relation with OID ____
-- SQL state: XX000 select * from testbbb(123457);
------- END SQL SCRIPT ----------


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs