PostgreSQL — Exception while using "AcquireAsync" or "TryAcquireAsync" multiple times

[P4rpleSky]

Hi! I'm trying to make a test console application with advisory locks for PostgreSQL. Here's the code:

using Medallion.Threading.Postgres;

const string connectionString = *my connection string*;

var lockKey = new PostgresAdvisoryLockKey(1337);
var postgresLock = new PostgresDistributedLock(lockKey, connectionString);

try
{
    await using var handle = await postgresLock.TryAcquireAsync(timeout: TimeSpan.FromSeconds(2));
    if (handle is null)
    {
        Console.WriteLine("Cannot get the lock!");
        return;
    }

    handle.HandleLostToken.Register(() =>
    {
        Console.WriteLine("The lock was lost!");
    });

    Console.WriteLine("Start sync...");
    await Task.Delay(TimeSpan.FromSeconds(10));
    Console.WriteLine("End sync...");
}
catch (Exception ex)
{
    throw new InvalidOperationException("Exception while trying to get the lock", ex);
}

It works fine on the first launch. After that, the exception is thrown:

 ---> Npgsql.PostgresException (0x80004005): 42P05: prepared statement "_p1" already exists
   at Npgsql.NpgsqlConnector.<ReadMessage>g__ReadMessageLong|194_0(NpgsqlConnector connector, Boolean async, DataRowLoadingMode dataRowLoadingMode, Boolean readingNotifications, Boolean isReadingPrependedMessage)
   at Npgsql.NpgsqlCommand.<Prepare>g__PrepareLong|74_0(NpgsqlCommand command, Boolean async, NpgsqlConnector connector, CancellationToken cancellationToken)
   at Medallion.Threading.Internal.Data.DatabaseCommand.ExecuteAsync[TResult](Func`3 executeAsync, Func`2 executeSync, CancellationToken cancellationToken, Boolean disallowAsyncCancellation, Boolean isConnectionMonitoringQuery)
in C:\source\DistributedLock-master\src\DistributedLock.Core\Internal\Data\DatabaseCommand.cs:line 81
   at Medallion.Threading.Postgres.PostgresAdvisoryLock.TryAcquireAsync(DatabaseConnection connection, String resourceName, TimeoutValue timeout, CancellationToken cancellationToken) in C:\source\DistributedLock-master\src\Distr
ibutedLock.Postgres\PostgresAdvisoryLock.cs:line 66
   at Medallion.Threading.Postgres.PostgresAdvisoryLock.TryAcquireAsync(DatabaseConnection connection, String resourceName, TimeoutValue timeout, CancellationToken cancellationToken) in C:\source\DistributedLock-master\src\Distr
ibutedLock.Postgres\PostgresAdvisoryLock.cs:line 98
   at Medallion.Threading.Internal.Data.DedicatedConnectionOrTransactionDbDistributedLock.TryAcquireAsync[TLockCookie](TimeoutValue timeout, IDbSynchronizationStrategy`1 strategy, CancellationToken cancellationToken, IDistribute
dSynchronizationHandle contextHandle)
   at Medallion.Threading.Internal.Data.DedicatedConnectionOrTransactionDbDistributedLock.TryAcquireAsync[TLockCookie](TimeoutValue timeout, IDbSynchronizationStrategy`1 strategy, CancellationToken cancellationToken, IDistribute
dSynchronizationHandle contextHandle) in C:\source\DistributedLock-master\src\DistributedLock.Core\Internal\Data\DedicatedConnectionOrTransactionDbDistributedLock.cs:line 94
   at Medallion.Threading.Internal.DistributedLockHelpers.Wrap[THandle](ValueTask`1 handleTask, Func`2 factory) in C:\source\DistributedLock-master\src\DistributedLock.Core\Internal\DistributedLockHelpers.cs:line 38
   at Program.<Main>$(String[] args) in C:\source\DistributedLock-master\src\ConsoleApp1\Program.cs:line 13
  Exception data:
    Severity: ERROR
    SqlState: 42P05
    MessageText: prepared statement "_p1" already exists
    File: prepare.c
    Line: 470
    Routine: StorePreparedStatement
   --- End of inner exception stack trace ---
   at Program.<Main>$(String[] args) in C:\source\DistributedLock-master\src\ConsoleApp1\Program.cs:line 31
   at Program.<Main>(String[] args)

I can fix this problem by running the SQL query deallocate all directly in my DB after every launch. Synchronous methods Acquire and TryAcquire work fine, but their async-analogues fail, as I described above.

Do you have any idea what I can do to avoid this? Thank you in advance for any suggestions.

[madelson]

@P4rpleSky thanks for filing.

What version of Npgsql are you using? Can you try with lower versions and seed if the result is the same?

Also, any chance you’re using pg bouncer? Someone reported a problem related to pg prepared statements here: https://github.com/madelson/DistributedLock/issues/168

[P4rpleSky]

@madelson Thanks for your reply! In the test application above, I didn't use anything other than your package, so I believe the version of Npgsql is the same as in v1.0.4 of your library (v5.0.4).

The issue that you mentioned in your comment helped me find the problem. I asked my colleagues: We have pgbouncer running in transaction mode. He does not allow the use of prepared statements. When I set ShouldPrepareCommands to false in library code, everything works well. We have a lot of high-load services that are not allowing us to turn this setting off. Moreover, I tried to set Max Auto Prepare=0 in my connection string, but this did not work.

Something is still unclear to me: how do synchronous methods work well, but async — not? As I understand it, they are using the same code.

We'll try to solve this problem somehow on our side. I'll keep you in touch if we can do something with it quickly.

[madelson]

Appreciate the follow-up @P4rpleSky ! I’m going to close this issue as a dupe of #168 . Lets continue any future discussion on that issue.

I agree that it is strange that the asynchrony methods work; likely there’s some difference in Npgsql’s implementation.

I would like the library to work seamlessly with pg_bouncer (assuming connection-based locks work with it in general), so I’m open to changing distributedlock to for that purpose.