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.

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-gnu — Vic Colborn
– Vic Colborn, Commented Nov 17, 2016 at 21:47
That's all very odd, especially that it happens in both pg versions. I can't reproduce the error. Can you clarify: removing either idx_temp_data_to_index_thisthat or idx_temp_data_to_index_unique resolves the issue. You mean you drop an index or you remove creation from the function? — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 20, 2016 at 2:23
Ah, yes, 'Removing' from the function creation in this case. So, commenting out 'CONSTRAINT idx_temp_data_to_index_unique UNIQUE (id,this_id,that_id)' or 'CREATE INDEX idx_temp_data_to_index_thisthat ON temp_data_to_index(this_id,that_id);' — Vic Colborn
– Vic Colborn, Commented Nov 21, 2016 at 19:38
What's the result of SHOW search_path; of user session creating/executing the function? — Guedes
– Guedes, Commented Nov 21, 2016 at 23:23

Nikhil · Accepted Answer · 2016-10-25 19:13:10Z

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;

answered Oct 25, 2016 at 19:13

Nikhil

6246 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nikhil Over a year ago

yes, also dropped and created the function again, working for me.

Nikhil Over a year ago

can you drop the function and create it again and try?

Vic Colborn · Accepted Answer · 2017-03-31 20:26:54Z

1

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.

answered Mar 31, 2017 at 20:26

Vic Colborn

2,0861 gold badge19 silver badges21 bronze badges

2 Comments

ozgune Over a year ago

Hi there -- you're right. Citus scribbles on the DDL command's parse tree and this becomes an issue when used across multiple statements / transactions in PL/pgSQL functions. We're tracking this issue here and have a proposed fix for it in this pull request.

ozgune Over a year ago

Thanks for reporting! We found the underlying issue and fixed it in this pull request. The fix is on the master branch and will be included in the upcoming Citus 6.2 packages.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related