On Mon, May 22, 2006 at 02:25:09PM -0400, April Lorenzen wrote: > It comes up repeatedly that I need to load fresh data into a new > table, build indexes and vacuum - then drop the old table and > substitute the new. Why are you vacuuming a brand new table? Just run analyze.
> Postgresql partioning makes this all almost possible: the master > automatically SELECTs from all the child tables - so it automatically > recoginize the new ones, and no loss when the old ones are dropped. How is that any better than: BEGIN; -- Don't do the drop right now, because it might take awhile ALTER TABLE tablename RENAME TO delete_tablename; ALTER TABLE temporary_tablename RENAME TO tablename; COMMIT; DROP delete_tablename; > Just one thing would make it near perfect: if I could keep the master > from being able to SELECT from the new child table while I'm COPYing > data into the new child, building the index and vacuuming it - without > sending an error back to the user who is querying the master table. > > I tried a CHECK constraint, a RULE - SELECT rule can't INSTEAD DO > NOTHING - #postgresql channel people say there's nothing that can do > this... revoking privs or changing the owner of the new child results > in an error back to the user who queries the master table. Tried > creating a dummy table with no records and making a rule for the child > to INSTEAD select from the dummy table but that was not allowed > because it would turn my non-empty child table into a view. > > Thank you, > > - April > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
