Re: Query hangs (and then timeout) after using COPY to import data

Since the query works in PgAdmin, but not in npgsql, the problem has to be
somewhere in Npgsql.

https://www.npgsql.org/doc/diagnostics/overview.html

Maybe increasing the log level will lead to a solution.

On Sun, Feb 11, 2024 at 6:13 PM <steott(at)gmail(dot)com> wrote:

> Thanks, Adrian, for the suggestion, but same problem.
>
> I've just tried to execute "ANALYZE" (on the whole database) after the
> import of all the tables (with COPY) and before the other queries, but the
> query always hangs (I hope this was the way you suggested).
>
> Stefano
>
> > -----Original Message-----
> > From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> > Sent: Sunday, February 11, 2024 10:42 PM
> > To: steott(at)gmail(dot)com; pgsql-general(at)lists(dot)postgresql(dot)org
> > Subject: Re: Query hangs (and then timeout) after using COPY to import
> data
> >
> > On 2/11/24 13:37, steott(at)gmail(dot)com wrote:
> > > Hello,
> > >
> > > I’m new to PostgreSQL. I’m trying to migrate an app from SqlServer to
> > > Postgres (it’s written in C# and uses Npgsql)
> > >
> > > I’ve tried with Postgres 16.1 and 16.2 on Windows Server 2019.
> > >
> > > This app used SqlServer’s Bulk Insert to import some tables (about 50
> > > tables) from another database, I replaced it with Postgres’ COPY
> > > function: this part works correctly.
> > >
> > > After the import, I execute sequentially (not in parallel) some
> > > queries in these tables, to update some data and to make some
> validations.
> > >
> > > At some point, systematically, one of these queries hangs, and after
> > > 10 minutes (the CommandTimeout that I set) it throws this exception:
> > >
> > > Exception while reading from stream ---> System.TimeoutException:
> > > Timeout during reading attempt
> > >
> > > at Npgsql.Internal.NpgsqlConnector
> > >
> > > The query is this one:
> > >
> > >
> > > SELECT Id FROM Item
> > >
> > > WHERE Id NOT IN (
> > >
> > > SELECT ItemId FROM ItemUom)
> > >
> > > LIMIT 100
> > >
> > > The same query, executed from pgAdmin, returns the result in less than
> > > a second (even if it’s executed while the query from my app is
> running).
> > >
> > > (actually the result are 0 record, but it’s correct: the query it’s
> > > just a validation that there are no records in that query)
> > >
> > > While the query is running from my app, I noticed that the CPU goes
> > > beyond 95%, even up to 100%, due to 3 postgres.exe processes.
> > >
> > > The RAM usage is less than 70%.
> > >
> > > In pgAdmin I’ve executed a query to list the running queries, and I
> > > can see that one.
> > >
> > > My issue seems to be very similar to this one:
> > > https://stackoverflow.com/questions/77195107/npgsql-timeout-during-
> > rea
> > > ding-attempt
> > > <https://stackoverflow.com/questions/77195107/npgsql-timeout-during-
> > re
> > > ading-attempt> but I didn’t understand how that guy solved the
> > > problem.
> > >
> > > If I import less tables from the external database, the query doesn’t
> > > hang and runs correctly, so this make me think about some resources
> > > that could “finish”, but I haven’t understood which one (for example
> > > the connections used to import the tables and all the commands and
> > > datareader used to execute the queries seem disposed correctly).
> > >
> > > I don’t know if it could be due to some Postgres parameter.
> > >
> > > Do you have any suggestions to solve this problem?
> > >
> >
> > Run ANALYZE on the tables/database.
> >
> > See:
> > https://www.postgresql.org/docs/current/sql-analyze.html
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com
>
>
>
>