asked on

PostgresSQL function output union with another select query -getting error

Hi,

PostgreSQL(version 9.4) function output when union with another select query on Greenplum 6.0 database, we are getting error. Please find below simple reproducible example with output.
could you please advise any work around to overcome this error, this seems product limitation but just looking for any work around solution to achieve same output

--Test UDF

CREATE OR REPLACE FUNCTION public.get_country()
RETURNS TABLE (
country_id integer,
country character varying(50)

)
AS $$

begin

drop table if exists public.country;
create table public.country( country_id integer,
country character varying(50));

insert into public.country
( country_id,
country)
select 111,'INDIA'
union all
select 222,'CANADA'
union all
select 333,'USA' ;

RETURN QUERY
SELECT
c.country_id,
c.country
FROM
public.country c;

END; $$
LANGUAGE 'plpgsql';

--Execute UDF
select * from public.get_country();

country_id|country|
----------|-------|
222|CANADA |
111|INDIA |
333|USA |

--Create another table to test union
create table public.country_d2 as select * from public.country;

insert into public.country_d2
( country_id,
country)
select 444,'JAPAN';

select * from public.country_d2

country_id|country|
----------|-------|
222|CANADA |
444|JAPAN |
333|USA |
111|INDIA |

--Union test

with MDData as (select * from public.get_country() )
select * from MDData
union
select * from public.country_d2;

Error:

SQL Error [XX000]: ERROR: could not open existing temporary file "base/pgsql_tmp/pgsql_tmp_SIRW_657190_26_0": No such file or directory (fd.c:1504)
ERROR: could not open existing temporary file "base/pgsql_tmp/pgsql_tmp_SIRW_657190_26_0": No such file or directory (fd.c:1504)
ERROR: could not open existing temporary file "base/pgsql_tmp/pgsql_tmp_SIRW_657190_26_0": No such file or directory (fd.c:1504)

slightwv (䄆 Netminder)

Seems to be an issue in Greenplum. That query works in native Postgres.

Don't know Greenplum but possibly create a view in Postgres and uses the view in Greenplum?

https://dbfiddle.uk/?rdbms=postgres_9.4&fiddle=2a9c475a2445e463dbd69894567bdd38

ASKER

Thanks for the reply. It is the same error with view approach as well not working on Greenplum6.0. Ran a quick test to see

drop view if exists public.test_vw;
create view public.test_vw as
with MDData as (select * from public.get_country() )
select * from MDData
union
select * from public.country_d2;

select * from public.test_vw;

ERROR: could not open existing temporary file "base/pgsql_tmp/pgsql_tmp_SIRW_808364_23_0": No such file or directory (fd.c:1504)

slightwv (䄆 Netminder)

The query works fine in native Postgres. The Greenplum code is on Git I don't know enough Postgres to fully understand how the temp files work but you can see in the code, there is a comment about keeping the temp file around.

From the Git code:

 * BUT: if the temp file is slated to outlive the current transaction,

	 * force it into the database's default tablespace, so that it will not

	 * pose a threat to possible tablespace drop attempts.

Open in new window

Wonder if it might be a permission or transaction issue of some type?

ASKER

do you have any example? how to force query in the current transaction to use default table space/segments.

slightwv (䄆 Netminder)

Sorry but I'm a n00t in Postgres. Learning it myself. Know nothing about Greenplum.

ASKER

Ok, No issues. Thanks for looking into it.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.