npgsql/npgsql

Npgsql is the .NET data provider for Postgresql. It allows any program developed for .NET framework to access a Postgresql database server. It is implemented in 100% C# code. Postgresql versions since 9.0 are officially supported, others may work.

akshayjoyinfo

@akshayjoyinfo

CONNECTIONS -100 TOTAL
I AM TRYING TO RUN READ QUERY for 50 k6 users parallel but npgsql ef core is able to ingest recot in parallel with npgsql connection, but with dapper Is connection with Transient dependency is failing for 10% requests
npgsql.npgsqlexception (0x80004005): exception while reading from stream ---> system.timeoutexception: too many requests

Ideal case everything working.. but 50 parallel users with ID connection dapper npqsql is failing

I am using 3.1.4

akshayjoyinfo

@akshayjoyinfo

Npgsql.NpgsqlException (0x80004005): Exception while reading from stream
---> System.TimeoutException: Timeout during reading attempt
at Npgsql.NpgsqlReadBuffer.<Ensure>gEnsureLong|40_0(NpgsqlReadBuffer buffer, Int32 count, Boolean async, Boolean readingNotifications)
at Npgsql.NpgsqlConnector.<ReadMessage>gReadMessageLong

akshayjoyinfo

@akshayjoyinfo

My query is quite huge that is essential, but this error only happens when you have do load testing with k6 , 50 users. This issue happens only when dapper with ISBCONNNECTION

jearmstrong

@jearmstrong

Hello, major newbie here, and I have a question that might show how deep my ignorance goes. Anyway, I don’t want to be long winded here, so my basic question is: Can a postgres table be edited through a connection with Excel? I suspect the answer is YES, and if confirmed I will provide more details of the situation I have. Thanks - james

Shay Rojansky

@roji

@akshayjoyinfo are you saying that the same scenario works well with EF Core, but fails with Dapper? That's sounds very odd. Make sure you haven't forgotten to close/dispose your NpgsqlConnection etc. Otherwise, there's generally not much I can help with for timeout/load exceptions - that's almost always a matter of your specific query, index usage, networking conditions, etc.

@jearmstrong yeah - you can install Npgsql at the Windows level, and then it appears as a "data source" inside Excel and can be accessed directly. Get the MSI installer for Npgsql 4.0.11 (https://github.com/npgsql/npgsql/releases?q=4.0.11&expanded=true) and make sure you check "GAC installation".

akshayjoyinfo

@akshayjoyinfo

@roji since I am using Transient IDbConnection there will be auto closing and opening connection, I have tried DBconnection Factory , but getting too many clients error

Shay Rojansky

@roji

@akshayjoyinfo you'd really need to show some minimal code samples - it's impossible to know exactly what's going on from the above. I'd suggest opening a question on stackoverflow with the tag "npgsql".

akshayjoyinfo

@akshayjoyinfo

Yeah I am creating one,

But what is the reason for this exception

While executing query

Shay Rojansky

@roji

@akshayjoyinfo the exception simply says there was a timeout while reading results from PostgreSQL. There could be many reasons for that - network outage, a very long query, an overloaded server... The exception itself can't provide enough information to know what the root cause is

Björn Harrtell

@bjornharrtell

Why is the usage example using await using in await using var conn = new NpgsqlConnection(connString);? I don't understand what await does in that context because construction cannot be async?

Brar Piening

@Brar

@bjornharrtell the using declaration is not about construction of objects but about their disposal. It basically tells the compiler to automatically call Dispose() on the object when it goes out of scope.
await using is the async counterpart of the using declaration that tells the compiler to call DisposeAsync() instead of Dispose(). See https://docs.microsoft.com/en-us/dotnet/standard/garbage-collection/implementing-disposeasync#using-async-disposable for more background.

Björn Harrtell

@bjornharrtell

@Brar: Ah, I was confused because I failed to note that it does implement both IDisposable and IAsyncDisposable.

JT Bruch

@JT-Bruch

I am feeling really stupid right now, but I don't understand why this doesn't work.

I have a POCO that looks like:

public class DbEquipmentFeature : EntityBase
    {
        [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int DbId { get; set; }

        public Guid Id { get; set; } = Guid.NewGuid();

        public DbEquipment Equipment { get; set; }
        public Guid EquipmentId { get; set; }

        [Column(TypeName = "jsonb")]
        public SerializableMultiLangString Value { get; set; }

    }

and SerializableMultiLangString is

public class SerializableMultiLangString
    {
        public Translation[] Translations { get; set; }
}

and Translation is


    public class Translation
    {

        public Translation() { }

        public LanguageCode LanguageId { get; set; } = 0;
        public string LanguageShortCode => LanguageId.ToString();
        public string Text { get; set; }
        public string Description { get; set; }
    }

But I keep getting System.InvalidOperationException: The entity type 'SerializableMultiLangString' requires a primary key to be defined. If you intended to use a keyless entity type call 'HasNoKey()'.

Which doesn't make sense because its not its own entity?

That is the error i get on trying to create the context

Shay Rojansky

@roji

@JT-Bruch just copying and pasting the above into a sample file works - there's likely something else you're not showing. Always good to point to a full, runnable code sample rather than snippets...

Sajan

@woodsongem

Hey All,
We have a customer and county table. We added below config and Customer.CountryId is optional. we are able to populate CountryDetail if CountryId is not null and get below error if Customer.CountryId is null. How to fix this issue? . TIA.

builder.HasOne(x => x. CountryDetail).WithOne().HasForeignKey< Country >(x => x. Id).HasPrincipalKey<Customer>(z => z. CountryId);

public class Customer {
  public int Id {get;set;}
  public int? CountryId {get;set;}
  public Country? CountryDetail {get;set;}
}

public class Country
{
 public int CountryId {get;set;}
 public string name  {get;set;}
}

Error:

System.InvalidCastException: Column 'CountryId is null.
   at Npgsql.ThrowHelper.ThrowInvalidCastException_NoValue(FieldDescription field)
   at Npgsql.NpgsqlDataReader.GetFieldValue[T](Int32 ordinal)
   at Npgsql.NpgsqlDataReader.GetInt32(Int32 ordinal)
  at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.ReadInt(DbDataReader reader, Int32 ordinal, ReaderColumn column)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.ReadRow()
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.BufferedDataRecord.InitializeAsync(DbDataReader reader, IReadOnlyList`1 columns, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.InitializeAsync(IReadOnlyList`1 columns, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.BufferedDataReader.InitializeAsync(IReadOnlyList`1 columns, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)

Shay Rojansky

@roji

Looks like you have a null value in the CountryIt column in the database? You're telling EF Core that this isn't possible (that property is a non-nullable int)...

(note that EF Core doesn't support nulls in primary keys)

Matthijs ter Woord

@mterwoord:matrix.org

[m]

Hi all! I recently upgraded a project to .net 6, entity framework core 6 and npgsql 6, and now I have to specify datetimekind everywhere (or resort to the legacy behavior)

Is theer an easy way to have my api assume all dates are in fact utc?

Shay Rojansky

@roji

@mterwoord:matrix.org the question is more why you have DateTimes with non-UTC Kinds in your application, when in fact you want them to mean UTC... For example, maybe you're using DateTime.Now when you should be using DateTime.UtcNow?

Regardless of Npgsql, it's important for the DateTime's Kind to correspond to what's actually being represented

Matthijs ter Woord

@mterwoord:matrix.org

[m]

stuff coming from api's

my api+server side is purely utc

Shay Rojansky

@roji

Like DateTime coming from JSON's being sent by cilents?

Matthijs ter Woord

@mterwoord:matrix.org

[m]

yes

Shay Rojansky

@roji

So if those timestamps really are UTC, I'd look at your JSON deserializer to make it deliver DateTimes with kind UTC

This way in your application you have the right DateTimes

I know it's a pain

Think of DateTimes with different kinds as different .NET types - UTC timestamps and local timestamps really are different things (it's a pity the .NET DateTime type is so badly designed...)

Matthijs ter Woord

@mterwoord:matrix.org

[m]

Sorry for delayed response. Thanks for responding, and suggesting to look at json. I think I fixed (most of) it now. lets see if all tests are green again

Matthijs ter Woord

@mterwoord:matrix.org

[m]

well, the ones caused by this: yes :-)

Thanks!

Matthijs ter Woord

@mterwoord:matrix.org

[m]

Follow-up question: It seems that data retrieved using EFCore doesn't have datetime.kind = utc. Is that something I have to change somewhere as well?

Shay Rojansky

@roji

@mterwoord:matrix.org what is the PostgreSQL column type? For UTC timestamps, it should be timestamp with time zone, and not timestamp without time zone

timestamp with time zone should be the default for DateTime properties in EF Core, so you should get timestamp without time zone only if you're explicitly specifying it

DateTime retrieved from timestamp with time zone should always have Kind=UTC

Matthijs ter Woord

@mterwoord:matrix.org

[m]

hmm, it seems "timestamp" but its all generated by EFC

Shay Rojansky

@roji

With EF Core 6.0?

Are you sure?

Matthijs ter Woord

@mterwoord:matrix.org

[m]

with 3.1 i think

Shay Rojansky

@roji

Ah that's very different indeed

Matthijs ter Woord

@mterwoord:matrix.org

[m]

i made a pgdump yesterday. lets see

Shay Rojansky

@roji

What I wrote above was for 6.0 - there have been significant changes around timestamp handling in that version

Where communities thrive

People

Repo info

Activity