Re: Avoid MVCC using exclusive lock possible?

On Tuesday 02 March 2004 06:29, Paul Tillotson wrote:
> 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.

Which isn't a big deal is it? You can write a single function to create entire
object and it's dependency. It is one time job but can save lots of time at
runtime.

> 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.

I still don't understand what is not so good about rename. All the indexes
remain there. Views need updation, I agree. Missed that last time. But what
you are suggesting is a part of rename if not complete of it.

I would always prefer to let PG handle these kind of details. Not very
comfortable with mucking around catalogs especially if there exists an
alternative.

Shridhar