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

BUG in temp tables involving a temp table not properly hiding a regular table as well as allowing non-existent column names

From: Frank van Vugt <ftm(dot)van(dot)vugt(at)foxi(dot)nl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG in temp tables involving a temp table not properly hiding a regular table as well as allowing non-existent column names
Date: 2005-06-07 16:06:32
Message-ID: 200506071806.34914.ftm.van.vugt@foxi.nl (view raw or flat)
Thread:
Lists: pgsql-bugs
L.S.

Looking forward to your analysis of the following bug:


database=# select version();
                                version
------------------------------------------------------------------------
 PostgreSQL 8.0.3 on i586-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)


****
**** START HERE TO REPRODUCE
****

CREATE TYPE full_sequence_type AS (id int);
CREATE OR REPLACE FUNCTION full_sequence(integer, integer)
	RETURNS SETOF full_sequence_type
	LANGUAGE 'plpgsql'
	IMMUTABLE
	STRICT
	SECURITY INVOKER
	AS '	DECLARE
			my_from ALIAS FOR $1;
			my_to ALIAS FOR $2;
			result full_sequence_type%ROWTYPE;
		BEGIN
			-- just loop
			FOR i IN my_from..my_to LOOP
				result.id = i;
				RETURN NEXT result;
			END LOOP;

			-- finish
			RETURN;
		END;';

-- create a regular table and show that it holds 100 records
create table f1 as select id as id2 from full_sequence(1, 100);
-- show that it holds 100 records
select count(*) from f1;

-- create a regular table and show that it holds 100 records
create temp table f1 as select id as id2 from full_sequence(1, 100);
select count(*) from f1;

-- remove 99 records from the temp table and show that 1 row remains
delete from f1 where id2 > 1;
select count(*) from f1;

-- try to select a non-existent column, show that it fails
select id from f1;
ERROR:  column "id" does not exist

-- however, the following query will happily run AND return a wrong result
-- based on the regular table instead of the temporary one
select count(*) from full_sequence(1, 100) where id in (select id from f1);
 count
-------
   100
(1 row)



****
**** USE THIS TO CLEANUP
****

DROP FUNCTION full_sequence(integer, integer);
DROP TYPE full_sequence_type;
DROP TABLE f1;
DROP TABLE f1;




-- 
Best,




Frank.


Responses

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2005-06-07 16:23:32
Subject: Re: BUG in temp tables involving a temp table not properly
Previous:From: Tom LaneDate: 2005-06-07 14:11:45
Subject: Re: BUG #1455: pg_dumpall fails

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