Errors parsing DSNs when using both psycopg2 and psycopg

[yolken-rippling]

Summary of problem

We're getting exceptions when trying to connect to postgres DBs with psycopg in our code with ddtrace enabled.

From looking at the code, I think the problem is in https://github.com/DataDog/dd-trace-py/blob/main/ddtrace/ext/sql.py. If psycopg2 is imported after psycopg, then parse_pg_dsn is set to the psycopg2 implementation. That implementation, however, can't handle newer DSN parameters like sslcertmode and crashes when a DB connection is attempted with psycopg.

One possible fix would be to have separate versions of parse_pg_dsn for the two versions of psycopg, and have callers use the correct one based on the version of the connection they're using. I've attempted to implement this in
#9415.

Which version of dd-trace-py are you using?

1.20.18

Which version of pip are you using?

23.2.1

Which libraries and their versions are you using?

The relevant dependencies are:

ddtrace==1.20.18
psycopg==3.1.19
psycopg-binary==3.1.19
psycopg-pool==3.2.0
psycopg2==2.9.9
psycopg2-binary==2.9.9

How can we reproduce your problem?

1. Create a docker-compose file that has python and postgres:

---
version: '2'
services:
  postgres:
    image: postgres:16
    environment:
      POSTGRES_PASSWORD: test

  python:
    image: python:3.11
    command:
      - sleep
      - infinity

2. Start docker-compose: docker-compose up -d
3. Exec into the python container: docker-compose exec python /bin/bash
4. Install the dependencies: pip install psycopg2==2.9.9 psycopg[binary]==3.1.19 ddtrace==1.20.18
5. Run python3, then the following commands in the repl:

from ddtrace import patch_all
patch_all()
import psycopg
import psycopg2
with psycopg.connect("dbname=postgres user=postgres password=test host=postgres port=5432") as conn:
    pass

What is the result that you get?

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.11/site-packages/ddtrace/contrib/psycopg/connection.py", line 107, in patched_connect
    return patch_conn(conn, pin=pin, traced_conn_cls=traced_conn_cls)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/ddtrace/contrib/psycopg/connection.py", line 71, in patch_conn
    dsn = sql.parse_pg_dsn(conn.info.dsn)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.ProgrammingError: invalid dsn: invalid connection option "sslcertmode"

What is the result that you expected?

No error.