I use this type of approach when mirroring data from a foxpro database
(yuck) to a read-only postgres database. It is quicker and cleaner than
deleting all of the rows and inserting them again (TRUNCATE is not
transaction safe, which I need).

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 majordomo@postgresql.org


Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 8 of 11 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 24, '04 at 4:49p
activeMar 25, '04 at 1:00p
posts11
users9
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase