On Tue, Dec 21, 2004 at 07:09:39PM -0500, Paul Tillotson wrote: > To use this system one would do this: > > (1) VACUUM KEEP_EARLY_FREE_PAGES mybloatedtable; -- item (a) > > (2) UPDATE mybloatedtable SET foo = foo WHERE ctid > '(nnnnn, 0)'; > --move tuples in end of the table to the front. > > (3) SHRINK TABLE mybloatedtable; -- item (b) > > Then repeat as many times as necessary to accomplish the desired shrinking.
It would be nice to have one command that would do all 3 (maybe in the backgound). I'd probably also keep KEEP_EARLY_FREE_PAGES available too, as it could be useful outside this context. ISTM that 2 and 3 are the brute-force way to accomplish this and that it could be done much more elegantly in the backend with some extra code. > P. S. Possible snags that I have thought of include: > > - I don't know for sure that UPDATE will use the free space map (will it > put the new tuple right back in the same page if there is room?) It's very likely it'll use the same page, but I don't really know. > - There is currently no < or > operator for tid's, so WHERE ctid > > '(nnnn, 0)' doesn't work as mentioned. AFAIK it should be easy to create < and > operators for tid's, though there's some hidden gotchas there with wraparound. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org