Re: Column storage positions

On Wed, Feb 21, 2007 at 12:06:30PM -0500, Phil Currier wrote:
> Well, for two reasons:
>
> 1) If you have a table with one very-frequently-accessed varchar()
> column and several not-frequently-accessed int columns, it might
> actually make sense to put the varchar column first. The system won't
> always be able to make the most intelligent decision about table
> layout.

Umm, the point of the exercise is that if you know there are int
columns, then you can skip over them, whereas you can never skip over a
varchar column. So there isn't really any situation where it would be
better to put the varchar first.

>
> don't see any good way to perform an upgrade between PG versions
> without rewriting each table's data. Maybe most people aren't doing
> upgrades like this right now, but it seems like it will only become
> more common in the future. In my opinion, this is more important than
> #1.

I don't see this either. For all current tables, the storage position
is the attribute number, no exception. You say:

> because the version X table could
> have dropped columns that might or might not be present in any given
> tuple on disk.

Whether they're there or not is irrelevent. Drop columns are not
necesarily empty, but in any case they occupy a storage position until
the table is rewritten. A dump/restore doesn't need to preserve this,
but pg_migrator will need some smarts to handle it. The system will
need to create a column of the appropriate type and drop it to get to
the right state.

If you really want to use pg_dump I'd suggest an option to pg_dump
--dump-dropped-columns which will include the dropped columns in the
CREATE TABLE but drop them immediatly after. It's really more a corner
case than anything else.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.