connection is already in state 'Executing' when inserting lots of objects [Using Dapper] #1776
Comments
|
So after some more debugging. I noticed the CommandInProgress property of the exception is null. Not sure if that means anything.
I also turned on logging for npgsql and have this. It starts with and ends with an abrupt rollback |
|
For anyone wondering, I switched to using postgresql_bulk_insert for the larger inserts. |
|
Sorry for taking so long to answer with this. It seems that postgresql_bulk_insert is a simple wrapper for the Npgsql COPY API, which you can also call directly. But I'm interested in understanding the original problem. Is there any way you can submit a small repro with Dapper that triggers the exception? That would allow me to investigate. |
|
Closing as no further information has been provided. |
|
I have faced the same issue. CREATE TABLE public.test_table1
(
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
name citext COLLATE pg_catalog."default",
CONSTRAINT test_table1_pkey PRIMARY KEY (id)
)Sample program: using Dapper;
using System;
using System.Transactions;
namespace ConsoleAppPostgre
{
class Program
{
static void Main(string[] args)
{
using var scope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(0));
using var cn = new Npgsql.NpgsqlConnection("Host=10.67.1.238;Database=erc_households_test5;Username=zhoec_app;Password=2020");
using var cm = cn.CreateCommand();
cn.Open();
Console.Write($"Processed records: ");
var cursorTop = Console.CursorTop;
for (int i = 0; i<1_000_000; i++ )
{
cn.Execute(@"INSERT INTO test_table1(name) VALUES (@name);", new { name = Guid.NewGuid().ToString() });
Console.Write(i);
Console.SetCursorPosition(19, cursorTop);
}
scope.Complete();
Console.WriteLine("\nAll done.");
}
}
}I get Npgsql.NpgsqlOperationInProgressException: 'The connection is already in state 'Executing'' after inserting ~400k records. In real project schema is more complex and exception occures after inserting ~100k records .net core 3.1 |
|
Was not able to replicate the issue. Could you provide the full stack trace? |
|
|
|
Okay, could you enable logs in Npgsql and paste them here? There should be a started action which has no corresponding end message: npgsql/src/Npgsql/NpgsqlConnector.cs Line 1556 in 68df5ae npgsql/src/Npgsql/NpgsqlConnector.cs Line 1588 in 68df5ae If you can patch the driver a bit first and output |
|
I've got another exception after turning on logging with trace level. Connection in use while trying to rollback, will cancel and retry (localid=6f4e39df-6b9d-494d-9e79-23d4ad6f1fe7:1 Socket connected to 10.67.1.238:5432 Cleaning up reader Exception during transaction rollback (localid=6f4e39df-6b9d-494d-9e79-23d4ad6f1fe7:1) In VS: |
|
Hello, I've got the same problem. When using Nuget version I've got a Dictionary index out of bound kind of message from Npgsql. Forked and integrated the last version of Npgsql in my project, and now seeing this message when inserting in a loop. Will try to get more information for you about this. |
|
@rudi-bruchez Do you have the problem in case if parallel execution or not? If you're able to reproduce the problem with using one thread or at least their count is less than PostgreSQL allows (see the settings), then I can take another look and fix it. With the example posted by @YVishnevsky am not able to reproduce it anymore (got it a few times only and detected some area, but it's still very large to understand the root). |
|
I'm pretty sure I can reproduce it. I'm in a synchronous call but in a transaction (TransactionScope), looping individual inserts, it is my first test, I want to batch inserts afterwards or using COPY. But when I'm throwing individual inserts at PG, after about 400 inserts I get the problem. If I'm running 100 I'm OK. Will try to get you specific info. |
|
Well, post the code and I will investigate. It would be better to avoid dapper if possible. |
|
I'm not using Dapper, plain npgsql calls. Sorry to mix subjects here, but I'm confused about thransactions : I'm using TransactionScope betwen SQL Server DELETE and PG INSERT, and it seems the transaction is getting rolled back. Does it try to create a distributed transaction and should I use enlist=false and manage the PG transaction manually? My code for now is very straightforward: try
{
using var conn = new NpgsqlConnection(connString);
conn.Open();
using var cmd = new NpgsqlCommand
{
Connection = conn
};
var i = 1;
foreach (var insert in inserts)
{
cmd.CommandText = insert;
cmd.ExecuteNonQuery(); // TODO Async
Log.Information($"row {i} inserted");
i++;
}
}
catch (Exception e)
{
Log.Error(e.Message);
throw;
}inserts is a List with insert statements in it. Nothing else, except it is enlisted in the TransactionScope |
|
What is the query and schema? |
|
Let me anonymize it, I'll send you some code to repro |
|
@rudi-bruchez, any chance to post the repro? |
|
Closing since no repro was posted back. |
I submitted a report on the dapper repo for this issue as well. DapperLib/Dapper#919
Steps to reproduce
Essentially, I have a bit of code that inserts rows into 2 tables. The second table has a Foreign Key to the first.
The issue
The code works find and we haven't had any issues.
I noticed however that an exception is thrown when the number of rows being inserted into both tables is more than approximately 20k total. In my case, I had about 11244 items in each list.
I made sure all calls are awaited so I do not get promoted to a distributed transaction since this is net core 2.0.
I run tests where I split the rows into batches of 5000 in an attempt to test. The exception gets thrown at around the 3rd or 4th iteration.
I am totally stumped here. Any pointers?
Further technical details
Npgsql version: 3.2.6
PostgreSQL version: 9.6
Operating system: Windows 10
Other details about my project setup:
Net Core 2.0
The text was updated successfully, but these errors were encountered: