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

On 9/27/05, Jim C. Nasby <jnasby(at)pervasive(dot)com> 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(at)pervasive(dot)com
> 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.