Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rZHW9-003HGv-Nf for pgsql-general@arkaria.postgresql.org; Sun, 11 Feb 2024 21:37:42 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rZHW8-0053WV-Q7 for pgsql-general@arkaria.postgresql.org; Sun, 11 Feb 2024 21:37:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rZHW8-0053WN-E1 for pgsql-general@lists.postgresql.org; Sun, 11 Feb 2024 21:37:40 +0000 Received: from mail-wr1-x42c.google.com ([2a00:1450:4864:20::42c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rZHW5-006hZK-IA for pgsql-general@lists.postgresql.org; Sun, 11 Feb 2024 21:37:39 +0000 Received: by mail-wr1-x42c.google.com with SMTP id ffacd0b85a97d-3394bec856fso2143672f8f.0 for ; Sun, 11 Feb 2024 13:37:37 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1707687455; x=1708292255; darn=lists.postgresql.org; h=content-language:thread-index:mime-version:message-id:date:subject :to:from:from:to:cc:subject:date:message-id:reply-to; bh=8qkt71oSHNNJgFewXCRU3Yw8WQpVKm7iasYZIhB/PtM=; b=X+3DB25xEUBZnDjkOkdCAE7xhNBWc8qptWPa/i/NH8OA/SAWXW1I4e69J8YDVj9GtB H/nwvkwFIDH2vw+6CvWcrlsqmJ2tVTICARZErGSkY9M4LVaWNHj0wegsTmc8qgE/zgHf 4Il/wV90QulA2Ytd+A2Wy9j8u/q30n1DvdBx4cVnZtWtfEQGAv33dNsseH/NTkxjPD/O EtAcs0IFkEJzWZM1en33ogFbOsOVC8Hcn24o0Gpp3Z6W+aVlntJXT4sQnCu8OYQWIw7f AyA/K2u1kD01ADikUf39vcFK2lurk/igdskHp7RPhdyFc/x6wuCEwKgju/q/+6j+X0sL 97NA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1707687455; x=1708292255; h=content-language:thread-index:mime-version:message-id:date:subject :to:from:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=8qkt71oSHNNJgFewXCRU3Yw8WQpVKm7iasYZIhB/PtM=; b=Pea19RDdlgeYhHwGDBi006f1UacMWuI2eJ4iSLeYmiuP4XY/CpQtIcO7VPrmzmoJZS ML6XJCFEfECny8Ex/+dfuPIOJ6gwjjwNLYcyXSMLFGDnEmoZGPTX7+VTYoxYNzXmf7tV 2S6s8con5lgpkatLKxkWs0JweqIcLnoLRdLoFaxJgAc3pjGBWd1fK0u3UU3V1mbbbUD0 qeSkmS55Hd9eKXYozCYjKjmFAZlMl/YIf/cjuf4ghUuZPUnz15WhfXKvyhzV0VUqc2ys QDCjxX50kldl1G/7/BC+B2ffo2kgZusT/476+vHvEUuq03YSD0wpjLPHb7tjdCnC8MeV JU5Q== X-Gm-Message-State: AOJu0YxJHKh/EuaJexRz6djtCbLJWuza5Er/bufbAUd4YncKqfJi6AZr yEUO4DoYyzclNw6TKVa0vsnIsc0kiFJJdUV8uMGDx2BALQD8DDWwymSCrQ6BI9k= X-Google-Smtp-Source: AGHT+IEcWOWvPgPKp1sCwzWeG7Pcf+wtSkE13Ks8yoFKnYDZKIMqL/gg1hKKcbji7R+PZRUh1ACIZg== X-Received: by 2002:a5d:56cb:0:b0:33b:58c7:8281 with SMTP id m11-20020a5d56cb000000b0033b58c78281mr3895396wrw.10.1707687455275; Sun, 11 Feb 2024 13:37:35 -0800 (PST) Received: from STEFANOOTTA36DB ([134.19.170.128]) by smtp.gmail.com with ESMTPSA id u7-20020a056000038700b0033b72aa93b5sm4394386wrf.89.2024.02.11.13.37.34 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 11 Feb 2024 13:37:34 -0800 (PST) From: To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0025_01DA5D3A.E7FC9F30" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AdpdLhbgiC3TxG6LSL2pssVSW4rqvw== Content-Language: it List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0025_01DA5D3A.E7FC9F30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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? ------=_NextPart_000_0025_01DA5D3A.E7FC9F30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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-ti= meout-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?

------=_NextPart_000_0025_01DA5D3A.E7FC9F30--