0

I have a .NET 6 C# console app connected to Denodo using Npgsql.

The main task is just to pull data with SELECT using different queries. Now I'm facing an error with one of the largest queries with 29K lines (it's big because it has some mapping to manage historic data).

The error that I get is:

Npgsql.PostgresException: Invalid data received. Length: 1816347. Max length of data: 1048576 bytes

This error happens just after the ExecuteReaderAsync.

As a reference, this is the fragment that executes the query:

await using var dataSource = NpgsqlDataSource.Create(connectionString);
await using (var cmd = dataSource.CreateCommand(query))
await using (var reader = await cmd.ExecuteReaderAsync())
{
    dsResult.Load(reader);
}

I tried to modify the app.config adding: Read Buffer Size=18000000;Socket Send Buffer Size=18000000;Maximum Pool Size=100; in the connection string with the same error message.

I thank you in advance for any guide about how to allow Npgsql to execute big queries.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
MarioG
  • 25
  • 1
  • 10
  • 1
    29000 lines of code sounds ridiculous. You probably need a mapping table and a simple join, but we can't help you without seeing a sample (just a few lines) of your SQL query. – Charlieface May 08 '23 at 16:44
  • I'm not sure that error is related to the # of records returned or not, I cannot find that error message in the src. What version of Npgsql are you using? – mxmissile May 08 '23 at 16:59
  • @mxmissile I'm using version 7.0.4. Charlieface, I agree, I'm exploring alternatives if this is not possible with configuration, maybe perform the mapping in the C#. Thank you guys. – MarioG May 08 '23 at 17:19
  • I'm not sure exactly what you're doing, but the error clearly says that the limitation is not an Npgsql one, but rather a PostgreSQL one. – Shay Rojansky May 09 '23 at 07:12
  • Hi Shay, the query runs properly in Denodo (with IBM DB2 under the hood). Also it runs fine with smaller/medium queries, this error pops with the large query almost immediately it reaches the ExecuteReader. – MarioG May 10 '23 at 22:10
  • Denodo is a different database - PostgreSQL is communicating clearly that you've gone over a max length limit. – Shay Rojansky May 17 '23 at 15:18

0 Answers0