Thread: Field's position in Table

Kalai R wrote:
> Hi ,
>
> I have a problem in Postgres.
>
> When adding a new field in the existing table, i want to add the filed
> in a particular position. Is there any way to change the fields
> position in the table. Any Solution to this problem would be
> much appreciated.

don't use * in SELECT statements, and its not a problem.

SQL relations should have neither field order nor row order unless such
is explicitly given

> When adding a new field in the existing table, i
> want to add the filed in a particular position.

I'm afraid the only way would be re-writing the whole table (pseudo sql):

BEGIN;
create table newtable as select field1, 'newfield default value', field2 from old_table;
create_all_indexes on newtable;
drop old_table;
commit;

things get complicated if you have foreign keys pointing to old_table...

On Thu, 20 Aug 2009 09:21:25 +0000 (GMT), Scara Maccai <m_lists@yahoo.it>
wrote:
>> When adding a new field in the existing table, i
>> want to add the filed in a particular position.
>
> I'm afraid the only way would be re-writing the whole table (pseudo
sql):
>
> BEGIN;
> create table newtable as select field1, 'newfield default value', field2
> from old_table;
> create_all_indexes on newtable;
> drop old_table;
> commit;
>
> things get complicated if you have foreign keys pointing to old_table...

Which is why you might be better off putting the new field at the end of
the table
and using an administrative view to make your viewing easier.

I can't really think of any real reason to put the field at a particular
position, applications
don't reallty care about the order of fields.

On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote:
> I can't really think of any real reason to put the field at a
> particular position, applications don't reallty care about the order
> of fields.

Because it's very convenient for ad-hoc queries!  PG currently assumes
that the column order is the same as when it was created but there are
(unimplemented) suggestions about how to "fix" this.  See for example:

  http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php

--
  Sam  http://samason.me.uk/

On 20/08/2009, at 7:24 PM, vinny wrote:

> I can't really think of any real reason to put the field at a
> particular
> position, applications
> don't reallty care about the order of fields.

... unless an application is brain-damaged by its using a wildcard
select, which is a well-known no-no even for home-made scripts, as it
has already been pointed out here.  My point here being that
applications' robustness to apparent field order, like liberty,
shouldn't be taken for granted: it needs to be explicitly minded,
protected and sometimes fought for. :-)

Yar

On Thu, Aug 20, 2009 at 7:45 PM, Yaroslav Tykhiy<yar@barnet.com.au> wrote:
> On 20/08/2009, at 7:24 PM, vinny wrote:
>
>> I can't really think of any real reason to put the field at a particular
>> position, applications
>> don't reallty care about the order of fields.
>
> ... unless an application is brain-damaged by its using a wildcard select,
> which is a well-known no-no even for home-made scripts, as it has already
> been pointed out here.  My point here being that applications' robustness to
> apparent field order, like liberty, shouldn't be taken for granted: it needs
> to be explicitly minded, protected and sometimes fought for. :-)

And if you're going to write some simplified application that depends
on column order, then you should be willing to accept the
responsibility of maintain that order.  I don't want or need such code
in pgsql really, so would rather not have someone playing with the
guts in pgsql to make this happen and breaking anything else.  And it
IS non-trivial to implement in pgsql

On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote:
> On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote:
> > I can't really think of any real reason to put the field at a
> > particular position, applications don't reallty care about the order
> > of fields.
>
> Because it's very convenient for ad-hoc queries!  PG currently assumes
> that the column order is the same as when it was created but there are
> (unimplemented) suggestions about how to "fix" this.  See for example:
>
>   http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php
>
> --
>   Sam  http://samason.me.uk/
>

But how is it convenient exactly, is it just a timesaver so you can
SELECT * instead of having to type SELECT firstname, lastname, email?

Adam Rich wrote:

> For me, saying all new fields must go at the end of the table is like
> saying all new functions must go at the end of your C source file. 
> Not that it makes *any* difference to the end user, or other
> applications using your libraries, but as developers we tend to
> be more organized than the general public.

Just because we don't have it implemented does not make it a bad idea.
I think (and others do as well) it's a good idea to be able to handle
this; it's just that nobody has gotten around to implement it.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Adam Rich wrote:
> For me, saying all new fields must go at the end of the table is like
> saying all new functions must go at the end of your C source file.
> Not that it makes *any* difference to the end user, or other
> applications using your libraries, but as developers we tend to
> be more organized than the general public.  Most programmers
> habitually organize their source code to keep related functions
> together.  It seems sloppy to have 10 memory-related functions
> together in the source, and then an 11th hidden 6 pages down in the
> middle of file-related functions.  And if you're writing OO code in
> C++ or Java, you even group private variables and methods separately
> from public ones.  Most of the people who advocate tacking new fields
> at the end of a table would never dream of following this convention
> for source code.
>


otoh, reordering the fields in a table would likely require a global
exclusive access lock on the table for the duration of the operation,
which for a large table could be substantial.  AFAIK, the current ALTER
TABLE ... ADD COLUMN just locks the table for updates, the pre-existing
fields can still be SELECTed until the ALTER completes and the new
columns become visible.

John R Pierce wrote:

> otoh, reordering the fields in a table would likely require a global
> exclusive access lock on the table for the duration of the
> operation, which for a large table could be substantial.

Obviously you haven't read the previous proposal on how to handle it.
It doesn't require rewriting the whole table.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

----- Original Message -----

From: Adam Rich

To: Sam Mason ; vinny

Cc: pgsql-general@postgresql.org

Sent: Monday, August 24, 2009 2:58 PM

Subject: Re: R: [GENERAL] Field's position in Table

From: vinny <vinny@xs4all.nl> Subject: Re: R: [GENERAL] Field's position in Table To: "Sam Mason" <sam@samason.me.uk> Cc: pgsql-general@postgresql.org Date: Monday, August 24, 2009, 2:38 PM On Thu, 2009-08-20 at 12:16 +0100, Sam Mason wrote: > On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: > > I can't really think of any real reason to put the field at a > > particular position, applications don't reallty care about the order > > of fields. > > Because it's very convenient for ad-hoc queries!  PG currently assumes > that the column order is the same as when it was created but there are > (unimplemented) suggestions about how to "fix" this.  See for example: > >   http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php > > -- >   Sam  http://samason.me.uk/ > But how is it convenient exactly, is it just a timesaver so you can SELECT * instead of having to type SELECT firstname, lastname, email?       For me, saying all new fields must go at the end of the table is like saying all new functions must go at the end of your C source file.  Not that it makes *any* difference to the end user, or other applications using your libraries, but as developers we tend to be more organized than the general public.  Most programmers habitually organize their source code to keep related functions together.  It seems sloppy to have 10 memory-related functions together in the source, and then an 11th hidden 6 pages down in the middle of file-related functions.  And if you're writing OO code in C++ or Java, you even group private variables and methods separately from public ones.  Most of the people who advocate tacking new fields at the end of a table would never dream of following this convention for source code.    So when I'm working in PgAdmin, I like to see my primary & foreign keys listed first, then data fields in logical groupings, and finally the standard "footer" fields we add to all tables like create & update by/date.  Whenever I'm developing and need to reference a table definition, (or do a select * in pgAdmin for sample data) I lose productivity having to scan through all the fields repeatedly instead of seeing at a glance the fields I want because I know where they *should* be in the listing.  Sometimes I have to scan through the fields several times before I finally see the one I want, because it was in the middle of unrelated items.   I *never* code my applications to depend on field order; I'm referring to development convenience only.   (Just my two cents, YMMV, etc)

Michael Gould wrote:
> I come from the Sybase world and more SQL Anywhere.  This is a feature that
> has been asked for on several occassions.  I believe that it is on the
> feature list for V12.  The only reason that it has been asked for is because
> of how rows are stored on a page.  Only the 1st 256 bytes are stored on the
> initial page and the rest of the data is stored on a overflow page.  Those
> columns that are in the first 256 bytes therefore cause less calls to be
> made if a index is created on a column(s) in the first 256 bytes because it
> doesn't need to look at the overflow page.  I don't know if this is true or
> not in PostGres.  If it isn't then I'm not sure what difference it would
> make other than allowing for "pretty" documentation.

Postgres does not overflow pages.  Tuples are stored wholly on a single
page.  If they don't fit, large attributes are stored in a separate
table (the TOAST table) and only a pointer is kept in the main table.
So reordering won't give you that benefit.

The other difference it would make is that it'd open the door for
optimizations like storing all fixed-length not nullable attributes
together at the start of the tuple.  That should give slightly better
performance.

--
Alvaro Herrera                         http://www.flickr.com/photos/alvherre/
"No tengo por qué estar de acuerdo con lo que pienso"
                             (Carlos Caszeli)

Alvaro Herrera wrote:
> Michael Gould wrote:
...
>> doesn't need to look at the overflow page.  I don't know if this is true or
>> not in PostGres.  If it isn't then I'm not sure what difference it would
>> make other than allowing for "pretty" documentation.
>
> Postgres does not overflow pages.  Tuples are stored wholly on a single
> page.  If they don't fit, large attributes are stored in a separate
> table (the TOAST table) and only a pointer is kept in the main table.
> So reordering won't give you that benefit.
>
> The other difference it would make is that it'd open the door for
> optimizations like storing all fixed-length not nullable attributes
> together at the start of the tuple.  That should give slightly better
> performance.
>
And which is quite easily done by:

BEGIN;
CREATE table reorder_footable AS
   SELECT b,c,a
   FROM footable;
DROP TABLE footable;
ALTER TABLE reorder_footable RENAME TO footable;
COMMIT;

yes of course this does not deal with FK correctly
so a lot more work would need to be done for a general
solution - but in some cases it should be all one needs
for the tuple optimization. I personally don't by the
prettyness argument for reordering columns since for
all practical use I prefer SELECT a,b,c over SELECT *

Regards
Tino

Tino Wildenhain wrote:

> And which is quite easily done by:
>
> [ some SQL commands ]

Yeah, there are workarounds, but they have limitations -- they don't
work with FKs, they don't work if there's inheritance, they lose
indexes, and so on.  They remind me how our CLUSTER command worked in
7.1.  Fortunately we fixed most of the problems of that one (except that
it is very slow).

This is one of the things that would be "nice to have".  It won't be a
life changer feature like, say, HOT or streaming replication.  Still,
if/when I have the time and inclination, I'll take a look at it if no
one beats me to it (probably not for 8.5 anyway).

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.