However, for this to be useful, your table must not have any indexes, views, foreign keys, sequences, triggers, etc., or else you must be prepared to re-create all of them using application level code.
I imagine this would break lots of things, but it would be nice if instead of Shridhar's rename step (see below) one could do this:
$table1node = query("SELECT relfilenode FROM pg_class WHERE relname = '$old_table';");
$table2node = query("SELECT relfilenode FROM pg_class WHERE relname = '$new_table';");
exec("UPDATE pg_class SET relfilenode = $table2node WHERE relname = '$old_table';");
exec("UPDATE pg_class SET relfilenode = $table1node WHERE relname = '$new_table';");
You would of course need to change the relfilenode for all of the toasted columns and indexes as well in the same atomic step, but it seems like this might be more compatible with postgresql's MVCC model than other ideas suggested.
Regards, Paul Tillotson
Shridhar Daithankar wrote:
I am sure people have answered the approach you have suggested so let me suggest a workaround for your problem.
You could run following in a transaction.
- begin
- Create another table with exact same structure
- write a procedure that reads from input table and updates the value in between
- drop the original table
- rename new table to old one
- commit
- analyze new table
Except for increased disk space, this approach has all the good things postgresql offers. Especially using transactable DDLs it is huge benefit. You certainly do save on vacuum.
If the entire table is updated then you can almost certainly get things done faster this way.
HTH
Shridhar
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
