Re: [HACKERS] ALTER COLUMN/logical column position
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 either a) an egoistic attemt of shutting out competition b) laziness or c) 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
Re: [HACKERS] ALTER COLUMN/logical column position
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
Re: [HACKERS] 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ALTER COLUMN/logical column position
Andreas Pflug kirjutas N, 20.11.2003 kell 01:38: Second, column type changes needing a nontrivial cast function should be implemented in a way that preserve attnum. This could be done like this: - decompile dependent objects, and memorize them for later recreation - ADD tmpCol, UPDATE tmpCol=col::newtype, DROP old column, cascading to dependent objects, RENAME tmpCol (known stuff) - restore old attnum, which is a simple UPDATE to pg_attribute at this stage and suddenly your table is broken, as you can't retrieve the tmpCol when the attnum points to the dropped old column which has data in the format for old type ... the whole point of separating attnum and attpos is that attnum is used internally to retrieve the data and you can't change it by just UPDATEing pg_attribute. --- Hannu ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ALTER COLUMN/logical column position
Hannu Krosing wrote: attnum is used internally to retrieve the data Oops... So if an additional column number is invented, it should not be a logical column number, but a physical storage number for internal data retrieval. This way, the user interface doesn't change, and all those SELECT ... FROM pg_attribute ORDER BY attnum continue delivering the expected result. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ALTER COLUMN/logical column position
Which is what started the whole discussion. Dave On Thu, 2003-11-20 at 04:40, Andreas Pflug wrote: Hannu Krosing wrote: attnum is used internally to retrieve the data Oops... So if an additional column number is invented, it should not be a logical column number, but a physical storage number for internal data retrieval. This way, the user interface doesn't change, and all those SELECT ... FROM pg_attribute ORDER BY attnum continue delivering the expected result. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ALTER COLUMN/logical column position
Andreas Pflug kirjutas N, 20.11.2003 kell 11:40: Hannu Krosing wrote: attnum is used internally to retrieve the data Oops... So if an additional column number is invented, it should not be a logical column number, but a physical storage number for internal data retrieval. You are just shifting the interface problems to a place needing way more changes in the backend. There will be some problems either way. also, tools needing knowledge should start using information schema as much as they can, making internal reshufflings less of a problem. 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 ;) 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. The tools needing internal knowledge about storage (meaning any tool doing select .. from pg_...) have always needed some upgrades for new verions. 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. - Hannu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] ALTER COLUMN/logical column position
Hannu Krosing [EMAIL PROTECTED] writes: You are just shifting the interface problems to a place needing way more changes in the backend. There will be some problems either way. Exactly. I'm considerably more worried about breaking out-of-the-way places in the backend than I am about what order someone's admin tool presents the columns in. 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. That is a good comparison point. I'm inclined to think that we should do it in a way that minimizes backend changes. The way to do that is to keep attnum with its current definition (physical position) and add a new column for the logical position, which only a small number of places will need to care about. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ALTER COLUMN/logical column position
Tom Lane writes: Exactly. I'm considerably more worried about breaking out-of-the-way places in the backend than I am about what order someone's admin tool presents the columns in. Clearly, the effort of adding logical column numbers will consist of making choices between physical and logical numbers in the backend in some places. So one option is to replace some uses of attnum by attlognum. The other optionis to replace *all* uses of attnum by attphysnum and then replace some uses of attphysnum by attnum. To me, this looks like an equal risk as far as the backend goes. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ALTER COLUMN/logical column position
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: Exactly. I'm considerably more worried about breaking out-of-the-way places in the backend than I am about what order someone's admin tool presents the columns in. Clearly, the effort of adding logical column numbers will consist of making choices between physical and logical numbers in the backend in some places. So one option is to replace some uses of attnum by attlognum. The other optionis to replace *all* uses of attnum by attphysnum and then replace some uses of attphysnum by attnum. To me, this looks like an equal risk as far as the backend goes. This would be a reasonable assessment if we had our hands on every line of backend code that exists. But you are neglecting the probability of breaking user-written C functions, PL languages outside the main distro, etc. If we were going to go about this in a way that does not localize the changes, I'd be inclined to use attlognum and attphysnum ... that is, *deliberately* break every use that hasn't been looked at and updated. Even that would not guarantee catching all the trouble spots; for example loop indexes and attnums passed as function parameters might not have names that would be caught by a simplistic search-and-replace update. I'm for localizing the changes. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ALTER COLUMN/logical column position
Hannu Krosing wrote: 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! Yeah, and the data should be read only :-) Seriously: Methinks that only a part of the -patches thread was turned over to -hackers, some important parts are missing. First, there are column type changes that don't need any index/view/constraint recheck or data transformation at all, being of the very popular class hell, I need to stuff 12 bytes in my varchar(10). Some months ago, this was discussed, and there was consense that binarily compatible types may be changed with few special precautions (e.g. varchar(12) - varchar(10) e.g. needs a check for len=10). As a consequence, this kind of column type change is implemented in pgAdmin3. Probably a large percentage of real life column type changes are such binarily compatible ones, so it's senseful to handle them separately. Second, column type changes needing a nontrivial cast function should be implemented in a way that preserve attnum. This could be done like this: - decompile dependent objects, and memorize them for later recreation - ADD tmpCol, UPDATE tmpCol=col::newtype, DROP old column, cascading to dependent objects, RENAME tmpCol (known stuff) - restore old attnum, which is a simple UPDATE to pg_attribute at this stage - recreate all dependent objects Voila! No need for an additional attpos. Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
