pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIssue with delete
7+ messages / 5 participants
[nested] [flat]
* Issue with delete
@ 2021-06-24 09:08 Rohit Rajput <rht.rajput@yahoo.com>
0 siblings, 2 replies; 7+ messages in thread
From: Rohit Rajput @ 2021-06-24 09:08 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with delete
@ 2021-06-24 09:22 Holger Jakobs <holger@jakobs.com>
parent: Rohit Rajput <rht.rajput@yahoo.com>
1 sibling, 1 reply; 7+ messages in thread
From: Holger Jakobs @ 2021-06-24 09:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with delete
@ 2021-06-24 09:34 Rohit Rajput <rht.rajput@yahoo.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rohit Rajput @ 2021-06-24 09:34 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Holger Jakobs <holger@jakobs.com>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with delete
@ 2021-06-24 14:03 Radoulov, Dimitre <cichomitiko@gmail.com>
parent: Rohit Rajput <rht.rajput@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Radoulov, Dimitre @ 2021-06-24 14:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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);
>
Wouldn't "delete from mytable where condition=true limit 1000" be
sufficient?
Regards
Dimitre
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with delete
@ 2021-06-24 14:48 Guillaume Lelarge <guillaume@lelarge.info>
parent: Radoulov, Dimitre <cichomitiko@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Guillaume Lelarge @ 2021-06-24 14:48 UTC (permalink / raw)
To: Radoulov, Dimitre <cichomitiko@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Le jeu. 24 juin 2021 à 16:02, Radoulov, Dimitre <cichomitiko@gmail.com> a
écrit :
>
> 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);
>
>
> Wouldn't "delete from mytable where condition=true limit 1000" be
> sufficient?
>
>
>
That syntax doesn't exist.
--
Guillaume.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with delete
@ 2021-06-24 14:55 Radoulov, Dimitre <cichomitiko@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 7+ messages in thread
From: Radoulov, Dimitre @ 2021-06-24 14:55 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On 24/06/2021 16.48, Guillaume Lelarge wrote:
> Le jeu. 24 juin 2021 à 16:02, Radoulov, Dimitre <cichomitiko@gmail.com
> <mailto:cichomitiko@gmail.com>> a écrit :
>
>
>> 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);
>>
>
> Wouldn't "delete from mytable where condition=true limit 1000" be
> sufficient?
>
>
>
> That syntax doesn't exist.
I should have tried the statement before asking, thanks!
I got confused by other databases (used it on Db2).
Best regards
Dimitre
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Issue with delete
@ 2021-07-12 20:30 Keith <keith@keithf4.com>
parent: Rohit Rajput <rht.rajput@yahoo.com>
1 sibling, 0 replies; 7+ messages in thread
From: Keith @ 2021-07-12 20:30 UTC (permalink / raw)
To: Rohit Rajput <rht.rajput@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Thu, Jun 24, 2021 at 5:08 AM Rohit Rajput <rht.rajput@yahoo.com> wrote:
> 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
>
As others said, there is no limit with the UPDATE/DELETE statements in
PostgreSQL. However, you can get around that using a common table
expression (CTE). We have a blog post on doing exactly this up at
CrunchyData
https://blog.crunchydata.com/blog/simulating-update-or-delete-with-limit-in-postgres-ctes-to-the-res...
Keith
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-07-12 20:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-06-24 09:08 Issue with delete Rohit Rajput <rht.rajput@yahoo.com>
2021-06-24 09:22 ` Holger Jakobs <holger@jakobs.com>
2021-06-24 09:34 ` Rohit Rajput <rht.rajput@yahoo.com>
2021-06-24 14:03 ` Radoulov, Dimitre <cichomitiko@gmail.com>
2021-06-24 14:48 ` Guillaume Lelarge <guillaume@lelarge.info>
2021-06-24 14:55 ` Radoulov, Dimitre <cichomitiko@gmail.com>
2021-07-12 20:30 ` Keith <keith@keithf4.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox