Re: [HACKERS] logical column position
Robert Treat [EMAIL PROTECTED] writes: Seems merging the two would work... attlogpos, the attributes logical position. Unless anyone has any further objections, I'll switch to using attlogpos. -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] logical column position
Tom Lane wrote: It's completely fallacious to imagine that we could make this change be transparent to external applications. To take two examples: 1. How many places do you think know that pg_attribute.attnum links to pg_attrdef.adnum? pg_dump, psql, and the JDBC driver all appear to know that, in a quick search of the CVS tree; I haven't even bothered to look at pgadmin and the other apps that are likely to have such dependencies. 2. How about linking pg_attribute.attnum to entries in pg_index.indkey? Lots of apps know about that too. Unless you are going to change the meanings of pg_index.indkey and pg_attrdef.adnum, you can't simply redefine attnum as a logical column position. And if you do make such a change you will break code elsewhere. If we add a *new* column attlogpos, without changing the semantics of attnum, then I think we have a fighting chance of making this work without an undue amount of effort. I see no prospect that we can change the meaning of attnum without breaking things far and wide. I don't quite understand your argumentation. Currently, attnum is used 1) to determine position (the concern) 2) as part of the unique identifier, as used by index, FK etc 3) as pointer for data retrieval. If only the retrieval functions would use attstoragenum or however you'd call it, all other references to attnum can remain untouched. Actual physical reordering would be hidden almost completely. This is a bit like abstracting a primary key from the row's physical storage location. Regards, Andreas regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
On Thu, 2003-11-20 at 23:27, Tom Lane wrote:
Neil Conway [EMAIL PROTECTED] writes:
Actually, I deliberately chose attpos rather than attlognum (which is
what some people had been calling this feature earlier). My reasoning
was that the logical number is really a nonsensical idea: we just
invented it on the spot.
True ...
In contrast, a position is a fairly natural
thing for an attribute to have -- it's a notion with some counterpart
in the real world.
But position could at least as logically be considered to mean the
physical position in the tuple. I still say that these names are ripe
for confusion.
I don't have a better choice of name offhand, but if we spend 1% of the
time already spent arguing about these issues on finding a better name,
I'm sure we can think of one ;-)
Seems merging the two would work... attlogpos, the attributes logical
position.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] logical column position
Andreas Pflug [EMAIL PROTECTED] writes: I don't quite understand your argumentation. My point is that to change attnum into a logical position without breaking client apps (which is the ostensible reason for doing it that way), we would need to redefine all system catalog entries that reference columns by attnum so that they also store logical rather than physical position. That has a number of serious problems, one big one being the difficulty of updating them all correctly during a column renumbering operation. More, it turns what would otherwise be a relatively localized patch into a massive and bug-prone backend modification. I think it is better to consider attnum as sort of a mini-OID: any one column has a uniquely assigned attnum that will never change and can be relied on to identify that column. This is essentially how it is being used now (remember attnum is part of the PK for pg_attribute) and the fact that it is also the physical position is really rather incidental as far as the system catalogs are concerned. You're quite right that attnum is serving three purposes, but that doesn't mean that we can choose at random which purpose(s) to decouple. Abandoning the assumption that attnum is a permanent identifier would break a lot of things --- probably not only in the backend, either. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] logical column position
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: I don't quite understand your argumentation. My point is that to change attnum into a logical position without breaking client apps (which is the ostensible reason for doing it that way), we would need to redefine all system catalog entries that reference columns by attnum so that they also store logical rather than physical position. That has a number of serious problems, one big one being the difficulty of updating them all correctly during a column renumbering operation. More, it turns what would otherwise be a relatively localized patch into a massive and bug-prone backend modification. I think it is better to consider attnum as sort of a mini-OID: any one column has a uniquely assigned attnum that will never change and can be relied on to identify that column. This is essentially how it is being used now (remember attnum is part of the PK for pg_attribute) and the fact that it is also the physical position is really rather incidental as far as the system catalogs are concerned. I agree considering attrelid/attnum as kind-of OID, but a relation's pg_class.oid won't change at ALTER TABLE either, I'd expect the same from ALTER COLUMN. You're quite right that attnum is serving three purposes, but that doesn't mean that we can choose at random which purpose(s) to decouple. Abandoning the assumption that attnum is a permanent identifier would break a lot of things --- probably not only in the backend, either. Maybe my proposal wasn't clear enough: Just as an index references a pg_class entry by it's OID, not some value identifying it's physical storage, all objects might continue referencing columns by attnum. Only tuple handling functions like heap_getattr and heap_formtuple need to know how to extract a Datum by its attnum from a HeapTuple or how to compile a HeapTuple correctly. If reshuffling columns is done inside of these functions, it would be transparent to the rest of the backend and the clients. Hopefully, there are not too much of such functions, or fancy modules bypassing them... Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
Andreas Pflug [EMAIL PROTECTED] writes: Maybe my proposal wasn't clear enough: Just as an index references a pg_class entry by it's OID, not some value identifying it's physical storage, all objects might continue referencing columns by attnum. That's exactly the same thing I am saying. Your mistake is to assume that this function can be combined with identification of a (changeable) logical column position. It can't. Changeability and immutability are just not compatible requirements. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] logical column position
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Maybe my proposal wasn't clear enough: Just as an index references a pg_class entry by it's OID, not some value identifying it's physical storage, all objects might continue referencing columns by attnum. That's exactly the same thing I am saying. Your mistake is to assume that this function can be combined with identification of a (changeable) logical column position. It can't. Changeability and immutability are just not compatible requirements. In the mind of a programmer, a ALTER COLUMN doesn't create a new column, but merely changes some attributes of an existing column. In this sense, changeability and immutability are not controversal. Digging deeper: TupDesc contains an array of physical attr descriptions, and to access a column description attnum is taken as index into that array (taken from fastgetattr). return fetchatt(tupleDesc-attrs[attnum-1], ...) The physical location can easily reordered if there's an additional array, to translate attnum into the array index. return fetchatt(tupleDesc-attrs[tupleDesc-attrpos[attnum-1]] ... For sure, reordering (i.e. changing the attrpos array) may only be performed as long as the column isn't referenced. Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] logical column position
Andreas Pflug [EMAIL PROTECTED] writes: To put it differently: a ALTER COLUMN command may never-ever change the identifier of the column, i.e. attrelid/attnum. If the ALTER is changing the column type, it's not really the same column anymore; I see nothing wrong with assigning a new attnum in that scenario. It's not like you can simply change the type and not go visit the references in such a case. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] logical column position
Tom Lane wrote: If the ALTER is changing the column type, it's not really the same column anymore; This doesn't strike. If the ALTER is changing the number of columns, it's not really the same table anymore is as true as your statement. Still, pg_class.oid remains the same for ADD and DROP column. I see nothing wrong with assigning a new attnum in that scenario. It's not like you can simply change the type and not go visit the references in such a case. But this fix is about automatically updating references as well, making the ALTER COLUMN appear a low-impact change to the user (which obviously isn't true, unless my proposed shortcut for binary compatible type changes is implemented). When dropping and recreating an object, nobody would expect to get the same identifier. When altering, I *do* expect the identifier to remain the same. Regards, Andreas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] logical column position
On Wed, 19 Nov 2003 19:07:23 +0100, Andreas Pflug [EMAIL PROTECTED] wrote: is there any DB system out there that allows to reshuffle the column ordering? Firebird: ALTER TABLE tname ALTER COLUMN cname POSITION 7; Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] logical column position
Neil Conway [EMAIL PROTECTED] writes: At present, attnum basically does three things: identifies an column within a relation, indicates which columns are system columns, and defines the order of a relation's columns. I'd like to move this last functionality into a separate pg_attribute column named attpos (or attlogicalpos): attpos is a horrid choice of name, because no one will be able to remember which of attnum and attpos is which. Pick a more distinct name. Offhand the best thing I can think of is attlognum or attlogpos. - when the table is created, attnum == attpos. System columns have attpos 0, as with attnum. At no point will two columns of the same relation have the same attpos. What are you going to do with deleted columns? I'd be inclined to give them all attlogpos = 0, but that destroys your last comment. (a) ISTM this should also apply to COPY TO and COPY FROM if the user didn't supply a column list. Is this reasonable? Yes, also INSERT INTO, also the implicit ordering of output columns of a JOIN, also the matching of aliases to columns in a FROM-list alias, probably one or two other places. SQL exposes column ordering in more places than just SELECT *. If we want to avoid this, one easy (but arguably unclean) way to do so would be to make the initial value of attpos == attnum * 1000, and make attpos an int4 rather than an int2. Then, we can do most column reordering operations with only a single pg_attribute update -- in the worst-case that enough re-orderings are done that we overflow the 999 padding positions, we can just fall-back to doing multiple pg_attribute updates. Is this worth doing, and/or is there a better way to achieve the same effect? That seems horribly messy. Just renumber. (c) Do I need to consider inheritance? Yes. I think it'd be good if things were constrained so that columns 1..n in a parent table always matched columns 1..n in every child, which is not true now after adding/dropping columns. That would make it easier/cheaper/more reliable to match up which child columns are to be referenced in an inherited query (see adjust_inherited_attrs). I think the effective constraints would have to be about the same as what we now impose on column names in an inheritance hierarchy. You have not presented any proposal for exactly what ALTER TABLE operations would be offered to manipulate the column positions. My recollection is that some consensus was reached on that point in the last thread we had on this issue --- have you consulted the archives? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] logical column position
On Thu, Nov 20, 2003 at 10:39:24AM -0500, Tom Lane wrote: (c) Do I need to consider inheritance? Yes. I think it'd be good if things were constrained so that columns 1..n in a parent table always matched columns 1..n in every child, which is not true now after adding/dropping columns. That would make it easier/cheaper/more reliable to match up which child columns are to be referenced in an inherited query (see adjust_inherited_attrs). No way, because of multiple inheritance. Each child should have an attparentnum, which would point to the parent's attnum for this to work ... -- Alvaro Herrera ([EMAIL PROTECTED]) Aprender sin pensar es inútil; pensar sin aprender, peligroso (Confucio) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
Alvaro Herrera Munoz [EMAIL PROTECTED] writes: On Thu, Nov 20, 2003 at 10:39:24AM -0500, Tom Lane wrote: (c) Do I need to consider inheritance? Yes. I think it'd be good if things were constrained so that columns 1..n in a parent table always matched columns 1..n in every child, which is not true now after adding/dropping columns. No way, because of multiple inheritance. Each child should have an attparentnum, which would point to the parent's attnum for this to work ... Hm, good point. And I think we merge identically-named columns inherited from different parents, which would mean that attparentnum wouldn't have a unique value anyway. Perhaps rearranging a parent's columns shouldn't have *any* direct effect on a child? Seems ugly though. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logical column position
Rod Taylor [EMAIL PROTECTED] writes: On Mon, 2003-11-17 at 20:24, Christopher Kings-Lynne wrote: BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. Yeah... how about maintaining attnum for the logical attribute number and create an attphysnum or something for the physical position instead? This is more intrusive into the source, but you don't need to teach new tricks to external entities. [ and similar remarks from other people elsewhere in the thread ] It's completely fallacious to imagine that we could make this change be transparent to external applications. To take two examples: 1. How many places do you think know that pg_attribute.attnum links to pg_attrdef.adnum? pg_dump, psql, and the JDBC driver all appear to know that, in a quick search of the CVS tree; I haven't even bothered to look at pgadmin and the other apps that are likely to have such dependencies. 2. How about linking pg_attribute.attnum to entries in pg_index.indkey? Lots of apps know about that too. Unless you are going to change the meanings of pg_index.indkey and pg_attrdef.adnum, you can't simply redefine attnum as a logical column position. And if you do make such a change you will break code elsewhere. If we add a *new* column attlogpos, without changing the semantics of attnum, then I think we have a fighting chance of making this work without an undue amount of effort. I see no prospect that we can change the meaning of attnum without breaking things far and wide. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logical column position
Tom Lane [EMAIL PROTECTED] writes: attpos is a horrid choice of name, because no one will be able to remember which of attnum and attpos is which. Pick a more distinct name. Offhand the best thing I can think of is attlognum or attlogpos. Actually, I deliberately chose attpos rather than attlognum (which is what some people had been calling this feature earlier). My reasoning was that the logical number is really a nonsensical idea: we just invented it on the spot. In contrast, a position is a fairly natural thing for an attribute to have -- it's a notion with some counterpart in the real world. To me, at least, it seems intuitive that an attnum would identify a column whereas an attpos would specify the column's position. I'm happy to change the name if there's a consensus that attpos isn't a good choice -- what does everyone think? What are you going to do with deleted columns? I'd be inclined to give them all attlogpos = 0, but that destroys your last comment. I hadn't planned to do anything in particular for deleted columns: since they are never displayed to the user, does it matter what their attpos is? In any event, the property that no two columns in a table have the same logical number isn't important anyway. You have not presented any proposal for exactly what ALTER TABLE operations would be offered to manipulate the column positions. I'd like to get the backend storage side of things implemented first. I'll take a look at the archives before I do any UI work -- thanks for the suggestion. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] logical column position
Neil Conway [EMAIL PROTECTED] writes: Actually, I deliberately chose attpos rather than attlognum (which is what some people had been calling this feature earlier). My reasoning was that the logical number is really a nonsensical idea: we just invented it on the spot. True ... In contrast, a position is a fairly natural thing for an attribute to have -- it's a notion with some counterpart in the real world. But position could at least as logically be considered to mean the physical position in the tuple. I still say that these names are ripe for confusion. I don't have a better choice of name offhand, but if we spend 1% of the time already spent arguing about these issues on finding a better name, I'm sure we can think of one ;-) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logical column position
I don't have a better choice of name offhand, but if we spend 1% of the time already spent arguing about these issues on finding a better name, I'm sure we can think of one ;-) virtual (attvirtnum) external (attextnum) atttisoywnum - attribute this is the one you want number ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] logical column position
Christopher Kings-Lynne wrote: Will adding the logical attribute number break all of the external tools? pg_dump, etc are all dependent on attnum now? Would it be possible to keep the meaning of attnum the same externally and add another column internally to represent the physical number? Interesting idea. It would require a lot of code renaming in the backend, but it could be done. Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc and all the main developers for those lists read all these posts, I think the massive amount of effort to maintain the external interface isn't worth it. I can vouch that it would take me exactly 2 minutes to add support for attlognums in phpPgAdmin. Lesson 1 in SQL for beginners says Don't use SELECT * if you rely on the order of columns. This discussion is about fixing a problem that only inexperienced programmers have. It's like an extra set of wheels on top of your car, just in case you drive wrong way... What's happening if you simply delete a column? Ordering 1,2,3,5? Insert another column of the same name, as a previously deleted, will it get the old position number? And so on. IMHO, way too much effort for working around situations that should be avoided anyway. Regards, Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logical column position
Andreas, The point of this is to maintain the column position. I don't think that an alter of a column type should move the column position. It may be that programmers should not rely on this, but it happens, and in very large projects. If we can avoid unexpected side-affects like moving the columns position, then I think we should. Dave On Wed, 2003-11-19 at 12:30, Andreas Pflug wrote: Christopher Kings-Lynne wrote: Will adding the logical attribute number break all of the external tools? pg_dump, etc are all dependent on attnum now? Would it be possible to keep the meaning of attnum the same externally and add another column internally to represent the physical number? Interesting idea. It would require a lot of code renaming in the backend, but it could be done. Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc and all the main developers for those lists read all these posts, I think the massive amount of effort to maintain the external interface isn't worth it. I can vouch that it would take me exactly 2 minutes to add support for attlognums in phpPgAdmin. Lesson 1 in SQL for beginners says Don't use SELECT * if you rely on the order of columns. This discussion is about fixing a problem that only inexperienced programmers have. It's like an extra set of wheels on top of your car, just in case you drive wrong way... What's happening if you simply delete a column? Ordering 1,2,3,5? Insert another column of the same name, as a previously deleted, will it get the old position number? And so on. IMHO, way too much effort for working around situations that should be avoided anyway. Regards, Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] logical column position
Dave Cramer wrote: Andreas, The point of this is to maintain the column position. I don't think that an alter of a column type should move the column position. Why should ALTER COLUMN change the column number, i.e. position? It may be that programmers should not rely on this, but it happens, and in very large projects. If we can avoid unexpected side-affects like moving the columns position, then I think we should. This is *expected* if behaviour if you delete and add columns; is there any DB system out there that allows to reshuffle the column ordering? Instead of some order-ordering facility it would be better to support all kinds of column type changes, not only binary compatible ones. This would help everybody, not only maintainers of ill-designed software. Regards, Andreas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logical column position
Andreas, On Wed, 2003-11-19 at 13:07, Andreas Pflug wrote: Dave Cramer wrote: Andreas, The point of this is to maintain the column position. I don't think that an alter of a column type should move the column position. Why should ALTER COLUMN change the column number, i.e. position? Rod's current proposed patch does that if you do an alter column alter type. This is an artifact of the underlying mechanism. (ren old col, add new col, update newcol=oldcol::newtype). Which is the point of the logical column number discussion, and the todo item. It may be that programmers should not rely on this, but it happens, and in very large projects. If we can avoid unexpected side-affects like moving the columns position, then I think we should. This is *expected* if behaviour if you delete and add columns; is there any DB system out there that allows to reshuffle the column ordering? Yes, informix allows you to add the column before|after a column, and mysql allows for add column after col. those are the only two I know about.there could be more. Instead of some order-ordering facility it would be better to support all kinds of column type changes, not only binary compatible ones. This would help everybody, not only maintainers of ill-designed software. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] logical column position
Dave Cramer wrote: Andreas, On Wed, 2003-11-19 at 13:07, Andreas Pflug wrote: Dave Cramer wrote: Andreas, The point of this is to maintain the column position. I don't think that an alter of a column type should move the column position. Why should ALTER COLUMN change the column number, i.e. position? Rod's current proposed patch does that if you do an alter column alter type. This is an artifact of the underlying mechanism. (ren old col, add new col, update newcol=oldcol::newtype). I must have missed that, can't find it in hackers?!? In this case the old attnum value should simply be reused, to retain the original ordering. IMHO this is necessary to prevent problems with any object referencing a column (index, view, ...) The current proposal invents the attpos for column ordering purpose only, but views/indexes/etc will reference attnum, and would need updates. Actually, a column that changes its attnum is just like a table changing its oid, i.e. it's not the same object any more. This will provoke problems in administration tools (at least in pgAdmin3, which will try to refresh its display with the formerly known oid/attnum af ter executing a change), and maybe other places too. To put it differently: a ALTER COLUMN command may never-ever change the identifier of the column, i.e. attrelid/attnum. Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
Andreas Pflug kirjutas K, 19.11.2003 kell 20:45: Dave Cramer wrote: Why should ALTER COLUMN change the column number, i.e. position? Rod's current proposed patch does that if you do an alter column alter type. This is an artifact of the underlying mechanism. (ren old col, add new col, update newcol=oldcol::newtype). I must have missed that, can't find it in hackers?!? Was on [PATCHES] IIRC. In this case the old attnum value should simply be reused, to retain the original ordering. IMHO this is necessary to prevent problems with any object referencing a column (index, view, ...) Actually these have to be recreaqted, especially when changing column type. Rod's patchs does that too ;) The current proposal invents the attpos for column ordering purpose only, That's the only place _user_ sees it. The other uses are taken care of inide database backend. but views/indexes/etc will reference attnum, and would need updates. they also reference column type, and thus need to be updated anyway when column type changes. Actually, a column that changes its attnum is just like a table changing its oid, i.e. it's not the same object any more. This will provoke problems in administration tools (at least in pgAdmin3, which will try to refresh its display with the formerly known oid/attnum af ter executing a change), and maybe other places too. Sure. _any_ change to database structure could break a client not (designed to be) aware of that change. 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! - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] logical column position
Why should ALTER COLUMN change the column number, i.e. position? Because it creates a NEW column. It may be that programmers should not rely on this, but it happens, and in very large projects. If we can avoid unexpected side-affects like moving the columns position, then I think we should. This is *expected* if behaviour if you delete and add columns; is there any DB system out there that allows to reshuffle the column ordering? MySQL Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] logical column position
Will adding the logical attribute number break all of the external tools? pg_dump, etc are all dependent on attnum now? Would it be possible to keep the meaning of attnum the same externally and add another column internally to represent the physical number? Dave ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logical column position
Dave Cramer wrote: Will adding the logical attribute number break all of the external tools? pg_dump, etc are all dependent on attnum now? Would it be possible to keep the meaning of attnum the same externally and add another column internally to represent the physical number? Interesting idea. It would require a lot of code renaming in the backend, but it could be done. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
Will adding the logical attribute number break all of the external tools? pg_dump, etc are all dependent on attnum now? Would it be possible to keep the meaning of attnum the same externally and add another column internally to represent the physical number? Interesting idea. It would require a lot of code renaming in the backend, but it could be done. Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc and all the main developers for those lists read all these posts, I think the massive amount of effort to maintain the external interface isn't worth it. I can vouch that it would take me exactly 2 minutes to add support for attlognums in phpPgAdmin. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] logical column position
I'd like to add a new column to pg_attribute that specifies the attribute's logical position within its relation. The idea here is to separate the logical order of the columns in a relation from the on-disk storage of the relation's tuples. This allows us to easily quickly change column order, add an additional column before or after an existing column, etc. At present, attnum basically does three things: identifies an column within a relation, indicates which columns are system columns, and defines the order of a relation's columns. I'd like to move this last functionality into a separate pg_attribute column named attpos (or attlogicalpos): - when the table is created, attnum == attpos. System columns have attpos 0, as with attnum. At no point will two columns of the same relation have the same attpos. - when returning output to the client and no column ordering is implied by the query (e.g. SELECT * ...), we sort the columns in ascending attpos order. - when storing a tuple on disk, we don't consider attpos - if we want to change the order of the column's in a relation, we can do so merely by updating pg_attribute; no changes to the on-disk storage of the relation should be necessary A few notes: (a) ISTM this should also apply to COPY TO and COPY FROM if the user didn't supply a column list. Is this reasonable? It would break dumps of the table's contents, but then again, dumps aren't guaranteed to remain valid over arbitrary changes to the table's meta-data. (b) Using the above scheme that attnum == attpos initially, there won't be any gaps in the sequence of attpos values. That means that if, for example, we want to move the column in position 50 to position 1, we'll need to change the position's of all the columns in positions [1..49] (and suffer the resulting MVCC bloat in pg_attribute). Changing the column order is hardly a performance critical operation, so that might be acceptable. If we want to avoid this, one easy (but arguably unclean) way to do so would be to make the initial value of attpos == attnum * 1000, and make attpos an int4 rather than an int2. Then, we can do most column reordering operations with only a single pg_attribute update -- in the worst-case that enough re-orderings are done that we overflow the 999 padding positions, we can just fall-back to doing multiple pg_attribute updates. Is this worth doing, and/or is there a better way to achieve the same effect? (c) Do I need to consider inheritance? Comments are welcome. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] logical column position
I'd like to add a new column to pg_attribute that specifies the attribute's logical position within its relation. The idea here is to separate the logical order of the columns in a relation from the on-disk storage of the relation's tuples. This allows us to easily quickly change column order, add an additional column before or after an existing column, etc. At present, attnum basically does three things: identifies an column within a relation, indicates which columns are system columns, and defines the order of a relation's columns. I'd like to move this last functionality into a separate pg_attribute column named attpos (or attlogicalpos): - when the table is created, attnum == attpos. System columns have attpos 0, as with attnum. At no point will two columns of the same relation have the same attpos. - when returning output to the client and no column ordering is implied by the query (e.g. SELECT * ...), we sort the columns in ascending attpos order. - when storing a tuple on disk, we don't consider attpos - if we want to change the order of the column's in a relation, we can do so merely by updating pg_attribute; no changes to the on-disk storage of the relation should be necessary A few notes: (a) ISTM this should also apply to COPY TO and COPY FROM if the user didn't supply a column list. Is this reasonable? It would break dumps of the table's contents, but then again, dumps aren't guaranteed to remain valid over arbitrary changes to the table's meta-data. (b) Using the above scheme that attnum == attpos initially, there won't be any gaps in the sequence of attpos values. That means that if, for example, we want to move the column in position 50 to position 1, we'll need to change the position's of all the columns in positions [1..49] (and suffer the resulting MVCC bloat in pg_attribute). Changing the column order is hardly a performance critical operation, so that might be acceptable. If we want to avoid this, one easy (but arguably unclean) way to do so would be to make the initial value of attpos == attnum * 1000, and make attpos an int4 rather than an int2. Then, we can do most column reordering operations with only a single pg_attribute update -- in the worst-case that enough re-orderings are done that we overflow the 999 padding positions, we can just fall-back to doing multiple pg_attribute updates. Is this worth doing, and/or is there a better way to achieve the same effect? (c) Do I need to consider inheritance? Comments are welcome. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
Neil Conway writes: (b) Using the above scheme that attnum == attpos initially, there won't be any gaps in the sequence of attpos values. That means that if, for example, we want to move the column in position 50 to position 1, we'll need to change the position's of all the columns in positions [1..49] (and suffer the resulting MVCC bloat in pg_attribute). Changing the column order is hardly a performance critical operation, so that might be acceptable. I don't think you can speak of bloat for pg_attribute. But you can speak of a problem when you want to do the old col = col + 1 in the presence of a unique index. (c) Do I need to consider inheritance? Inheritance is based on column names, so initially no, but if there is a command to alter the column order, then it should have an ONLY option. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logical column position
On Mon, 17 Nov 2003, Neil Conway wrote: I'd like to add a new column to pg_attribute that specifies the attribute's logical position within its relation. The idea here is to separate the logical order of the columns in a relation from the on-disk storage of the relation's tuples. This allows us to easily quickly change column order, add an additional column before or after an existing column, etc. That sounds excellent! (a) ISTM this should also apply to COPY TO and COPY FROM if the user didn't supply a column list. Is this reasonable? It would break dumps of the table's contents, but then again, dumps aren't guaranteed to remain valid over arbitrary changes to the table's meta-data. You're just saying it'd break old dumps, right? I'd assume COPY FROM would use attpos ordering when writing out columns, or that every user-visible interaction with the table pretends the columns are in attpos order. So dumps would break no more or less than when adding or dropping a column currently, right? Jon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] logical column position
Peter Eisentraut [EMAIL PROTECTED] writes: I don't think you can speak of bloat for pg_attribute. But you can speak of a problem when you want to do the old col = col + 1 in the presence of a unique index. I'm sorry, but I'm not sure what either of these comments mean -- can you elaborate? -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logical column position
Jon Jensen [EMAIL PROTECTED] writes: You're just saying it'd break old dumps, right? I'd assume COPY FROM would use attpos ordering when writing out columns, or that every user-visible interaction with the table pretends the columns are in attpos order. So dumps would break no more or less than when adding or dropping a column currently, right? Right -- AFAICS, the only change in COPY compatibility would be if you COPY TO'd a table and then changed the logical column order in some fashion, you would no longer be able to restore the dump (unless you specified a column list for the COPY FROM -- which, btw, pg_dump does). I don't think it will be a problem, I just thought I'd mention it. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] logical column position
Right -- AFAICS, the only change in COPY compatibility would be if you COPY TO'd a table and then changed the logical column order in some fashion, you would no longer be able to restore the dump (unless you specified a column list for the COPY FROM -- which, btw, pg_dump does). I don't think it will be a problem, I just thought I'd mention it. Well it's the same problem as if you'd dropped a column in the middle of the table. BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] logical column position
Neil Conway wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I don't think you can speak of bloat for pg_attribute. But you can speak of a problem when you want to do the old col = col + 1 in the presence of a unique index. I'm sorry, but I'm not sure what either of these comments mean -- can you elaborate? Peter is pointing out a problem with our unique indexes that might cause you a problem. Suppose you have a unique index in attlognum: test= create table xx( lognum int); CREATE TABLE test= insert into xx values (1); INSERT 17145 1 test= insert into xx values (2); INSERT 17146 1 test= update xx set lognum = lognum + 1; UPDATE 2 test= create unique index yy on xx (lognum); CREATE INDEX test= update xx set lognum = lognum + 1; ERROR: duplicate key violates unique constraint yy There is discussion to delay unique constraint failures until commit, then recheck them to see if they are still valid, sort of like what we do with deferred triggers. This would fix the problem because on commit, those values are unique, but aren't while the rows are updated invidually. If we don't get that working you might want to use the 1000 gap idea because it doesn't cause this problem, and we don't support 1600 columns, so a 1000 gap shouldn't cause a problem and can be modified later. If they hit 999 updates, just tell them to dump/reload the table. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logical column position
Neil Conway wrote: I'd like to add a new column to pg_attribute that specifies the attribute's logical position within its relation. The idea here is to separate the logical order of the columns in a relation from the on-disk storage of the relation's tuples. This allows us to easily quickly change column order, add an additional column before or after an existing column, etc. At present, attnum basically does three things: identifies an column within a relation, indicates which columns are system columns, and defines the order of a relation's columns. I'd like to move this last functionality into a separate pg_attribute column named attpos (or attlogicalpos): - when the table is created, attnum == attpos. System columns have attpos 0, as with attnum. At no point will two columns of the same relation have the same attpos. - when returning output to the client and no column ordering is implied by the query (e.g. SELECT * ...), we sort the columns in ascending attpos order. Seems the only cases where attpos would be used would be SELECT *, INSERT with no column list, and COPY --- seems like a nifty feature. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logical column position
On Mon, 2003-11-17 at 20:24, Christopher Kings-Lynne wrote: Right -- AFAICS, the only change in COPY compatibility would be if you COPY TO'd a table and then changed the logical column order in some fashion, you would no longer be able to restore the dump (unless you specified a column list for the COPY FROM -- which, btw, pg_dump does). I don't think it will be a problem, I just thought I'd mention it. Well it's the same problem as if you'd dropped a column in the middle of the table. BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. Yeah... how about maintaining attnum for the logical attribute number and create an attphysnum or something for the physical position instead? This is more intrusive into the source, but you don't need to teach new tricks to external entities. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logical column position
Christopher Kings-Lynne writes: BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. But that is only really important if they've also used the ALTER TABLE RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they need to do another. That seems fair. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logical column position
BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. But that is only really important if they've also used the ALTER TABLE RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they need to do another. That seems fair. Good point. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logical column position
On Tue, 18 Nov 2003, Peter Eisentraut wrote: Christopher Kings-Lynne writes: BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. But that is only really important if they've also used the ALTER TABLE RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they need to do another. That seems fair. The ability to reshuffle and to get the correct ordering in a client app are separate unless we're going to assume that all access goes through that particular client. If one user uses psql and shuffles them, a second user using fooclient may not see the new ordering. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
