postgresql error: 'schema "" does not exist' on temporary table

Question

I'm finding that this rather simple postgresql 9.6 function

CREATE OR REPLACE FUNCTION public.trying_to_index_me()
RETURNS VOID  AS
$BODY$
    BEGIN
    CREATE TABLE public.table_to_index ( 
        id INTEGER NOT NULL,    
        this_id UUID NOT NULL,
        that_id smallint NOT NULL,
        CONSTRAINT idx_table_to_index_unique
            UNIQUE (id,this_id,that_id)     
    ); 
    CREATE INDEX idx_table_to_index_thisthat ON public.table_to_index(this_id,that_id);  
    DROP TABLE public.table_to_index;
END;
$BODY$ LANGUAGE plpgsql;

--SELECT public.trying_to_index_me();

is resulting in a schema "" does not exist error. The exact error is:

ERROR:  schema "" does not exist
SQL state: 3F000
Context: SQL statement "CREATE INDEX idx_table_to_index_thisthat 
ON public.table_to_index(this_id,that_id)" 
PL/pgSQL function trying_to_index_me() line 10 at SQL statement

and occurs reliably on the second and subsequent executions. Cut/Pasting the above SQL chunk reproduces the error...for me. Quite interested if that's not the case for you. I have the following clues:

• The schema detected in the error message varies. Mostly it is reported as "", but others like "0MA{Start of Text} " or some snippet of sql statement/comment from a previous statement in the transaction. Sounds memory pointer related.
• It will error consistently once its in.
• I find that if I CREATE OR REPLACE the function, I'll get one execution and then the errored state will occur again.
• I find that if I open a new pgadminIII window (without dropping or recreating), I'll get the same one execution and then the errored state will occur again...regardless of if it was errored in a different window. Sounds connection related.
• I find that commenting out the creation of either idx_temp_data_to_index_thisthat or idx_temp_data_to_index_unique resolves the issue.
• Occurs in both "PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit" and in the 9.6 implementation.
• If the above function is executed from another function, the CREATE OR REPLACE FUNCTION 1-time resolve above does not work on the parent function, only when replacing the child function. And doesn't matter if that occurs in a separate pgadmin window. PSounds like neither the client or the transaction.

Truly appreciate your thoughts.

Answer 1

I think a comma is missing after that_id smallint NOT NULL

CREATE OR REPLACE FUNCTION trying_to_index_me()
RETURNS VOID  AS
$BODY$
    BEGIN
    CREATE Temporary TABLE temp_data_to_index ( 
        id INTEGER NOT NULL,    
        this_id UUID NOT NULL,
        that_id smallint NOT NULL,
        CONSTRAINT idx_temp_data_to_index_unique
            UNIQUE (id,this_id,that_id)     
    ); 
    CREATE INDEX idx_temp_data_to_index_thisthat ON temp_data_to_index(this_id,that_id);  
    DROP TABLE temp_data_to_index;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Answer 2

This appears to have been caused by the citus data extension. The error is caused by corrupted memory that occurs once the default stack of 2M is exceeded. It doesn't show up in the logs, and never makes it to the "stack depth limit exceeded" exception that would have been thrown.

That's all speculative finger pointing.

Uninstalling citus extension resolved the issue for me.