2 identical queries one after the other, second one fails with syntax error

I can’t figure out what’s going on. I use Npgsql driver for C#.

Unhandled exception. Npgsql.PostgresException (0x80004005): 42601: at or near "close": syntax error
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)
   at CockroachDbTest.CockroachDbStorageService.CreateUser(ArraySegment`1 req, ArraySegment`1 email, ArraySegment`1 username, Byte[] passwordHash) in C:\Projects\dotnet\CockroachDbTest\Program.cs:line 101
   at CockroachDbTest.Program.Main(String[] args) in C:\Projects\dotnet\CockroachDbTest\Program.cs:line 165
   at CockroachDbTest.Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: at or near "close": syntax error
    Detail: source SQL:
CLOSE ALL
^
    File: lexer.go
    Line: 175
    Routine: Error

The same code (with couple of syntactic differences) works fine on postgres. I managed to narrow down the erroneous code. Here is a minimal reproducible example:

public class CockroachDbStorageService : IDisposable {
    private readonly NpgsqlConnection dbConn;

    private Task openConnectionTask;

    private static string connectionString;

    static CockroachDbStorageService() {
        var connectionStringBuilder = new NpgsqlConnectionStringBuilder();
        connectionStringBuilder.Host = "localhost";
        connectionStringBuilder.Port = 26257;
        connectionStringBuilder.Database = "owl";
        connectionStringBuilder.Username = "CheKhenKho";
        connectionStringBuilder.SslMode = SslMode.Require;

        connectionString = connectionStringBuilder.ConnectionString;
    }

    public CockroachDbStorageService() {
        dbConn = new NpgsqlConnection(connectionString);

        // code for these 2 functions omitted for brevity
        dbConn.ProvideClientCertificatesCallback += provideClientCertificatesCallback;
        dbConn.UserCertificateValidationCallback += userCertificateValidationCallback;

        openConnectionTask = dbConn.OpenAsync();
    }

    async void IDisposable.Dispose() {
        Console.WriteLine("Dispose");
        await openConnectionTask;
        await dbConn.CloseAsync();
    }

    public async Task Initialize() {
        await openConnectionTask;
        await dropTables();
        await createTables();
    }

    private async Task dropTables() {
        using (var cmd = new NpgsqlCommand("DROP TABLE IF EXISTS Users", dbConn)) {
            await cmd.ExecuteNonQueryAsync();
        }
    }

    private async Task createTables() {
        var query = @"
            CREATE TABLE Users (
                RequestIdentifier BYTEA NOT NULL,
                Identifier UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                Id INT DEFAULT unique_rowid(),
                Email BYTEA NOT NULL UNIQUE,
                Username VARCHAR(30) NOT NULL,
                PasswordHash BYTEA NOT NULL
            )
        ";

        using (var cmd = new NpgsqlCommand(query, dbConn)) {
            await cmd.ExecuteNonQueryAsync();
        }
    }

    public async Task<byte[]> CreateUser(
        ArraySegment<byte> req, ArraySegment<byte> email,
        ArraySegment<byte> username, byte[] passwordHash
    ) {
        await openConnectionTask;

        var query = @"
            INSERT INTO Users (RequestIdentifier, Email, Username, PasswordHash)
            VALUES (@RequestIdentifier, @Email, @Username, @PasswordHash)
            RETURNING Identifier
        ";

        byte[] userIdentifier = null;
        using (var cmd = new NpgsqlCommand(query, dbConn)) {
            cmd.Parameters.Add("RequestIdentifier", NpgsqlDbType.Bytea);
            cmd.Parameters.Add("Email", NpgsqlDbType.Bytea);
            cmd.Parameters.Add("Username", NpgsqlDbType.Varchar);
            cmd.Parameters.Add("PasswordHash", NpgsqlDbType.Bytea);

            await cmd.PrepareAsync();

            cmd.Parameters[0].Value = req;
            cmd.Parameters[1].Value = email;
            cmd.Parameters[2].Value = Encoding.UTF8.GetString(username);
            cmd.Parameters[3].Value = passwordHash;

            userIdentifier = ((Guid)await cmd.ExecuteScalarAsync()).ToByteArray();
        }

        return userIdentifier;
    }
}

class Program {
    public static async Task Main(string[] args) {
        var container = new Container(); // SimpleInjector
        container.Options.DefaultScopedLifestyle = new AsyncScopedLifestyle();
        container.Register<CockroachDbStorageService>(Lifestyle.Scoped);

        using (AsyncScopedLifestyle.BeginScope(container)) {
            var storageService = container.GetInstance<CockroachDbStorageService>();
            await storageService.Initialize();
        }

        using (AsyncScopedLifestyle.BeginScope(container)) {
            var storageService = container.GetInstance<CockroachDbStorageService>();

            var userIdentifier = await storageService.CreateUser(
                req: Guid.NewGuid().ToByteArray(),
                email: Encoding.UTF8.GetBytes($"bye@mail.ru"),
                username: Encoding.UTF8.GetBytes("alala"),
                passwordHash: new byte[] { 1, 2, 3, 4, 5 }
            );
        }

        using (AsyncScopedLifestyle.BeginScope(container)) {
            var storageService = container.GetInstance<CockroachDbStorageService>();

            var userIdentifier = await storageService.CreateUser(
                req: Guid.NewGuid().ToByteArray(),
                email: Encoding.UTF8.GetBytes($"hello@mail.ru"),
                username: Encoding.UTF8.GetBytes("alala"),
                passwordHash: new byte[] { 1, 2, 3, 4, 5 }
            );
        }
    }
}

There are a few strange things. Exception is thrown on the second CreateUser --> ExecuteScalarAsync, but both users gets inserted, despite the error. And here is the output:

Dispose
Dispose
Dispose
Unhandled exception. Npgsql.PostgresException (0x80004005): 42601: at or near "close": syntax error
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteScalar(Boolean async, CancellationToken cancellationToken)
   at CockroachDbTest.CockroachDbStorageService.CreateUser(ArraySegment`1 req, ArraySegment`1 email, ArraySegment`1 username, Byte[] passwordHash) in C:\Projects\dotnet\CockroachDbTest\Program.cs:line 101
   at CockroachDbTest.Program.Main(String[] args) in C:\Projects\dotnet\CockroachDbTest\Program.cs:line 165
   at CockroachDbTest.Program.<Main>(String[] args)
  Exception data:
    Severity: ERROR
    SqlState: 42601
    MessageText: at or near "close": syntax error
    Detail: source SQL:
CLOSE ALL
^
    File: lexer.go
    Line: 175
    Routine: Error

How can it throw an exception after dispose is already called, if we await ExecuteScalarAsync ?

I am not sure whether it is really a cockroachdb issue, but the same code works for postgres.

If I launch multiple inserts and await them all together (Task.WhenAll), everything works. If I await every insert separately - I get an exception. I think I am getting crazy.

So this works:

            Func<int, Task> foo = async (i) => {
                var dbConn1 = new NpgsqlConnection(connectionString);
                dbConn1.ProvideClientCertificatesCallback += provideClientCertificatesCallback;
                dbConn1.UserCertificateValidationCallback += userCertificateValidationCallback;

                await dbConn1.OpenAsync();

                query = @"
                    INSERT INTO Users (Email)
                    VALUES (@Email)
                ";

                using (var cmd = new NpgsqlCommand(query, dbConn1)) {
                    cmd.Parameters.Add("Email", NpgsqlDbType.Bytea);

                    await cmd.PrepareAsync();

                    cmd.Parameters[0].Value = new byte[] {(byte)i, 2, 3, 4, 5};

                    await cmd.ExecuteNonQueryAsync();
                }

                await dbConn1.CloseAsync();
            };

            var tasks = new List<Task>();

            for (int i = 0; i < 20; ++i) {
                tasks.Add(foo(i));
                // await foo(i);
            }

            await Task.WhenAll(tasks);

Doing await foo(i) instead of await Task.WhenAll leads to an exception.

Tried on Ubuntu. Same result, only it does not insert the second record.

If I remove all using’s and do not close/dispose connections manually, everything works. But obviously I can’t do this in production.

Just checked on different machine. Same issue. Both windows.

Even eliminating wrapper class entirely and making all the methods sync does not solve the issue.

var connectionStringBuilder = new NpgsqlConnectionStringBuilder();
connectionStringBuilder.Host = "localhost";
connectionStringBuilder.Port = 26257;
connectionStringBuilder.Database = "owl";
connectionStringBuilder.Username = "CheKhenKho";
connectionStringBuilder.SslMode = SslMode.Require;

var connectionString = connectionStringBuilder.ConnectionString;

using (var dbConn = new NpgsqlConnection(connectionString)) {
    dbConn.ProvideClientCertificatesCallback += provideClientCertificatesCallback;
    dbConn.UserCertificateValidationCallback += userCertificateValidationCallback;

    dbConn.Open();

    using (var cmd = new NpgsqlCommand("DROP TABLE IF EXISTS Users", dbConn)) {
        cmd.ExecuteNonQuery();
    }

    var query = @"
        CREATE TABLE Users (
            Email BYTEA NOT NULL UNIQUE
        )
    ";

    using (var cmd = new NpgsqlCommand(query, dbConn)) {
        cmd.ExecuteNonQuery();
    }
}

using (var dbConn = new NpgsqlConnection(connectionString)) {
    dbConn.ProvideClientCertificatesCallback += provideClientCertificatesCallback;
    dbConn.UserCertificateValidationCallback += userCertificateValidationCallback;

    dbConn.Open();

    var query = @"
        INSERT INTO Users (Email)
        VALUES (@Email)
    ";

    using (var cmd = new NpgsqlCommand(query, dbConn)) {
        cmd.Parameters.Add("Email", NpgsqlDbType.Bytea);

        cmd.Prepare();

        cmd.Parameters[0].Value = new byte[] {1, 1, 1, 1, 1};

        cmd.ExecuteNonQuery();
    }
}

using (var dbConn = new NpgsqlConnection(connectionString)) {
    dbConn.ProvideClientCertificatesCallback += provideClientCertificatesCallback;
    dbConn.UserCertificateValidationCallback += userCertificateValidationCallback;

    dbConn.Open();

    var query = @"
        INSERT INTO Users (Email)
        VALUES (@Email)
    ";

    using (var cmd = new NpgsqlCommand(query, dbConn)) {
        cmd.Parameters.Add("Email", NpgsqlDbType.Bytea);

        cmd.Prepare();

        cmd.Parameters[0].Value = new byte[] {2, 2, 2, 2, 2};

        cmd.ExecuteNonQuery();
    }
}

Both insertions are successful, but last ExecuteNonQuery throws an exception. Why insertion succeeds if an error occurs?

I stand corrected. That was not a minimal reproducible example. It never occurred to me to test the code without the DI container before. Now I tested, and it fails without as well. So no dependencies other than Npgsql driver. I use 3-node cluster on my local machine for testing.

using (var storageService = new CockroachDbStorageService()) {
    await storageService.Initialize();
}

using (var storageService = new CockroachDbStorageService()) {
    var userIdentifier = await storageService.CreateUser(
        req: Guid.NewGuid().ToByteArray(),
        email: Encoding.UTF8.GetBytes($"wtf@mail.ru"),
        username: Encoding.UTF8.GetBytes("alala"),
        passwordHash: new byte[] { 1, 2, 3, 4, 5 }
    );
}

using (var storageService = new CockroachDbStorageService()) {
    var userIdentifier = await storageService.CreateUser(
        req: Guid.NewGuid().ToByteArray(),
        email: Encoding.UTF8.GetBytes($"yeah@mail.ru"),
        username: Encoding.UTF8.GetBytes("alala"),
        passwordHash: new byte[] { 1, 2, 3, 4, 5 }
    );
}

This code produces the same error.

CockroachDB doesn’t support cursors, which means it also doesn’t the CLOSE ALL statement. Is there a way to disable npgsql’s cursor support? That would be the way to move forward here.

yeah, I figured as such. I couldn’t find anything related to cursors in Npgsql.
What I don’t understand is why no one seems to report the same issue. This is like the most basic thing you can do, and seeing as no one else seems to encounter it, this must be a problem on my side. What I also do not understand is why some queries work and some don’t. And why successful execution seems to depend on the relative timing of calling conn.Close for different connections.

Seeing as not closing connections or closing all connections together after all the queries on all the connections are done seemed to work, I thought the issue’s with reusing connection from the pool, and yeah, disabling pooling fixes the issue. But this is also an unacceptable option for us.

I’m not sure, but we’ll try to get a fix for this issue into our next major release, and the maintainer of npgsql filed an issue on our repo to discuss how to mitigate on npgsql’s side, so it sounds like there’s progress there too.

Thanks for reporting this issue. We want to improve support for npgsql over time.