pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query hangs (and then timeout) after using COPY to import data
8+ messages / 4 participants
[nested] [flat]

* Query hangs (and then timeout) after using COPY to import data
@ 2024-02-11 21:37 steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 8+ messages in thread

From: steott@gmail.com @ 2024-02-11 21:37 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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?

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
@ 2024-02-11 21:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-11 23:12   ` Re: Query hangs (and then timeout) after using COPY to import data Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-11 23:13   ` RE: Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  0 siblings, 2 replies; 8+ messages in thread

From: Adrian Klaver @ 2024-02-11 21:41 UTC (permalink / raw)
  To: steott@gmail.com; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-11 23:12   ` Ron Johnson <ronljohnsonjr@gmail.com>
  1 sibling, 0 replies; 8+ messages in thread

From: Ron Johnson @ 2024-02-11 23:12 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

On Sun, Feb 11, 2024 at 4:41 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 2/11/24 13:37, steott@gmail.com wrote:
>
[snip]

> >
> > 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)
>
[snip]

> > Do you have any suggestions to solve this problem?
> >
>
> Run ANALYZE on the tables/database.
>

If ANALYZE was the cure, why does it successfully run from PgAdmin?

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* RE: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-11 23:13   ` steott@gmail.com
  2024-02-11 23:27     ` Re: Query hangs (and then timeout) after using COPY to import data Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-12 16:42     ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 2 replies; 8+ messages in thread

From: steott@gmail.com @ 2024-02-11 23:13 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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 the other 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






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-11 23:13   ` RE: Query hangs (and then timeout) after using COPY to import data steott@gmail.com
@ 2024-02-11 23:27     ` Ron Johnson <ronljohnsonjr@gmail.com>
  2024-02-11 23:43       ` Re: Query hangs (and then timeout) after using COPY to import data Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 8+ messages in thread

From: Ron Johnson @ 2024-02-11 23:27 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

Since the query works in PgAdmin, but not in npgsql, the problem has to be
somewhere in Npgsql.

https://www.npgsql.org/doc/diagnostics/overview.html

Maybe increasing the log level will lead to a solution.

On Sun, Feb 11, 2024 at 6:13 PM <steott@gmail.com> wrote:

> 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 the other 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
>
>
>
>

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-11 23:13   ` RE: Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 23:27     ` Re: Query hangs (and then timeout) after using COPY to import data Ron Johnson <ronljohnsonjr@gmail.com>
@ 2024-02-11 23:43       ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2024-02-11 23:43 UTC (permalink / raw)
  To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>

Ron Johnson <ronljohnsonjr@gmail.com> writes:
> Since the query works in PgAdmin, but not in npgsql, the problem has to be
> somewhere in Npgsql.

I'm wondering if npgsql is executing the query as a cursor and that
is causing the planner to make a poor choice of plan.  There is a
bias towards fast-start plans if you use a cursor, and that doesn't
always work well :-(

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-11 23:13   ` RE: Query hangs (and then timeout) after using COPY to import data steott@gmail.com
@ 2024-02-12 16:42     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-12 17:01       ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 8+ messages in thread

From: Adrian Klaver @ 2024-02-12 16:42 UTC (permalink / raw)
  To: steott@gmail.com; pgsql-general@lists.postgresql.org

On 2/11/24 15:13, steott@gmail.com wrote:
> 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 the other queries, but the query always hangs (I hope this was the way you suggested).

Yes, this is what I was suggesting. Anytime I see data migration and 
slow queries, my first suggestion is to run ANALYZE to get the planner 
statistics up to date. That at least eliminates one possible source of 
slowness. In this case though there seems to be something else at work, 
see Tom Lanes suggestion. This may end up requiring installing auto-explain:

https://www.postgresql.org/docs/current/auto-explain.html

to get an EXPLAIN of the query.

> 
> Stefano


-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query hangs (and then timeout) after using COPY to import data
  2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-11 21:41 ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
  2024-02-11 23:13   ` RE: Query hangs (and then timeout) after using COPY to import data steott@gmail.com
  2024-02-12 16:42     ` Re: Query hangs (and then timeout) after using COPY to import data Adrian Klaver <adrian.klaver@aklaver.com>
@ 2024-02-12 17:01       ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Adrian Klaver @ 2024-02-12 17:01 UTC (permalink / raw)
  To: steott@gmail.com; pgsql-general@lists.postgresql.org

On 2/12/24 08:42, Adrian Klaver wrote:
> On 2/11/24 15:13, steott@gmail.com wrote:
>> 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 the other queries, but 
>> the query always hangs (I hope this was the way you suggested).
> 
> Yes, this is what I was suggesting. Anytime I see data migration and 
> slow queries, my first suggestion is to run ANALYZE to get the planner 
> statistics up to date. That at least eliminates one possible source of 
> slowness. In this case though there seems to be something else at work, 
> see Tom Lanes suggestion. This may end up requiring installing 
> auto-explain:
> 
> https://www.postgresql.org/docs/current/auto-explain.html
> 
> to get an EXPLAIN of the query.

I should have added, auto-explain incurs overhead so you just want to 
run it until you solve the issue.

> 
>>
>> Stefano
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2024-02-12 17:01 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-11 21:37 Query hangs (and then timeout) after using COPY to import data steott@gmail.com
2024-02-11 21:41 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-11 23:12   ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-11 23:13   ` steott@gmail.com
2024-02-11 23:27     ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-02-11 23:43       ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-12 16:42     ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-02-12 17:01       ` Adrian Klaver <adrian.klaver@aklaver.com>

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