Обсуждение: REINDEX during a transaction
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
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
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