Thread: error-free disabling of individual child partition tables
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. Deleting old and inserting new records is too slow - not to mention the vaccuming. Loading with COPY, then building indexes, then vacuuming is very fast by comparison. I'm dealing with over 100 million records several times daily. Users are disrupted by the time taken to delete... insert... vacuum. Users are not disrupted at all if the fresh tables can be loaded with data and have indexes built - then the old tables turned off and new turned on. 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. 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
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > > 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; > > What if there are dependencies? Might be better to have a view, which everything depends on, and change the view definition from one table to another back and forth. cheers andrew
On Mon, May 22, 2006 at 03:28:39PM -0400, Andrew Dunstan wrote: > Jim C. Nasby wrote: > > > >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; > > > > > > What if there are dependencies? Might be better to have a view, which > everything depends on, and change the view definition from one table to > another back and forth. What dependencies would there be that a view would solve? You can't define RI on a view AFAIK... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: > On Mon, May 22, 2006 at 03:28:39PM -0400, Andrew Dunstan wrote: > >> Jim C. Nasby wrote: >> >>> 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; >>> >>> >>> >> What if there are dependencies? Might be better to have a view, which >> everything depends on, and change the view definition from one table to >> another back and forth. >> > > What dependencies would there be that a view would solve? You can't > define RI on a view AFAIK... > functions, rules, other views ... RI is not the only source of dependencies. you would do this: < load table_a > create or replace view v as select * from table_a; drop if exists table table_b; next time around swap table_a and table_b, or you could write some plpgsql or plperl to do it nicely for you. cheers andrew
On Mon, 2006-05-22 at 14:25 -0400, April Lorenzen wrote: > 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. > > 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. There is not currently any way to make an existing table become a child table of another table. I propose a TODO item to allow this: ALTER TABLE childN INHERITS ( parent1, ... ); This would only succeed if all of the columns that would have been inherited from all parent tables already exist with the same name and datatype, nullability and default values. Additional columns would be allowed in the child table. This would also allow you to use CREATE TABLE AS SELECT and then move that table underneath a parent. We don't need a disinherit do we? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com
Simon Riggs wrote: > > There is not currently any way to make an existing table become a child > table of another table. I propose a TODO item to allow this: > > ALTER TABLE childN INHERITS ( parent1, ... ); > > This would only succeed if all of the columns that would have been > inherited from all parent tables already exist with the same name and > datatype, nullability and default values. Additional columns would be > allowed in the child table. > > This would also allow you to use CREATE TABLE AS SELECT and then move > that table underneath a parent. > > We don't need a disinherit do we? > > O, yes, I think we do. I can imagine that the ability to swap a table out of a set without deleting it could be very useful (e.g. you might move it in as a child of an archive table). These two would add markedly to the usefulness of inheritance as a partitioning mechanism. cheers andrew