[Feature] Allow for changing the block cache shard

[trader-payne]

Description

Currently there's no way of managing the shard location of a block cache once it has been deployed. As in, the cache lives in shard A and I want to move it to shard B. The problem is even if they try to delete it, it asks to get rid of all the subgraphs first.

David suggested the following:

In graph-node.toml, change the shard for the block cache (don't restart yet)
In your primary, do something like update chains set name = 'thechain-old' where name = 'thechain'
In the shard where the old block cache lives do update ethereum_networks set name = 'thechain-old' where name = 'thechain'
Restart all graph-node processes
That will make graph-node think that thechain is a new chain and it will set up the cache in the shard you specified in (1)
If you want to get rid of the old block cache, you could then do that with graphman chain remove thechain-old

But unfortunately this doesn't work, as per following logs:

ERROR:  update or delete on table "chains" violates foreign key constraint "deployment_schemas_network_fkey" on table "deployment_schemas"
DETAIL:  Key (name)=(mainnet) is still referenced from table "deployment_schemas".

Are you aware of any blockers that must be resolved before implementing this feature? If so, which? Link to any relevant GitHub issues.

No response

Some information to help us out

• [ ] Tick this box if you plan on implementing this feature yourself.
• [X] I have searched the issue tracker to make sure this issue is not a duplicate.

[stake-machine]

This issue is quite important for our operations, because we can't move our infra to another location without removing all indexed data. Is there any update? Maybe some workaround with temporary disabling foreign keys...