ED2 KC- Analytics: Temp Table Already Exists

---

This error can pop up when analytics fails for some reason (not exactly sure, possible if the server goes down while running analytics). Essentially there is “cruft” left over in the database where the temporary tables are not dropped successfully.

Since there is already a table, the new ones cannot be created and analytics fails.

To resolve this error, ask the server team to drop the temporary tables which are generated by analytics. It seems the Data Admin > Maintenance > Clear Analytics does not remove Temp tables (because they are supposed to be dropped in the course of analytics processing anyway).

Then re-run analytics.

-- Tables beginning with 'analytics_*'

DO $$ DECLARE
    r RECORD;
BEGIN
    -- if the schema you operate on is not "current", you will want to
    -- replace current_schema() in query with 'schematodeletetablesfrom'
    -- *and* update the generate 'DROP...' accordingly.
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema() AND tablename LIKE 'analytics\_%') LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename);
    END LOOP;
END $$;

Example Error:

"message": "Process failed: StatementCallback; SQL [create table analytics_completenesstarget_temp (\"Wrx2x6BwOJq\" character(11),\"OhazSMX9953\" character(11),\"W8VqEnq8ji8\" character(11),\"JE5sy36j08A\" character(11),\"VV4WtUaBIBH\" character(11),\"ouopeningdate\" date,\"oucloseddate\" date,\"costartdate\" date,\"coenddate\" date,\"dx\" character(11) not null,\"ao\" character(11) not null,\"value\" double precision) with(autovacuum_enabled = false)]; ERROR: duplicate key value violates unique constraint \"pg_type_typname_nsp_index\"\n Detail: Key (typname, typnamespace)=(analytics_completenesstarget_temp, 734353814) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint \"pg_type_typname_nsp_index\"\n Detail: Key (typname, typnamespace)=(analytics_completenesstarget_temp, 734353814) already exists.",