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