Recursive CTE task may fail when its temporary staging table is cleaned up prematurely

Summary

TemporaryTableCleaner determines temporary-table liveness from frontend client sessions only. Scheduler TaskRuns own an internal ConnectContext and session ID but are not frontend client sessions, so the cleaner can incorrectly remove a temporary table while its task is still running.

This was observed when a recursive CTE task's generated staging table was removed while a CN was finishing its anchor insert. Including session IDs from active asynchronous and synchronous task runs in the cleaner's liveness set should fix the issue.

I am investigating and validating a fix. You can assign this to me.

Steps to reproduce the behavior (Required)

The issue is timing-sensitive. TemporaryTableCleaner inherits the fixed 30-second Daemon interval, so the recursive task must remain active for more than 30 seconds after its staging table is created. A small parent/child fixture alone will normally finish too quickly and is not a reliable reproducer. Paimon was the workload that exposed the race; it is not required as a target.

1. Create or select a Paimon source table with id and parent_id columns. Use enough data, deliberately slow storage, or an equivalent workload to make the recursive task run for more than 30 seconds. Include at least one valid parent/child relationship if recursive output is desired. For example, use any standard Paimon writer to populate:

   复制

   PAIMON_CATALOG.PAIMON_DB.recursive_source(id BIGINT, parent_id BIGINT)

2. In an internal StarRocks database, create a native target table:

   sql复制

   USE default_catalog.TEST_DB;
   
   DROP TASK IF EXISTS recursive_paimon_task;
   DROP TABLE IF EXISTS recursive_paimon_target;
   
   CREATE TABLE recursive_paimon_target (
       id BIGINT,
       parent_id BIGINT,
       depth INT
   )
   DUPLICATE KEY (id)
   DISTRIBUTED BY HASH(id) BUCKETS 1
   PROPERTIES ("replication_num" = "1");

3. Submit a recursive task. Replace the Paimon catalog/database/table and ROOT_ID with valid values. Ensure the anchor/recursive workload remains active for more than 30 seconds so it crosses at least one cleaner pass.

   sql复制

   SUBMIT TASK recursive_paimon_task
   PROPERTIES (
       "session.enable_recursive_cte" = "true",
       "session.recursive_cte_max_depth" = "25"
   )
   AS
   INSERT OVERWRITE recursive_paimon_target
   WITH RECURSIVE hierarchy (id, parent_id, depth) AS (
       SELECT id, parent_id, 0 AS depth
       FROM PAIMON_CATALOG.PAIMON_DB.recursive_source
       WHERE id = ROOT_ID
   
       UNION ALL
   
       SELECT child.id, child.parent_id, parent.depth + 1
       FROM PAIMON_CATALOG.PAIMON_DB.recursive_source AS child
       JOIN hierarchy AS parent
           ON child.parent_id = parent.id
       WHERE parent.depth < 20
   )
   SELECT id, parent_id, depth
   FROM hierarchy;

4. Inspect the task result:

   sql复制

   SELECT task_name, state, error_code, error_message, create_time, finish_time
   FROM information_schema.task_runs
   WHERE task_name = 'recursive_paimon_task'
   ORDER BY create_time DESC;

5. On a compute node, inspect logs for the failed query. In the observed failure, the CN receives the staging-table shard first, then cannot retrieve its schema while completing the sink write.

Expected behavior (Required)

A temporary table owned by an active TaskRun must remain available until the task completes or explicitly drops it. The recursive task should successfully read Paimon data and write its result to the native target.

Real behavior (Required)

A long-running recursive task could fail in the recursive CTE start statement:

Error occurred during executing recursive CTE start statement:
<compute-node-host>: table not found, may be dropped,
db id: 16061, table id: 52300

CN logs show that the temporary staging table existed at first and was later absent while the CN was still finishing its write:

05:10:14  Add shard id: 52305
           file path: .../db16061/52300/52302

05:10:32  table_schema_service.cpp:331 failed to get schema
           db_id: 16061, table_id: 52300, schema_id: 52301,
           tablet_id: 52305, txn_id: 12920
           error: Table not exist: table not found, may be dropped

05:10:32  async_delta_writer.cpp:244 Fail to finish write
05:10:32  lake_tablets_channel.cpp:613 Fail to finish tablet 52305

The canceled pipeline was:

connector_scan -> chunk_accumulate -> project -> olap_table_sink

StarRocks version (Required)

• StarRocks version: 4.1.1
• Deployment mode: shared-data