Re: logical column position

Andreas Pflug kirjutas K, 19.11.2003 kell 20:45:
> Dave Cramer wrote:
> >>Why should ALTER COLUMN change the column number, i.e. position?
> >
> >Rod's current proposed patch does that if you do an alter column alter
> >type. This is an artifact of the underlying mechanism. (ren old col, add
> >new col, update newcol=oldcol::newtype).
> >
> I must have missed that, can't find it in hackers?!?

Was on [PATCHES] IIRC.

> In this case the old attnum value should simply be reused, to retain the
> original ordering. IMHO this is necessary to prevent problems with any
> object referencing a column (index, view, ...)

Actually these have to be recreaqted, especially when changing column
type.

Rod's patchs does that too ;)

> The current proposal
> invents the attpos for column ordering purpose only,

That's the only place _user_ sees it. The other uses are taken care of
inide database backend.

> but
> views/indexes/etc will reference attnum, and would need updates.

they also "reference" column type, and thus need to be updated anyway
when column type changes.

> Actually, a column that changes its attnum is just like a table changing
> its oid, i.e. it's not the same object any more. This will provoke
> problems in administration tools (at least in pgAdmin3, which will try
> to refresh its display with the formerly known oid/attnum af ter
> executing a change), and maybe other places too.

Sure. _any_ change to database structure could break a client not
(designed to be) aware of that change.

> To put it differently: a ALTER COLUMN command may never-ever change the
> identifier of the column, i.e. attrelid/attnum.

to be even more restirictive: ALTER COLUMN may never-ever change the
type of the column, as this too may break some apps. Nah!

-------------
Hannu