pgwire: python3 py-postgresql driver error

[catroot]

Tried to connect using py-postgresql driver

postgresql.exceptions.ClientCannotConnectError: could not establish connection to server
  CODE: 08001
  LOCATION: CLIENT
CONNECTION: [failed]
  failures[0]:
    NOSSL socket('192.168.8.170', 26257)
    postgresql.exceptions.ProtocolError: expected message of types b'K', but received b'Z' instead
      CODE: 08P01
      LOCATION: CLIENT
CONNECTOR: [IP4] pq://[email protected]:26257
  category: None
DRIVER: postgresql.driver.pq3.Driver

[jordanlewis]

Hi @catroot, thanks for your report. I've been able to reproduce your issue - we'll investigate this soon. In the meantime, psycopg2 is a good bet for a compatible Python postgres driver.

[jordanlewis]

This seems to be a small bug in py-postgresql, which requires a BackendKeyData message be sent during connection initialization. I don't think that this message is strictly required by the Postgres wire protocol format, so I've opened python-postgres/fe#84 for the library author to investigate further.

[jordanlewis]

This will also be fixed by #13009.

[catroot]

Thank you.

[vivekmenezes]

Bilal take a look #13009 for the discussion on how cancellation can fix this issue. Assigning to you. Thanks!

[jordanlewis]

Unfortunately, this isn't going to make it into 2.0 either. In 2.1, we should look at @asubiotto's recommended approach from #13009 - to send a random query id to just unstick problems with the protocol. Cancellation won't work against that query id, but at least drivers like this one will be able to connect.

[knz]

cc @BramGruneir to integrate in the meta lists

[jordanlewis]

Hey @rafiss, could we fix this one by issuing a BackendKeyData response with obviously wrong values like 0, 0? Or will that cause issues for clients? I feel like it could be fine - clients already receive an error when trying to cancel a query using the Postgres cancellation protocol, so if they tried to use our obviously false BackendKeyData after such a PR no behavior would really change...

[rafiss]

@jordanlewis Sorry I missed your message in October! I think we could use an approach like that. Looks like that's what #13009 did, but it didn't reach the finish line. I don't quite understand why not, but I am in favor of bringing it back for the 21.1 release.