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

Andreas Pflug kirjutas N, 20.11.2003 kell 16:10:
> 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.

With stress on "unknown number" ;)

> >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.

It should. This is the sole reason for existance of it. If it is not
enough, then it should be updated.

Updating information_schema would also make developers of other admin
tools happy. Remember - competition is good ;)

Compatibility with old verions of admin tools wont happen anyway, so we
should not let that lock backend into bad development decisions. Look
what happened to DOS-WIN16-WIN32.

> >>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

This won't work anyway if table is missing column attpos . You have to
have different queries for different versions. Add it is preferential to
keep these different queries in information_schema of corresponding
databases not all in frontend tool.

You still need different queries for old databases which did not support
schemas.

>  (and the unique key to pg_attribute, as seen from the tool, changes 
> from refoid/attnum to refoid/attindex too).

the key needs no change, just the ORDER BY clause.

> >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.

Still there were several predictions of all admin tools breaking as a
result of gaps.

> > 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. 

Can you name one PG version change from A.N to A.M where old admin tools
have not needed any changes ?

> The discussed change is 
> problematic because old tools *seem* to work ok, but their attnum 
> interpretation would be wrong.

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 ;)

> >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.

I think keeping know-how about retrieving postgresql structure inside of
an application instead of making it readily available in
information_schema is eithera) an egoistic attemt of shutting out competitionb) laziness
orc) both

<grin>

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 ?

> 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.

>  while index, FK and so forth will continue to display 
> attnum. This seems quite unwanted to me.

Actually the column name is shown (at least I could not find attnum
anywhere in keys or indexes)

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

Sorry, I must have missed it ;(  could you give a link to archived copy.

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.

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