REINDEX during a transaction

From: Ashley Moran <ashley(dot)moran(at)codeweavers(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: REINDEX during a transaction
Date: 2006-02-21 12:50:44
Message-ID: 200602211250.44749.ashley.moran@codeweavers.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Alvaro Herrera 2006-02-21 12:57:46 Re: Disk crash problems: postgres database not functioning anymore
Previous Message sandhya 2006-02-21 12:13:53 Re: problems to install PostgreSQL