Feedback-ID: i76984098:Fastmail
Message-ID: <2d7af6b8-fb04-6aed-2397-6557b09a64db@aklaver.com>
Date: Mon, 24 Apr 2023 14:22:06 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.10.0
Subject: Re: Consecutive Inserts Freeze Execution of Psycopg3
To: "Michael P. McDonnell" <bzaks1424@gmail.com>
Cc: psycopg@postgresql.org
References: 
 <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
 <237c1e3f-4118-6ffc-6e0b-ddce824f1f55@aklaver.com>
 <CAHmCLHrdxjeuRDxS7w2P-9LZ_C5wxsoVyLrVJwRRwrMOXVu4dQ@mail.gmail.com>
 <a565b356-d3d4-21e6-054c-7eca541daee6@aklaver.com>
 <CAHmCLHq5hHaDfVi_=p9PAaLjdsKEiZvDZVqUOgTd46+UizHBaQ@mail.gmail.com>
 <04fea413-8776-dd4e-7f0e-4b235b454f19@aklaver.com>
 <CAHmCLHrrT5geecTN1LbkJbvQdA6XhU4gW7bsroRtMEMfLY0aPw@mail.gmail.com>
 <021e900b-53d0-c5a0-2750-a0fe60230e68@aklaver.com>
 <CAHmCLHrDTDhQnof4nCD34vqY+kLmtY-FHkwMLU30V=0wDxhvKA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CAHmCLHrDTDhQnof4nCD34vqY+kLmtY-FHkwMLU30V=0wDxhvKA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/2d7af6b8-fb04-6aed-2397-6557b09a64db%40aklaver.com>
Precedence: bulk

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