PostgreSQL Citus Extension Temp Table

Question

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).

• Luciano Andress Martini: All postgresql temporary tables, are maked in a system schema named pg_temp, its look like your extension, just try to rename the schema to pg_temp_6, so it looks like not compatible.... Because as the error says prefix pg_ are reserved for system schemas...

Answer 1

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 ?

• Slobodan Savkovic: Creating regular reference table would probably be too slow for query that has to run under a second. However, I have managed to restructure my queries and will write an answer based on that. Thanks for help!

Answer 2

As it turns out it is not possible to create reference nor distribute temp table to worker nodes as of citus 7.3.

There are two possible workarounds:

1. Modify your queries to avoid joins between the temp table and the distributed table (see SQL workarounds)
2. Create temp table as regular table and then mark it as reference table before populating

Since I needed sub-second queries for user response the first option worked better for me.

Here is an example of how I modified a query:

 DROP TABLE IF EXISTS myresult1;
 CREATE TEMP TABLE myresult1 AS 
 select mpoint, mgrsid, st_distance(mgrspoint, mpoint) as distance from fact_point_kpi
 inner join mypoint on st_dwithin(mgrspoint, mpoint, 0.001)
 ORDER BY mpoint, distance;
--becomes =>
 DROP TABLE IF EXISTS myresult1;
 CREATE TEMP TABLE myresult1 AS 
 SELECT mpoint, mgrsid, st_distance(mgrspoint, mpoint) AS distance
 FROM (SELECT mpoint FROM mypoint) AS mptmp
 JOIN fact_point_kpi ON st_dwithin(mgrspoint, mptmp.mpoint, 0.001)
 ORDER BY mptmp.mpoint, distance;