On Mon, Sep 26, 2005 at 08:41:03PM +0200, Yonatan Ben-Nes wrote:
> Anyway I saw the idea: > BEGIN; > CREATE new_table; > SELECT INTO new_table * FROM temp_table; > DROP TABLE table;
Instead of dropping it here, just rename to a different name and then after doing the next step drop the table.
> ALTER TABLE new_table RENAME TO table; > COMMIT;
you can do like this,
BEGIN; CREATE new_table; SELECT INTO new_table * FROM temp_table; ALTER TABLE table RENAME TO temp_table_orig; ALTER TABLE new_table RENAME TO table; COMMIT; drop table temp_table_orig;
> > Where if I understood correctly "table" is the final table, "temp_table" > is the table that receive all the proccess and at the end of it got > 10mil delete tuples and 5mil active and finally "new_table" is the > receipent of all of the active tuples from "temp_table". > > Its looking quite promising to me but I did alittle check and saw that > between the drop table command & the commit I get a lock on the table > (obvious but problematic to a 24/7 site) so im wondering to myself how > much time such a transaction will take from the drop command point? > > If users wont be able to access the table for some extremly small amount > of time (less then a second obviously) then though I dont like it much > it is better then running a vacuum full which will slow all my server > for a considerable amount of time... > > So anyone know how much time does such a process take? (tried to explain > analyze it with no success :)).
Based on http://lnk.nu/developer.postgresql.org/44b.c, line 1478 on, there's not a lot that happens during the ALTER TABLE. Likewise DROP (line 517) doesn't do much either. So basically, anything trying to access the old table will block for a while waiting for the update to happen.
But keep in mind that 'a while' will depend on what's happening on the system. Imagine...
Start long transaction involving table Run code above; drop aquires lock on table
