PostgreSQL Citus Extension – Using Temp Tables

postgresqlpostgresql-extensions

Is it possible with citus extension in PostgreSQL to create temp table that is copied to each worker node (like reference table)?

When I run SQL like this:

DROP TABLE IF EXISTS mypoint; 
CREATE TEMP TABLE mypoint (mpoint geometry primary key); 
SELECT create_reference_table('mypoint');

I get the error:

ERROR: unacceptable schema name "pg_temp_6" DETAIL: The prefix "pg_"
is reserved for system schemas. CONTEXT: while executing command on
mynode01:5432 SQL state: 42939

The reason I am asking this is because there are more SQL commands afterwards where I need to do a join between the temp table and a distributed table (which is not allowed with citus extension).

Best Answer

In this particular example the temp table is created in schema pg_temp_6. create_reference_table tries to create the exact same schema in the worker, and fails.

Could you just create a regular reference table, and drop it when you are done instead of creating a temporary table ?

Related Question