RE: Query hangs (and then timeout) after using COPY to import data - Mailing list pgsql-general

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
theother queries, but the query always hangs (I hope this was the way you suggested). 

Stefano

> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Sunday, February 11, 2024 10:42 PM
> To: steott@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, 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-
> 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.klaver@aklaver.com