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 1lwLke-0003nS-0Z for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 09:34:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lwLka-0007Vf-6j for pgsql-admin@arkaria.postgresql.org; Thu, 24 Jun 2021 09:34:20 +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 1lwLkZ-0007VU-PV for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 09:34:19 +0000 Received: from sonic310-19.consmr.mail.sg3.yahoo.com ([106.10.244.139]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1lwLkX-00084o-9E for pgsql-admin@lists.postgresql.org; Thu, 24 Jun 2021 09:34:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1624527252; bh=bhXj71AWuZzQuODAIftlcdbE5b/LAY7VMlvusijAjHo=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject:Reply-To; b=V8ZCXGgZidWBYhcVT3poaj7w+ZDV1iTSz9G1rbuio5Q81AOfkNpBgYRKj9qFjxGpGkhloTx+udR5MaTK/rZjDhi5yr3/9J+A8QqNwsAwRmM9S9ORlZPh6WRnLfYOEtq/ciI0yES5tJ10a1uYHVL7aTa6JSrYHCqP+X3zG47kQTkZpEKWJkfN4fddhMov74sMNTrLuXTS52TlVNCWFQVs0YhaXJHzjB09bwPewqGY7Cc8U8/PlUK/c1AYTfbrabcfJ5LHWBK7iNCu78vUoaNYKVlksG2NYZjXoubynfJSJx2hd0o8e6IUDtHozj2iVjjoSwkRTODTDhOLyt4mljQMzg== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1624527252; bh=TKv79O3+iFMzTxigL8IWPMRevAPJ+7+Dwe5iSH7IgNH=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=ly5KXimWlkvPZuTq5cQOZccvXi29driWjbhaTmGp4vvNo42/lwETQs3fU32XRlCbjrk48zUdveiwr8rsZFsp/71geaMTpF2i4wWNAtQ+W/t4EglQWDUk31lebACKH7pSNNjKEL+PBi43PHhGfo53cqv22dMQJSys7kkZEBboQhuqFASbr+BlvOuphsNeSrC3oXl7UE2mYXLEVZi0e+C19ZgcGK65CqV9odvRxdCAyGCKCCd64fqfU/n6PPmSDXFtSHsawllRuzBlMxOY2t8s/Ef4e3JevB3ztH6yK7xkxociEJuYQLqn8Z/vchCGr7/5+P7SQFhrJhix+w2YLByvYQ== X-YMail-OSG: 3fqU7s0VM1l7qKCWjLPfGiep8AH9luxNpx0frR3PI9sNmahNzztYTiE0UQT0pJP drks1xHMID9VpyEupRW9Ktm3qiJJei9QL1QKUOQfaY1j5jKco.KaSrAtXijX9_.0VhBgxTWzyhsX _EayusWRnn6.2F.CT2d93kQSZabo8ZrOmVQqQDjSdPmeLclqStBEdrgVBjoqKTqPQ5QBG3F9o5KN EdMXyb_ZpN4NMWJXIcvz_1SgDQcpn9JSONyqMaPX8uy9J0wtrM28yJbsg6gx62Y_hbJCvMQ.pFA4 zsqRp69oKU06HNmmiKLnR.E6C9KnSA1_TQeIZpftm5.esy9b_S2AjIFspDerGnCmoWKYZKejtq0c rcnMKCBU8Pd41VJPv_1UD8u7z_PCV7DMya5iK8hDFT36vCUcFucUfZ7.B4s70mhnJucDHspLf_VB xE0GB.UM3dq7Z.oes.AgVmpKnFHlFIa1iLPDlLSw40Hzdt_Kb8iSz0I3ueTe6BnJZXT7memo95mx d64JQzgph74FtESXwPSqvAgUtnZ1.GnnrujR6bYD8YDyonP5zN9DtLFPKroPfhfxJja9BEZPZgA0 cXrYQo1T_uD3qSfOXSrh_ayyrWFfiE9ouoYVtsrTOaK.wjkdCSRINn9fNdXkJzRD8SmEsPQ3jArX _iiNy_RSovbo3qT2Vilw37PTsQmhqeHoE_VP0FRcC_BpgMV4yvLOA3.FfZH8ME4CHNIcWQwSqvT1 dknBpEjzE3sOPVbzOZykjwXwj4q0U0.RBPxls.g3HBBxVpR46nIGcJkGEuuMtBX1tOPh.GyynJbj pd6HuL3um0UGM0ELE64G0vFCLl6vS9OtOUafA3QwQ_70JbNE8Vgjj5Mq3KH.wNmwkFWpDlp5Ummd OydC5UmRUPx9VVOAT3G6LmcpoxhyjbXzbA3PvteOrvXFRaTJS9sQD5o4.DLjD7NNQLi6Sgvnu8lT 0TpLeH3wnzX9LzhfRjTZFNlTjn_13ZJPuZsVsD2U2QNSXF58zFO1swG_CKWP7MyjnGbeQsjafUbr xWxpHc1qb4loLZawVVWxdOmVU.9Y.pJwxgCP6qR1Dwn0NVvmlLkL756WA_919z7ESvYAlwYn4Gq_ lo_EY7xwovti1sPeucnrp8OW4W6Ddf5qIUcR6EoOtrjGO5FFr5.K1Z33MCpbWtCyoevj6cMV8X46 ZE9duTRRHfQe9oAZYzP53i72UhemuaDKZeo_PPIXuJGz4almjqH2JDB.tV2to.az8lfNPCrTvi2G _mTnpg.Rvr2oQT8hwMVjoE60nuz55TDoQW9cHIW8TVdh7pmFFJOcHonjGSBg3jcK0vDnmN7JKTCX mlk65eMij2OpZ.cntJtWrEYjzlOy9nTVtAcEUdsn5IOFoo5ys8ZbeE4k3dSHDUmni_SY2ecjbSCR 8fQXoU9k6BHjnTaM2AwilCDiyjDgH3bBzWz4owrYdA3SMYw2ne8cFQ_FXbGICmsW_yE7B9s1j5Em HLyi2tcc_bkEg7FzzUwruOvoAeMn3qbryh8QD3JegXW_YdqtQDMc1DVs7CfTc8UqLjQpvf80PHBA 6J3GWwJzaNgmSxsBSvboIszEYRkrgPlroHUmGwpaYZZgGLb30OlgrL5d7HkxU2Isa8BMkNuImt3V SmVPDKO4ZXGQXa9m.yMxfQm9_Fg1841fNtzZdOySf3cWRvxSePZkPPhFc9tZ26UeKbuzXylkf7.z lVFVHCwYmaq9vClnG2wQFs4tWSXct3CBdcqjNb8uZMKhs2nPxKxeXTaTxXhKTDwAkrR7SWpsJgKZ _tATA1_mekyHndyQDEMeYU3fyBkvn3cx5IJp5dlej3mJFAk83i4MMDkerSS7bYCDqxJLB4qM4qT1 FLTvpF1EvqD5Li3mTxsnMwSuJa.Fux0dF1t771qer36TDaLBf3sBbV5X3Zf1fzZrj5S4x__bP91L cPKVCSuy9k2yuCFmG29nv.22vjhQn.xzodT5XFE36v2I.RdBdCBrPS3w8CzvpQ0BbTIxsqt6m4eu 8BYQ.pepKEIQFtliKiysucv3VUxndxKLstP9VyYGDQedF1vtEm2I5gByDuSApJ1RfKWk9QWlv5mP SshljvVVSgBl0n162sZZn0gowDDTncxwtgFgMpSUCWxV5huI50IPjQhRiBFoZ.fe6NpGMEFHVJN5 CslpfP_3gPQZy_Ka.s7L39tax81wSLXAxjt7KYsYEl9NRAez_D9RH4RbZfUPsQjF9m4__TgtDIZw 4yVk.2GcduvCs4f8Iuc5k8yh_FKvYbNly1XN9gGVnKS5U6wCVSrD6B9ag23VJkRwWsYnxCXHfUSr NjAJaDQqvgpCirwiZMPP8ow8z7FPZ.kjc.rXv2TsYvw_dSBEwFDDRufzlqAVsDVk0B5Yb7.afHZ1 YJJ5xZRuJo1Sp34S5Tk7YaeOWK5SRU.WF5xYWzRvngdIDRW1m.FuObXT04N7iQKvcf8UT52rA8xv 978Wbf8O21WiWKzapbvRRk11AfKq2jp_pJQ707BmkgDlz0c.KsIrw3rBkHU4r1R0lfkADoD0kJZL UbepvnSwVqJ8xNl5R9dNbqsJJcQ-- X-Sonic-MF: Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.sg3.yahoo.com with HTTP; Thu, 24 Jun 2021 09:34:12 +0000 Date: Thu, 24 Jun 2021 09:34:10 +0000 (UTC) From: Rohit Rajput To: "pgsql-admin@lists.postgresql.org" , Holger Jakobs Message-ID: <651524086.2768711.1624527250102@mail.yahoo.com> In-Reply-To: References: <266147493.2758919.1624525689006.ref@mail.yahoo.com> <266147493.2758919.1624525689006@mail.yahoo.com> Subject: Re: Issue with delete MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2768710_764733740.1624527250100" X-Mailer: WebService/1.1.18469 YMailNorrin Content-Length: 4665 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2768710_764733740.1624527250100 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Thanks Holger. My bad. appreciate it. On Thursday, 24 June, 2021, 02:53:02 pm IST, Holger Jakobs wrote: =20 =20 Am 24.06.21 um 11:08 schrieb Rohit Rajput: =20 =20 Good day everyone,=20 I have few tables where i need to delete above 50 million rows each. Sinc= e i dont want to delete them all in one shot and block my table for extende= d time, i am trying to use limit with delete but its giving me error every = time. May i please get some assistance here? Thanks=20 Here is what i am running:=20 delete from mytable where id in (select id from mytable where condition= =3Dtrue limit 1000);=20 where i run this, i get following error:=20 =C2=A0 =C2=A0 Error: current transaction is aborted, commands ignored unt= il end of transaction block. SQL state: 25P02.=20 =20 I am running it in RDS.=20 =20 TIA, Best, Rohit =20 This message stems from a previous error within the same transaction. Unlik= e Oracle, PostgreSQL cannot execute commands within a transaction after an = error has occurred. =20 Make sure that this statement is the first in your transaction or use it in= autocommit mode. =20 =20 --=20 Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 =20 ------=_Part_2768710_764733740.1624527250100 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
Thanks Holger. My bad. appreciate it.

On Thursday, 24 June, 2021, 02:53:02 pm IST, Holger Jakobs <holger@jakobs.com> wrote:


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=true limit 1000);

where i run this, i get following error:

    Error: current 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.


-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
------=_Part_2768710_764733740.1624527250100--