Support for disabling transactions

Nobody has claimed this yet.

* **asyncpg version**: 0.28.0
* **PostgreSQL version**: CrateDB 5.4.2 https://github.com/crate/crate
* **Do you use a PostgreSQL SaaS? If so, which? Can you reproduce
the issue with a local PostgreSQL install?**: Using CrateDB locally
* **Python version**: 3.11.4
* **Platform**: Linux arm64
* **Do you use pgbouncer?**: No
* **Did you install asyncpg with pip?**: Yes
* **If you built asyncpg locally, which version of Cython did you use?**: 0.29.36
* **Can the issue be reproduced under both asyncio and
[uvloop](https://github.com/magicstack/uvloop)?**: Yes

It would be really useful to be able to disable transaction support for Postgres-like database systems that don't support transactions.

CrateDB is an OLAP database with Postgres Wire Protocol support, however it does not have support for transactions. (`BEGIN` is a no-op, but `ROLLBACK` is an undefined term and causes issues)

Currently using asyncpg like this:

```
asyncpg_pool = await asyncpg.create_pool(**CRATE_DB_CONFIG, loop=loop, max_size=10)

async with asyncpg_pool.acquire() as connection:
await connection.execute("""insert into some_table (event_time, field1, field2) values ($1, $2, $3);""", datetime.now(), user_id, session_id)
```

I'm deliberately avoiding any `connection.transaction()` calls yet asyncpg still appears to wrap everything in a transaction block.

Start by tracing the create_pool and pool.acquire usage shown in the issue, then compare it with the connection.transaction behavior. Reproduce the workload against a local CrateDB instance and identify where implicit transaction commands are issued. Done means a supported way exists to disable that behavior so the shown insert does not fail on CrateDB.

Written by the indexing model from the issue text.