[Access] Improve SendAndSubscribe status handling

[peterargue]

Problem Definition

Currently, the SendAndSubscribeTransactionStatuses endpoint will first return a message that the tx is pending, then finalized, executed and sealed all at once.

2024-10-16 07:19:48.442 INF transaction PENDING successfully
2024-10-16 07:19:51.646 INF transaction FINALIZED successfully
2024-10-16 07:19:51.646 INF transaction EXECUTED successfully
2024-10-16 07:19:51.647 INF transaction SEALED successfully

sometimes, finalized is returned earlier, but executed and sealed are returned at once.

2024-10-16 07:21:09.504 INF transaction PENDING successfully
2024-10-16 07:21:13.288 INF transaction FINALIZED successfully
2024-10-16 07:21:16.251 INF transaction EXECUTED successfully
2024-10-16 07:21:16.251 INF transaction SEALED successfully

The goal of this endpoint is to provide feedback to the user quickly as the changes are happening, so waiting until the block seals to return the TransactionResult breaks that.

I'm not totally sure why, but I bet it's related to this check

flow-go/engine/access/rpc/backend/backend_stream_transactions.go

Lines 257 to 263 in f6814f8

	_, err := b.executionResults.ByBlockID(txInfo.BlockID)
	if err != nil {
	if errors.Is(err, storage.ErrNotFound) {
	return nil, nil
	}
	return nil, fmt.Errorf("failed to get execution result for block %s: %w", txInfo.BlockID, err)
	}

That doesn't explain why it sometimes returns finalized at the same time as executed and sealed. it's possible that's because the node took a long time to get the collection from the collection nodes, but we should review the code closely to identify if there are any other sources of delays.