References: 
 <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
In-Reply-To: 
 <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: 7bit
Content-Type: multipart/alternative;
	boundary=Apple-Mail-AD076FF1-FF4E-4562-B289-440FED34441B
Message-Id: <2000E26E-35FC-4801-90F3-3CFBDA7B41DF@yahoo.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Savepoints in transactions for speed?
Date: Tue, 27 Nov 2012 18:16:23 -0600
To: Mike Blackwell <mike.blackwell@rrd.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


--Apple-Mail-AD076FF1-FF4E-4562-B289-440FED34441B
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

Mike,

Is there anything that the 1.5 million rows have in common that would allow y=
ou to use partitions?  if so, you could load the new data into a partition  a=
t your leisure, start a transaction, alter the partition table with the old d=
ata to no longer inherit from the parent, alter the new partition table to
inherit from the parent, commit, then drop the old table.  This operation wo=
uld be very fast, the users probably won't even notice.=20

Bob Lunney

On Nov 27, 2012, at 4:04 PM, Mike Blackwell <mike.blackwell@rrd.com> wrote:

> I need to delete about 1.5 million records from a table and reload it in o=
ne transaction.  The usual advice when loading with inserts seems to be grou=
p them into transactions of around 1k records.  Committing at that point wou=
ld leave the table in an inconsistent state.  Would issuing a savepoint ever=
y 1k or so records negate whatever downside there is to keeping a transactio=
n open for all 1.5 million records, or just add more overhead?
>=20
> The data to reload the table is coming from a Perl DBI connection to a dif=
ferent database (not PostgreSQL) so I'm not sure the COPY alternative applie=
s here.
>=20
> Any suggestions are welcome.
>=20
>=20
> Mike

--Apple-Mail-AD076FF1-FF4E-4562-B289-440FED34441B
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head></head><body bgcolor=3D"#FFFFFF"><div>Mike,</div><div><br></div>=
<div>Is there anything that the 1.5 million rows have in common that would a=
llow you to use partitions? &nbsp;if so, you could load the new data into a p=
artition &nbsp;at your leisure, start a transaction, alter the partition tab=
le with the old data to no longer inherit from the parent, alter the new par=
tition table to</div><div>inherit from the parent, commit, then drop the old=
 table. &nbsp;This operation would be very fast, the users probably won't ev=
en notice.&nbsp;</div><div><br>Bob Lunney</div><div><br>On Nov 27, 2012, at 4=
:04 PM, Mike Blackwell &lt;<a href=3D"mailto:mike.blackwell@rrd.com">mike.bl=
ackwell@rrd.com</a>&gt; wrote:<br><br></div><div></div><blockquote type=3D"c=
ite"><div>I need to delete about 1.5 million records from a table and reload=
 it in one transaction. &nbsp;The usual advice when loading with inserts see=
ms to be group them into transactions of around 1k records. &nbsp;Committing=
 at that point would leave the table in an inconsistent state. &nbsp;Would i=
ssuing a savepoint every 1k or so records negate whatever downside there is t=
o keeping a transaction open for all 1.5 million records, or just add more o=
verhead?<div>
<br></div><div>The data to reload the table is coming from a Perl DBI connec=
tion to a different database (not PostgreSQL) so I'm not sure the COPY alter=
native applies here.</div><div><br></div><div>Any suggestions are welcome.<b=
r clear=3D"all">
<p style=3D"line-height:12pt"><br></p><p style=3D"line-height:12pt">Mike</p>=

</div>
</div></blockquote></body></html>=

--Apple-Mail-AD076FF1-FF4E-4562-B289-440FED34441B--