asked on

Npgsql error - 08P01: invalid message format - c# bulk insert into postgres 11

Hi, I am trying to bulk insert data into a postgres 11 table with a C# .net core console app.

I have a string builder which prepares the insert data statement for 20,000 records
It then tries to run the following code:

using (NpgsqlConnection con = new NpgsqlConnection(DbConnectionString))
{
	con.Open();
	NpgsqlCommand cmd;
	NpgsqlTransaction transactSql = con.BeginTransaction();

	cmd = new NpgsqlCommand(sb.ToString(), con, transactSql)
	{
		CommandType = CommandType.Text
	};

	cmd.ExecuteNonQuery();
}

Open in new window

However I get the following error

08P01: invalid message format

   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- 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 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 1178
   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
   at DbService.Program.<>c__DisplayClass25_0.<BulkInsertDataToDatabase>b__0() in C:\Source\test\Program.cs:line 417
   at DbService.Program.<>c__DisplayClass25_0.<BulkInsertDataToDatabase>b__0() in C:\Source\test\Program.cs:line 417

Open in new window

If I take the the stringbuilder data and post into navicat, it inserts fine
I've read something online about this, but for reading data, whereas i'm writing data

EDIT: it appears to work fine with smaller sample data without long strings

David Favor

To debug this is fairly simple.

Just build the entire transaction as a text string, then execute the string. If an exception is raised, then dump the entire string to issue manually for testing.

If string length turns out to be the problem, you may require changing bindings... In other words, whatever code provides the blue between C# + PostgreSQL will be replaced.

Said another way, the problem is either your SQL formatting or the library binding.

SQL formatting, you can fix.

Library Binding code, you can't normally fix.

websss

ASKER

thanks
I have the sql statement, it runs fine in navicat

ASKER CERTIFIED SOLUTION

websss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial