Re: ALTER COLUMN/logical column position

Hannu Krosing wrote:

>
>
>You are just shifting the interface problems to a place needing way more
>changes in the backend. There will be some problems either way.
>
>
Not quite. Certainly, basing internal storage on attstoragenum is more
work in the backend, but less (precisely: zero) work on an unknown
number of frontend tools and apps.

>also, tools needing knowledge should start using information schema as
>much as they can, making internal reshufflings less of a problem.
>
>
We had this discussion. information_schema doesn't deliver enough info
needed for admin tools.

>
>
>>This way, the "user interface" doesn't change, and all those
>>"SELECT ... FROM pg_attribute ORDER BY attnum" continue delivering the
>>expected result.
>>
>>
>
>Depending on what you expect ;)
>
>
Usually, nobody should care about the column ordering, but for those
unfortunate guys that rely on a specific SELECT * ordering the list of
columns displayed in admin tools must show that ordering; this is what
current admin tools expect from attnum. No SQL user would ever care
about internal storage details/pointers/counters, so any admin tool
would need to ORDER BY CASE WHEN version>=7.5 THEN attpos ELSE attnum
END (and the unique key to pg_attribute, as seen from the tool, changes
from refoid/attnum to refoid/attindex too).

>If you expect the above to give you all active columns as orderd as they
>are stored, then it does not give you what you expect.
>
>Btw, most of these concerns (and more) were already iterated when DROP
>column was done causing gaps in attnum. There were a lot of doomsday
>profecies, but in the end it went quite smoothly.
>
I don't bother about missing attnum values, even 1,2,3,5,6,8 is nicely
ordered.

> The tools needing
>internal knowledge about storage (meaning any tool doing select .. from
>pg_...) have always needed some upgrades for new verions.
>
>
Yes, but changes to pg_... should retain the usual meanings as much as
possible, so older tools continue to work. The discussed change is
problematic because old tools *seem* to work ok, but their attnum
interpretation would be wrong.

>IMHO, The only behaviour visible to common user we should worry about is
>SELECT * , and a special column for solving this is _the_ easiest way to
>do it.
>
>
>
Surely this is the easiest way. But it has the biggest impact on clients
too. I'm just imagining what would happen to pgAdmin3. The column number
would have to display attpos (this is what the user is interested in to
see the ordering), while index, FK and so forth will continue to display
attnum. This seems quite unwanted to me.

---
Are there any comments on the proposed lean way to alter columns for
trivial type changes?

Regards,
Andreas