Re: How many insert + update should one transaction

On Fri, 2005-09-23 at 14:53, Dawid Kuroczko wrote:
> On 9/23/05, Yonatan Ben-Nes < da(at)canaan(dot)co(dot)il <mailto:da(at)canaan(dot)co(dot)il> >
> wrote:
>
> Hi all,
>
> Every few days I need to DELETE all of the content of few tables and
> INSERT new data in them.
> The amount of new data is about 5 million rows and each row get about 3
> queries (INSERT + UPDATE).
>
<snip>
> Or you could even try (haven't tested it):
> BEGIN;
> CREATE new_table;
> SELECT INTO new_table * FROM temp_table;
> DROP TABLE table;
> ALTER TABLE new_table RENAME TO table;
> COMMIT; -- leaving you with fresh 5mln new tuples table
> ...with a risk of loosing all the changes made to old table after BEGIN;
>

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
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL