Re: Query hangs (and then timeout) after using COPY to import data
От | Adrian Klaver |
---|---|
Тема | Re: Query hangs (and then timeout) after using COPY to import data |
Дата | |
Msg-id | 98a7e782-96bc-4c55-a919-bda0d5d35f87@aklaver.com обсуждение исходный текст |
Ответ на | Query hangs (and then timeout) after using COPY to import data (<steott@gmail.com>) |
Ответы |
Re: Query hangs (and then timeout) after using COPY to import data
(Ron Johnson <ronljohnsonjr@gmail.com>)
RE: Query hangs (and then timeout) after using COPY to import data (<steott@gmail.com>) |
Список | pgsql-general |
On 2/11/24 13:37, steott@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-reading-attempt <https://stackoverflow.com/questions/77195107/npgsql-timeout-during-reading-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.klaver@aklaver.com
В списке pgsql-general по дате отправления: