Re: transaction timeout - Mailing list pgsql-general

Dr NoName wrote:

>>Sure.  Like this:
>>
>>Client A accesses table T, and "hangs."
>>Client B attempts to get an ACCESS EXCLUSIVE lock on
>>table T in
>>preparation for VACUUM FULL.
>>Client C connects to the database and waits for
>>client B to get and
>>release his lock on table T.
>>Client D connects to the database and waits for
>>client B to get and
>>release his lock on table T.
>>Client E connects to the database and waits for
>>client B to get and
>>release his lock on table T.
>>etc...
>>
>>
>
>oh! my! gawd!
>Finally a clear explanation that makes perfect sense.
>Now why did it take so long?
>
>
>
I think you did not get the explanation sooner because you did not
mention that you were doing VACUUM FULL from a cron job, and you got
drawn into an argument about what postgres should do rather than WHY it
did what it did.

I had a lot of sympathy with your position as something similar happened
to me, but you did not give the detail that allowed me to guess (i.e.,
the VACUUM FULL) until several exchanges had taken place.

>So all I need to do is take out the FULL? Is regular
>VACUUM sufficient? How often do we need FULL? (I know
>it's a stupid question without providing some more
>context, but how can I estimate it?)
>
>
>
You never have to run VACUUM FULL.  The only thing that it does that
plain ole VACUUM does not is that it can actually shrink a table.   If
your table doesn't need shrinking you don't need VACUUM FULL. It is
really only for people in desperate straits who let a table get way too
large without running regular VACUUM on it.

As another poster already pointed out, you need to set the free space
map configuration high enough.  The general process is to let the
database go 1 day without VACUUMing, and then run VACUUM VERBOSE.  This
will print a lot of information about each table that you don't really
care about, and then at the end, it will tell you how many pages you
need in the free space map like this:

INFO:  free space map: 248 relations, 242 pages stored; 4032 total pages
needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.

This example from my box shows that I have the free space tracking 1000
relations and 20000 pages, but I only need 248 relations and 4000
pages.  In your own case, unless you are short on RAM, multiplying the
amount it says you need by a factor of 4 is probably a good rule of thumb.

>Out of curiousity, how is lock acquisition implemented
>in postgresql? All the processes have to go through
>some sort of queue, so that locks are granted in FIFO
>order, as you described. Just trying to understand it
>better.
>
>
>
Not all locks--only locks that conflict with each other must wait on
each other in this fashion.  If every lock did, then you would only need
1 lock in the whole database, as it would protect against any sort of
concurrent access. :)

There are two main kinds of locks--shared locks and exclusive locks.
Multiple shared locks can be granted on the same table or row, but only
one exclusive lock can be.

select, insert, update, and delete, and regular vacuum take no exclusive
locks, hence the excellent general performance of postgres.* (see below)

The important thing to remember is that if 1 process is waiting trying
to get an exclusive lock on some table, then every other process asking
for shared lock on the same table will have to wait.

I know offhand that VACUUM FULL, ALTER TABLE, and REINDEX take exclusive
locks.  These are probably the only commands that people would be
tempted to run via a cron job.

You might find this informative:
http://www.postgresql.org/docs/8.0/interactive/explicit-locking.html

Regards,
Paul Tillotson

(*) Actually, you can get this kind of deadlock with just UPDATES.
Suppose that your web application does:

BEGIN;
UPDATE hits SET count = count + 1 WHERE page = 'somepage.aspx';
[other stuff]
COMMIT;

If you have another transaction that tries to update the SAME ROW, then
it will wait for the first transaction to finish.  Thus, if your client
does the update and then hangs while doing [other stuff], every other
client that tries to update that row will block until the transaction
commits, even though the rest of the database will be unaffected.