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

Tue, 19 Oct 1999 12:18:35 -0700 (PDT)
Mike Mascari <mascarim@yahoo.com>
Re: [HACKERS] Re: [BUGS] Postgres problems with 6.4 / 6.5 (fwd) 