Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pqrKL-0007j9-Er for psycopg@arkaria.postgresql.org; Mon, 24 Apr 2023 08:13:37 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pqrKJ-00066R-W9 for psycopg@arkaria.postgresql.org; Mon, 24 Apr 2023 08:13:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pqk2i-0000ex-9u for psycopg@lists.postgresql.org; Mon, 24 Apr 2023 00:26:56 +0000 Received: from mail-pl1-x633.google.com ([2607:f8b0:4864:20::633]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pqk2b-001XEl-BJ for psycopg@postgresql.org; Mon, 24 Apr 2023 00:26:55 +0000 Received: by mail-pl1-x633.google.com with SMTP id d9443c01a7336-1a66911f5faso32751425ad.0 for ; Sun, 23 Apr 2023 17:26:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1682296007; x=1684888007; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=EyTEQkH1+CcBNnv7jCPqGFaykDynmqoKs1G+SZybyOY=; b=MkOrvN81yMAMAJ26Hrtdul68FebnXyl2AIcMJM+mijIGbDE6sJmX9l/qcuqjWLwu9c DmNg/EemI9BeCob3dBdk3lmkr7WCVFaDn+A3Q6EhX186uxZDBUhkqSp3hxvWTbghLSqx cEnS6cdnJOhojCvLnAeR7cAqSnKsLQqtXKCnVDQZjPtjgUWcFDM7da5iu1BUkTacgirB /w1PxlnbfT9TR57KnSZ69jDZJhBh6pAvnmQBcqM1QvgfBoPCl/cHmoeJqBAFtlfALORc +5nVPw6jF7ocBa9LmKknKPjbdIJPDuvY6ac5CkEnaCZjaBRKOtHBkYio8+lWu1TugWBi kwbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1682296007; x=1684888007; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=EyTEQkH1+CcBNnv7jCPqGFaykDynmqoKs1G+SZybyOY=; b=f5git5OtEvIgnceB4XIgnMBPULaaJZ3GNeeGRfZHxH4WNEZvnk37oPX1BownpC2GsN AjpCUX34XsDw+rrYmM9B+SOnBnj1z3h6iUD2ualbv2kJSpgzUmOlfWXof82R/be3bjAk hXs1li0Ch13sqyn+uSBS1cZ7hKITjxoGvTRtLobyC1oUe0diDTudmfjiSrJQMAFUE2iF hvxT3t42lnAgpG0XXf/SDgZxCtXfhRVAcu0NtucxOmR238BwlLlG5qlIBXmmBGUJ4r6z 1umxsrCv31s/uXOiINU9+5OOEzSiCakopvXXm6wJEXa36tEejok/NZEn+hd4L+y2h5c2 IxJA== X-Gm-Message-State: AAQBX9c7zD8XKMoiSMGL+amgETp+4T2WOlrTjS7U1CDHYnHVv5x0GfcH MIRlI55Q0KP7ysR13XbmarRjxHOM9qZLMpKzttM= X-Google-Smtp-Source: AKy350ZNDNAQFYTP5IUfrAwQtw+agUOLBVfWNxtqd6Pd7X5zVs/nE692gzhBLtjS1UBIY5IlVzC5cWLrKmMYJIG9W8c= X-Received: by 2002:a17:902:e30b:b0:1a9:734f:360a with SMTP id q11-20020a170902e30b00b001a9734f360amr2239419plc.13.1682296007401; Sun, 23 Apr 2023 17:26:47 -0700 (PDT) MIME-Version: 1.0 References: <237c1e3f-4118-6ffc-6e0b-ddce824f1f55@aklaver.com> In-Reply-To: From: "Michael P. McDonnell" Date: Sun, 23 Apr 2023 19:26:37 -0500 Message-ID: Subject: Re: Consecutive Inserts Freeze Execution of Psycopg3 To: Adrian Klaver Cc: psycopg@postgresql.org Content-Type: multipart/alternative; boundary="000000000000eda78f05fa0a0feb" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000eda78f05fa0a0feb Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Thanks Adrian - I appreciate it; and I've been pouring through documentation to try and get to this point. I can't help but feel I'm doing it "wrong" but no website I can find recently seems to have a "right" way of doing things that's reasonably kept up. It would be nice if "wrong" had a way of shooting me in the foot with verbose errors or warnings. -Mike On Sun, Apr 23, 2023 at 7:15=E2=80=AFPM Adrian Klaver wrote: > On 4/23/23 14:55, Michael P. McDonnell wrote: > > That helped a ton, I don't understand why I've had to rewrite the crap > > out of all of this to get it to work (dropping SqlAlchemy, upgrading > > from psycopg2 to psycopg, etc...) but it's working now and I can work > > around it. Thank you. > > Well: > > 1) SQLAlchemy is an ORM that tries to make all databases look the same. > > 2) psycopg2 !=3D psycopg. For details see: > > https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html > > 3) It would have been more of a surprise if you did not have to change > anything. > > 4) And this > > with self.connection.cursor() as conn: > > was just plain wrong. You where trying to make a cursor be a connection > and that is not going to work. > > > > > > > > On Sun, Apr 23, 2023 at 4:25=E2=80=AFPM Adrian Klaver > > wrote: > > > > 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 =3D queries[qi] > > > qparams =3D 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 wi= th > > 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 quer= y. > > > > > > 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 stick= s. > > > > -- > > Adrian Klaver > > adrian.klaver@aklaver.com > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000eda78f05fa0a0feb Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Adrian -=C2=A0
I appreciate it; and I've be= en pouring through documentation to try and get to this point.
I = can't help but feel I'm doing it "wrong" but no website I= can find recently seems to have a "right" way of doing things th= at's reasonably kept up.
It would be nice if "wrong"= ; had a way of shooting me in the foot with verbose errors or warnings.

-Mike

On Sun, Apr 23, 2023 at 7:15=E2=80=AFPM Ad= rian Klaver <adrian.klaver@= aklaver.com> wrote:
On 4/23/23 14:55, Michael P. McDonnell wrote:
> That helped a ton, I don't understand why I've had to rewrite = the crap
> out of all of this to get it to work (dropping SqlAlchemy, upgrading <= br> > from psycopg2 to psycopg, etc...) but it's working now and I can w= ork
> around it. Thank you.

Well:

1) SQLAlchemy is an ORM that tries to make all databases look the same.

2) psycopg2 !=3D psycopg. For details see:

https://www.psycopg.org/psycopg3/docs/bas= ic/from_pg2.html

3) It would have been more of a surprise if you did not have to change
anything.

4) And this

with self.connection.cursor() as conn:

was just plain wrong. You where trying to make a cursor be a connection and that is not going to work.


>
>
> On Sun, Apr 23, 2023 at 4:25=E2=80=AFPM Adrian Klaver <adrian.klaver@aklaver.co= m
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 4/23/23 13:45, Michael P. McDonnell wrote:
>=C2=A0 =C2=A0 =C2=A0 > Python 3.10.6
>=C2=A0 =C2=A0 =C2=A0 > psycopg library 3.1.8
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Running consecutive inserts sourced in files.=
>=C2=A0 =C2=A0 =C2=A0 > All inserts are of the same format:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > INSERT INTO _____ (field1, field2, field3) >=C2=A0 =C2=A0 =C2=A0 > SELECT field1, field2, field3 FROM ____, Join= ___, join ___ etc...
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > The code I've written is this:
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > for qi in range(qlen):
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 query =3D queries[qi]
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 qparams =3D params[qi]
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 with self.connection.cursor() as conn:
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 conn.execute(query, qparams)
>
>=C2=A0 =C2=A0 =C2=A0In above you are running the context manager(with) = over the cursor not
>=C2=A0 =C2=A0 =C2=A0the connection. This will not automatically commit = the transaction. You
>=C2=A0 =C2=A0 =C2=A0will need to either explicitly do connection.commit= () or use the with
>=C2=A0 =C2=A0 =C2=A0over the connection per:
>
>=C2=A0 =C2=A0 =C2=A0https://www.psy= copg.org/psycopg3/docs/basic/transactions.html
>=C2=A0 =C2=A0 =C2=A0<https://www= .psycopg.org/psycopg3/docs/basic/transactions.html>
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > When I run the queries in dbeaver - the first= query takes 120s (it's
>=C2=A0 =C2=A0 =C2=A0 > 1.9M rows), the second query takes 2s (7000 r= ows).
>=C2=A0 =C2=A0 =C2=A0 > When I run the queries in python - it freezes= on the second query.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Any guidance on how to attack this would be a= wesome as I have
>=C2=A0 =C2=A0 =C2=A0re-written
>=C2=A0 =C2=A0 =C2=A0 > my code a dozen times and am just slinging mu= d to see what sticks.
>
>=C2=A0 =C2=A0 =C2=A0--
>=C2=A0 =C2=A0 =C2=A0Adrian Klaver
>=C2=A0 =C2=A0 =C2=A0adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com><= br> >

--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000eda78f05fa0a0feb--