And, of course, in the next release (or in current),
you'll be able to do a:
TRUNCATE TABLE frequentlyusedtable;
INSERT INTO frequentlyusedtable SELECT...;
and not have to worry about ever-growing indexes,
grants, etc.
;-)
Mike Mascari
(mascarim@yahoo.com)
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Hi Andrew,
>
> > 1) Doing a pg_dump and psql -f on a database I get
> lots of errors saying
> > "query buffer max length of 16384 exceeded" and
> then (eventually) I get
> > a segmentation fault. The load lines don't seem
> to be that large (the
> > full insert statement, including error, is maybe
> 220 bytes. It seems
> > that if I split the dumped file into 40-line
> chunks and do a vacuum
> > after each one, I can get the whole thing to load
> without the errors.
>
> I think there must be some specific peculiarity in
> your data that's
> causing this; certainly lots of people rely on
> pg_dump for backup
> without problems. Can you provide a sample script
> that triggers the
> problem?
>
> > Further investigation reveals that if I do a
> VACUUM immediately after
> > the DROP TABLE that things are OK, but otherwise
> the pg_attribute* files
> > in the database directory just get bigger and
> bigger. This is even the
> > case when I do a VACUUM after every second 'DROP
> TABLE' - for the space
> > to be recovered, I have to VACUUM immediately
> after a DROP TABLE, which
> > doesn't seem right somehow.
>
> That does seem odd. If you just create and drop
> tables like mad then
> I'd expect pg_class, pg_attribute, etc to grow ---
> the rows in them
> that describe your dropped tables don't get recycled
> until you vacuum.
> But vacuum should reclaim the space.
>
> Actually, wait a minute. Is it pg_attribute itself
> that fails to shrink
> after vacuum, or is it the indexes on pg_attribute?
> IIRC we have a known
> problem with vacuum failing to reclaim space in
> indexes. There is a
> patch available that improves the behavior for
> 6.5.*, and I believe that
> improving it further is on the TODO list for 7.0.
>
> I think you can find that patch in the patch mailing
> list archives at
> www.postgresql.org, or it may already be in 6.5.2
> (or failing that,
> in the upcoming 6.5.3). [Anyone know for sure?]
>
> For user tables it's possible to work around the
> problem by dropping and
> rebuilding indexes every so often, but DO NOT try
> that on pg_attribute.
> As a stopgap solution you might consider not
> dropping and recreating
> your temp table; leave it around and just delete all
> the rows in it
> between uses.
>
> regards, tom lane
=====
__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com
