From: steott@gmail.com
To: pgsql-general@lists.postgresql.org
Subject: Query hangs (and then timeout) after using COPY to import data
Date: Sun, 11 Feb 2024 22:37:33 +0100
Message-ID: <002401da5d32$8637c200$92a74600$@gmail.com> (raw)
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-a
ttempt
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: steott@gmail.com
Subject: Re: Query hangs (and then timeout) after using COPY to import data
In-Reply-To: <002401da5d32$8637c200$92a74600$@gmail.com>
References: <002401da5d32$8637c200$92a74600$@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox