Re: ALTER COLUMN/logical column position - Mailing list pgsql-hackers

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