[Bug] Subgraph hit error: internal constraint violated: Batches must go forward. Can't append a batch with block pointer...

Bug report

Subgraph failed after hitting this error:
Jul 14 00:13:20.858 ERRO Subgraph failed with non-deterministic error: Failed to transact block operations: internal constraint violated: Batches must go forward. Can't append a batch with block pointer #6265702 (c2d228257112989474262df4754e5bceae3932c36094aa32feb07c69da390797) to one with block pointer #6265702 (c2d228257112989474262df4754e5bceae3932c36094aa32feb07c69da390797), retry_delay_s: 213, attempt: 1, sgd: 365, component: SubgraphInstanceManager

The subgraph started syncing after restarting graph-node.

What caused this error and how can we avoid it in the future?

Some information to help us out

• Tick this box if this bug is caused by a regression found in the latest release.
• Tick this box if this bug is specific to the hosted service.
• I have searched the issue tracker to make sure this issue is not a duplicate.

OS information

Linux

Start with the reported SubgraphInstanceManager failure and the transaction that appends block batches, focusing on why identical block pointers are appended after graph-node restarts. Done means identifying the root cause and documenting a verified way to avoid or recover from the error.