从流中读取时出现的Npgsql异常，Postgres

Question

我在我的代码中经常遇到这个错误。有时会连续出现多次，有时只会出现一次。我在我的SQL查询中没有做任何特殊或独特的操作，不像StackOverflow上的其他帖子中那位使用COPY命令的用户。我所做的只是SELECT查询。

以下是堆栈跟踪：

Exception while reading from stream
at Npgsql.ReadBuffer.Ensure(Int32 count, Boolean dontBreakOnTimeouts)
at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode  dataRowLoadingMode, Boolean isPrependedMessage)
at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRow LoadingMode) 
at Npgsql.NpgsqlConnector.ReadMessage(DataRowLoadingMode dataRowLoadingMode) 
at Npgsql.NpgsqlConnector.ReadExpecting[T]() 
at Npgsql.NpgsqlDataReader.NextResultInternal() 
at Npgsql.NpgsqlDataReader.NextResult() 
at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior) 
at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior) 
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at System.Data.Common.DbCommand.ExecuteReader() 
at Npgsql.NpgsqlCommand.ExecuteReader() 
at JBetaFinder.Program.portfolioSimulation(String beginResult, String endResult) in c:\Users\j\Documents\Visual Studio 2013\Projects\JBetaFinder\JBetaFinder\Program.cs:line 571

有没有关于如何避免这个错误的建议？这是Npgsql和postgres的问题吗？

以下是我最困难的SQL语句：

select leg1.trade_date, sum(p.qty) as totalqty, max(gas.net_change)*10000 as avggaschange,  
            sum(((leg1.settlement_price - leg2.settlement_price) - (leg3.settlement_price - leg4.settlement_price))*qty*1000000) as spread_value_weight
            from quant_portfolio p
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg1
                            on p.leg1 = leg1.strip
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg2
                            on p.leg2 = leg2.strip and leg1.trade_date = leg2.trade_date                
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg3
                            on p.leg3 = leg3.strip and leg1.trade_date = leg3.trade_date                
            inner join (select distinct trade_date, hub, product, strip, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') leg4
                            on p.leg4 = leg4.strip and leg1.trade_date = leg4.trade_date  
            inner join (select distinct trade_date, hub, product, strip, contract, settlement_price, net_change
                            from public.icecleared_gas where contract = 'H') gas
                            on gas.strip = (select min(leg1) from quant_portfolio where commodity = 'NG') and gas.trade_date = leg1.trade_date                         
            where p.commodity = 'NG'
            AND (leg1.trade_date>='xxx' and leg1.trade_date<='yyy')
            group by leg1.trade_date
            order by leg1.trade_date

我尝试重新排列SQL，将子查询全部转换为JOIN；但没有帮助，仍然出现相同的错误。

以下是调用Npgsql的C#代码：

query = new NpgsqlCommand(getFullQuantPortBeta.ToString().Replace("xxx", beginResult.ToString()).Replace("yyy", endResult.ToString()), conn);
            dr = query.ExecuteReader();//code does not get past this line!
            beta = 0;
            while (dr.Read())
            {
                baselineData.Add(double.Parse(dr[2].ToString()));
                responseData.Add(double.Parse(dr[3].ToString()));
                if (baselineData.Count > 3)
                {
                    Tuple<double, double> result = MathNet.Numerics.LinearRegression.SimpleRegression.Fit(baselineData.ToArray(), responseData.ToArray());
                    beta = result.Item2 * BETA_MULT;
                    Console.WriteLine("WEIGHT BETA = " + beta);
                }
            }
            dr.Close();

- RDS_JAF

---

欢迎来到 Stack Overflow，请问您能否发布一下调用 Npgsql 的 C# 代码？ - Dr Rob Lang

您的异常报告不完整：您没有发布异常类型，也没有任何内部异常（可能包含真正的问题）。请将它们添加到您的问题中。 - Shay Rojansky

Answer 1

conn = new NpgsqlConnection("Server=myserver;
User Id=postgres;
Password=somepw;
Database=somedb;
Pooling=false;
Timeout=300;
CommandTimeout=300");

我在我的连接字符串中添加了CommandTimeout属性，现在似乎它正在工作。超时错误的异常很奇怪...

- RDS_JAF

---

“超时错误的异常情况很奇怪” - 超时错误出现在内部异常中，可能是“System.TimeoutException: Timeout during reading attempt”。 - Click Ok

Answer 2

请尝试此设置。

KeepAlive = 300

Npgsql 在发送保持连接查询之前，连接空闲的秒数。将其设置为 0 (默认值) 以禁用。

请参见文档。

- RafaelSantos

Answer 3

检查是否在调用方法之前忘记使用await。在我的情况下是这样的

advisorRepository.UpdateAsync(entity, cancellationToken);

没有 await

- Akhmed