Re: Consecutive Inserts Freeze Execution of Psycopg3

On 4/24/23 14:16, Michael P. McDonnell wrote:
> Hey Adrian -
> Just wanted to say thank you again for helping me with getting the 
> session stuff structured appropriately. I actually found my issue and it 
> turns out that while my code is suboptimal in a lot of ways - it was 
> actually a separate database issue as a whole that I'm just now tracking 
> down.
> Just thought you'd like to know that it was not a psycopg(3 or 2) issue, 
> not a sqlalchemy issue, and it has something to do with how the queries 
> seem to work.
> 
> And the technical info (not an ask, just an inform)
> The queries are just an ETL from one table to another based on how the 
> data needs to be accessed. If I trunc all the tables, and load the 
> originating table - the first ETL works fine (table1 -> table2).
> However - when I then call the 2nd ETL from table2 to table3 - the query 
> "freezes". I'm still looking into what that means and how to find what's 
> going on.

Look at:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

and

https://www.postgresql.org/docs/current/view-pg-locks.html


> If I cancel the execution of that query and re-run the 2nd ETL (table2 
> to table3) - it runs in the amount of time I expected (2-3 seconds).
> 
> So this fundamentally was never about a session, or commits, or anything 
> else; those were working as expected - the query itself was just hung up 
> in the server doing its "thing".
> 
> Anywho - hopefully someone is googling and this pops up and helps them 
> look at the problem differently. Have a great day.
> -Mike
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com