Pivotal Knowledge Base

Follow

Greenplum: Drop-Create Temp Table Error Inside a Transactional Function

Environment

 Product  Version
 Pivotal Greenplum  4.3.x
 OS  RHEL 6.x

Symptom

If there is a function with a create temp table statement, drop and create a new temp table in that function.

Example:

CREATE OR REPLACE FUNCTION public.f_temp_table()
  RETURNS void AS
$BODY$

DECLARE
	v_table_oid integer ;
BEGIN
	RAISE INFO '% - Start',to_char(clock_timestamp(), 'yyyy-mm-dd hh24:mi:ss');
	RAISE INFO '% - Drop temp table', to_char(clock_timestamp(), 'yyyy-mm-dd hh24:mi:ss');
	drop  table if exists t_tmp_new_changes;
	RAISE INFO '% - Create temp table', to_char(clock_timestamp(), 'yyyy-mm-dd hh24:mi:ss');
	create temporary table t_tmp_new_changes
	(member_id integer NULL);
	select oid into v_table_oid from pg_class c
	where relname = 't_tmp_new_changes'
	and c.relnamespace = pg_my_temp_schema();
	RAISE INFO '% - OID temp table = %', to_char(clock_timestamp(), 'yyyy-mm-dd hh24:mi:ss'), v_table_oid;
	RAISE INFO '% - Insert into temp table', to_char(clock_timestamp(), 'yyyy-mm-dd hh24:mi:ss');
  	insert into t_tmp_new_changes (member_id ) select 1;
	RAISE INFO '% - End', to_char(clock_timestamp(), 'yyyy-mm-dd hh24:mi:ss');
	RETURN;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION public.f_temp_table()  OWNER TO gpadmin;
GRANT EXECUTE ON FUNCTION public.f_temp_table() TO public

Running this function by itself is fine:

gpadmin=# select public.f_temp_table();
INFO:  2016-10-17 20:56:02 - Start
INFO:  2016-10-17 20:56:02 - Drop temp table
INFO:  2016-10-17 20:56:02 - Create temp table
INFO:  2016-10-17 20:56:02 - OID temp table = 30189
INFO:  2016-10-17 20:56:02 - Insert into temp table
INFO:  2016-10-17 20:56:02 - End
 f_temp_table
--------------

(1 row)

gpadmin=# select public.f_temp_table();
INFO:  2016-10-17 20:56:03 - Start
INFO:  2016-10-17 20:56:03 - Drop temp table
INFO:  2016-10-17 20:56:03 - Create temp table
INFO:  2016-10-17 20:56:03 - OID temp table = 30215
INFO:  2016-10-17 20:56:03 - Insert into temp table
INFO:  2016-10-17 20:56:03 - End
 f_temp_table
--------------

(1 row)

But when you run it in transaction, it fails after the first run:

gpadmin=# begin;
BEGIN
gpadmin=# select public.f_temp_table();
INFO:  2016-10-17 20:56:21 - Start
INFO:  2016-10-17 20:56:21 - Drop temp table
INFO:  2016-10-17 20:56:21 - Create temp table
INFO:  2016-10-17 20:56:21 - OID temp table = 30241
INFO:  2016-10-17 20:56:21 - Insert into temp table
INFO:  2016-10-17 20:56:21 - End
 f_temp_table
--------------

(1 row)

gpadmin=# select public.f_temp_table();
INFO:  2016-10-17 20:56:21 - Start
INFO:  2016-10-17 20:56:21 - Drop temp table
INFO:  2016-10-17 20:56:21 - Create temp table
INFO:  2016-10-17 20:56:21 - OID temp table = 30267
INFO:  2016-10-17 20:56:21 - Insert into temp table
ERROR:  relation with OID 30241 does not exist
CONTEXT:  SQL statement "insert into t_tmp_new_changes (member_id ) select 1"
PL/pgSQL function "f_temp_table" line 26 at SQL statement

Resolution

This has been fixed in 5.0 version. 

Comments

Powered by Zendesk