Modifying parameter list doesn't invalidate prepared statements

[roji]

Once an NpgsqlCommand is prepared (explicit or auto), executing it repeatedly with different parameter values skips SQL parsing/rewriting for better perf. If the CommandText is altered at this point, preparation is reset and we parse/rewrite on the next execute.

However, if the parameter list is changed we don't reset, leading to incorrect execution:

[Test]
public async Task Prepare_and_switch_around_parameter_names()
{
    await using var conn = await OpenConnectionAsync();
    await using var cmd = new NpgsqlCommand("SELECT @p1, @p2", conn);
    var x = new NpgsqlParameter("p1", NpgsqlDbType.Integer);
    var y = new NpgsqlParameter("p2", NpgsqlDbType.Integer);
    cmd.Parameters.Add(x);
    cmd.Parameters.Add(y);
    await cmd.PrepareAsync();
    x.Value = 8;
    y.Value = 9;

    await using (var reader = await cmd.ExecuteReaderAsync())
    {
        await reader.ReadAsync();
        Assert.That(reader[0], Is.EqualTo(8));
        Assert.That(reader[1], Is.EqualTo(9));
    }

    x.ParameterName = "p2";
    y.ParameterName = "p1";

    await using (var reader = await cmd.ExecuteReaderAsync())
    {
        await reader.ReadAsync();
        Assert.That(reader[0], Is.EqualTo(9));
        Assert.That(reader[1], Is.EqualTo(8));
    }
}

[lillo42]

I would like to try fix this issue

[roji]

Design decision: this can be solved by disconnecting NpgsqlCommand from its prepared statement after each execution. We don't think this is problematic since reusing the same NpgsqlCommand instance for multiple executions is very rare.

However, we're not even sure that it makes sense to fix this: a user would need to reuse the same command instance and screw up the parameter list in an incompatible way.

[NinoFloris]

Example of a user using different types on the same statement can be found in https://github.com/npgsql/npgsql/issues/2987