Thread: Toward a column reorder solution

Toward a column reorder solution

From
Nilson
Date:
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.

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

Re: Toward a column reorder solution

From
Andrew Dunstan
Date:

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


Re: Toward a column reorder solution

From
Robert Haas
Date:
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


Re: Toward a column reorder solution

From
Andrew Dunstan
Date:

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


Re: Toward a column reorder solution

From
"Joshua D. Drake"
Date:
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

Re: Toward a column reorder solution

From
Nilson Damasceno
Date:
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:

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

Re: Toward a column reorder solution

From
Andrew Dunstan
Date:

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



Re: Toward a column reorder solution

From
"David E. Wheeler"
Date:
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



Re: Toward a column reorder solution

From
"Joshua D. Drake"
Date:
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



Re: Toward a column reorder solution

From
"Joshua D. Drake"
Date:
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
 


Re: Toward a column reorder solution

From
"David E. Wheeler"
Date:
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


Re: Toward a column reorder solution

From
"Joshua D. Drake"
Date:
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

Re: Toward a column reorder solution

From
"Joshua D. Drake"
Date:
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