On Wed, 28 Feb 2007, Tom Lane wrote:
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
Martijn van Oosterhout wrote:
Generally, rolling back a transaction doesn't reduce the amount of disk
used. Only VACUUM FULL actually shrinks relations.
Advertising
Right, but what I mean was -- if we rollback because we hit quota we
could potentially cause even more maintenance to have to happen (vacuum).
It's worse than that, because VACUUM FULL will actually bloat the
indexes on the way to being able to reduce the table size (since it has
to make new index entries for rows it moves). If the limit is strictly
enforced then a user who has reached his quota is really totally
screwed: the only easy way to get back under quota will be to completely
drop tables, ie, discard data. VACUUM probably won't reduce the
physical table size much, and VACUUM FULL will fail, and other
approaches such as CLUSTER won't work either.
[ thinks for a bit... ] Possibly you could drop all your indexes,
VACUUM FULL, reconstruct indexes. But it would be painful and would
certainly prevent you from working normally until you finish that
maintenance. If the quota limit includes temp files you might find that
rebuilding the indexes fails, too, because of the transient space needed
to rebuild.
Plus, all that forced maintenance activity will be degrading response
for other users while it happens.
On the whole I'm not convinced that a quota is a good idea.
On database level it's possible to have soft user quote, just measure
disk usage and warn user if database size is over. This could be realized
using external tools. But Sergey wanted finer granulation. As a workaround,
we could have function which return size of db objects owned by user and
let administrator run cron job to realize soft quota. This will not provide
foundation for enterprize level of QoS, but we certainly don't want to
introduce too much overhead. It's interesting and challenging task though.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
