sqlproxy: does not take transaction state into account before attempting a session migration

Currently the sqlproxyccl will attempt to transfer connections when they are in a safe state, which is determine by this logic: https://github.com/cockroachdb/cockroach/blob/d926d5eb2621672aa5fe4fed46a1457433e9fc27/pkg/ccl/sqlproxyccl/conn_migration.go#L300. This logic only looks for the ReadyForQuery state, which can happen within a portal.

When we are executing within a portal, not exec statements are allowed causing these transfers to fail at:
https://github.com/cockroachdb/cockroach/blob/b82225e86794124ff914c6dac58cece32dde64c9/pkg/sql/pgwire/command_result.go#L655

This happens because Sync commands within a portal will also send by a ReadyForQuery state: https://github.com/cockroachdb/cockroach/blob/b82225e86794124ff914c6dac58cece32dde64c9/pkg/sql/pgwire/command_result.go#L634

To address this, we should either support observer commands within portals, at the very least SHOW TRANSFER STATE or we should modify the sqlproxyccl to be more precise and exclude cases where we are in middle of a txn.

Jira issue: CRDB-42525