Thread: Default Access Exclusive Lock on Update?

This may be a newbie question, but according to the 7.4 docs, an ACCESS
EXCLUSIVE lock is only acquired by the ALTER TABLE, DROP TABLE,
REINDEX, CLUSTER, and VACUUM FULL commands.

However, when viewing pg_locks during the execution of a stored
procedure that does not perform any of the above commands, I see that
the table it is working on is locked by ACCESS EXCLUSIVE.

I have also tested that I can not perform a simple SELECT on the locked
table while the SP is executing.

Can anyone provide some insight?

Thanks

Centuries ago, Nostradamus foresaw when seth.m.green@gmail.com would write:
> For anyone that is interested, my problem was solved on another list.
> Turns out the TRUNCATE command that I run at the beginning of the SP
> creates and holds an access exclusive lock on the table for the entire
> duration of the SP.  I changed it to DELETE FROM and my problem was
> fixed.

I was thinking of mentioning it; didn't think to...

By the way, if you're taking the approach of emptying the table out
entirely this way, you'll want to either:

 a) Vacuum the table each time, or
 b) Make sure you do use TRUNCATE once in a while

Otherwise the number of dead tuples will grow ungraciously, and you'll
find efficiency drops :-(.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://linuxdatabases.info/info/slony.html
Inclusion of very old messages from others makes for an impressive show.
-- from the Symbolics Guidelines for Sending Mail

For anyone that is interested, my problem was solved on another list.
Turns out the TRUNCATE command that I run at the beginning of the SP
creates and holds an access exclusive lock on the table for the entire
duration of the SP.  I changed it to DELETE FROM and my problem was
fixed.

Full discussion here -> http://tinyurl.com/rp9jx