postgresql 从流中读取时出现 Npgsql 异常，Postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40364449/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Npgsql Exception while reading from stream, Postgres
提问by RDS_JAF
I am getting this error intermittently in my code. Sometimes it happens time-after-time-after-time. Sometimes it happens 1-out-of-10 times. I am not doing anything unique or special in my SQL unlike the other poster on StackOverflow who was doing a COPY command. All I am doing is SELECTs.
我在我的代码中间歇性地收到此错误。有时它会一次又一次地发生。有时它会发生 10 次中的 1 次。与 StackOverflow 上正在执行 COPY 命令的另一张海报不同，我在 SQL 中没有做任何独特或特别的事情。我所做的只是选择。
Here is the stack trace:
这是堆栈跟踪：
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
Any suggestions on how to avoid this error? Is this a problem with Npgsql and postgres?
有关如何避免此错误的任何建议？这是 Npgsql 和 postgres 的问题吗？
Here is my SQL Statement that seems to be the most problematic:
这是我的 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
I tried re-arranging the SQL to take out the sub-SELECTS and make them all joins; didn't help, same error.
我尝试重新安排 SQL 以取出子 SELECTS 并使它们全部连接；没有帮助，同样的错误。
Here is the C# code calling Npgsql:
这是调用 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();
回答by RDS_JAF
conn = new NpgsqlConnection("Server=myserver;
User Id=postgres;
Password=somepw;
Database=somedb;
Pooling=false;
Timeout=300;
CommandTimeout=300");
I added the CommandTimeoutproperty to my connection string and it seems to be working now. Weird exception for a timeout error...
我将该CommandTimeout属性添加到我的连接字符串中，现在似乎可以正常工作了。超时错误的奇怪异常......
回答by RafaelSantos
Please try the setting
请尝试设置
KeepAlive = 300
The number of seconds of connection inactivity before Npgsql sends a keepalive query. Set to 0 (the default) to disable.
Npgsql 发送 keepalive 查询之前连接不活动的秒数。设置为 0（默认值）以禁用。
See the documentation.
请参阅文档。
