Обсуждение: COPY FROM and TABLE LOCK question

I have an application that has a very high rate of row updates and
inserts.  I'm already delaying transaction commits until large numbers of
inserts/updates have happened.  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

Benjamin Franks <benjamin@dzhan.com> writes:
> does the copy from command do an exclusive access lock on the table it is
> writing to?

Certainly not.  It gets the same kind of lock as INSERT does, viz
RowExclusiveLock.

> 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.

You will want to grab an explicit lock on the table.  Generally, PG's
philosophy is to get the weakest allowable lock on a table.  If you
want a stronger lock, you can grab it via a LOCK command before doing
whatever you want to do.

Dropping indexes requires an exclusive lock, so if you insist on the
above recipe then nothing less than exclusive lock will do.

            regards, tom lane