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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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