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 <ste...@gmail.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.kla...@aklaver.com> > > Sent: Sunday, February 11, 2024 10:42 PM > > To: ste...@gmail.com; pgsql-general@lists.postgresql.org > > Subject: Re: Query hangs (and then timeout) after using COPY to import > data > > > > On 2/11/24 13:37, ste...@gmail.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.kla...@aklaver.com > > > >