0

Problem is following: remove all records from one table, and insert them to another.

I have a table that is partitioned by date criteria. To avoid partitioning each record one by one, I'm collecting the data in one table, and periodically move them to another table. Copied records have to be removed from first table. I'm using DELETE query with RETURNING, but the side effect is that autovacuum is having a lot of work to do to clean up the mess from original table.

I'm trying to achieve the same effect (copy and remove records), but without creating additional work for vacuum mechanism.

As I'm removing all rows (by delete without where conditions), I was thinking about TRUNCATE, but it does not support RETURNING clause. Another idea was to somehow configure the table, to automatically remove tuple from page on delete operation, without waiting for vacuum, but I did not found if it is possible.

Can you suggest something, that I could use to solve my problem?

Pawel P
  • 131
  • 5
  • 1
    I dont see how you need returning here?.. why not just `begin; insert into part_table select * from short_table; truncate short_table; end`?.. – Vao Tsun Apr 26 '17 at 11:58
  • 1
    @VaoTsun: If there are concurrent writes, you'll need a table lock to avoid data loss. – Nick Barnes Apr 26 '17 at 12:33
  • @Vao Tsun, the cause is exactly as Nick Barnes wrote. There are concurrent writes. By using delete with returning I sure, that I have not violated the data consistency. I cannot lock the table, because it will block other modules. – Pawel P Apr 26 '17 at 12:39
  • indeed - you need lock table in transaction in my example – Vao Tsun Apr 26 '17 at 12:41
  • @PawelP if you use rules anyway - why not then define rule ON DML DO INSTEAD to other table, truncate it, destroy rules, do the delta from other table? – Vao Tsun Apr 26 '17 at 12:43
  • @Vao Tsun, I will have to check the performance, I think that batch job will be faster then processing every record by its own. – Pawel P Apr 26 '17 at 13:01
  • 1
    I don't see why you can't partition the data at the time of inserting it. What is the mechanism of inserting data? – Shamps Apr 26 '17 at 13:31
  • @Shamps well, that's actually a pretty good notice. There are few more operations that are done on this data before they are moved, so I have to take care of those operations. Your comment just reminded me that simplest solutions are the best ones, so I will try to review my application and check, if I can change this logic. – Pawel P Apr 26 '17 at 13:44

1 Answers1

1

You need to use something like:

--Open your transaction

BEGIN;

--Prevent concurrent writes, but allow concurrent data access

LOCK TABLE table_a IN SHARE MODE;

--Copy the data from table_a to table_b, you can also use CREATE TABLE AS to do this

INSERT INTO table_b AS SELECT * FROM table_a;

--Zeroying table_a

TRUNCATE TABLE table_a;

--Commits and release the lock

COMMIT;
Michel Milezzi
  • 10,087
  • 3
  • 21
  • 36