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

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 C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461