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 1lwLZV-0003OA-Dn for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 09:22:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lwLZU-0007Ig-24 for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 09:22:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lwLZT-0007IY-Pb for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 09:22:51 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lwLZS-0002cX-6T for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 09:22:51 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id B8BA33804D3; Thu, 24 Jun 2021 11:22:48 +0200 (CEST) Received: from rs.plausibolo.de ([127.0.0.1]) by localhost (h2367442.stratoserver.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id b-CbKVDzDcqP; Thu, 24 Jun 2021 11:22:48 +0200 (CEST) Received: from tuxedo.local.de (ip-37-201-154-46.hsi13.unitymediagroup.de [37.201.154.46]) by rs.plausibolo.de (Postfix) with ESMTPSA id 7489F3803EA; Thu, 24 Jun 2021 11:22:48 +0200 (CEST) Subject: Re: Issue with delete To: pgsql-admin@lists.postgresql.org References: <266147493.2758919.1624525689006.ref@mail.yahoo.com> <266147493.2758919.1624525689006@mail.yahoo.com> From: Holger Jakobs Message-ID: Date: Thu, 24 Jun 2021 11:22:47 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Thunderbird/78.11.0 MIME-Version: 1.0 In-Reply-To: <266147493.2758919.1624525689006@mail.yahoo.com> Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="eHV587A3oEtHCF0LdXUYZZZfhecYx3SdV" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --eHV587A3oEtHCF0LdXUYZZZfhecYx3SdV Content-Type: multipart/mixed; boundary="8WIlDJuy9cMiY1nNGgNw3cI8wbsckuiGc"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: Subject: Re: Issue with delete References: <266147493.2758919.1624525689006.ref@mail.yahoo.com> <266147493.2758919.1624525689006@mail.yahoo.com> In-Reply-To: <266147493.2758919.1624525689006@mail.yahoo.com> --8WIlDJuy9cMiY1nNGgNw3cI8wbsckuiGc Content-Type: multipart/alternative; boundary="------------45A2E570A2D66971B1CAD9C3" Content-Language: de-DE This is a multi-part message in MIME format. --------------45A2E570A2D66971B1CAD9C3 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: quoted-printable Am 24.06.21 um 11:08 schrieb Rohit Rajput: > Good day everyone, > > I have few tables where i need to delete above 50 million rows each.=20 > Since i dont want to delete them all in one shot and block my table=20 > for extended time, i am trying to use limit with delete but its giving = > me error every time. May i please get some assistance here? Thanks > > Here is what i am running: > > delete from mytable where id in (select id from mytable where=20 > condition=3Dtrue limit 1000); > > where i run this, i get following error: > > =C2=A0 =C2=A0 Error: current transaction is aborted, commands ignored u= ntil end=20 > of transaction block. > SQL state: 25P02. > > > I am running it in RDS. > > > TIA, Best, > Rohit This message stems from a previous error within the same transaction.=20 Unlike Oracle, PostgreSQL cannot execute commands within a transaction=20 after an error has occurred. Make sure that this statement is the first in your transaction or use it = in autocommit mode. --=20 Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 --------------45A2E570A2D66971B1CAD9C3 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Am 24.06.21 um 11:08 schrieb Rohit Rajput:
Good day everyone,

I have few tables where i need to delete above 50 million rows each. Since i dont want to delete them all in one shot and block my table for extended time, i am trying to use limit with delete but its giving me error every time. May i please get some assistance here? Thanks

Here is what i am running:=

delete from mytable where = id in (select id from mytable where condition=3Dtrue limit 1000);<= /div>

where i run this, i get following error:

=C2=A0 =C2=A0 Error: curre= nt transaction is aborted, commands ignored until end of transaction block.
SQL state: 25P02.


I am running it in RDS.


TIA, Best,
Rohit

This message stems from a previous error within the same transaction. Unlike Oracle, PostgreSQL cannot execute commands within a transaction after an error has occurred.

Make sure that this statement is the first in your transaction or use it in autocommit mode.


--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------45A2E570A2D66971B1CAD9C3-- --8WIlDJuy9cMiY1nNGgNw3cI8wbsckuiGc-- --eHV587A3oEtHCF0LdXUYZZZfhecYx3SdV Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCYNRO5wUDAAAAAAAKCRCKV5yTsxlhukqB AKDMNFtXI1HpGxYuQOZs1PyZFJOohQCgxvFNY3IP8O0HBH561Xe60jMdBVI= =yqZT -----END PGP SIGNATURE----- --eHV587A3oEtHCF0LdXUYZZZfhecYx3SdV--