sql: implement pg_terminate_backend [originally: EnsureDeleted does not work in .NET EFCore]

[balloman]

Trying to use ensure deleted fails with the exception

Npgsql.PostgresException: 42883: unknown function: pg_terminate_backend()
    at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|226_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)

Jira issue: CRDB-23379

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-sessions (found keywords: pg_)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[ecwall]

It looks like this similar issue was closed back in 2019 #35897.

@rafiss I saw you commented on it here #35897 (comment) - do you think we can implement the function by having the server close the connection or have it be a no-op so that there is not a client side error?

[rafiss]

do you think we can implement the function by having the server close the connection

This doesn't seem possible, since the PID is not enough to uniquely identify the connection. It's a 32-bit value, but it can't just be random bits because of the design described in the query cancellation RFC.

or have it be a no-op so that there is not a client side error?

Maybe this is an option. But I'm worried that it could cause confusion if the command completes successfully but doesn't do anything. People may view that as a bug or even a security issue.

---

I think this could be reported to the npgsql/efcore.pg repo. It seems like pg_terminate_backend function is only called in one place, so maybe there is a workaround that can be added if CockroachDB is being used. That project has other CRDB-specific code, so maybe this could be another time to do that.

[dikshant]

This should be addressed as part of #92623