pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCommiting after certain no of rows have been deleted
2+ messages / 2 participants
[nested] [flat]
* Commiting after certain no of rows have been deleted
@ 2005-12-16 19:37 Smita Mahadik <smita.mahadik@fedex.com>
0 siblings, 1 reply; 2+ messages in thread
From: Smita Mahadik @ 2005-12-16 19:37 UTC (permalink / raw)
To: pgsql-sql
Hi,
In my application I m deleteing large no of rows from table based on certain condition. This takes lot of time and if sometimes my application fails it starts all over again...since the coomit is done at the end of transactions. Is there a way i can do commit when certain no of rows have been deleted? For eg if i need to delete 2 million rows i should be able to commit after say 10,000 rows.
Regards
smita
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Commiting after certain no of rows have been deleted
@ 2005-12-21 09:38 Richard Huxton <dev@archonet.com>
parent: Smita Mahadik <smita.mahadik@fedex.com>
0 siblings, 0 replies; 2+ messages in thread
From: Richard Huxton @ 2005-12-21 09:38 UTC (permalink / raw)
To: Smita Mahadik <smita.mahadik@fedex.com>; +Cc: pgsql-sql
Smita Mahadik wrote:
> Hi,
>
> In my application I m deleteing large no of rows from table based on
> certain condition. This takes lot of time and if sometimes my
> application fails it starts all over again...since the coomit is done
> at the end of transactions. Is there a way i can do commit when
> certain no of rows have been deleted? For eg if i need to delete 2
> million rows i should be able to commit after say 10,000 rows.
No - the whole point of the transaction is it all works or none of it
does. But, if you have a suitable ID/Name/timestamp/other varying column
you can break it into smaller chunks:
DELETE FROM contacts WHERE surname LIKE 'A%';
DELETE FROM contacts WHERE surname LIKE 'B%';
...etc... don't forget a final "full" delete to catch anything you miss
DELETE FROM contacts;
Ideally you'll have something with an index on it.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-12-21 09:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-16 19:37 Commiting after certain no of rows have been deleted Smita Mahadik <smita.mahadik@fedex.com>
2005-12-21 09:38 ` Richard Huxton <dev@archonet.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