Thread: COPY and index updating

COPY and index updating

From
"David Monarchi"
Date:
PG 8.2.4

Hello -

I need to append rows to an existing table that is in a production db.  There are 60 fields in the table and 17 indexes, including the primary key.  I cannot drop the indexes because the data is being actively accessed by users.  I can't create a duplicate table because we do not have enough disk storage to do so at this time.

I believe that the SQL COPY statement will delay updating the indexes until after it has inserted the rows because (again, I believe) the copy is treated as a single transaction that either succeeds or fails. 

I would appreciate knowing if I am correct about this.

Thank you,

David

Re: COPY and index updating

From
Sean Davis
Date:
David Monarchi wrote:
> PG 8.2.4
>
> Hello -
>
> I need to append rows to an existing table that is in a production
> db.  There are 60 fields in the table and 17 indexes, including the
> primary key.  I cannot drop the indexes because the data is being
> actively accessed by users.  I can't create a duplicate table because
> we do not have enough disk storage to do so at this time.
>
> I believe that the SQL COPY statement will delay updating the indexes
> until after it has inserted the rows because (again, I believe) the
> copy is treated as a single transaction that either succeeds or fails.

No, COPY does not delay updating indexes.  You are correct that a COPY
command is atomic (occurs inside a transaction).  See here for details:

http://www.postgresql.org/docs/8.2/static/populate.html

Sean