Unknown function pg_advisory_unlock_all() cockroachdb

[richardwalenga]

Steps to reproduce

I am running an ASP.NET Core MVC 7 project locally on a Windows 11 machine and for the select I happen to be using Dapper Nuget Package version 2.0.123, although the insert immediately preceding the fetch of the data involves an Npgsql prepared command.

I am testing out a file parsing and import process into a table and after it loads the 26,421 records in my sample file, I have the application redirect me to the index page which as a simple grid to fetch only the first 50 rows of data.

The issue

Once it redirects to the listing, the exception below is raised. This doesn't trigger when I set Pooling=false in my connection string. I initially had a non-zero Max Auto Prepare (I believe it was set to 8) in my connection string but removing it alone didn't help.

Npgsql.PostgresException (0x80004005): 42883: unknown function: pg_advisory_unlock_all(): function undefined
   at Npgsql.Internal.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|233_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
   at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 418
   ...
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: unknown function: pg_advisory_unlock_all(): function undefined
    File: function_name.go
    Line: 29
    Routine: init

Further technical details

Npgsql version: 7.0.2
PostgreSQL version: Using a single node cluster of cockroachdb installed locally using these instructions. When I start it as a single node cluster, I get this version information for the build: CCL v22.2.6 @ 2023/03/03 19:54:51 (go1.19.4)
Operating system: Windows 11

[roji]

Very similar to #4515 - please see the workaround with No Reset On Close=true. Beyond that, it's a good idea to signal this to the CockroachDB team: we generally try to not adjust Npgsql for each variant of PG too much, and I know CockroachDB have a goal of being quite compatible with PostgreSQL, so I think they'll want to simply ignore this.

[vonzshik]

cockroachdb/cockroach#13546 seems to be about implementing pg_advisory_lock.

[richardwalenga]

@roji Understood and thank you so much for the quick workaround suggestion. I can confirm putting it in place no longer triggers the issue.