4

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

  • 1
    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... – Luciano Andress Martini Mar 17 '18 at 22:30
0

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 ?

  • 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! – Slobodan Savkovic Mar 20 '18 at 14:30
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.