Обсуждение: COPY and index updating
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
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
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