FAQ
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

Search Discussions

  • Sean Davis at Jul 4, 2007 at 8:57 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJul 4, '07 at 8:23p
activeJul 4, '07 at 8:57p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

David Monarchi: 1 post Sean Davis: 1 post

People

Translate

site design / logo © 2022 Grokbase