npgsql: Prepared statement incompatible with NpgsqlDataSource

@BillJones50 the things inside the BatchCommands list are NpgsqlBatchCommands, which have a Parameters property just like NpgsqlCommand. So you can do the following:

await using var batch = new NpgsqlBatch(conn)
{
    BatchCommands =
    {
        new("UPDATE table1 set col1=$1 where col2=$2")
        {
            Parameters =
            {
                new() { Value = 8 },
                new() { Value = "foo"}
            }
        },
        new("SELECT * FROM table2 where col1=$3 and col2=$4")
    }
};

For usage questions such as the above, please open questions on e.g. stackoverflow; github issues are generally more for when you believe you’ve hit a bug, have a feature request, etc.

If your calculations take any time, I’d recommend closing the connection while they’re done; this frees up the connection for use by other threads, rather than keeping it “locked” and idle as the calculations are done.

Otherwise there’s a small perf overhead to closing and then reopening the connection, but Npgsql’s pooling implementation is very optimized, so that’s unlikely to be significant for most applications.

@BillJones50 your questions have nothing to do with this issue - please avoid thread hijacking.

Npgsql document talks about batch command below. My first question is how do we parameterize this?

From our docs on batching:

An NpgsqlBatch simply contains a list of NpgsqlBatchCommands, each of which has a CommandText and a list of parameters (much like an NpgsqlCommand).

So just add parameters to the NpgsqlBatchCommands without the batch, just like you’d do with an NpgsqlCommand.

if first command is UPDATE and second is SELECT, do we still need to specify two resultset?

Since UPDATE doesn’t return a resultset (unless you use the RETURNING clause), no - you don’t call NextResultAsync().

@john2014 your code looks OK. In most applications it makes sense to just turn on auto-prepare rather than using explicit preparation (with PrepareAsync), but other than nothing else to say.

Would it be useful to others if Npgsql docs has a new page called “Best Practices”? […]

We may not have a single snippet containing that, but our getting started page does seem to cover all of this. Note that there’s nothing paricularly high-performance here - it’s just regular Npgsql code; the partial exception to this is PrepareAsync, which once again I wouldn’t necessarily recommend for the general case (as opposed to auto-preparation).

One “bug” I noticed: Some pages in Npgsql docs still shows examples using “sync” instead of “async” […]

There’s nothing inherently wrong with sync - it’s a fully supported option. But you’re right that this point our samples should generally show only async - I’m modifying those code samples in #311.

@john2014 you probably want to give this doc page a good read.

1. Is there a performance difference when using “NpgsqlCommand” instead of “dataSource.CreateCommand” or is it exactly the same?

It’s the same for all practical purposes.

(For completeness, Npgsql has an experimental mode called “multiplexing” which will - in the future - only work with dataSource.CreateCommand, but that’s not really relevant for the normal case.)

1. In our DB connection string if we specify “Max Auto Prepare=100”, then do we still need to specify “await cmd.PrepareAsync();” in each DB call?

No; as explained in the docs, auto preparation and explicit preparation are two different techniques. You can still combine them (i.e. if you want to make sure some SQL is always prepared regardless of how often it’s used), but that’s a niche scenario. Read the docs to understand more about this.

1. If we set “Max AutoPrepare=500”, does that affect performance (example would it use up too much database memory)?

Yes, prepared statements cost e.g. memory resources in PostgreSQL - it’s up to you to benchmark and determine the right number. Also, it’s a bit extreme to have 500 different SQLs in your application - this may be a sign that you’re doing dynamic SQL generation, which is bad for performance.

3b. Where are prepared statements stored? Is it in hard disk or in memory? If memory, would it survive restarting server?

That’s a PostgreSQL question that’s unrelated to Npgsql - I suggest researching that in PostgreSQL resources. But overall they are stored in-memory, and must be re-prepared after server restart.

1. If we do not specify “Max Auto Prepare” in connection string, then does it means Npgsql would NOT automatically prepare any statements

Yes, this is pretty clearly spelled out in the docs.

4b. But if we manually add “await cmd.PrepareAsync();” to each DB call, then it will start preparing these statements?

It will, that’s called explicit preparation.

1. When we add parameters, is it better to also specify NpgsqlDbType for each parameter. Example for “value1” vs “value2” line below, which one would you recommend:

There’s no difference in the common case; NpgsqlDbType is usually useful in cases where the CLR type doesn’t get you the PostgreSQL type you want; for example, a string parameter gets send as PostgreSQL text by default, but you can send it as jsonb by specifying NpgsqlDbType.

Technically there may be a very small performance benefit in specifying NpgsqlDbType, but that’s likely to be negligible for almost all regular scenarios. Benchmark to see the difference rather than just using NpgsqlDbType everywhere.

I’m going to go ahead and close this as the behavior is by-design and all questions have been answered. I suggest reading both Npgsql and PostgreSQL docs to better understand prepared statements (the information is out there). If you still have outstanding questions after that, feel free to post here again and I’ll do my best to help.