Cockroachdb misses support for: 'UNLISTEN *' 'DISCARD SEQUENCES' 'DISCARD TEMP'

[Meai]

Describe the problem

Prepared statements with npqsql don't work.

To Reproduce

Try an sql insert with prepared parameters.

Expected behavior
No errors

Additional data / screenshots

The following sql features are used here:
https://github.com/npgsql/npgsql/blob/001c5ef35cf29ec9109e9a8d2ae54108a5d7ac9e/src/Npgsql/Internal/NpgsqlConnector.cs#L2061

Similar issue was already reported and fixed here but I don't know why that user didn't immediately have a problem again with the next sql statements in that method. #75435

1. UNLISTEN *

2. DISCARD SEQUENCES Npgsql.PostgresException (0x80004005): 0A000: at or near "sequences": syntax error: unimplemented: this syntax

3. DISCARD TEMP Npgsql.PostgresException (0x80004005): 0A000: at or near "temp": syntax error: unimplemented: this syntax

Environment:

• Npgsql version: current trunk commit npgsql/npgsql@0d8941a
• Cockroachdb version: v22.1.1
• Operating system: Opensuse Leap 15.3

Additional context
can't use cockroachdb unless I accept not having connections properly reset as described here: npgsql/npgsql#4515

Jira issue: CRDB-16832
Epic CRDB-14049

[blathers-crl]

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

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

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

• @rafiss (assigned to Add RESET ALL (errors when using C# npgsql) #75435)
• @cockroachdb/sql-experience (found keywords: sql statement)

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.}

[roji]

For context... When a connection is returned to the pool, Npgsql resets it to prevent state leak. Usually this is done by sending a DISCARD ALL, and that seems to work with Cockroach.

However, if any prepared statements are in use, we want to persist them across pooled connections, to speed up short-lived connection scenarios (e.g. web app doing the same SQLs, but only once per pooled connection). DISCARD ALL deallocates all prepared statements, so we can't do that; instead we send the commands that DISCARD ALL is made up of, separately - except DEALLOCATE ALL (see the DISCARD ALL docs).

I suspect in Cockroach at least some of these are irrelevant, so you could simply ignore them. Let me know if anything is unclear or you need more info.

[roji]

And until this is fixed on the Cockroach side, this can be worked around by specifying No Reset On Close=true on the connection string - but that means state can leak across pooled connections.

[rafiss]

Thanks for this report! We should be able to implement the two DISCARD statements.

We don't support LISTEN/NOTIFY, but we should be able to implement UNLISTEN by making at a no-op that shows a NOTICE that CockroachDB does not implement this feature.