Feedback-ID: i76984098:Fastmail
Message-ID: <237c1e3f-4118-6ffc-6e0b-ddce824f1f55@aklaver.com>
Date: Sun, 23 Apr 2023 14:25:25 -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>, psycopg@postgresql.org
References: 
 <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: 
 <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/237c1e3f-4118-6ffc-6e0b-ddce824f1f55%40aklaver.com>
Precedence: bulk

On 4/23/23 13:45, Michael P. McDonnell wrote:
> Python 3.10.6
> psycopg library 3.1.8
> 
> Running consecutive inserts sourced in files.
> All inserts are of the same format:
> 
> INSERT INTO _____ (field1, field2, field3)
> SELECT field1, field2, field3 FROM ____, Join ___, join ___ etc...
> 
> The code I've written is this:
> 
> for qi in range(qlen):
>              query = queries[qi]
>              qparams = params[qi]
>              with self.connection.cursor() as conn:
>                  conn.execute(query, qparams)

In above you are running the context manager(with) over the cursor not 
the connection. This will not automatically commit the transaction. You 
will need to either explicitly do connection.commit() or use the with 
over the connection per:

https://www.psycopg.org/psycopg3/docs/basic/transactions.html

> 
> When I run the queries in dbeaver - the first query takes 120s (it's 
> 1.9M rows), the second query takes 2s (7000 rows).
> When I run the queries in python - it freezes on the second query.
> 
> Any guidance on how to attack this would be awesome as I have re-written 
> my code a dozen times and am just slinging mud to see what sticks.

-- 
Adrian Klaver
adrian.klaver@aklaver.com