There are various applications where we want to completely replace the
contents of a table with new/re-calculated data.
It seems fairly obvious to be able to do this like...
1. Prepare new data into "new_table" and build indexes
2. Swap old for new
BEGIN;
DROP TABLE "old_table";
ALTER TABLE "new_table" RENAME to "old_table";
COMMIT;
Step (2) works, but any people queuing to access the table will see ERROR: could not open relation with OID xxxxx
What we need is a way to atomically replace the contents of a table
without receiving this error. (You can't use views).
What I propose is to write a function/command to allow this to be
explicitly achievable by the server.
ALTER TABLE "old_table" REPLACE WITH "new_table";
This would do the following:
* Check that *content* definitions of old and new are the same
* Drop all old indexes
* Move new relfilenode into place
* Move all indexes from new to old (the set of indexes may change)
* All triggers, non-index constraints, defaults etc would remain same
* "new_table" is TRUNCATEd.
TRUNCATE already achieves something similar, and is equivalent to
REPLACE WITH an empty table, so we know it is possible. Obviously this
breaks MVCC, but the applications for this don't care.
Of course, as with all things, this can be done with a function and some
dodgy catalog updates. I'd rather avoid that and have this as a full
strength capability on the server, since it has a very wide range of
potential applications of use to all Postgres users.
Similar, though not inspired by EXCHANGE PARTITION in Oracle.
It looks a short project to me, just some checks and a few updates.
Objections?
-- Simon Riggs http://www.2ndQuadrant.com/books/PostgreSQL Development, 24x7 Support, Training and Services