Error "Npgsql.PostgresException : 26000: prepared statement "_p7" does not exist" when under high load

[madelson]

Steps to reproduce

I see the error (full output below) fairly regularly when running the Postgres test suite for https://github.com/madelson/DistributedLock/tree/release-2.0 after upgrading that branch's code to reference Npgsql 5.0.1.1 instead of 4.1.4. If you'd like to try and repro through running this suite, I'd be happy to assist with any setup questions.

The test that typically sees this error is one that kicks off 100 async tasks which each do (a) openasync new connection, (b) acquire advisory lock (explicit prepareasync followed by executeasync), (c) await Task.Delay for 1ms, (d) release the advisory lock (c) dispose the connection.

I've had trouble isolating a small reproduction in part because the tests in question currently fail only when running the full suite, which puts even more load on the database with different things running in parallel (and might be affecting the connection pool in various ways).

The issue

This code works fine under 4.1.4, but fails with 5.0.1.1. I'm surprised to see the error in question because of the auto-preparation feature (and especially because I'm explicitly calling PrepareAsync() beforehand).

 TestParallelism
   Source: DistributedLockCoreTestCases.cs line 164
   Duration: 1.7 sec

  Message: 
    System.AggregateException : One or more errors occurred. (26000: prepared statement "_p7" does not exist)
      ----> Npgsql.PostgresException : 26000: prepared statement "_p7" does not exist
    Data:
      Severity: ERROR
      InvariantSeverity: ERROR
      SqlState: 26000
      MessageText: prepared statement "_p7" does not exist
      File: d:\pginstaller_12.auto\postgres.windows-x64\src\backend\commands\prepare.c
      Line: 505
      Routine: FetchPreparedStatement
    
  Stack Trace: 
    Task.WaitAllCore(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
    Task.WaitAll(Task[] tasks, TimeSpan timeout)
    DistributedLockCoreTestCases`2.TestParallelism() line 194
    --PostgresException
    NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
    NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming, CancellationToken cancellationToken)
    NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
    NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
    NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)

Further technical details

Npgsql version: 5.0.1.1 (but not 4.1.4)
PostgreSQL version: 12
Operating system: Windows 10

[vonzshik]

Yep, there is a bug, if you attempt to call PrepareAsync with an already cancelled token. Submitted a pr to fix this.

[madelson]

I really appreciate the quick fix @vonzshik ! I'll be on the lookout for the new version.