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

Author

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

Yonatan Ben-Nes

2005-09-26, 8:24 pm

Robert Treat wrote:
> On Fri, 2005-09-23 at 14:53, Dawid Kuroczko wrote:
>
>
> <snip>
>
>
>
> yeah, i was thinking
>
> create newtable;
> ~~ load data into newtable
>
> begin;
> drop oldtable;
> alter table newtable rename to oldtable
> commit;
>
> this seperates the data loading piece from the piece where you promote
> the data to live data, plus then the time you have to hold the
> transaction open is only for the drop and rename, which will be quite
> fast.
>
> the only potential issues would be making sure you dont have FK/View
> type issues, but it doesn't sound like it would apply here.
>
>
> Robert Treat

Sorry everyone for not responding... I just didnt know that the
discussion continued :)

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 :)).

Thanks alot everyone!
Ben-Nes Yonatan

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Jim C. Nasby

2005-09-26, 8:24 pm

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@pervasive.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

Sponsored Links