pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
REINDEX during a transaction
3+ messages / 3 participants
[nested] [flat]

* REINDEX during a transaction
@ 2006-02-21 12:50 Ashley Moran <ashley.moran@codeweavers.net>
  2006-03-01 15:23 ` Re: REINDEX during a transaction Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Ashley Moran @ 2006-02-21 12:50 UTC (permalink / raw)
  To: pgsql-admin

I'm sure this is a simple question but the docs only go as far as "For all 
indexes except the shared system catalogs, REINDEX is crash-safe and 
transaction-safe."

I have a transaction that imports about 2.3 GB of data as pairs of DELETE 
FROM / COPY.

What is the difference between reindexing tables during this transaction vs 
after? ie 

BEGIN WORK;
DELETE FROM X; COPY X ...; REINDEX TABLE X;
DELETE FROM Y; COPY Y ...; REINDEX TABLE Y;
DELETE FROM Z; COPY Z ...; REINDEX TABLE Z;
COMMIT;

vs

BEGIN WORK;
DELETE FROM X; COPY X ...;
DELETE FROM Y; COPY Y ...;
DELETE FROM Z; COPY Z ...;
COMMIT;

REINDEX TABLE X;
REINDEX TABLE Y;
REINDEX TABLE Z;


Thanks 

Ashley



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: REINDEX during a transaction
  2006-02-21 12:50 REINDEX during a transaction Ashley Moran <ashley.moran@codeweavers.net>
@ 2006-03-01 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-03-01 19:21   ` Re: REINDEX during a transaction Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2006-03-01 15:23 UTC (permalink / raw)
  To: Ashley Moran <ashley.moran@codeweavers.net>; +Cc: pgsql-admin

Ashley Moran <ashley.moran@codeweavers.net> writes:
> BEGIN WORK;
> DELETE FROM X; COPY X ...; REINDEX TABLE X;
> DELETE FROM Y; COPY Y ...; REINDEX TABLE Y;
> DELETE FROM Z; COPY Z ...; REINDEX TABLE Z;
> COMMIT;

Why don't you use TRUNCATE?  Why do you think you need REINDEX at all?

If you do need it, you'd be best off to drop the indexes, truncate,
copy, re-create the indexes.  See
http://www.postgresql.org/docs/8.1/static/populate.html#POPULATE-RM-INDEXES

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: REINDEX during a transaction
  2006-02-21 12:50 REINDEX during a transaction Ashley Moran <ashley.moran@codeweavers.net>
  2006-03-01 15:23 ` Re: REINDEX during a transaction Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-03-01 19:21   ` Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Jim C. Nasby @ 2006-03-01 19:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ashley Moran <ashley.moran@codeweavers.net>; pgsql-admin

On Wed, Mar 01, 2006 at 10:23:41AM -0500, Tom Lane wrote:
> Ashley Moran <ashley.moran@codeweavers.net> writes:
> > BEGIN WORK;
> > DELETE FROM X; COPY X ...; REINDEX TABLE X;
> > DELETE FROM Y; COPY Y ...; REINDEX TABLE Y;
> > DELETE FROM Z; COPY Z ...; REINDEX TABLE Z;
> > COMMIT;
> 
> Why don't you use TRUNCATE?  Why do you think you need REINDEX at all?
> 
> If you do need it, you'd be best off to drop the indexes, truncate,
> copy, re-create the indexes.  See
> http://www.postgresql.org/docs/8.1/static/populate.html#POPULATE-RM-INDEXES

And to answer some of your original question, I don't believe there's
any advantage to wrapping the REINDEXes into the transaction. If can't
use Tom's suggestion of dropping the indexes before the COPY, you might
want to instead create new indexes with the same definition of existing
ones, and then drop the old ones; readers of the table will react
differently to the two.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2006-03-01 19:21 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-21 12:50 REINDEX during a transaction Ashley Moran <ashley.moran@codeweavers.net>
2006-03-01 15:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-01 19:21   ` Jim C. Nasby <jnasby@pervasive.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