Skip site navigation (1) Skip section navigation (2)

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

From: Yonatan Ben-Nes <da(at)canaan(dot)co(dot)il>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>,Gnanavel S <s(dot)gnanavel(at)gmail(dot)com>
Cc: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>,Dawid Kuroczko <qnex42(at)gmail(dot)com>,"pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>,bruno(at)wolff(dot)to, smarlowe(at)g2switchworks(dot)com
Subject: Re: How many insert + update should one transaction handle?
Date: 2005-09-27 11:34:37
Message-ID: 43392E4D.4040000@canaan.co.il (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Yonatan Ben-NesDate: 2005-09-27 11:45:41
Subject: Re: Index use in BETWEEN statement...
Previous:From: Sean DavisDate: 2005-09-27 10:54:51
Subject: Re: Index use in BETWEEN statement...

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group