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-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(dot)klaver(at)aklaver(dot)com