Re: pg_dump / Unique constraints

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>> Why can't COPY recognize for itself that rebuilding the indexes after
>> loading data is a better strategy than incremental index update?
>> (The simplest implementation would restrict this to happen only if the
>> table is empty when COPY starts, which'd be sufficient for pg_dump.)

> COPY would have to check to see if the table is already empty.

That's what I said ... or intended to say, anyway. If there's already
data then the tradeoff between incremental update and index rebuild is
not so obvious, and the easiest first implementation would just be to
always do incremental update in that case. Or we could add an option
to the COPY command to tell it which to do, and let the user do the
guessing ;-)

There'd also be a locking issue, now that I think about it: to do an
index rebuild, we'd have to be sure that no other transaction is adding
data to the table at the same time. So we'd need to get a stronger lock
than a plain write lock to do it that way. A COPY option is sounding
better and better...

regards, tom lane