Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On 9/27/05, Jim C. Nasby <jnasby@xxxxxxxxxxxxx> 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;

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

Everything else against table will now block, waiting for the DROP to
happen.
--
Jim C. Nasby, Sr. Engineering Consultant       jnasby@xxxxxxxxxxxxx
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf        cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend



--
with regards,
S.Gnanavel
Satyam Computer Services Ltd.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux