Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Prepared statement does not exist #2665

Open
inelisoni opened this issue Oct 2, 2019 · 0 comments

Comments

@inelisoni
Copy link

commented Oct 2, 2019

Steps to reproduce

string connectionString = GetConnectionString();

try
{
    Insert(connectionString);
}
catch (PostgresException)
{
    // Table does not exist, it's expected
}

// Create table
{
    using var connection = new NpgsqlConnection(connectionString);
    connection.Open();
    const string query = "create table test_table(id integer)";
    using var command = new NpgsqlCommand(query, connection);
    command.ExecuteNonQuery();
}

// Retry insert
try
{
    Insert(connectionString);
}
catch (PostgresException)
{
    // prepared statement does not exist, unexpected behaviour
}

static string GetConnectionString()
{
    return new NpgsqlConnectionStringBuilder
        {
            Host = "localhost",
            Port = 5434,
            Username = "postgres",
            Password = "postgres",
            Database = "postgres",
            Pooling = true,
            MinPoolSize = 1,
            MaxPoolSize = 1,
        }.
        ConnectionString;
}

static void Insert(string connectionString)
{
    using var connection = new NpgsqlConnection(connectionString);
    connection.Open();
    
    const string query = "insert into test_table(id) values (@id)";
    using var command = new NpgsqlCommand(query, connection);
    var param = new NpgsqlParameter<int>("id", 1);
    command.Parameters.Add(param);
    command.Prepare();
    command.ExecuteNonQuery();
}

The issue

Cannot insert into the table if the previous insert failed on the same connection.
Workaround is to add UnprepareAll before connection disposing.

prepared statement "_p1" does not exist
   
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 953
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 973
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 416
   at Npgsql.NpgsqlDataReader.NextResult() in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 298
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1177
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken) in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 977
   at Npgsql.NpgsqlCommand.ExecuteNonQuery() in C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 959

Further technical details

Npgsql version: 4.1.1
PostgreSQL version: 11
Operating system: Windows 10 1903 x64

@roji roji added the bug label Oct 14, 2019
@roji roji self-assigned this Oct 14, 2019
@roji roji added this to the 4.1.2 milestone Oct 14, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.