Jim C. Nasby wrote:
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;
ALTER TABLE new_table RENAME TO table;
COMMIT;
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
Everything else against table will now block, waiting for the DROP to
happen.
Jim unless I didnt understand you I think that at my case I wont need to
make any long transaction which will handle the DROP & renaming of tables.
I will actually have 2 transactions now:
1. which will handle the INSERT + UPDATE of the data into the temp_table
& at the end will move all of the new data (without the deleted tuples)
to the new_table and create its indexes.
2. the second transaction will only handle the drop & renaming of the 2
tables (new_table & table);
The question is whats the expected time for the second transaction to
run? will it create problems to the constant availability of the site?
S.Gnanavel I tried your idea but sadly it gives me the same block as I
would DROP the table and not RENAME it.
Thanks everyone again,
Ben-Nes Yonatan
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
