[Bug]: CN insert and multi_update memory-pressure S3 flushes orphan objects when a later error never registers them on the transaction #29257

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

main

Commit ID

66da387

Other Environment Information

• Found by reading origin/main at the commit above. Not reproduced with a live SQL session.
• Hardware parameters: code inspection
• OS type: n/a
• Others: CN insert / multi_update memory-pressure flush versus the TN sinker cleanup contract

Actual Behavior

A CN INSERT or multi_update can upload object-storage objects during a memory-pressure flush and then leak them when a later error aborts the statement. The statement itself fails. The objects are never registered on the transaction, and nothing deletes them.

Sinker.Write → pushStaged calls trySpill once inMemorySize >= memorySizeThreshold (pkg/objectio/ioutil/sinker.go). CN writers set that threshold from WriteS3Threshold (128 * mpool.MB, pkg/sql/colexec/s3util.go). A successful syncFileSinker appends the object stats to staged.persisted.

Sinker.Close then drops that ownership without deleting:

sinker.staged.persisted = nil
sinker.staged.unpublished = nil

Close does not call DeletePersisted. DeletePersisted is the owner that deletes staged.unpublished, staged.persisted, result.persisted, and pipeline persisted/unpublished names. Its production callers are TN checkpoint (pkg/vm/engine/tae/logtail/ckp_writer.go), publication (pkg/publication/filter_object.go), and transferred tombstone (pkg/vm/engine/tae/txn/txnimpl/transferred_tombstone_sink.go). CN insert and multi_update are not among them.

syncFileSinker records staged.unpublished only for the active writer name when the current Sync fails. Objects already appended to staged.persisted by an earlier successful spill are not in that list. Close discards the unpublished list too, so even the commit-ambiguous current file is not deleted on the CN path. RecordUnpublishedObjectCleanup (restart-safe marker before the object exists) is only called from TN GC PrepareUnpublishedObject (pkg/vm/engine/tae/db/gc/v3/checkpoint.go). The CN BlockWriter / FSinker path does not record it.

Two CN call sites make the window worse by detaching stats before the caller has stored them:

1. CNS3Writer.SyncAndFillBlockInfoBat calls SyncAndTakeResults first. That nils result.persisted and returns the stats. If ExpandObjectStatsToBatch then fails, the function returns the error and the stats are not returned to the caller (pkg/sql/colexec/s3util.go). Insert.flushS3WriterOnMemoryPressure returns that error (pkg/sql/colexec/insert/insert.go). Insert.Reset / Insert.Free call s3Writer.Close(). The objects are in neither the sinker nor txn.writes.
2. The same flush returns if ctr.buf.Append fails after a successful SyncAndFillBlockInfoBat. The block-info batch is local. It is not appended to ctr.buf, so flushTail never hands those objects to WriteFile.

multi_update has the same class of leak. sortAndSyncOneTable creates a local CNS3Writer and defer s3Writer.Close() (pkg/sql/colexec/multi_update/s3writer_delegate.go). Write can trySpill into staged.persisted. A later Write, Sync, or FillBlockInfoBat error returns before fillDeleteBlockInfo / fillInsertBlockInfo. Close nils the names. flushTailAndWriteToOutput also Syncs persistent insert sinkers; a later table's error returns before those stats are emitted to the output batch, and s3WriterDelegate.reset calls CNS3Writer.Reset, not DeletePersisted.

Rollback cannot see these objects. Transaction.gcObjsByIdxRange (pkg/vm/engine/disttae/types.go) only walks txn.writes entries that already have fileName and an object-stats batch. Objects that never reached WriteFile are not in that slice. GC Verify (pkg/vm/engine/tae/db/gc/v3/check.go) lists filesystem objects missing from checkpoints and the catalog window, logs them as not found, and returns a diagnostic string. It does not delete them. Ordinary GC only removes objects that entered catalog metadata and aged out.

Expected Behavior

If a CN insert or multi_update uploads an object and a later error prevents that object from being registered on the transaction, the CN owner must delete it before dropping the reference. That is the same contract DeletePersisted already implements for TN checkpoint, publication, and transferred tombstone. SyncAndTakeResults must not drop sinker ownership until the caller has either registered the stats or deleted them. A failed statement must not leave permanent objects that no catalog entry and no rollback GC will ever name.

Steps to Reproduce

Code path on main @ 66da3877e9. No live cluster run.

1. INSERT enough rows through Insert.insert_s3 that Sinker.pushStaged crosses WriteS3Threshold (128MiB) and trySpill → syncFileSinker appends stats to staged.persisted.
2. Fail the statement after that upload and before WriteFile: cancel the context, fail a later Write, or fail ctr.buf.Append inside flushS3WriterOnMemoryPressure after SyncAndFillBlockInfoBat has already called SyncAndTakeResults.
3. Observe Insert.Free → CNS3Writer.Close → Sinker.Close assign staged.persisted = nil and staged.unpublished = nil with no fs.Delete.
4. For UPDATE/DELETE via MultiUpdate.update_s3, repeat with sortAndSyncOneTable: one table's Write spills, a later Sync or a later table's Write returns an error, and defer s3Writer.Close() drops staged.persisted.
5. List the data filesystem after the transaction rolls back. The spilled object name is still present. gcObjsByIdxRange has no txn.writes entry for it.

Additional information

• This is not #25204. That issue tracks workspaceSize / counter accounting (D1–D3, repair landed as #26581) and D4: an async fs.Delete failure that is only logged for objects already on txn.writes. The objects in this report never reach WriteFile, so D4's delete path never observes them. #25204 is deferred as a GC-reliability design and does not ask CN insert / multi_update to call DeletePersisted.
• This is not #27627. These paths return moerr.NewInternalErrorf or the sinker Write/Sync error. They do not surface *ExecutionResourceError / MySQL 20101.
• The SQL statement is fail-closed. The bug is the surviving object, not a partial committed insert.
• syncFileSinker on a failed Sync records only ActiveObjectName() of the file that just failed. Earlier successful spills in staged.persisted are a separate, already-uploaded set. Both sets are dropped by Close.