From smita.mahadik@fedex.com Fri Dec  16 19:37:52 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 7130B9DCD9E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 16 Dec 2005 15:37:52 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 25455-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 16 Dec 2005 15:37:51 -0400 (AST)
Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159])
	by postgresql.org (Postfix) with ESMTP id A9F5F9DCDA6
	for <pgsql-sql@postgresql.org>; Fri, 16 Dec 2005 15:37:49 -0400 (AST)
Received: from mx21.sac.fedex.com (mx21.sac.fedex.com [199.81.218.126])
	by svr4.postgresql.org (Postfix) with ESMTP id DDDB05AF1B1
	for <pgsql-sql@postgresql.org>; Fri, 16 Dec 2005 19:37:50 +0000 (GMT)
Received: from inet06.prod.fedex.com (inet06.prod.fedex.com [199.81.10.64])
	by mx21.sac.fedex.com (8.13.4/8.13.4) with ESMTP id jBGJbi7T043422
	for <pgsql-sql@postgresql.org>; Fri, 16 Dec 2005 13:37:44 -0600 (CST)
	(envelope-from smita.mahadik@fedex.com)
Received: from netsvr01 (netsvr01.prod.fedex.com [199.81.10.27])
	by inet06.prod.fedex.com (8.12.11/8.12.11) with ESMTP id jBGJbgh5002269
	for <pgsql-sql@postgresql.org>; Fri, 16 Dec 2005 13:37:43 -0600 (CST)
Received: from smita ([199.82.234.134]) by emmsvr01.prod.fedex.com
	(iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep  8 2003))
	with SMTP id <0IRL00BU3VUU9R@emmsvr01.prod.fedex.com> for
	pgsql-sql@postgresql.org; Fri, 16 Dec 2005 13:37:42 -0600 (CST)
Date: Fri, 16 Dec 2005 13:37:33 -0600
From: Smita Mahadik <smita.mahadik@fedex.com>
Subject: Commiting after certain no of rows have been deleted
To: pgsql-sql@postgresql.org
Message-id: <006d01c60278$2943d440$86ea52c7@smita>
MIME-version: 1.0
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
Content-type: multipart/alternative;
	boundary="Boundary_(ID_XTfSFQOKjVpAGukdoHFUFA)"
X-Priority: 3
X-MSMail-priority: Normal
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.441 required=5 tests=[DNS_FROM_RFC_POST=1.44,
	HTML_MESSAGE=0.001]
X-Spam-Score: 1.441
X-Spam-Level: *
X-Archive-Number: 200512/187
X-Sequence-Number: 23729

This is a multi-part message in MIME format.

--Boundary_(ID_XTfSFQOKjVpAGukdoHFUFA)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

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

--Boundary_(ID_XTfSFQOKjVpAGukdoHFUFA)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2627" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>Regards</FONT></DIV>
<DIV><FONT face=Arial size=2>smita</FONT></DIV></BODY></HTML>

--Boundary_(ID_XTfSFQOKjVpAGukdoHFUFA)--



From dev@archonet.com Wed Dec  21 09:39:19 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 9982D9DCBB0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 21 Dec 2005 05:39:19 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 86789-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 21 Dec 2005 05:39:20 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 497599DCBBB
	for <pgsql-sql@postgresql.org>; Wed, 21 Dec 2005 05:39:16 -0400 (AST)
Received: from mainbox.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP
	id F3EEF4130C3; Wed, 21 Dec 2005 09:38:22 +0000 (GMT)
Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17])
	by mainbox.archonet.com (Postfix) with ESMTP id 17FE515EA4;
	Wed, 21 Dec 2005 09:38:28 +0000 (GMT)
Message-ID: <43A92293.6000309@archonet.com>
Date: Wed, 21 Dec 2005 09:38:27 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 1.5 (X11/20051025)
MIME-Version: 1.0
To: Smita Mahadik <smita.mahadik@fedex.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Commiting after certain no of rows have been deleted
References: <006d01c60278$2943d440$86ea52c7@smita>
In-Reply-To: <006d01c60278$2943d440$86ea52c7@smita>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.01 required=5 tests=[AWL=0.010]
X-Spam-Score: 0.01
X-Spam-Level: 
X-Archive-Number: 200512/191
X-Sequence-Number: 23733

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