Bug: background ddls are not cleaned after cancel followed by recovery

[kwannoel]

Describe the bug

In Drop Stream Jobs, we immediately drop the table fragments.
Whereas in cancel stream jobs, we don't, we only do so once initial barrier for cancel is collected.
So after recovery, the stream job can still be present, if the barrier for cancelling the stream job was not collected.
Perhaps we can solve it by unifying drop and cancel logics.

Error message/log

No response

To Reproduce

No response

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

[kwannoel]

Workaround: Trigger recovery first to shift ownership of stream job to barrier manager, then run the cancel stream job command, and trigger another recovery again to let it take effect.

[kwannoel]

#14471

[github-actions]

This issue has been open for 60 days with no activity.

If you think it is still relevant today, and needs to be done in the near future, you can comment to update the status, or just manually remove the no-issue-activity label.

You can also confidently close this issue as not planned to keep our backlog clean.
Don't worry if you think the issue is still valuable to continue in the future.
It's searchable and can be reopened when it's time. 😄