NPGSQL Begin Text Import throws exception on larger CSV files

Question

EDIT: After doing some more testing this error is directly related to HAPROXY. When I took that out of the stack it worked as expected.

I have been struggling with this issue for several days. This code does work for smaller CSV files (25 KB) but once you try to do larger files (5000 KB) it throws an exception.

My stack is the below code from asp.net core 3.0, NPGSQL 4.1.1, to an HAPROXY, to a PostgreSQL 12 instance.

Here is the code:

      string query = "COPY " + seedingDataApi.tableName + " FROM STDIN CSV ENCODING 'UTF8' QUOTE '\"'; ";
                using (var conn = postgresContext.GetDBConnectionPG())
                {

                    conn.Open();
                    using (var writer = conn.BeginTextImport(query))
                    {
                        _logger.LogInformation("Text import started");
                        using (StreamReader sr = new StreamReader(seedingDataApi.tableName + ".csv"))
                        {

                            _logger.LogInformation("Read stream started.");
                            while (!sr.EndOfStream)
                            {
                                string testString = "";

                                testString = sr.ReadLine();
                                while ("<EOL>" != testString.Substring(Math.Max(0, testString.Length - 5)))
                                {
                                    testString += sr.ReadLine();
                                }
                                testString = testString.Remove(testString.Length - 5);
                                writer.WriteLine(testString);
                            }

                        }
                    }
                }

The exception thrown by the application is:

 System.Exception: Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
 ---> System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|0>d.MoveNext()
   at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|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()
--- End of stack trace from previous location where exception was thrown ---
   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.ReadMessage(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlRawCopyStream..ctor(NpgsqlConnector connector, String copyCommand)
   at Npgsql.NpgsqlConnection.BeginTextImport(String copyFromCommand)
   at MiniAppApiServer.Repositories.TableSeedRepo.TableSeedRepository.SeedTableDataAsync(TableSeedDataParams seedingDataApi, CancellationToken ct) in /source/MiniAppApiServer/Repositories/TableSeedRepo/TableSeedRepository.cs:line 306System.IO.EndOfStreamException: Attempted to read past the end of the stream.
   at Npgsql.NpgsqlReadBuffer.<>c__DisplayClass34_0.<<Ensure>g__EnsureLong|0>d.MoveNext()
   at MiniAppApiServer.Repositories.TableSeedRepo.TableSeedRepository.SeedTableDataAsync(TableSeedDataParams seedingDataApi, CancellationToken ct) in /source/MiniAppApiServer/Repositories/TableSeedRepo/TableSeedRepository.cs:line 339
   at MiniAppApiServer.Supervisors.TableSeedSupervisor.TableSeedSupervisor.SeedTableDataAsync(Stream data, String tablename, CancellationToken ct) in /source/MiniAppApiServer/Supervisors/TableSeedSupervisor/TableSeedSupervisor.cs:line 86
   at MiniAppApiServer.Controllers.TableSeedingController.PostTableDataSeed(IFormFile file, CancellationToken ct) in /source/MiniAppApiServer/Controllers/TableSeedingController.cs:line 114

Thank you so much!

Answer 1

Solution reached! The issue was with the HAProxy configuration. I changed the following values:

timeout client          1m
timeout server          1m

to this:

timeout client          60m
timeout server          60m