We have a frequently updated (peak > 5/sec) table with about 1000 rows.

We run VACCUM FULL on this table every 5 minutes.

The regular updates are not long in duration, and the vacuum is fast, so they
do not produce noticeable delays.

When we run a pg_dump on the database:

- the dump takes a long standing AccessShareLock lock on this table
(the database is large, and the table is locked for the duration).

- the regular updates run quite happily

- the VACUUM FULL comes along and asks for a AccessExclusiveLock
(which is not granted due to PG_DUMP).

- the regular updates hang until the dump completes

Is it possible to set up a vacuum to fail if a lock is not granted in
a limited period of time (eg. 1 minute)?

We could use lock files to synchronize our dumps with our vacuums, but
were hoping for a way of managing this within the DB so that ad-hoc dumps
will not cause a problem.


----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Philip Warner <pjw@rhyme.com.au> writes:
> Am I correct in saying that the FSM now tracks the entire table, and that
> the FSM parameters just determine how much is stored in memory?

No. Any free space that can't be remembered in FSM is lost to use.
(Not completely --- an update of a row on the same page can reuse it ---
but for the most part you want to make FSM large enough to remember all
the useful free space.)

> Is any type of opportunistic locking likely/planned for a future version
> (ie. a has lock, b asks for conflicting lock, c asks for lock that is OK
> with a but denied by b; so c's lock is allowed and b stays waiting).

That's deliberately disallowed by the current logic because of the risk
of starving b indefinitely. IIRC it would be a trivial code change to
do the other, but I doubt it's a good idea. The typical situation is
exactly a VACUUM that wants an exclusive lock, versus a fairly
continuous stream of shared lock requests for select/insert/update/delete.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

At 02:53 AM 30/01/2005, Tom Lane wrote:
>Philip Warner <pjw@rhyme.com.au> writes:
> > We have a frequently updated (peak > 5/sec) table with about 1000 rows.
> > We run VACCUM FULL on this table every 5 minutes.
>
>Plain vacuum (perhaps executed even more often, like
>once a minute) will cause fewer locking headaches.

We have done both in the past, but found some tables still just grew
(perhaps just because of infrequent locks that prevented the plain VACUUM).
I'll go back to the plain VACUUM and monitor the table growth.

Am I correct in saying that the FSM now tracks the entire table, and that
the FSM parameters just determine how much is stored in memory?


>I think you could do that by setting a statement timeout.

This would be a good solution if we still see growth with plain VACUUM.


Is any type of opportunistic locking likely/planned for a future version
(ie. a has lock, b asks for conflicting lock, c asks for lock that is OK
with a but denied by b; so c's lock is allowed and b stays waiting).




----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 03 5330 3172 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp.mit.edu:11371 |/


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster