Re: How many insert + update should one transaction handle?
| От | Dawid Kuroczko |
|---|---|
| Тема | Re: How many insert + update should one transaction handle? |
| Дата | |
| Msg-id | 758d5e7f0509231153255649b6@mail.gmail.com обсуждение исходный текст |
| Ответ на | How many insert + update should one transaction handle? (Yonatan Ben-Nes <da@canaan.co.il>) |
| Ответы |
Re: How many insert + update should one transaction
|
| Список | pgsql-general |
On 9/23/05, Yonatan Ben-Nes <da@canaan.co.il> wrote:
If I understand you well, in transaction you do:
DELETE FROM table; -- remove all columns, you have a table with 5m dead tuples
then you:
INSERT into the table, -- you have 5m dead tuples + 5m new tuples
then you:
UPDATE what you've inserted -- you have 10m dead tuples + 5m new tuples
and then you:
UPDATE once again what you've inserted -- you have 15m dead tuples + 5 new tuples
and then COMMIT;
Alternatively you want to do:
CREATE TEMPORARY temp_table and UPDATE it as you please
BEGIN;
DELETE FROM table;
SELECT INTO table * FROM temp_table;
COMMIT; -- which will leave you with 5m dead and 5m live.
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;
Regards,
Dawid
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).
If I understand you well, in transaction you do:
DELETE FROM table; -- remove all columns, you have a table with 5m dead tuples
then you:
INSERT into the table, -- you have 5m dead tuples + 5m new tuples
then you:
UPDATE what you've inserted -- you have 10m dead tuples + 5m new tuples
and then you:
UPDATE once again what you've inserted -- you have 15m dead tuples + 5 new tuples
and then COMMIT;
Alternatively you want to do:
CREATE TEMPORARY temp_table and UPDATE it as you please
BEGIN;
DELETE FROM table;
SELECT INTO table * FROM temp_table;
COMMIT; -- which will leave you with 5m dead and 5m live.
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;
Regards,
Dawid
В списке pgsql-general по дате отправления: