Dropping a schema that has a type used in a distribution column of a table fails

[onurctirtir]

Reproducible on 10.2 (and maybe in older versions too):

create schema my_schema;
set search_path to my_schema;

create type my_type as (int_field int);
create table tbl (a my_schema.my_type);
SELECT create_distributed_table('tbl', 'a');

drop schema my_schema cascade;
-- ERROR:  cache lookup failed for type 326845

set search_path to public;

drop schema my_schema cascade;
-- ERROR:  cache lookup failed for type 326845

-- and here is how we can workaround "cache lookup failed" errors
drop table my_schema.tbl;
drop schema my_schema cascade;

[onderkalaci]

This should be fixed on Citus 11 via #5372.

Can you repro on master?

[onurctirtir]

This should be fixed on Citus 11 via #5372.

Can you repro on master?

yeah, I can reproduce on master too.

[hanefi]

This looks like a different codepath reached from citus_drop_trigger that still depend on Citus caches . citus_drop_trigger calls citus_drop_all_shards, and this UDF depends on the existence and validity of the cache.

My suggestion is to update this codepath and introduce new helper functions that read the information from catalog tables directly, and remove the dependency on the caches.

errstart(int elevel, const char * domain) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\utils\error\elog.c:341)
errstart_cold(int elevel, const char * domain) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\utils\error\elog.c:326)
getBaseTypeAndTypmod(Oid typid, int32 * typmod) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\utils\cache\lsyscache.c:2497)
getBaseType(Oid typid) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\utils\cache\lsyscache.c:2472)
GetDefaultOpClass(Oid type_id, Oid am_id) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\commands\indexcmds.c:2112)
citus.so!GetFunctionInfo(Oid typeId, Oid accessMethodId, int16 procedureId) (\home\hanefi\code\citus\src\backend\distributed\worker\worker_partition_protocol.c:340)
citus.so!BuildCachedShardList(CitusTableCacheEntry * cacheEntry) (\home\hanefi\code\citus\src\backend\distributed\metadata\metadata_cache.c:1600)
citus.so!BuildCitusTableCacheEntry(Oid relationId) (\home\hanefi\code\citus\src\backend\distributed\metadata\metadata_cache.c:1484)
citus.so!LookupCitusTableCacheEntry(Oid relationId) (\home\hanefi\code\citus\src\backend\distributed\metadata\metadata_cache.c:1273)
citus.so!RefreshTableCacheEntryIfInvalid(ShardIdCacheEntry * shardEntry) (\home\hanefi\code\citus\src\backend\distributed\metadata\metadata_cache.c:1086)
citus.so!LookupShardIdCacheEntry(int64 shardId) (\home\hanefi\code\citus\src\backend\distributed\metadata\metadata_cache.c:1116)
citus.so!ShardPlacementListIncludingOrphanedPlacements(uint64 shardId) (\home\hanefi\code\citus\src\backend\distributed\metadata\metadata_cache.c:1008)
citus.so!DropTaskList(Oid relationId, char * schemaName, char * relationName, List * deletableShardIntervalList) (\home\hanefi\code\citus\src\backend\distributed\operations\delete_protocol.c:376)
citus.so!DropShards(Oid relationId, char * schemaName, char * relationName, List * deletableShardIntervalList, _Bool dropShardsMetadataOnly) (\home\hanefi\code\citus\src\backend\distributed\operations\delete_protocol.c:247)
citus.so!citus_drop_all_shards(FunctionCallInfo fcinfo) (\home\hanefi\code\citus\src\backend\distributed\operations\delete_protocol.c:143)
ExecInterpExpr(ExprState * state, ExprContext * econtext, _Bool * isnull) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\execExprInterp.c:749)
ExecInterpExprStillValid(ExprState * state, ExprContext * econtext, _Bool * isNull) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\execExprInterp.c:1824)
ExecEvalExprSwitchContext(_Bool * isNull, ExprContext * econtext, ExprState * state) (\home\hanefi\.pgenv\src\postgresql-14.0\src\include\executor\executor.h:339)
ExecProject(ProjectionInfo * projInfo) (\home\hanefi\.pgenv\src\postgresql-14.0\src\include\executor\executor.h:373)
ExecResult(PlanState * pstate) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\nodeResult.c:136)
ExecProcNodeFirst(PlanState * node) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\execProcnode.c:463)
ExecProcNode(PlanState * node) (\home\hanefi\.pgenv\src\postgresql-14.0\src\include\executor\executor.h:257)
ExecutePlan(EState * estate, PlanState * planstate, _Bool use_parallel_mode, CmdType operation, _Bool sendTuples, uint64 numberTuples, ScanDirection direction, DestReceiver * dest, _Bool execute_once) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\execMain.c:1551)
standard_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\execMain.c:361)
citus.so!CitusExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (\home\hanefi\code\citus\src\backend\distributed\executor\multi_executor.c:220)
pg_stat_statements.so!pgss_ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (\home\hanefi\.pgenv\src\postgresql-14.0\contrib\pg_stat_statements\pg_stat_statements.c:1001)
ExecutorRun(QueryDesc * queryDesc, ScanDirection direction, uint64 count, _Bool execute_once) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\execMain.c:303)
_SPI_pquery(QueryDesc * queryDesc, _Bool fire_triggers, uint64 tcount) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\spi.c:2760)
_SPI_execute_plan(SPIPlanPtr plan, ParamListInfo paramLI, Snapshot snapshot, Snapshot crosscheck_snapshot, _Bool read_only, _Bool allow_nonatomic, _Bool fire_triggers, uint64 tcount, DestReceiver * caller_dest, ResourceOwner plan_owner) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\spi.c:2532)
SPI_execute_plan_with_paramlist(SPIPlanPtr plan, ParamListInfo params, _Bool read_only, long tcount) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\executor\spi.c:651)
plpgsql.so!exec_run_select(PLpgSQL_execstate * estate, PLpgSQL_expr * expr, long maxtuples, Portal * portalP) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:5762)
plpgsql.so!exec_stmt_perform(PLpgSQL_execstate * estate, PLpgSQL_stmt_perform * stmt) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:2139)
plpgsql.so!exec_stmts(PLpgSQL_execstate * estate, List * stmts) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:1991)
plpgsql.so!exec_stmt_if(PLpgSQL_execstate * estate, PLpgSQL_stmt_if * stmt) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:2491)
plpgsql.so!exec_stmts(PLpgSQL_execstate * estate, List * stmts) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:2003)
plpgsql.so!exec_for_query(PLpgSQL_execstate * estate, PLpgSQL_stmt_forq * stmt, Portal portal, _Bool prefetch_ok) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:5915)
plpgsql.so!exec_stmt_fors(PLpgSQL_execstate * estate, PLpgSQL_stmt_fors * stmt) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:2796)
plpgsql.so!exec_stmts(PLpgSQL_execstate * estate, List * stmts) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:2023)
plpgsql.so!exec_stmt_block(PLpgSQL_execstate * estate, PLpgSQL_stmt_block * block) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:1910)
plpgsql.so!exec_toplevel_block(PLpgSQL_execstate * estate, PLpgSQL_stmt_block * block) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:1608)
plpgsql.so!plpgsql_exec_event_trigger(PLpgSQL_function * func, EventTriggerData * trigdata) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_exec.c:1182)
plpgsql.so!plpgsql_call_handler(FunctionCallInfo fcinfo) (\home\hanefi\.pgenv\src\postgresql-14.0\src\pl\plpgsql\src\pl_handler.c:272)
fmgr_security_definer(FunctionCallInfo fcinfo) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\utils\fmgr\fmgr.c:746)
EventTriggerInvoke(List * fn_oid_list, EventTriggerData * trigdata) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\commands\event_trigger.c:920)
EventTriggerSQLDrop(Node * parsetree) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\commands\event_trigger.c:791)
ProcessUtilitySlow(ParseState * pstate, PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\utility.c:1906)
standard_ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\utility.c:978)
citus.so!ProcessUtilityInternal(PlannedStmt * pstmt, const char * queryString, ProcessUtilityContext context, ParamListInfo params, struct QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * completionTag) (\home\hanefi\code\citus\src\backend\distributed\commands\utility_hook.c:631)
citus.so!multi_ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, struct QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * completionTag) (\home\hanefi\code\citus\src\backend\distributed\commands\utility_hook.c:288)
citus.so!ColumnarProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, struct QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * completionTag) (\home\hanefi\code\citus\src\backend\columnar\columnar_tableam.c:2133)
pg_stat_statements.so!pgss_ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\contrib\pg_stat_statements\pg_stat_statements.c:1131)
ProcessUtility(PlannedStmt * pstmt, const char * queryString, _Bool readOnlyTree, ProcessUtilityContext context, ParamListInfo params, QueryEnvironment * queryEnv, DestReceiver * dest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\utility.c:523)
PortalRunUtility(Portal portal, PlannedStmt * pstmt, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\pquery.c:1147)
PortalRunMulti(Portal portal, _Bool isTopLevel, _Bool setHoldSnapshot, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\pquery.c:1304)
PortalRun(Portal portal, long count, _Bool isTopLevel, _Bool run_once, DestReceiver * dest, DestReceiver * altdest, QueryCompletion * qc) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\pquery.c:786)
exec_simple_query(const char * query_string) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\postgres.c:1214)
PostgresMain(int argc, char ** argv, const char * dbname, const char * username) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\tcop\postgres.c:4486)
BackendRun(Port * port) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\postmaster\postmaster.c:4506)
BackendStartup(Port * port) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\postmaster\postmaster.c:4228)
ServerLoop() (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\postmaster\postmaster.c:1745)
PostmasterMain(int argc, char ** argv) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\postmaster\postmaster.c:1417)
main(int argc, char ** argv) (\home\hanefi\.pgenv\src\postgresql-14.0\src\backend\main\main.c:209)

[naisila]

What we fail to access is the distribution column and its type, because it is dropped from previous DROP TYPE comp_type CASCADE. The issue can be reproduced more easily (without including schemas) as follows:

SET citus.shard_count TO 4;

CREATE TYPE my_type AS (int_field int);
CREATE TABLE tbl (a my_type);
SELECT create_distributed_table('tbl', 'a');

-- the following should error out but it doesn't
-- it drops the distribution column of the table in the coordinator -- OOPS!
-- doesn't propagate anything to shards
DROP TYPE my_type CASCADE;
NOTICE:  drop cascades to column a of table tbl

-- OOPS no distribution column
\d+ tbl
               Table "public.tbl"
 Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------

-- shards are still there!!
SELECT run_command_on_workers($$ SELECT json_agg(relname) FROM pg_class WHERE relname LIKE 'tbl%'$$);
                     run_command_on_workers
----------------------------------------------------------------
 (localhost,9701,t,"[""tbl"", ""tbl_102008"", ""tbl_102010""]")
 (localhost,9702,t,"[""tbl"", ""tbl_102009"", ""tbl_102011""]")
(2 rows)

SELECT * FROM citus_shards;
ERROR:  cache lookup failed for type 17132

DROP TABLE tbl;
ERROR:  cache lookup failed for type 17132

EDIT: problem already mentioned in https://github.com/citusdata/citus-enterprise/issues/687