Re: How many insert + update should one transaction handle?

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