When multiple API calls are came, the time out exception occurred in
NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory()) connection.
Below is my code .
my function is below.
public async Task<List<AggregationModel>> datafetch(QueryModelRawData model,string tablename)
{
var result = new List<AggregationModel>();
NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory())
using (NpgsqlConnection sqlCon = new NpgsqlConnection(SqlConnectionString))
{
var tableName = "testtable";
sqlCon.Open();
var cmd = $"select timestamp as unixtime,reading from {tableName} where id=@s AND timestamp>=@st AND timestamp<=@et ORDER BY unixtime ASC";
using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, sqlCon))
{
sqlCmd.Parameters.AddWithValue("s", model.id);
sqlCmd.Parameters.AddWithValue("st", model.starttime);
sqlCmd.Parameters.AddWithValue("et", model.endtime);
using (var reader = await sqlCmd.ExecuteReaderAsync())
{
while (reader.HasRows && await reader.ReadAsync())
{
double readingValue = readingToDouble(reader);
AggregationModel item = new AggregationModel
{
reading = readingValue,
unixtime = (double)reader["unixtime"]
};
result.Add(item);
}
}
}
}
return result;
}
i called the same function every time for each query execution and is this correct way ? Any example is available ?
The CrateDB specific DataFactory should not be needed anymore from CrateDB v4.2 and higher. You should be able to connect following standard npgsql guides for Postgres.
is unixtime a timestamp?
If yes, I don’t think your cast (double)reader["unixtime"] works
I tried to replicate it
CREATE TABLE testtable (id TEXT, timestamp timestamp ,reading double);
INSERT INTO testtable VALUES ('a',now(),random());
using System;
using Npgsql;
namespace test
{
class Program
{
static void Main(string[] args)
{
string connString = "Host=localhost;Username=crate;SSL Mode=Prefer;Database=doc";
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
var tableName = "testtable";
var cmd = $"select timestamp as unixtime,reading from {tableName} where id=@s AND timestamp>=@st AND timestamp<=@et ORDER BY unixtime ASC";
using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, conn))
{
sqlCmd.Parameters.AddWithValue("s", "a");
sqlCmd.Parameters.AddWithValue("st", 1622716474405);
sqlCmd.Parameters.AddWithValue("et", 1622716474837);
using (var reader = sqlCmd.ExecuteReader())
{
while (reader.HasRows && reader.Read())
{
Console.WriteLine(reader["unixtime"]);
}
}
}
}
}
}
}
thank you for the reply…
My problem is connection with crate db. just leave the other part.
Now i remove the RegisterFactory and using Npgsql;
Then i got an error
> Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
> ---> System.TimeoutException: Timeout during reading attempt
> at Npgsql.NpgsqlReadBuffer.<Ensure>g__EnsureLong|40_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
> at Npgsql.NpgsqlConnector.RawOpen(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
> at Npgsql.NpgsqlConnector.Open(NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
> at Npgsql.ConnectorPool.OpenNewConnector(NpgsqlConnection conn, NpgsqlTimeout timeout, Boolean async, CancellationToken cancellationToken)
> at Npgsql.ConnectorPool.<>c__DisplayClass38_0.<<Rent>g__RentAsync|0>d.MoveNext()
> --- End of stack trace from previous location where exception was thrown ---
> at Npgsql.NpgsqlConnection.<>c__DisplayClass41_0.<<Open>g__OpenAsync|0>d.MoveNext()
> --- End of stack trace from previous location where exception was thrown ---
> at Npgsql.NpgsqlConnection.Open()
at Implementation/crateRepository.cs:line 15
My code is
using System;
using Npgsql;
namespace test
{
class Program
{
static void Main(string[] args)
{
string connString = "Host=localhost;Username=crate;SSL Mode=Prefer;Database=doc";
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
var tableName = "testtable";
var cmd = $"select timestamp as unixtime,reading from {tableName} where id=@s AND timestamp>=@st AND timestamp<=@et ORDER BY unixtime ASC";
using (NpgsqlCommand sqlCmd = new NpgsqlCommand(cmd, conn))
{
sqlCmd.Parameters.AddWithValue("s", "a");
sqlCmd.Parameters.AddWithValue("st", 1622716474405);
sqlCmd.Parameters.AddWithValue("et", 1622716474837);
using (var reader = sqlCmd.ExecuteReader())
{
while (reader.HasRows && reader.Read())
{
Console.WriteLine(reader["unixtime"]);
}
}
}
}
}
}
}
Have you forwarded the port to localhost from the pod or are you using any load balancer? To me this now seems most likely like a war one kubernetes setup
Which version of npgsql are you using?
How big is the table queried?
Did you have succes connecting with any other client (e.g. crash or psql)?
Yes. I deployed this crate db in kubernetes. not deployed any load balance for crate db. the c# application connecting the DB through the internal service. check the crate Internal service documentation. Both crate db and c# web api connection deployed in same kubernetes cluster. (connection between c# application and crate db are in same network. so no need any load balancer or port forward to connecting it )
Npgsql version ==> Include=“Npgsql” Version=“5.0.5”/>.
Only 50000 data in table.But api request (multiple db query execution call happens at the same time). I didn’t try any other client for c# application. I found only Npgsql documentation for c#.
There is a 500 data write into database in every 1 minute and 10 parallel get query executed parallel(this get call happens once in a every 15 minute).
