Add function to allow batch inserts without transaction

[kaeedo]

Hello.

Currently, to do a batch insert you would use the Sql.executeTransactionAsync with a list of queries and parameters. However, this function starts and commits a transaction implicitly. Would it be possible to get a function that doesn't implicitly create a transaction, allowing for separate control similar to this section in the readme. So essentially manually starting a transaction, then executing a list of queries with a single trip to the DB, and then manually committing/rolling back the transaction.

Thanks

[Zaid-Ajaj]

Hi @kaeedo I am a bit confused because what you describe is exactly what this section in the readme does. So I am not sure what kind of API you expect to use from the library

[kaeedo]

The way I understood that section is that there will be 10 separate trips to to database, instead of a single network request with 10 insert statements. Looking at the source code, executeNonQuery runs the makeCommand function once, executes it, and then disposes the connection. So if you were to run that in a for loop, it would be a single network request for each loop

Or am i completely misunderstanding how it works?

[Zaid-Ajaj]

@kaeedo I believe you are right. There will be a trip to the database on each query. However, that is not the problem that transactions solve. If you want a single query to the database, consider using a lower-level query with

BEGIN TRANSACTION ... COMMIT;

That said, if there is a way to implement this from Npgsql itself, we can see if a F# API can be implemented for it from this library.

[kaeedo]

@Zaid-Ajaj Sorry, maybe i should've explained my use case first. At my job, we're making use of executeTransactionAsync to insert many items into the DB. However, now we have the need to manually either commit or rollback the transaction based on some other things happening shortly before or after the call to insert the items.

We can manually start a transaction, but then we can't call executeTransactionAsync because it also starts a transaction internally, and postgres doesn't support nested transactions (only savepoints)

What I propose is a new function executeManyAsync (or similar name) that takes a transaction as a parameter and hands that to use batch = new NpgsqlBatch(connection, transaction) instead of creating a transaction internally.

I'm willing to contribute a pull request if you're happy with this addition

[pihai]

I agree with @kaeedo that the ability to execute commands in batch without implicitly opening a transaction would be a great addition.

We use the Unit of work pattern to manage our transactions. The repositories use Npgsql.FSharp to execute SQL commands. Currently we cannot use executeTransactionAsync to run multiple commands in batch because there is already a transaction open in the unit of work.

Even though our use case is to insert multiple rows in a batch, such API could also be used to execute multiple queries and read the results back. See the example here: https://www.npgsql.org/doc/performance.html#batchingpipelining