Toward a column reorder solution
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
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.
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:
Show quoted text
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
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.
Frigging copycat.
Any sponsorship for PGXN in there? ;-P
Best,
David
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.
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