Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EC85A2E0316 for ; Fri, 30 Nov 2007 17:42:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 36505-09 for ; Fri, 30 Nov 2007 17:42:16 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id EC3A22E033F for ; Fri, 30 Nov 2007 17:42:15 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so1971823nfh for ; Fri, 30 Nov 2007 13:42:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=dZC9jo5B8szkAbvM3l2v/T+EOqvPK+kWY8dFSpy6Km0=; b=tXJueF4oBLF0Ixu3q4USHL+70nrgrtBkVDziC455xv61ofD7UqIUMu1/3FRg/pVQgfr6uokSYd5Rw65YBe6e7rmWIb0H5QEaHJ1jpyRZrJH4ikRDxIt998FKXpieDPASXSl05isFk9UoSXKDxGvpJu65mPNKqipQ/cQaWQwD27U= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=qNmz12EsMFHsb8a9czPha6uh49VAgGiGUHUt4pR+rdyjAqgUxZEuQsdAsvJD0tpFqgCl0ZVWagql8ba04BH6Vi57jCt10tIEFop7oC/njEXfi7K+mCrRjTHvmQsYj+HEutZaE4bwLlcmYjWy8TF7ufUHhpviQKlIBkscd4PbBiQ= Received: by 10.78.122.16 with SMTP id u16mr9481976huc.1196458933970; Fri, 30 Nov 2007 13:42:13 -0800 (PST) Received: by 10.78.151.9 with HTTP; Fri, 30 Nov 2007 13:42:13 -0800 (PST) Message-ID: Date: Sat, 1 Dec 2007 02:42:13 +0500 From: "Usama Dar" To: "Campbell, Lance" Subject: Re: Enhancement request Cc: "posgres support" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_75_29882349.1196458933925" References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/265 X-Sequence-Number: 27508 ------=_Part_75_29882349.1196458933925 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Dec 1, 2007 12:46 AM, Campbell, Lance wrote: > Could you please add to your to do list a schema parameter for vacuum? > > > > Example: > > > > VACUUM SCHEMA xyz; > > > > PostgreSQL would get a list of all of the tables found in the schema. It > would then loop through vacuuming each table in the schema. > > > > I found today that I did not have max_fsm_pages set high enough in order > to vacuum the entire database. I vacuum daily to avoid problems. After > realizing that I cannot vacuum the entire database now, I then had to do it > at the table level. I had to go through all 13 of my schemas and list each > table I found into an SQL vacuum script. This was a real waist of time. I > will eventually restart PostgreSQL with an increased value for max_fsm_pages > so this will not be an issue. > > > > But until then I will have to run a new script. I created a script with > 420 SQL vacuum statements at the table level. I would have preferred to > create 13 vacuum SQL statements at the schema level. > i generally use an sql to generate vaccum statement for all tables in a schema and then simple copy paste the output in an sql file and then just run it. something like following postgres=# select 'vacuum ' || relname || ';' from pg_class c , pg_namespace n where c.relnamespace = n.oid and n.nspname='public' and relkind='r'; ?column? -------------- vacuum b; vacuum a; vacuum test; vacuum xyz; vacuum foo; (5 rows) You can redirect this output to a file and then strip off the heading and the feedback of (5 rows) and just run that file > > Thanks for considering this enhancement, > > > > Lance Campbell > > Project Manager/Software Architect > > Web Services at Public Affairs > > University of Illinois > > 217.333.0382 > > http://webservices.uiuc.edu > > > -- Usama Munir Dar http://linkedin.com/in/usamadar Consultant Architect Cell:+92 321 5020666 Skype: usamadar ------=_Part_75_29882349.1196458933925 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On Dec 1, 2007 12:46 AM, Campbell, Lance <lance@uiuc.edu> wrote:

Could you please add to your to do list a schema parameter for vacuum?

 

Example:

 

VACUUM SCHEMA xyz;

 

PostgreSQL would get a list of all of the tables found in the schema.  It would then loop through vacuuming each table in the schema.

 

I found today that I did not have max_fsm_pages set high enough in order to vacuum the entire database.  I vacuum daily to avoid problems.  After realizing that I cannot vacuum the entire database now, I then had to do it at the table level.  I had to go through all 13 of my schemas and list each table I found into an SQL vacuum script.  This was a real waist of time.  I will eventually restart PostgreSQL with an increased value for max_fsm_pages so this will not be an issue. 

 

But until then I will have to run a new script.  I created a script with 420 SQL vacuum statements at the table level.  I would have preferred to create 13 vacuum SQL statements at the schema level.


i generally use an sql to generate vaccum statement for all tables in a schema and then simple copy paste the output in  an sql file and then just run it. something like following

postgres=# select 'vacuum ' || relname || ';'
from pg_class c , pg_namespace n
where c.relnamespace = n.oid
and n.nspname='public' and relkind='r';

   ?column?
--------------
 vacuum b;
 vacuum a;
 vacuum test;
 vacuum xyz;
 vacuum foo;
(5 rows)

You can redirect this output to a file and then strip off the heading and the feedback of (5 rows) and just run that file


 

Thanks for considering this enhancement,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 




--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar
------=_Part_75_29882349.1196458933925--