MIME-Version: 1.0
From: "Michael P. McDonnell" <bzaks1424@gmail.com>
Date: Sun, 23 Apr 2023 15:45:56 -0500
Message-ID: 
 <CAHmCLHrztOYi=uLqc6jKrytz5=e4ZKhYP1LTyLfTi-KGC1pR9w@mail.gmail.com>
Subject: Consecutive Inserts Freeze Execution of Psycopg3
To: psycopg@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000b9d34505fa06fa1e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHmCLHrztOYi%3DuLqc6jKrytz5%3De4ZKhYP1LTyLfTi-KGC1pR9w%40mail.gmail.com>
Precedence: bulk

--000000000000b9d34505fa06fa1e
Content-Type: text/plain; charset="UTF-8"

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)

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.

--000000000000b9d34505fa06fa1e
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Python 3.10.6<br>psycopg library 3.1.8<br><br>Running cons=
ecutive inserts sourced in files.<br>All inserts are of the same format:<br=
><br>INSERT INTO _____ (field1, field2, field3)<br>SELECT field1, field2, f=
ield3 FROM ____, Join ___, join ___ etc...<br><br><div>The code I&#39;ve wr=
itten is this:<br><br>for qi in range(qlen):<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 query =3D queries[qi]<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 qparams =3D params[qi]<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
with self.connection.cursor() as conn:<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 conn.execute(query, qparams)<div><br></div><div>Wh=
en I run the queries in dbeaver - the first query takes 120s (it&#39;s 1.9M=
 rows), the second query takes 2s (7000 rows).<br>When I run the queries in=
 python - it freezes on the second query.<br><br>Any guidance on how to att=
ack this would be awesome as I have re-written my code a dozen times and am=
 just slinging mud to see what sticks.<br></div></div></div>

--000000000000b9d34505fa06fa1e--