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

>>We had this discussion. information_schema doesn't deliver enough info 
>>needed for admin tools.
>>    
>>
>
>It should. This is the sole reason for existance of it. If it is not
>enough, then it should be updated.
>  
>
It can't. ANSI says only objects owned by the user are shown. Admins 
might be quite unhappy about that...
pg_catalog views don't help either, just look at pg_tables. It doesn't 
even have the oid, how should a table be identified uniquely?
The system views are unusable for hardcore admin purposes, until they 
include *. So I'd rather use the tables directly.

>>
>>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
>>    
>>
>
>This won't work anyway if table is missing column attpos .
>
Sorry to be not precise enough, this was meant as meta code. Of course 
the query must be built version dependent.

>Still there were several predictions of all admin tools breaking as a
>result of gaps.
>  
>
wasn' me.

>
>Can you name one PG version change from A.N to A.M where old admin tools
>have not needed any changes ?
>  
>
Older tools usually continue to work, they just don't know new features. 
Maybe some esotheric features break, so few people notice.

>attnum interpretation of pgAdmin3 is already wrong - it claims it to be
>Position even when some previous columns are dropped. So you can have a
>table which has 1 column with "Position" 3 ;)
>  
>
attnum isn't interpreted at all in pgAdmin3, only used for ordering. It 
can't be used as key to a column any more, if altering a column would 
create a new attnum with old name (and old attpos). In this sense, the 
key is attrelid/attpos, because only these don't change for an ALTER 
COLUMN statement.

Imagine a sql update to a non-pk column would change the pk of the row, 
so you'd have to reread the row by its content to obtain the new pk 
value. This wouldn't make you happy, right? Same here.

>  
>
>>>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. 
>>    
>>
>
>Not all clients - just admin tools. And we dont have that many admin
>tools. And IMNSHO admin tools should move to using information_schema as
>much as possible.
>
>And if information_schema is inadequate then fix it instead of bypassing
>it.
>  
>
It is unfixable. Being ANSI-conformant, it prevents the admin seeing 
everything.

>I think keeping know-how about retrieving postgresql structure inside of
>an application instead of making it readily available in
>information_schema is either
> a) an egoistic attemt of shutting out competition
> b) laziness
>or
> c) both
>
><grin>
>
>  
>
<comment mode=suppress> ******** </comment>

>It is also bad design, as it ties ones tool to backend structure too
>tightly. 
>
>Backend structure will keep changing and the last thing we want to hold
>it back is some frontend tool which thinks it knows better how to
>organize data in backend. What if some completely new storage is added
>to postgreSQL (ancient Postgres versions had built-in support for
>several storages). Should all frontend tools (including ?DBC drivers)
>need updating or just information_schema ?
>  
>

Again, I'm not against using information_schema. I tried to use it (for 
non-admin tool purposes!) and failed badly. pgAdmin3 is a tool for *all* 
PostgreSQL features, not just for some common ANSI stuff.

>>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),
>>    
>>
>
>No they are interested in position as you mentioned above, they didn't
>want to see attnum (i.e 1,2,3,5,6,8 in your example) before either.
>
>I think it is a bug that pgAdmin3 shows attnum instead the real
>position.
>
>  
>
It shows what's in the db, to identify the column. I can't imagine why I 
should show a column number, programmers should address by column name 
and nothing else.

>Sorry, I must have missed it ;(  could you give a link to archived copy.
>  
>
About a week ago, when the message you answered on was posted. Took a 
week now to appear...

>But I think that nobody objected, but nobody didn't volunteer to do the
>work either ;)
>
>At least that was the impression i got from an answer to my similar
>question on growing varchars and dropping isnull's without forcing
>column copies and constraint checks.
>  
>

Yeah, interesting. For my observations, these trivial changes make 90-95 
% of daily column change work, that's why I implemented it in pgAdmin3 
(targeting the system tables directly...), so it's worth the effort 
handling them separately. I might add it some time to the backend (as an 
additional code path to the big version).

Regards,
Andreas