COPY FROM and TABLE LOCK question

From: Benjamin Franks <benjamin(at)dzhan(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: COPY FROM and TABLE LOCK question
Date: 2002-02-27 23:41:24
Message-ID: 20020227152624.M35867-100000@crimea.dzhan.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have an application that has a very high rate of row updates and
inserts. Currently that is my performance bottleneck. To circumvent
this, I'm investigating doing something like periodically dumping the
update/insert data to a file and then using the copy from command. The
raw file i/o and copy from file combination seems to be able to get a lot
more data into the database much faster.

does the copy from command do an exclusive access lock on the table it is
writing to? i'd like to do the following:
--delete all rows from the table
--drop table indexes
--copy from the file to the table
--recreate indexes

however, i don't want other applications to try to select, insert, or
update information from the table while the COPY FROM command is
executing. Should I explicitly lock the table in a BEGIN/END block, or is
naturally taken care of? If the table is exclusively locked, what happens
to another application that attempts to access the table...does it wait
(blocking/non-blocking), or does it return an error? Is there a function
that will determine if the table the application plans to access is currently locked?

Thanks,
--Ben

Browse pgsql-general by date

  From Date Subject
Next Message Rip 2002-02-27 23:45:12 Re: Re-write rules on views
Previous Message Tom Lane 2002-02-27 23:39:47 Re: abnormal exits: what am I overlooking?