Re: COPY and index updating

From: Sean Davis
Subject: Re: COPY and index updating
Date: ,
Msg-id: 468C09AD.4000500@mail.nih.gov
(view: Whole thread, Raw)
In response to: COPY and index updating  ("David Monarchi")
List: pgsql-novice

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


pgsql-novice by date:

From: "s anwar"
Date:
Subject: Postgres not using indices defined on my table with certain queries using "in"
From: Tom Lane
Date:
Subject: Re: Postgres not using indices defined on my table with certain queries using "in"