pgx failover support without database/sql

### Describe the feature

It seems this wrapper was designed to be used with database/sql only and not when using pgx directly, which is often required for advanced Postgres features. Any ideas/plans on how to leverage fast failover detection when using pgx/v5 without database/sql ?

### Use Case

We often can't use database/sql. It is also more performant to use pgx directly instead of via database/sql.

### Proposed Solution

_No response_

### Other Information

_No response_

### Acknowledgements

- [ ] I may be able to implement this feature request
- [ ] This feature might incur a breaking change

### The AWS Advanced Go Wrapper version used

2025-07-31

### Go version used

1.24

### Operating System and version

Linux

Start by reviewing how the wrapper currently detects failover through database/sql and how direct pgx/v5 connections are handled. Identify the integration boundary and the behavior required for direct pgx use; done means fast failover detection works without database/sql and is covered by appropriate tests.

Written by the indexing model from the issue text.