Обсуждение: Toward a column reorder solution
Quoting "wiki.postgresql.org/wiki/Alter_column_position" :
"The idea of allowing re-ordering of column position is not one the postgresql developers are against, it is more a case where no one has stepped forward to do the work."
Well, a hard journey starts with a single step.
"The idea of allowing re-ordering of column position is not one the postgresql developers are against, it is more a case where no one has stepped forward to do the work."
Well, a hard journey starts with a single step.
Then, in a future release - 9.1 for example - the postgres team can make attnum changeable using something like ALTER COLUMN POSITION?
Pros:
- Requires only a couple of changes in main postgreSQL code. It seems to be very simple.
This will give time to developers of that code to detect the impact of semantics change, make the arrangements necessary and also allow the release of production level software using the new feature before attnum becomes changeable.
So, when attnum becomes read/write, all that software will be ready.Cons
- More 4 bytes in each row of the catalog.
Nilson
Nilson wrote: > Quoting "wiki.postgresql.org/wiki/Alter_column_position > <http://wiki.postgresql.org/wiki/Alter_column_position>" : > > "The idea of allowing re-ordering of column position is not one the > postgresql developers are against, it is more a case where no one has > stepped forward to do the work." > > Well, a hard journey starts with a single step. > > Why not, in the next release that requires to run initdb, add a > *attraw* column (a better name is welcome) in the catalog that stores > the physical position of column forever, i.e., the same semantics of > *attnum*? > > Then, in a future release - 9.1 for example - the postgres team can > make *attnum* changeable using something like ALTER COLUMN POSITION? > > Pros: > > - Requires only a couple of changes in main postgreSQL code. It seems > to be very simple. > > - Allows a smooth and decentralized rewrite of the whole code that may > needs the *attraw *attribute - postgreSQL, contribs, pgAdmin, > drivers, tools etc. > This will give time to developers of that code to detect the impact > of semantics change, make the arrangements necessary and also allow > the release of production level software using the new feature before > *attnum *becomes changeable. > So, when *attnum *becomes read/write, all that software will be ready. > > Cons > > - More 4 bytes in each row of the catalog. > > Nilson Please review the previous discussions on this. In particular, see this proposal from Tom Lane that I believe represents the consensus way we want to go on this: <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php> cheers andrew
On Tue, Jul 27, 2010 at 5:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote: > > > Nilson wrote: >> >> Quoting "wiki.postgresql.org/wiki/Alter_column_position >> <http://wiki.postgresql.org/wiki/Alter_column_position>" : >> >> "The idea of allowing re-ordering of column position is not one the >> postgresql developers are against, it is more a case where no one has >> stepped forward to do the work." >> >> Well, a hard journey starts with a single step. >> >> Why not, in the next release that requires to run initdb, add a *attraw* >> column (a better name is welcome) in the catalog that stores the physical >> position of column forever, i.e., the same semantics of *attnum*? >> >> Then, in a future release - 9.1 for example - the postgres team can make >> *attnum* changeable using something like ALTER COLUMN POSITION? >> >> Pros: >> >> - Requires only a couple of changes in main postgreSQL code. It seems to >> be very simple. >> >> - Allows a smooth and decentralized rewrite of the whole code that may >> needs the *attraw *attribute - postgreSQL, contribs, pgAdmin, drivers, >> tools etc. This will give time to developers of that code to detect the >> impact of semantics change, make the arrangements necessary and also allow >> the release of production level software using the new feature before >> *attnum *becomes changeable. >> So, when *attnum *becomes read/write, all that software will be ready. >> >> Cons >> >> - More 4 bytes in each row of the catalog. >> >> Nilson > > > Please review the previous discussions on this. In particular, see this > proposal from Tom Lane that I believe represents the consensus way we want > to go on this: > <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php> Alvaro is planning to work on this for 9.1, I believe. http://archives.postgresql.org/pgsql-hackers/2010-07/msg00188.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas wrote: >> Please review the previous discussions on this. In particular, see this >> proposal from Tom Lane that I believe represents the consensus way we want >> to go on this: >> <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php> >> > > Alvaro is planning to work on this for 9.1, I believe. > > http://archives.postgresql.org/pgsql-hackers/2010-07/msg00188.php > > Yay! cheers andrew
On Tue, 2010-07-27 at 17:56 -0400, Andrew Dunstan wrote: > > Robert Haas wrote: > >> Please review the previous discussions on this. In particular, see this > >> proposal from Tom Lane that I believe represents the consensus way we want > >> to go on this: > >> <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php> > >> > > > > Alvaro is planning to work on this for 9.1, I believe. > > > > http://archives.postgresql.org/pgsql-hackers/2010-07/msg00188.php > > > > > > Yay! Correct. We are also hoping to get some sponsorship for it. https://www.fossexperts.com/ Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
Andrew,
The Tom's message was in Dec/2006. We are in 2010.
Sincerelly, I'm not afraid to seem naive, but I believe that a column that inherits the persistent semantics of attnum solves the 99.9% problem with column reordering of legacy software.
The exceptions seems to be:
1) software that address buffers based on attnum. Tipically a core/hacker software.
2) INSERTs without naming the columns. This could be solved when attnum become changeable with a server ou database variable allow_attnum_changes with false default value.
The problem addressed by Tom about the need of a primary key for attributes is almost the same of the current solutions to reorder the columns:
a) recreate the table
b) "shift" the columns.
Nilson
On Tue, Jul 27, 2010 at 6:45 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
Nilson wrote:Please review the previous discussions on this. In particular, see this proposal from Tom Lane that I believe represents the consensus way we want to go on this: <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php>Quoting "wiki.postgresql.org/wiki/Alter_column_position <http://wiki.postgresql.org/wiki/Alter_column_position>" :
"The idea of allowing re-ordering of column position is not one the postgresql developers are against, it is more a case where no one has stepped forward to do the work."
Well, a hard journey starts with a single step.
Why not, in the next release that requires to run initdb, add a *attraw* column (a better name is welcome) in the catalog that stores the physical position of column forever, i.e., the same semantics of *attnum*?
Then, in a future release - 9.1 for example - the postgres team can make *attnum* changeable using something like ALTER COLUMN POSITION?
Pros:
- Requires only a couple of changes in main postgreSQL code. It seems to be very simple.
- Allows a smooth and decentralized rewrite of the whole code that may needs the *attraw *attribute - postgreSQL, contribs, pgAdmin, drivers, tools etc. This will give time to developers of that code to detect the impact of semantics change, make the arrangements necessary and also allow the release of production level software using the new feature before *attnum *becomes changeable.
So, when *attnum *becomes read/write, all that software will be ready.
Cons
- More 4 bytes in each row of the catalog.
Nilson
cheers
andrew
Nilson Damasceno wrote: > > The Tom's message was in Dec/2006. We are in 2010. So what? The problem hasn't changed. > > Sincerelly, I'm not afraid to seem naive, but I believe that a column > that inherits the persistent semantics of attnum solves the 99.9% > problem with column reordering of legacy software. You're assuming that the only thing we want to be able to do related to column position is to reorder columns logically. That assumption is not correct. (Incidentally, please don't top-answer). cheers andrew
On Jul 27, 2010, at 3:01 PM, Joshua D. Drake wrote: > Correct. We are also hoping to get some sponsorship for it. > > https://www.fossexperts.com/ Frigging copycat. Any sponsorship for PGXN in there? ;-P Best, David
On Tue, 2010-07-27 at 17:56 -0400, Andrew Dunstan wrote: > > Robert Haas wrote: > >> Please review the previous discussions on this. In particular, see this > >> proposal from Tom Lane that I believe represents the consensus way we want > >> to go on this: > >> <http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php> > >> > > > > Alvaro is planning to work on this for 9.1, I believe. > > > > http://archives.postgresql.org/pgsql-hackers/2010-07/msg00188.php > > > > > > Yay! Correct. We are also hoping to get some sponsorship for it. https://www.fossexperts.com/ Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Tue, 27 Jul 2010 19:55:18 -0700, "David E. Wheeler" <david@kineticode.com> wrote: > On Jul 27, 2010, at 3:01 PM, Joshua D. Drake wrote: > >> Correct. We are also hoping to get some sponsorship for it. >> >> https://www.fossexperts.com/ > > Frigging copycat. Hah! I gave you kudos :P (you are in the FAQ) JD -- PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
On Jul 28, 2010, at 7:57 AM, Joshua D. Drake wrote: > Hah! I gave you kudos :P (you are in the FAQ) Ah, thanks. The link is missing a "G": It's "PGXN," not "PXN". Best, David
On Wed, 2010-07-28 at 09:30 -0700, David E. Wheeler wrote: > On Jul 28, 2010, at 7:57 AM, Joshua D. Drake wrote: > > > Hah! I gave you kudos :P (you are in the FAQ) > > Ah, thanks. The link is missing a "G": It's "PGXN," not "PXN". Yeah that is already fixed, just waiting for cache to clear (on the server). Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
On Wed, 2010-07-28 at 09:30 -0700, David E. Wheeler wrote: > On Jul 28, 2010, at 7:57 AM, Joshua D. Drake wrote: > > > Hah! I gave you kudos :P (you are in the FAQ) > > Ah, thanks. The link is missing a "G": It's "PGXN," not "PXN". Yeah that is already fixed, just waiting for cache to clear (on the server). Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt