Re: Avoid MVCC using exclusive lock possible?

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(at)postgresql(dot)org
>
>
>
>