Every other query fails with syntax error

1

I use CockroachDb and Npgsql driver.

I have a simple Users table. When I insert new record, every other query fails with syntax error, which seems bizarre to me.

CREATE TABLE Users (
    RequestIdentifier BYTEA NOT NULL UNIQUE,
    Identifier UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    Id INT8 NOT NULL DEFAULT unique_rowid(),
    Email BYTEA NOT NULL UNIQUE,
    Username BYTEA NOT NULL,
    PasswordHash BYTEA NOT NULL
);
var q = @"
    INSERT INTO Users (RequestIdentifier, Email, Username, PasswordHash)
    VALUES (@RequestIdentifier, @Email, @Username, @PasswordHash)
    ON CONFLICT (RequestIdentifier)
    DO NOTHING
    RETURNING Identifier
";

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

    await cmd.PrepareAsync();

    cmd.Parameters[0].Value = msg.RequestIdentifier;
    cmd.Parameters[1].Value = msg.Email;
    cmd.Parameters[2].Value = msg.Username;
    cmd.Parameters[3].Value = passwordHash;

    try {
        userIdentifier = ((Guid) await cmd.ExecuteScalarAsync()).ToByteArray();
    } catch (PostgresException e) when (e.SqlState == SqlErrorCodes.UniqueViolation) {
        logger.Information("Email {Email} already in use", UTF8.GetString(msg.Email));
    } catch (PostgresException e) {
        logger.Error("{Exception}", e);
        throw;
    }
}
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 OwlAuthentication.Services.CockroachDbStorageService.CreateOrRetrieveExistingUser(SignUpMessage msg) in C:\Users\Che Khen Kho\Desktop\dotnet\OwlAuthentication\Services\CockroachDbStorageService.cs:line 94
  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

If I try, say, 10 queries, 5 of them would fail with this exception, but quite often more than 5 rows would actually get inserted (sometimes 6, sometimes 8, etc.).

I tested it with PostgreSQL as well (using uuid_generate_v4 instead of gen_random_uuid and BIGSERIAL for Id column), and everything works fine.

npgsql
cockroachdb
asked on Stack Overflow Jan 16, 2020 by CKK • edited Jan 16, 2020 by a_horse_with_no_name

1 Answer

1

I think this is covered by this issue (https://github.com/cockroachdb/cockroach/issues/45490) which is fixed in CockroachDB v20.1

answered on Stack Overflow Dec 8, 2020 by rafiss

User contributions licensed under CC BY-SA 3.0