Thread: full featured alter table?

full featured alter table?

From
Sven Koehler
Date:
Hi,

one of the biggest disease of PostGreSQL is, that i can't change the
definition of a column.

In order to do that, i'd have to drop any keys, drop the column and
create a new one with all indexes etc.

Are there any plans to overcome that problem?
Even simple changes like varchar(20) to varchar(200) are not allowed.

I asked this question about 2 years ago, and there were only some guys,
that told me that i wouldn't need to change my DB if i'd plan it well.

So my DB is planned well, but i have to change it every now and than
because i must implement the changes that my client demands me to do,
and have some extra work that nobody will pay me for, if there's no way
to change a column.

This is the only missing feature, that prevent me to use this DBMS - i'd
love to, because it's an ORDBMS and that's what i'd have needed sometimes.


Re: full featured alter table?

From
Jonathan Bartlett
Date:
The process you describe really is not that difficult.  My general method
is:

Rename existing column
Create new column
Repopulate from existing column
Drop existing column
Re-Add constraints/indexes (honestly I don't use constraints that much).

Jon

On Thu, 12 Jun 2003, Sven Koehler wrote:

> Hi,
>
> one of the biggest disease of PostGreSQL is, that i can't change the
> definition of a column.
>
> In order to do that, i'd have to drop any keys, drop the column and
> create a new one with all indexes etc.
>
> Are there any plans to overcome that problem?
> Even simple changes like varchar(20) to varchar(200) are not allowed.
>
> I asked this question about 2 years ago, and there were only some guys,
> that told me that i wouldn't need to change my DB if i'd plan it well.
>
> So my DB is planned well, but i have to change it every now and than
> because i must implement the changes that my client demands me to do,
> and have some extra work that nobody will pay me for, if there's no way
> to change a column.
>
> This is the only missing feature, that prevent me to use this DBMS - i'd
> love to, because it's an ORDBMS and that's what i'd have needed sometimes.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>


Re: full featured alter table?

From
Sven Koehler
Date:
> The process you describe really is not that difficult.  My general method
> is:

yes, but it takes time and is error-prone.
it a feature that most DBMS have, but postgresql does not, and what you
describe is a nice workaround, but not an answer to my question.

when will postgresql will finally have a full-featured alter table command?


Re: full featured alter table?

From
Stephan Szabo
Date:
On Thu, 12 Jun 2003, Sven Koehler wrote:

> one of the biggest disease of PostGreSQL is, that i can't change the
> definition of a column.
>
> In order to do that, i'd have to drop any keys, drop the column and
> create a new one with all indexes etc.
>
> Are there any plans to overcome that problem?

Pretty much when someone who cares about it enough comes along with
a sufficient plan (and preferrably code) to implement it without breaking
things would be my guess (especially given that AFAICS it's not part of
either SQL92 or SQL99). Note that a sufficient plan would possibly
involve a lot of things not directly involved with changing the type
such as being able to deal with cached query plans for functions and
such.

> Even simple changes like varchar(20) to varchar(200) are not allowed.

True, but at least these are simple manipulations of the system catalog.

> So my DB is planned well, but i have to change it every now and than
> because i must implement the changes that my client demands me to do,
> and have some extra work that nobody will pay me for, if there's no way
> to change a column.

Why?  If your client asks for a change, they should pay for what's
involved. You have to make it known that changes are more costly for
this setup up front, but if it has other advantages, they can choose
which way to go.



Re: full featured alter table?

From
Sven Koehler
Date:
> Pretty much when someone who cares about it enough comes along with
> a sufficient plan (and preferrably code) to implement it without breaking
> things would be my guess (especially given that AFAICS it's not part of
> either SQL92 or SQL99). Note that a sufficient plan would possibly
> involve a lot of things not directly involved with changing the type
> such as being able to deal with cached query plans for functions and
> such.

In other word, nobody cares about that at the moment, and so this will
not be implemented.

something i hate most about opensource movement are these comments like
"submit a patch". your staments are not that rough, but i'd need more
time to get into the code than to implement the feature.
so if this feature was never foreseen, than it might get a heavy task,
and it might be even havier in a few years to implement that.

even a tool could help to improve that. perhaps pgAdmin could be teached
to change a column's type by performing the steps it would need.

i'll submit a feature-request to the pgadmin team.


Re: full featured alter table?

From
Stephan Szabo
Date:
On Thu, 12 Jun 2003, Sven Koehler wrote:

> > Pretty much when someone who cares about it enough comes along with
> > a sufficient plan (and preferrably code) to implement it without breaking
> > things would be my guess (especially given that AFAICS it's not part of
> > either SQL92 or SQL99). Note that a sufficient plan would possibly
> > involve a lot of things not directly involved with changing the type
> > such as being able to deal with cached query plans for functions and
> > such.
>
> In other word, nobody cares about that at the moment, and so this will
> not be implemented.

Actually, it's more that it's a big job (bigger than actually just
changing the column) and it has some side issues about things like storage
(can we use 2x space to do it, do we need to do something else, what
happens if it fails part way due to something like a failure to convert
data), stored plans (do plpgsql functions and foreign keys continue
functioning after it, what about set returning functions that return that
type), and probably other things that I can't think of. With limited
resources, you have to make choices about what you work on. I'm sure Tom
(for example) could have done it, but he possibly wouldn't have been able
to do some or all of the error code stuff, protocol changes, expression
indexes, various optimizer enhancements, bug fixes, etc.

> something i hate most about opensource movement are these comments like
> "submit a patch". your staments are not that rough, but i'd need more
> time to get into the code than to implement the feature.
> so if this feature was never foreseen, than it might get a heavy task,
> and it might be even havier in a few years to implement that.

It's actually probably going to move towards being easier over time
hopefully, since some of the side issues are things that are probably
going to be addressed anyway.


Re: full featured alter table?

From
Reynard Hilman
Date:
I would agree with you Sven, I really like that feature to be added too,
because that's one of the important reasons I still prefer mysql
whenever I don't have to use other features that postgres has.

- reynard

Sven Koehler wrote:

> In other word, nobody cares about that at the moment, and so this will
> not be implemented.
>
> something i hate most about opensource movement are these comments
> like "submit a patch". your staments are not that rough, but i'd need
> more time to get into the code than to implement the feature.
> so if this feature was never foreseen, than it might get a heavy task,
> and it might be even havier in a few years to implement that.
>
> even a tool could help to improve that. perhaps pgAdmin could be
> teached to change a column's type by performing the steps it would need.
>
> i'll submit a feature-request to the pgadmin team.




Re: full featured alter table?

From
"scott.marlowe"
Date:
On Thu, 12 Jun 2003, Sven Koehler wrote:

> Hi,
>
> one of the biggest disease of PostGreSQL is, that i can't change the
> definition of a column.
>
> In order to do that, i'd have to drop any keys, drop the column and
> create a new one with all indexes etc.
>
> Are there any plans to overcome that problem?
> Even simple changes like varchar(20) to varchar(200) are not allowed.
>
> I asked this question about 2 years ago, and there were only some guys,
> that told me that i wouldn't need to change my DB if i'd plan it well.
>
> So my DB is planned well, but i have to change it every now and than
> because i must implement the changes that my client demands me to do,
> and have some extra work that nobody will pay me for, if there's no way
> to change a column.
>
> This is the only missing feature, that prevent me to use this DBMS - i'd
> love to, because it's an ORDBMS and that's what i'd have needed sometimes.

OK, so how do we handle things like converting a varchar to a timestamp
with time zone?  What if one of the rows has invalid date syntax?  Do we
convert the column anyway, or throw the whole change out with an error?

What about converting int8 to int4 where we have integers that are LARGER
than what an int4 can hold.  Do we just set the int4 to max/min when the
int8 is out of range?

How about converting varchar(64) to varchar(32).  Do you want it to error
out if there are any fields over 32 chars, or just chop them off?

MySQL has a "feature" like the one you want.  It's not well thought out,
and easily causes as much misery and pain as it prevents.  If you convert
a column to date type it silently converts any field it can't suss out to
the very wonderful value of 0000-00-00.  Since their DDL is not
transactable, you've just lost all your dates.  Hope you had a backup
laying around.

Converting column types is like walking around with a gun pointed at your
foot.  If the possible error cases aren't well thought out, then bam! the
gun goes off and your data is so much mush.

Other vendors may not care as much about data integrity as the postgresql
team, and if you prefer their product feel free to use it.  The Postgresql
team has a tendency of not incorporating such changes until they can work
for virtually all cases without error.

Here's how I do it:

# pg_dump -a -t tabletochange >tabledata.sql
# pg_dump -s -t tabletochange >tableschema.sql
vi tableschema.sql (make changes)
psql testdatabase <tableschema.sql
psql testdatabase <tabledata.sql
psql testdatabase

look and see if your data is still there.

Changing column types is playing fast and loose with your data and is not
recommended.

It's also, as near as I can tell, not in the SQL3 spec.

It may bug you that open source projects say "submit a patch and we'll
see" but that's way better than "oh yeah, we have that feature.  what?
Your data got mucked up?  Wow, that's never happened before."


Re: full featured alter table?

From
Reynard Hilman
Date:
>OK, so how do we handle things like converting a varchar to a timestamp
>with time zone?  What if one of the rows has invalid date syntax?  Do we
>convert the column anyway, or throw the whole change out with an error?
>
being a developer instead of DBA makes me think little about the danger
of losing data when you change column type. But, I think you're right,
very BAD things could happen with your data if the database silently
convert / truncate your data when you change the column type.
As far as I remember when working with Oracle, it allows you to change
the data as long as you don't lose or corrupt your data. So, for
example, changing varchar(20) to varchar(40) should be ok, but the
reverse might not be ok. It'd be nice if it allows you to change the
type from varchar(40) to varchar(20) if you don't have any data that is
larger than varchar(20). I don't know how much complexity that will add,
though. well that's just some idea from me.

- reynard



Re: full featured alter table?

From
Alvaro Herrera
Date:
On Fri, Jun 13, 2003 at 05:05:32PM +0000, Reynard Hilman wrote:

> As far as I remember when working with Oracle, it allows you to change
> the data as long as you don't lose or corrupt your data. So, for
> example, changing varchar(20) to varchar(40) should be ok, but the
> reverse might not be ok. It'd be nice if it allows you to change the
> type from varchar(40) to varchar(20) if you don't have any data that is
> larger than varchar(20). I don't know how much complexity that will add,
> though. well that's just some idea from me.

It's not _that_ hard IMHO.  It's just that no one has ever bothered to
code it.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El dia que dejes de cambiar dejaras de vivir"

Re: full featured alter table?

From
Tom Lane
Date:
"scott.marlowe" <scott.marlowe@ihs.com> writes:
> OK, so how do we handle things like converting a varchar to a timestamp
> with time zone?  What if one of the rows has invalid date syntax?  Do we
> convert the column anyway, or throw the whole change out with an error?

I think this particular point is a red herring.  We have data conversion
functions.  My thought would be to apply the same cast function we
would if you were doing an implicit-cast assignment.  That is, the
behavior would be exactly like

    ALTER TABLE tab ADD COLUMN newcol newtype;
    UPDATE tab SET newcol = oldcol;

If there is no cast function, or the cast function burps at any row,
then the command fails and rolls back ... then it's up to you to fix
the data and try again, or use a manual process with some more-complex
conversion function.

However, there are plenty of much-more-subtle semantic issues to worry
about.  Here are a couple:

* Indexes.  How do you translate an index definition involving one
datatype into an index involving another?  There may be no index opclass
at all for the given index type and the new datatype, or there might be
multiple opclasses. Consider for example the recent discussions about
providing reverse-sort opclasses standardly.  I'm not sure how ALTER
TABLE could pick the right opclass if there's more than one choice.

* Constraints.  If the old column has, say, CHECK (foo(x) > 0), how do
we translate this to a new datatype?  Should we assume that if we can
find a function named foo() on the new datatype, it's the right thing
to use?  Seems like a mighty dangerous assumption to me.

Functional indexes present *both* of these sets of problems, and I think
there are probably other issues lurking in the advanced features.

Now a human working through the conversion process by hand probably
wouldn't have a lot of problems deciding what to do, but I'm unconvinced
that an automatic ALTER command would get these things right.

What would make more sense to me than a tightly-wrapped-up ALTER command
at the SQL level is some sort of interactive conversion wizard, perhaps
as part of phppgadmin or Red Hat's graphical tool suite.  It could walk
you through all these considerations, get your okay on each nontrivial
semantic change, and then apply all the operations within a single
transaction.  AFAIK we have all the ALTER functionality needed to
support such a tool.

            regards, tom lane

Re: full featured alter table?

From
weigelt@metux.de
Date:
On Fri, Jun 13, 2003 at 05:05:32PM +0000, Reynard Hilman wrote:

<snip>
> It'd be nice if it allows you to change the type from varchar(40)
> to varchar(20) if you don't have any data that is larger than varchar(20).
Yeah, and then an application comes in and wants to write more than
20 chars ...

well, i dont like such size limitations at all - i'm using "text" instead.
what about efficiency ? does it bring _anything_ to limit the size
of varchar fields ?

> I don't know how much complexity that will add, though. well that's
> just some idea from me.
hmm, i dont think, its really needed. if you really want to do that,
you should also think _very carefully_ 'bout what you're doing.
and so you can type the 3 more statements to create a new table,
copy the data, drop the old table and rename the new one.

cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux ITS
 Webhosting ab 5 EUR/Monat.          UUCP, rawIP und vieles mehr.

 phone:     +49 36207 519931         www:       http://www.metux.de/
 fax:       +49 36207 519932         email:     contact@metux.de
 cellphone: +49 174 7066481         smsgate:   sms.weigelt@metux.de
---------------------------------------------------------------------
 Diese Mail wurde mit UUCP versandt.      http://www.metux.de/uucp/

Re: full featured alter table?

From
"Jim C. Nasby"
Date:
On Fri, Jun 13, 2003 at 06:49:01PM -0400, Tom Lane wrote:
> What would make more sense to me than a tightly-wrapped-up ALTER command
> at the SQL level is some sort of interactive conversion wizard, perhaps
> as part of phppgadmin or Red Hat's graphical tool suite.  It could walk
> you through all these considerations, get your okay on each nontrivial
> semantic change, and then apply all the operations within a single
> transaction.  AFAIK we have all the ALTER functionality needed to
> support such a tool.

The one thing we don't have that I think would be useful is a way to
re-order the columns in a table. Maybe it's just me, but I tend to want
column to appear in a specific order, and the only way you can
accomplish this today is by re-creating the entire table.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: full featured alter table?

From
Sven Köhler
Date:
> The one thing we don't have that I think would be useful is a way to
> re-order the columns in a table. Maybe it's just me, but I tend to want
> column to appear in a specific order, and the only way you can
> accomplish this today is by re-creating the entire table.

i agree. i'd love to "reorder" columns.



Re: full featured alter table?

From
Guillaume LELARGE
Date:
Le Samedi 14 Juin 2003 15:34, Sven Köhler a écrit :
> > The one thing we don't have that I think would be useful is a way to
> > re-order the columns in a table. Maybe it's just me, but I tend to want
> > column to appear in a specific order, and the only way you can
> > accomplish this today is by re-creating the entire table.
>
> i agree. i'd love to "reorder" columns.
>
Same for me. It would be great to be able to reorganize some tables...

--
Guillaume <!-- http://absfr.tuxfamily.org/ -->.

Re: full featured alter table?

From
Bruno Wolff III
Date:
On Sat, Jun 14, 2003 at 10:31:02 -0500,
  "Jim C. Nasby" <jim@nasby.net> wrote:
>
> The one thing we don't have that I think would be useful is a way to
> re-order the columns in a table. Maybe it's just me, but I tend to want
> column to appear in a specific order, and the only way you can
> accomplish this today is by re-creating the entire table.

You can specify the order that columns are output now. The real underlying
order should really be up to postgres to decide based on what is most
efficient.
If you were thinking about the order you get when you use * as a shorthand
for all of the columns, perhaps there could be some way to change that
without actually changing the order of the columns in the table.
I doubt it would be a good idea to do that though. It is already a bad
idea to use * in applications in case something does change the order
or number of columns. * is mostly going to be used in adhoc queries
where you care about saving some typing. It may not be worth adding
a feature like that for the limited amount of use it would get.

RE : full featured alter table?

From
"Bruno BAGUETTE"
Date:
Hello everybody,

> The one thing we don't have that I think would be useful is a
> way to re-order the columns in a table. Maybe it's just me,
> but I tend to want column to appear in a specific order, and
> the only way you can accomplish this today is by re-creating
> the entire table.

I agree with Jim, this would be a 'cool but not mandatory' feature ! :-)

This feature is not mandatory since I avoid the use SELECT * FROM...
(and I forbid the use of SELECT * to my subordinates).

But, it would be very nice to have the possibility order the different
columns of a table in PostgreSQL. Why ? For example, I use a
reverse-engineering tool that generate graphical database map but that
tool don't give me the possibility to change the display order... So,
this feature would be very nice ! :-)

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net


Re: full featured alter table?

From
merlyn@stonehenge.com (Randal L. Schwartz)
Date:
>>>>> "Jim" == Jim C Nasby <jim@nasby.net> writes:

Jim> The one thing we don't have that I think would be useful is a way to
Jim> re-order the columns in a table. Maybe it's just me, but I tend to want
Jim> column to appear in a specific order, and the only way you can
Jim> accomplish this today is by re-creating the entire table.

Column order shouldn't matter.  It matters only when you say "SELECT *",
and the best literature out there warns against the foibles of such.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@stonehenge.com> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!

Re: full featured alter table?

From
Sven Köhler
Date:
 > You can specify the order that columns are output now. The real
 > underlying
 > order should really be up to postgres to decide based on what is most
 > efficient.

how can i specify it?
i think we all ment cosmetical changes, not the "real" underlying order
which should be up to postgresql of course!

 > If you were thinking about the order you get when you use * as a
 > shorthand
 > for all of the columns, perhaps there could be some way to change that
 > without actually changing the order of the columns in the table.

"select *" should refelect the cosmetical order of the columns.
"select *" could be tranformed into something like "select col1, col2,
..." according to the cosmetical order that's defined.
if that's not the case at the moment, i don't care, because i don't use
"select *" by assuming any column-order for the reasons you already
mentioned.



Re: full featured alter table?

From
Sven Köhler
Date:
 >>> You can specify the order that columns are output now. The real
 >>> underlying
 >>> order should really be up to postgres to decide based on what is
 >>> most
 >>> efficient.
 >>
 >> how can i specify it?
 >> i think we all ment cosmetical changes, not the "real" underlying
 >> order which should be up to postgresql of course!

let's express it another way: we want to define the order of the columns
when they are shown in tools like phpPgAdmin or PgAdminII.

there should be some statement which is used by the tools to query the
list of columns in the table - the resultset contains the columns in a
defined order which is - in most cases - the order in which the columns
were created. we'd like to be abled influence the order of the columns
within that resultset.

 >>> If you were thinking about the order you get when you use * as a
 >>> shorthand
 >>> for all of the columns, perhaps there could be some way to change
 >>> that
 >>> without actually changing the order of the columns in the table.
 >>
 >> "select *" should refelect the cosmetical order of the columns.
 >> "select *" could be tranformed into something like "select col1,
 >> col2, ..." according to the cosmetical order that's defined.
 >> if that's not the case at the moment, i don't care, because i don't
 >> use "select *" by assuming any column-order for the reasons you
 >> already mentioned.
 >
 > It sounds what you are looking for is soem way to set the order so
 > that you can save some typing when doing adhoc queries?

no - i don't do any "select *" - it's just a cosmetical thing, because
after some time - especially when you have to perform the discussed
steps to change a column definition - your columns will mix-up.
Usually the order of the columns in a table follows some intension of
the user who created it.


Re: full featured alter table?

From
Sven Köhler
Date:
> Jim> The one thing we don't have that I think would be useful is a way to
> Jim> re-order the columns in a table. Maybe it's just me, but I tend to want
> Jim> column to appear in a specific order, and the only way you can
> Jim> accomplish this today is by re-creating the entire table.
>
> Column order shouldn't matter.  It matters only when you say "SELECT *",
> and the best literature out there warns against the foibles of such.

I think we don't want to reorder our column to make "select *" have a
order that fits our needs.

as i said in my other posts, we only want it for our selves to have a
defined order, that doesn't look that much chaotic.


Re: full featured alter table?

From
Bruno Wolff III
Date:
On Sat, Jun 14, 2003 at 19:35:15 +0200,
  Sven Köhler <skoehler@upb.de> wrote:
>
> let's express it another way: we want to define the order of the columns
> when they are shown in tools like phpPgAdmin or PgAdminII.

That is something that those tools should do then. It isn't really a
postgres issue as it already provides ways for the tools to get
information about columns of a table.

Re: full featured alter table?

From
Sven Köhler
Date:
>>let's express it another way: we want to define the order of the columns
>>when they are shown in tools like phpPgAdmin or PgAdminII.
>
> That is something that those tools should do then. It isn't really a
> postgres issue as it already provides ways for the tools to get
> information about columns of a table.

The problem is, that the information should be stored somewhere in the
database.
If each tool does maintain its own column-order storage, that we'll have
a chaos.


Re: full featured alter table?

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
>   Sven K�hler <skoehler@upb.de> wrote:
>> let's express it another way: we want to define the order of the columns
>> when they are shown in tools like phpPgAdmin or PgAdminII.

> That is something that those tools should do then. It isn't really a
> postgres issue as it already provides ways for the tools to get
> information about columns of a table.

Perhaps, but pg_attribute is the natural place to keep the column
ordering info.

ISTM we talked months ago about adding an "attlognum" column to
pg_attribute, which would be separate from "attnum" (the physical column
position) and would be used by the backend only to determine the order
of SELECT * expansion.  Admin tools could use this to determine column
display order too.  It didn't get done, but it still seems like a
reasonable idea to me.

            regards, tom lane

Re: full featured alter table?

From
Sven Köhler
Date:
> Perhaps, but pg_attribute is the natural place to keep the column
> ordering info.

i don't know what pg_attribute is, but sound to me like it is a good
sollution.

> ISTM we talked months ago about adding an "attlognum" column to
> pg_attribute, which would be separate from "attnum" (the physical column
> position) and would be used by the backend only to determine the order
> of SELECT * expansion.  Admin tools could use this to determine column
> display order too.  It didn't get done, but it still seems like a
> reasonable idea to me.

it wouldn't matter much to me, if "select *" wouldn't be expanded in the
correct order, but it might worry other users.
the first thing to achieve is to define and offer a place where tools
can store the ordering information.
the second would be to make "select *" to use this information, although
  this information should be well cached than if "select *" is expected
to be used often.


Re: full featured alter table?

From
Tino Wildenhain
Date:
Hi,

Reynard Hilman wrote:
>
>> OK, so how do we handle things like converting a varchar to a
>> timestamp with time zone?  What if one of the rows has invalid date
>> syntax?  Do we convert the column anyway, or throw the whole change
>> out with an error?
>>
> being a developer instead of DBA makes me think little about the danger
> of losing data when you change column type. But, I think you're right,
> very BAD things could happen with your data if the database silently
> convert / truncate your data when you change the column type.
> As far as I remember when working with Oracle, it allows you to change
> the data as long as you don't lose or corrupt your data. So, for
> example, changing varchar(20) to varchar(40) should be ok, but the
> reverse might not be ok. It'd be nice if it allows you to change the
> type from varchar(40) to varchar(20) if you don't have any data that is
> larger than varchar(20). I don't know how much complexity that will add,
> though. well that's just some idea from me.

This of course would be convenient, but otoh it would add a great value
of complexity to the backend for a rarely used feature. At least it
should be rarely used :)
For the rare cases where someone has to change column type, maybe its
more easy if (s)he does this by hand - thus not complaining to the
overloaded core-developers about data loss or unexpected results.
The only thing which is a bit complicated is the disabling/enabling
trigger sequence which can be borrowed from pg_dump output.
The remaining part is possible to put all in one transaction;
or at least the delete rows/copy back part:
create table ... as select .... from original ...;
delete from original;
alter table original create column (with new type)
alter table remove old column
insert into original select * from temptable

Regards
Tino


Re: full featured alter table?

From
weigelt@metux.de
Date:
On Sat, Jun 14, 2003 at 07:35:15PM +0200, Sven K?hler wrote:

<snip>
> let's express it another way: we want to define the order of the columns
> when they are shown in tools like phpPgAdmin or PgAdminII.
>
> there should be some statement which is used by the tools to query the
> list of columns in the table - the resultset contains the columns in a
> defined order which is - in most cases - the order in which the columns
> were created. we'd like to be abled influence the order of the columns
> within that resultset.
this does _NOT_ belong into the postmaster. you're free to define
your own tables for storing this (which are not shown to the user
in your frontend). pgaccess goes this way.

cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux ITS
 Webhosting ab 5 EUR/Monat.          UUCP, rawIP und vieles mehr.

 phone:     +49 36207 519931         www:       http://www.metux.de/
 fax:       +49 36207 519932         email:     contact@metux.de
 cellphone: +49 174 7066481         smsgate:   sms.weigelt@metux.de
---------------------------------------------------------------------
 Diese Mail wurde mit UUCP versandt.      http://www.metux.de/uucp/

Re: full featured alter table?

From
Sven Köhler
Date:
>>there should be some statement which is used by the tools to query the
>>list of columns in the table - the resultset contains the columns in a
>>defined order which is - in most cases - the order in which the columns
>>were created. we'd like to be abled influence the order of the columns
>>within that resultset.
>
> this does _NOT_ belong into the postmaster. you're free to define
> your own tables for storing this (which are not shown to the user
> in your frontend). pgaccess goes this way.

what are you talking about? it is not _my_ frontend!
we're talking "frontends" about pgAdmin, phpPgAdmin etc.
we are talking about the order of the columns that postgresql shows to
it's clients - and yes: this data belongs somewhere into the sys-tables
of postgre.


Re: full featured alter table?

From
Sven Köhler
Date:
>>It'd be nice if it allows you to change the type from varchar(40)
>>to varchar(20) if you don't have any data that is larger than varchar(20).
>
> Yeah, and then an application comes in and wants to write more than
> 20 chars ...

why should i define a column as varchar(20) and should than want to
write more data than that?
i'd only define it as varchar(20) if i'm sure that 20 chars are enough.

> well, i dont like such size limitations at all - i'm using "text" instead.
> what about efficiency ? does it bring _anything_ to limit the size
> of varchar fields ?

well - than use text instead of varchar.


Re: full featured alter table?

From
weigelt@metux.de
Date:
On Sat, Jun 14, 2003 at 10:18:30PM +0200, Sven K?hler wrote:

<snip>
> what are you talking about? it is not _my_ frontend!
> we're talking "frontends" about pgAdmin, phpPgAdmin etc.
yes. why couldnt they simply use an extra table for this - just
like pgaccess does for storing its config ?
user-side column odering is frontend stuff.
it dont like the idea that SELECT * doesnt use the physical order
(from pg_attribute - which you also can see w/ \d in the pgsql frontend)

cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux ITS
 Webhosting ab 5 EUR/Monat.          UUCP, rawIP und vieles mehr.

 phone:     +49 36207 519931         www:       http://www.metux.de/
 fax:       +49 36207 519932         email:     contact@metux.de
 cellphone: +49 174 7066481         smsgate:   sms.weigelt@metux.de
---------------------------------------------------------------------
 Diese Mail wurde mit UUCP versandt.      http://www.metux.de/uucp/

Re: full featured alter table?

From
weigelt@metux.de
Date:
On Sat, Jun 14, 2003 at 10:21:16PM +0200, Sven K?hler wrote:

<snip>
> >Yeah, and then an application comes in and wants to write more than
> >20 chars ...
>
> why should i define a column as varchar(20) and should than want to
> write more data than that?
> i'd only define it as varchar(20) if i'm sure that 20 chars are enough.
Perhaps there's still some code which still expects longer fields ?
especially in larger applications w/ many developers/admis this can
be really dangerous.

well, if you really want to do this, an perl script which creates
a new table and copies the data could also suit your needs.

we shouldnt make the postmaster codebase too big. each single line
can contain many errors.

cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux ITS
 Webhosting ab 5 EUR/Monat.          UUCP, rawIP und vieles mehr.

 phone:     +49 36207 519931         www:       http://www.metux.de/
 fax:       +49 36207 519932         email:     contact@metux.de
 cellphone: +49 174 7066481         smsgate:   sms.weigelt@metux.de
---------------------------------------------------------------------
 Diese Mail wurde mit UUCP versandt.      http://www.metux.de/uucp/

Re: full featured alter table?

From
"Jay O'Connor"
Date:
At 10:32 PM 06/14/2003 +0200, weigelt@metux.de wrote:
>On Sat, Jun 14, 2003 at 10:18:30PM +0200, Sven K?hler wrote:
>
><snip>
>> what are you talking about? it is not _my_ frontend!
>> we're talking "frontends" about pgAdmin, phpPgAdmin etc.
>yes. why couldnt they simply use an extra table for this - just
>like pgaccess does for storing its config ?
>user-side column odering is frontend stuff.
>it dont like the idea that SELECT * doesnt use the physical order
>(from pg_attribute - which you also can see w/ \d in the pgsql frontend)

I tend to agree that column ordering does not belong in that database.
Simply because I find it hard to believe that yuo would only and always
want "SELECT *" to come across to the client in a specific order.  Context
indicates what the column order needs to be.

One other wrinkle is what to do wth "SELECT * from tablea, tableb"  Do you
use all of tablea and then all of tableb?  Or interleave with (first from
tablea, first from tableb, second from tablea, second from tableb...)

Besides I usually load a record in a dictionary structure in my client
language (python or smalltalk) which means a) the order is based on the
hash ordering of the dictionary structure anyway and b) I'm doing things by
name, not position.

Take care,
Jay

Re: full featured alter table?

From
Sven Köhler
Date:
>>why should i define a column as varchar(20) and should than want to
>>write more data than that?
>>i'd only define it as varchar(20) if i'm sure that 20 chars are enough.
>
> Perhaps there's still some code which still expects longer fields ?
> especially in larger applications w/ many developers/admis this can
> be really dangerous.

well - you assume that i don't know the needs of my app.
i know what i do, so i know why i change a column to varchar(20).


Re: full featured alter table?

From
Sven Köhler
Date:
>>what are you talking about? it is not _my_ frontend!
>>we're talking about "frontends" like pgAdmin, phpPgAdmin etc.
>
> yes. why couldnt they simply use an extra table for this - just
> like pgaccess does for storing its config ?
> user-side column odering is frontend stuff.
> it dont like the idea that SELECT * doesnt use the physical order
> (from pg_attribute - which you also can see w/ \d in the pgsql frontend)

so why don't you like it to use the user-defined order? do _you_ use
"select *" ?
BTW: if you change the user-defined order, it will match the physical
order, so you it's something you don't have to care about.

If the postgresql-team decides, that the column-order is not to be
stored in the sys-tables, that it woul be the last step to store it in
some other tables, but that is the worst step, as each tool will surely
have it own tables etc. if it ones to support column ordering.

if you think that column-ordering is just luxury, than i might remind
you, table-names and column-names are just luxury too ;-)


Re: full featured alter table?

From
Sven Köhler
Date:
> I tend to agree that column ordering does not belong in that database.
> Simply because I find it hard to believe that yuo would only and always
> want "SELECT *" to come across to the client in a specific order.  Context
> indicates what the column order needs to be.

how many times must i pretend, that "SELECT *" is the last thing i'm
worrying about, huh?


Re: full featured alter table?

From
"Nigel J. Andrews"
Date:
On Sun, 15 Jun 2003, Sven Köhler wrote:

> >>what are you talking about? it is not _my_ frontend!
> >>we're talking about "frontends" like pgAdmin, phpPgAdmin etc.
> >
> > yes. why couldnt they simply use an extra table for this - just
> > like pgaccess does for storing its config ?
> > user-side column odering is frontend stuff.
> > it dont like the idea that SELECT * doesnt use the physical order
> > (from pg_attribute - which you also can see w/ \d in the pgsql frontend)
>
> so why don't you like it to use the user-defined order? do _you_ use
> "select *" ?
> BTW: if you change the user-defined order, it will match the physical
> order, so you it's something you don't have to care about.
>
> If the postgresql-team decides, that the column-order is not to be
> stored in the sys-tables, that it woul be the last step to store it in
> some other tables, but that is the worst step, as each tool will surely
> have it own tables etc. if it ones to support column ordering.
>
> if you think that column-ordering is just luxury, than i might remind
> you, table-names and column-names are just luxury too ;-)

Well, on the basis that column ordering is presentation issue it does belong in
the frontend. However, as Tom pointed out this discussion was had a few
months. I can't remember the outcome but apparently a suggestion was to have a
column in pg_attribute giving the presentational order.

The archives throw up:
http://archives.postgresql.org/pgsql-hackers/2002-09/msg00966.php however I
don't think Christopher has completely covered the issues.

The idea for implementing this is so tools, such as pgaccess, can list a
table's columns in some arbitrary order, presumably by someone displaying the
table and drag and dropping a column from one position to another. What about
the support for that? To change pg_attribute the tool has to be connected as a
superuser or there has to be some command, probably part of ALTER TABLE? And
then there is the issue of priviledge to use that, you don't want the column
order as shown to you changed just because some other person wanted to see them
in a different order. Imagine if both were working at the same time and
continually trying to get the display order they want.


--
Nigel J. Andrews



Re: full featured alter table?

From
Ernest E Vogelsinger
Date:
At 12:33 15.06.2003, Nigel J. Andrews said:
--------------------[snip]--------------------
>Well, on the basis that column ordering is presentation issue it does
>belong in
>the frontend. However, as Tom pointed out this discussion was had a few
>months. I can't remember the outcome but apparently a suggestion was to have a
>column in pg_attribute giving the presentational order.
--------------------[snip]--------------------

Holdit.

This whole thread eveolves to something asking a car designer to design a
car in a way a particular color would come best...

I believe column ordering should be decided by the database itself, to
maximize output/throughput or otherwise beneficially influent performance
and/or disk usage. If you want a specific column sequence, put it into the
SELECT statement. '*' just means "gimme all of them", not in a particular
order.

If some frontende decides to display the columns in a particular order, ok,
but it should store this order somewhere else, either in its own private
tables, a configuration or ini file, or elsewhere. It simply doesn't belong
to the database layout, or pg_attribute.

Just my 2c,

--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/



Re: full featured alter table?

From
Mike Mascari
Date:
Ernest E Vogelsinger wrote:

> At 12:33 15.06.2003, Nigel J. Andrews said:
> --------------------[snip]--------------------
>
>>Well, on the basis that column ordering is presentation issue it does
>>belong in
>>the frontend. However, as Tom pointed out this discussion was had a few
>>months. I can't remember the outcome but apparently a suggestion was to have a
>>column in pg_attribute giving the presentational order.
>
> --------------------[snip]--------------------
>
> Holdit.
>
> This whole thread eveolves to something asking a car designer to design a
> car in a way a particular color would come best...
>
> I believe column ordering should be decided by the database itself, to
> maximize output/throughput or otherwise beneficially influent performance
> and/or disk usage. If you want a specific column sequence, put it into the
> SELECT statement. '*' just means "gimme all of them", not in a particular
> order.

The proposal does beg the question:

Why would a default tuple-attribute order be stored in the database
but not relation-tuple order?

Mike Mascari
mascarm@mascari.com



Re: full featured alter table?

From
Tom Lane
Date:
Ernest E Vogelsinger <ernest@vogelsinger.at> writes:
> If some frontende decides to display the columns in a particular order, ok,
> but it should store this order somewhere else, either in its own private
> tables, a configuration or ini file, or elsewhere. It simply doesn't belong
> to the database layout, or pg_attribute.

The trouble with that rationale is that if you are using several
different tools, you'd probably like them to agree on what the column
ordering is.  If the tools all have to invent their own private storage
methods for ordering info, this will never happen.

Also, if history is any guide, people will not like admin tools that
clutter the database with add-on tables in which to store info like
this.  pgAdmin (or was it pgaccess?) used to do that some versions back,
and it was a big annoyance.  Perhaps the availability of schemas in
current releases would mitigate that problem, but I'd still prefer to
use a tool that doesn't need such things.

Finally, no tool can affect the behavior of "SELECT *" or INSERT-without-
a-column-list, if there's no support for it in the database.  Whatever
your opinions on the safety of using these constructs in application
code, people do use 'em a lot in hand-typed SQL.  If these constructs
do not follow the column order that a user is used to seeing in his
admin tool, you are opening yourself up to problems.

            regards, tom lane

Re: full featured alter table?

From
Tom Lane
Date:
Mike Mascari <mascarm@mascari.com> writes:
> The proposal does beg the question:
> Why would a default tuple-attribute order be stored in the database
> but not relation-tuple order?

A good argument in terms of pure relational-calculus theory ... but
we're dealing with SQL here, not relational calculus.  The SQL spec
explicitly states that column ordering is significant while row ordering
is not.

            regards, tom lane

Re: full featured alter table?

From
Tino Wildenhain
Date:
Hi,


Tom Lane wrote:
...

> The trouble with that rationale is that if you are using several
> different tools, you'd probably like them to agree on what the column
> ordering is.  If the tools all have to invent their own private storage
> methods for ordering info, this will never happen.
...

> Finally, no tool can affect the behavior of "SELECT *" or INSERT-without-
> a-column-list, if there's no support for it in the database.  Whatever
> your opinions on the safety of using these constructs in application
> code, people do use 'em a lot in hand-typed SQL.  If these constructs
> do not follow the column order that a user is used to seeing in his
> admin tool, you are opening yourself up to problems.
...

why not just order the output columns in alphabetical order?
This is quite easy to implement and requires no additional
storage. The output would be the same all the time
and with all tools.

People again should be warned to use select *
in applications, but this is another thing...


Just my 0.02c

Regards
Tino


RE : full featured alter table?

From
"Bruno BAGUETTE"
Date:
Hello,

> why not just order the output columns in alphabetical order?
> This is quite easy to implement and requires no additional
> storage. The output would be the same all the time and with all tools.

No, what we are talking about is to have the possibility to choose the
output order of the columns of a table (by adding a field in the
pg_attribute table).

I don't like GUI applications that creates their own tables in my schema
to store ordering or displaying informations. All the GUI that can
manages PostgreSQL database could use the pg_attribute table to store
the output order of the columns of the table.

I think that displays the output columns in alphabetical order will not
bring anything interesting...    But if the pg_attributes table can be
enhanced to store order output, you could have the possibility to sort
yourself the ouput order. (Logical order, alphabetical order, or another
order...). The pg_attributes table would store (as I imagine) the
ordering number of each column.

> People again should be warned to use select *
> in applications, but this is another thing...

I agree with you, SELECT * must be avoided as much as possible.

Regards,

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net


RE : full featured alter table?

From
"Bruno BAGUETTE"
Date:
Nigel J. Andrews wrote :

...
> I believe column ordering should be decided by the database
> itself, to maximize output/throughput or otherwise
> beneficially influent performance and/or disk usage. If you
> want a specific column sequence, put it into the SELECT
> statement. '*' just means "gimme all of them", not in a
> particular order.

We are not talking about choose the internal column ordering, but the
output column ordering. This is the columns presentation order that we
would like to have the possibility to change.

I think that having the possibility to change the columns presentation
order would be a really nice feature because on some large applications
you don't have the time to build a temporary table (with a CREATE TABLE
temp_table AS SELECT field1,field3,field2 FROM table) and to re-add all
the constraints, triggers and others things...

So, having the possibility to change that order using a ALTER TABLE
would be really time-saving !

> If some frontende decides to display the columns in a
> particular order, ok, but it should store this order
> somewhere else, either in its own private tables, a
> configuration or ini file, or elsewhere. It simply doesn't
> belong to the database layout, or pg_attribute.

I disagree, I don't like when frontends and other GUI are creating new
tables in the database. Plus, if I use a frontend (pgAdmin, for example)
to set the columns order, I want to get that presentation order when I
do a SELECT * using the PostgreSQL API or using the psql frontend...

So, storing the columns order in the pg_attributes table is, I think, a
good solution. :-)

Regards,


--------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net


Re: full featured alter table?

From
Tom Lane
Date:
Tino Wildenhain <tino@wildenhain.de> writes:
> why not just order the output columns in alphabetical order?

Because it would violate the SQL spec.  While the spec does not propose
any ALTER TABLE command that would let one rearrange the column order,
it is perfectly clear about the semantics of "SELECT *" and "INSERT"
when one has only done CREATE TABLE and ALTER TABLE ADD COLUMN.  We do
not get to invent our own behavior there.

            regards, tom lane

RE : full featured alter table?

From
Ernest E Vogelsinger
Date:
At 23:30 15.06.2003, Bruno BAGUETTE said:
--------------------[snip]--------------------
>> If some frontende decides to display the columns in a
>> particular order, ok, but it should store this order
>> somewhere else, either in its own private tables, a
>> configuration or ini file, or elsewhere. It simply doesn't
>> belong to the database layout, or pg_attribute.
>
>I disagree, I don't like when frontends and other GUI are creating new
>tables in the database. Plus, if I use a frontend (pgAdmin, for example)
>to set the columns order, I want to get that presentation order when I
>do a SELECT * using the PostgreSQL API or using the psql frontend...
>
>So, storing the columns order in the pg_attributes table is, I think, a
>good solution. :-)
--------------------[snip]--------------------

This may come in handy of you're the only one using the database.

What if:
a) multiple users/developers are "maintaining" their own "private" column
ordering?
b) what happens to those (poorly written but existing) applications that
use columnless INSERTs? Will they break or what?

I don't think changing the column order in pg_attributes would be a good
thing - too much to break loose. Maybe there could be something non-SQL
conforming
   CREATE COLUMNORDER "order_name" ON TABLE "table_name" ("column", ...)
and you could use this with a SELECT extension:
   SELECT * FROM "table_name" COLUMNORDER "order_name"

but that can be easily done using views:
   CREATE VIEW "viewname" AS SELECT (col3, col5, col2) FROM "table_name"

Well, views are read-only unless you're using an appropriate rule.


--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/



Re: full featured alter table?

From
"Jim C. Nasby"
Date:
On Sat, Jun 14, 2003 at 10:59:08AM -0500, Bruno Wolff III wrote:
> On Sat, Jun 14, 2003 at 10:31:02 -0500,
>   "Jim C. Nasby" <jim@nasby.net> wrote:
> >
> > The one thing we don't have that I think would be useful is a way to
> > re-order the columns in a table. Maybe it's just me, but I tend to want
> > column to appear in a specific order, and the only way you can
> > accomplish this today is by re-creating the entire table.
>
> You can specify the order that columns are output now. The real underlying
> order should really be up to postgres to decide based on what is most
> efficient.

Actually, I was talking about the underlying order, because (at least
the last time I asked about this), pgsql doesn't do any intelligent
ordering under the covers. Because of that, I'd like to be able to order
based on the optimal ordering I was given (fixed width before variable,
then not null before null).

Of course it would certainly be best if pgsql would automagically
optimize internal storage order for me.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: RE : full featured alter table?

From
"Jim C. Nasby"
Date:
On Sat, Jun 14, 2003 at 06:55:36PM +0200, Bruno BAGUETTE wrote:
> Hello everybody,
>
> > The one thing we don't have that I think would be useful is a
> > way to re-order the columns in a table. Maybe it's just me,
> > but I tend to want column to appear in a specific order, and
> > the only way you can accomplish this today is by re-creating
> > the entire table.
>
> I agree with Jim, this would be a 'cool but not mandatory' feature ! :-)
>
> This feature is not mandatory since I avoid the use SELECT * FROM...
> (and I forbid the use of SELECT * to my subordinates).

99.999% of the time, if you put SELECT * into code, you should be strung
up by your own entrails. But do you mean to tell me that when you're
testing stuff on the command line you never, ever use SELECT *?

BTW, de-coupling column definition and presentation order from internal
column order in the tuples is mandatory before PGSQL could store things
internally in the most optimal order. I find it very interesting that
many people assumed that PGSQL ordered columns however it wanted in the
tuples; I don't know of any major database that does this, even though
it probably makes a lot of sense (of course some DBA's would be very
unhappy about the loss of control, so you'd probably want an over-ride).
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: full featured alter table?

From
"Nigel J. Andrews"
Date:
On Mon, 16 Jun 2003, Jim C. Nasby wrote:

> On Sat, Jun 14, 2003 at 10:59:08AM -0500, Bruno Wolff III wrote:
> > On Sat, Jun 14, 2003 at 10:31:02 -0500,
> >   "Jim C. Nasby" <jim@nasby.net> wrote:
> > >
> > > The one thing we don't have that I think would be useful is a way to
> > > re-order the columns in a table. Maybe it's just me, but I tend to want
> > > column to appear in a specific order, and the only way you can
> > > accomplish this today is by re-creating the entire table.
> >
> > You can specify the order that columns are output now. The real underlying
> > order should really be up to postgres to decide based on what is most
> > efficient.
>
> Actually, I was talking about the underlying order, because (at least
> the last time I asked about this), pgsql doesn't do any intelligent
> ordering under the covers. Because of that, I'd like to be able to order
> based on the optimal ordering I was given (fixed width before variable,
> then not null before null).

Ah, you see that's where the confusion comes from, everyone else is not on
about the physical ordering. I don't imagine core would consider an alter table
command to reorder columns in the tuple store.


--
Nigel J. Andrews


Re: full featured alter table?

From
Greg Stark
Date:
Bruno Wolff III <bruno@wolff.to> writes:

> It is already a bad idea to use * in applications in case something does
> change the order or number of columns. * is mostly going to be used in adhoc
> queries where you care about saving some typing.

Since this seems to have gone by without any contention I'll throw in 2c on
the other side of this argument.

I agree 100% with the logic but reach the opposite conclusion. One of the main
goals of program design is to isolate the places that would have to be edited
to accomplish any changes.

If you explicitly list every or nearly every column in your queries, then
whenever a new attribute is added you'll have to go through and add the new
attribute to every query that fetched it.

Whereas if you use select * and make the presentation code handle any columns
that appear in the result then adding a new column can be done purely in the
database. The query and the presentation layer can adapt without changes.

Of course it's not always possible to achieve this 100%. Often the
presentation layer wants to do specific formatting for each attribute and
doesn't want to display every single attribute. But using select * means
there's one fewer place that needs to be edited to effect the new column.

This all depends on having a driver that gives you an API that lets you access
columns by name rather than position though.

--
greg

Re: RE : full featured alter table?

From
Ernest E Vogelsinger
Date:
At 07:55 16.06.2003, Jim C. Nasby said:
--------------------[snip]--------------------
>99.999% of the time, if you put SELECT * into code, you should be strung
>up by your own entrails. But do you mean to tell me that when you're
>testing stuff on the command line you never, ever use SELECT *?
--------------------[snip]--------------------

Sure I do. But I don't really care about column ordering when doing a
SELECT * from the psql command line. If I care I usually have a dummy file
named "x" (because that's so damn short ;->), hack in my complex test
queries there, and do a \i x in psql. If I just want to see if something's
there (or not) I'm not interected in column order. Are you?

Basically as I understand it, SELECT * means "gimme all", not in any
particular order. How if at all is that defined in ANSI SQL?


--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/



Re: RE : full featured alter table?

From
Mike Mascari
Date:
Ernest E Vogelsinger wrote:

> At 07:55 16.06.2003, Jim C. Nasby said:
>
>>99.999% of the time, if you put SELECT * into code, you should be strung
>>up by your own entrails. But do you mean to tell me that when you're
>>testing stuff on the command line you never, ever use SELECT *?
>
> Sure I do. But I don't really care about column ordering when doing a
> SELECT * from the psql command line. If I care I usually have a dummy file
> named "x" (because that's so damn short ;->), hack in my complex test
> queries there, and do a \i x in psql. If I just want to see if something's
> there (or not) I'm not interected in column order. Are you?
>
> Basically as I understand it, SELECT * means "gimme all", not in any
> particular order. How if at all is that defined in ANSI SQL?

SQL92:

7.9  <query specification>

Syntax Rules

1) Let T be the result of the <table expression>.

2) The degree of the table specified by a <query specification> is
equal to the cardinality of the <select list>.

3) Case:

a) If the <select list> "*" is simply contained in a <subquery> that
is immediately contained in an <exists predicate>, then the <select
list> is equivalent to a <value expression> that is an arbitrary
<literal>.

b) Otherwise, the <select list> "*" is equivalent to a <value
expression> sequence in which each <value expression> is a <column
reference> that references a column of T and each column of T is
referenced exactly once. The columns are referenced in the ascending
sequence of their ordinal position within T.

It's that last sentence:

"The columns are referenced in the ascending sequence of their ordinal
position within T."

As Tom pointed out earlier, it may not make sense purely from a
relational point of view, but it is required by the standard. Given
that it is required, it would be nice if the user could modify the
ordinal position within T.

Mike Mascari
mascarm@mascari.com











Re: RE : full featured alter table?

From
Ernest E Vogelsinger
Date:
At 16:21 16.06.2003, Mike Mascari said:
--------------------[snip]--------------------
>It's that last sentence:
>
>"The columns are referenced in the ascending sequence of their ordinal
>position within T."
>
>As Tom pointed out earlier, it may not make sense purely from a
>relational point of view, but it is required by the standard. Given
>that it is required, it would be nice if the user could modify the
>ordinal position within T.

Thanks for clearing this up! I had the perception column sequence was not
mentioned at all - too much time since I last read that stuff...

>TIP 7: don't forget to increase your free space map settings

Yes, but how much, and to what point? (not directed to you, but...)



--
   >O     Ernest E. Vogelsinger
   (\)    ICQ #13394035
    ^     http://www.vogelsinger.at/



Re: RE : full featured alter table?

From
Sven Köhler
Date:
> "The columns are referenced in the ascending sequence of their ordinal
> position within T."
>
> As Tom pointed out earlier, it may not make sense purely from a
> relational point of view, but it is required by the standard. Given
> that it is required, it would be nice if the user could modify the
> ordinal position within T.

Now that there are some people that agree with me that this feature can
be handy, i thought about how an alter table command should look like to
re-order columns.

how about

ALTER TABLE <table> ALTER COLUMN <column> POSITION <int i>

where 1<=i<=number of cols in table and the given column will be moved
between the (i-1)-th and the i-th column within the table.

if a admin-tool wants to redefine the order of the columns, it just has
to submit one command for every column with ascending positions.

is this acceptable?
if i understood the other people correct, than we need such a command,
because no user can access the pg_attribute table directly, right?


Re: full featured alter table?

From
Jan Wieck
Date:
Greg Stark wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
>
>> It is already a bad idea to use * in applications in case something does
>> change the order or number of columns. * is mostly going to be used in adhoc
>> queries where you care about saving some typing.
>
> Since this seems to have gone by without any contention I'll throw in 2c on
> the other side of this argument.
>
> I agree 100% with the logic but reach the opposite conclusion. One of the main
> goals of program design is to isolate the places that would have to be edited
> to accomplish any changes.
>
> If you explicitly list every or nearly every column in your queries, then
> whenever a new attribute is added you'll have to go through and add the new
> attribute to every query that fetched it.

Disagreed. There can not be any "query that fetched it" because it
didn't exist before. And if the surrounding application issuing that
query didn't have that attribute before, what's it going to do with it
now? It will hopefully just swallow it silently.

>
> Whereas if you use select * and make the presentation code handle any columns
> that appear in the result then adding a new column can be done purely in the
> database. The query and the presentation layer can adapt without changes.

Ah ... we are talking about presentation code only. I have seen code
that does a SELECT * from a table having multi-megabyte sized BLOB's
only to check IF FOUND afterwards ... saving a few keystrokes can be
expensive.

In the case you have dynamic presentation code that handles this sort of
thing, you are IMHO supposed to let it query a view. And this view has
to be a separate view just for this "presentation" (view is a synonym
for that, isn't it?). That way you are free to change the tables layout
in whatever way you want to, from adding/renaming/dropping columns,
splitting up the attributes into separate tables or changing data types
... whatever fits your "technical" needs. You can allways adjust the
view so that the presentation will be OK again.

>
> Of course it's not always possible to achieve this 100%. Often the
> presentation layer wants to do specific formatting for each attribute and
> doesn't want to display every single attribute. But using select * means
> there's one fewer place that needs to be edited to effect the new column.

Using SELECT * means that there is one more place to carefully check for
possible side effects.

>
> This all depends on having a driver that gives you an API that lets you access
> columns by name rather than position though.

You need an API that gives you access to the list of attribute names
first, and then dynamically access those attributes. PL/pgSQL for one
does not have this capability.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: RE : full featured alter table?

From
Jan Wieck
Date:
Ernest E Vogelsinger wrote:

> Yes, but how much, and to what point? (not directed to you, but...)

As much as required by the complexity and size of all the databases
served by that postmaster.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: RE : full featured alter table?

From
"Jim C. Nasby"
Date:
On Mon, Jun 16, 2003 at 06:49:50PM +0200, Sven K?hler wrote:
> ALTER TABLE <table> ALTER COLUMN <column> POSITION <int i>
>
> where 1<=i<=number of cols in table and the given column will be moved
> between the (i-1)-th and the i-th column within the table.

I think that's fine. I think the normal case for doing this will be
after you've added a new column, I think it would be pretty rare to want
to re-order everything in the table. Of course if you did want to
re-order everything in the table, it would be easier to just specify the
list of column names in the new order you'd like to see them in.

> if a admin-tool wants to redefine the order of the columns, it just has
> to submit one command for every column with ascending positions.

BTW, I've heard a lot of people talking about 'dragging columns around
in select output', and I don't think that's a very good use case for
this. The order of columns should be considered to be part of the table
definition. Changing it should require the same privledges as adding or
dropping a column. If a front-end wants to provide some wiz-bang
interface customization on a per-user basis this isn't the mechanism
that should be used.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: RE : full featured alter table?

From
Sven Köhler
Date:
>>ALTER TABLE <table> ALTER COLUMN <column> POSITION <int i>
>>
>>where 1<=i<=number of cols in table and the given column will be moved
>>between the (i-1)-th and the i-th column within the table.
>
> I think that's fine. I think the normal case for doing this will be
> after you've added a new column, I think it would be pretty rare to want
> to re-order everything in the table. Of course if you did want to
> re-order everything in the table, it would be easier to just specify the
> list of column names in the new order you'd like to see them in.

perhaps we could also think about a
ALTER TABLE <table> POSITIONS <column1>,<column2>,...

but that is unnessary in my eyes. To issue 15 statements to re-order a
complete table shouldn't be a problem. (assuming our table has 15 columns)

>>if a admin-tool wants to redefine the order of the columns, it just has
>>to submit one command for every column with ascending positions.
>
> BTW, I've heard a lot of people talking about 'dragging columns around
> in select output', and I don't think that's a very good use case for
> this. The order of columns should be considered to be part of the table
> definition. Changing it should require the same privledges as adding or
> dropping a column. If a front-end wants to provide some wiz-bang
> interface customization on a per-user basis this isn't the mechanism
> that should be used.

Of course the described command should require the priviledge for
modifying a table. I think we don't want any user to be abled to do
that, because that will lead to chaos.


Re: RE : full featured alter table?

From
Tino Wildenhain
Date:
Hi,

Bruno BAGUETTE wrote:
> Hello,
>
>
>>why not just order the output columns in alphabetical order?
>>This is quite easy to implement and requires no additional
>>storage. The output would be the same all the time and with all tools.
>
>
> No, what we are talking about is to have the possibility to choose the
> output order of the columns of a table (by adding a field in the
> pg_attribute table).

Sure, I did understand that. But I think it is quite pointless
to burden the db or the app with such functionality.
Just to have random ordered columns which do not correspondent
to any physical ordering by the db.
So it does not influence query performance to arrange the
selected columns this or that order. So why confusing
the user by pretending it has any meaning to order the columns?

I think there are quite more praxis relevant problems to solve :)

Regards
Tino


Re: full featured alter table?

From
Greg Stark
Date:
Jan Wieck <JanWieck@yahoo.com> writes:

> Disagreed. There can not be any "query that fetched it" because it didn't exist
> before. And if the surrounding application issuing that query didn't have that
> attribute before, what's it going to do with it now? It will hopefully just
> swallow it silently.

Sorry, dangling pronoun. "it" would have to be the entity, not the attribute.

> > Whereas if you use select * and make the presentation code handle any columns
> > that appear in the result then adding a new column can be done purely in the
> > database. The query and the presentation layer can adapt without changes.
>
> Ah ... we are talking about presentation code only. I have seen code that does
> a SELECT * from a table having multi-megabyte sized BLOB's only to check IF
> FOUND afterwards ... saving a few keystrokes can be expensive.

Well that's just one reason storing multi-megabyte objects in databases is
usually a bad idea. Certainly it would be pretty unlikely and almost certainly
a bad idea to have an existing entity that suddenly grows a multimegabyte
attribute you hadn't thought of before.

> In the case you have dynamic presentation code that handles this sort of thing,
> you are IMHO supposed to let it query a view. And this view has to be a
> separate view just for this "presentation" (view is a synonym for that, isn't
> it?). That way you are free to change the tables layout in whatever way you
> want to, from adding/renaming/dropping columns, splitting up the attributes
> into separate tables or changing data types ... whatever fits your "technical"
> needs. You can allways adjust the view so that the presentation will be OK
> again.

Bleagh. what a way to create tons of extra work for nothing. So now every time
I add a new attribute to an existing entity I not only have to create the new
column in the table, and create the new presentation code to handle the
attribute, I not only have to check through all the queries that fetch the
data for presentation and add the columns there, I now have to also check
through all the views those queries use and add the columns there as well?

The goal here is to avoid having to adjust ten million places every time you
make a minor change to internal data. It's the whole reason ADTs and objects
were invented. To avoid having to have every piece of code know the internals
of every data type. What all the attributes of an entity are is just such an
internal that should be hidden from code that doesn't need it. Unfortunately
the best SQL can do on that front is SELECT * and even that doesn't work too
well. But it's better than hard coding explicit lists of attributes everywhere
throughout the application.

> > Of course it's not always possible to achieve this 100%. Often the
> > presentation layer wants to do specific formatting for each attribute and
> > doesn't want to display every single attribute. But using select * means
> > there's one fewer place that needs to be edited to effect the new column.
>
> Using SELECT * means that there is one more place to carefully check for
> possible side effects.

sure, except the "possible side effect" is the addition of one more attribute
to the select list, which would be precisely what you're hoping to accomplish
by adding the column.

> > This all depends on having a driver that gives you an API that lets you access
> > columns by name rather than position though.
>
> You need an API that gives you access to the list of attribute names first, and
> then dynamically access those attributes. PL/pgSQL for one does not have this
> capability.

Well, to avoid hard coding the column names in the query you just need to be
able to access the columns by name. To avoid hard coding them in the
presentation layer then yes you would need to be able to retrieve the list of
column names, which most interfaces do in fact provide. I don't think PL/PgSQL
would be my first choice for writing presentation layer code in though. That
said, avoiding it in the presentation layer is usually a lost cause anyways.

--
greg

Re: full featured alter table?

From
Jean-Luc Lachance
Date:
Really?

What if two PgAdmin sessions want two different order?
Presentation order should be done at the application level.

JLL

Tom Lane wrote:
>
> Bruno Wolff III <bruno@wolff.to> writes:
> >   Sven Köhler <skoehler@upb.de> wrote:
> >> let's express it another way: we want to define the order of the columns
> >> when they are shown in tools like phpPgAdmin or PgAdminII.
>
> > That is something that those tools should do then. It isn't really a
> > postgres issue as it already provides ways for the tools to get
> > information about columns of a table.
>
> Perhaps, but pg_attribute is the natural place to keep the column
> ordering info.
>
> ISTM we talked months ago about adding an "attlognum" column to
> pg_attribute, which would be separate from "attnum" (the physical column
> position) and would be used by the backend only to determine the order
> of SELECT * expansion.  Admin tools could use this to determine column
> display order too.  It didn't get done, but it still seems like a
> reasonable idea to me.
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: full featured alter table?

From
Sven Köhler
Date:
> Really?
>
> What if two PgAdmin sessions want two different order?
> Presentation order should be done at the application level.

*sigh* ...

The order of columns, the column type etc. is all part of table
definition. if two guys want different column-orders this is just not
possible - but if both have the possibility to agree and define one
column ordering is better than force them to use what every postgresql
decides (in most cases it is the chronological order, in which the
columns have been created).

and yes - column order of a select should be defined at application
level - especially if the application uses indexes to access the columns
- but some APIs offer access to the columns by using their name.


Re: RE : full featured alter table?

From
Tom Lane
Date:
=?ISO-8859-1?Q?Sven_K=F6hler?= <skoehler@upb.de> writes:
> perhaps we could also think about a
> ALTER TABLE <table> POSITIONS <column1>,<column2>,...

You could invent a syntax that supports both use cases, along the lines
of

ALTER ... POSITION <i> <column1> [ , <column2> ... ]

with the meaning that the named columns are inserted sequentially between
positions i-1 and i, moving them from wherever they were, and leaving
all not-mentioned columns in their existing relative order.  This
degenerates to the same as your first proposal if one column is named,
and at the other extreme allows all the columns to be re-ordered in one
command.

It could get a little confusing if some of the named columns previously
occupied positions less than <i>.  I'd suggest the following
more-concrete specification:

1. <i> must be in the range 1 to (<number of columns in table> -
   <number of columns named in statement> + 1).
2. After the ALTER, the named columns have ordinal positions <i>, <i+1>,
   etc.
3. Any columns not named are placed into the remaining slots (1..i-1
   and i+nnamedcols..ntablecols) in the same relative order they had
   before.

            regards, tom lane

Re: RE : full featured alter table?

From
"scott.marlowe"
Date:
On Mon, 16 Jun 2003, Jim C. Nasby wrote:

> On Sat, Jun 14, 2003 at 06:55:36PM +0200, Bruno BAGUETTE wrote:
> > Hello everybody,
> >
> > > The one thing we don't have that I think would be useful is a
> > > way to re-order the columns in a table. Maybe it's just me,
> > > but I tend to want column to appear in a specific order, and
> > > the only way you can accomplish this today is by re-creating
> > > the entire table.
> >
> > I agree with Jim, this would be a 'cool but not mandatory' feature ! :-)
> >
> > This feature is not mandatory since I avoid the use SELECT * FROM...
> > (and I forbid the use of SELECT * to my subordinates).
>
> 99.999% of the time, if you put SELECT * into code, you should be strung
> up by your own entrails. But do you mean to tell me that when you're
> testing stuff on the command line you never, ever use SELECT *?

I wouldn't go that far.  I build updatable views, select * from them,
cycle through the fields getting name / type and build generic forms to
let the user edit / insert new records.

It allows me to reuse the same basic chunk of code over and over.

Of course, it's select * on a view, not a table, so I set the order when I
create the view.

Now, using select * and ASSUMING the order of the variables in your
application code is a punishable offense, but as long as you determine the
name / type of the fields after the select * it's not so bad.


Re: RE : full featured alter table?

From
Dennis Gearon
Date:
I agree 100%.

this makes very portable code, and usable libraries.

scott.marlowe wrote:

> On Mon, 16 Jun 2003, Jim C. Nasby wrote:
>
>
>>On Sat, Jun 14, 2003 at 06:55:36PM +0200, Bruno BAGUETTE wrote:
>>
>>>Hello everybody,
>>>
>>>
>>>>The one thing we don't have that I think would be useful is a
>>>>way to re-order the columns in a table. Maybe it's just me,
>>>>but I tend to want column to appear in a specific order, and
>>>>the only way you can accomplish this today is by re-creating
>>>>the entire table.
>>>
>>>I agree with Jim, this would be a 'cool but not mandatory' feature ! :-)
>>>
>>>This feature is not mandatory since I avoid the use SELECT * FROM...
>>>(and I forbid the use of SELECT * to my subordinates).
>>
>>
>>99.999% of the time, if you put SELECT * into code, you should be strung
>>up by your own entrails. But do you mean to tell me that when you're
>>testing stuff on the command line you never, ever use SELECT *?
>
>
> I wouldn't go that far.  I build updatable views, select * from them,
> cycle through the fields getting name / type and build generic forms to
> let the user edit / insert new records.
>
> It allows me to reuse the same basic chunk of code over and over.
>
> Of course, it's select * on a view, not a table, so I set the order when I
> create the view.
>
> Now, using select * and ASSUMING the order of the variables in your
> application code is a punishable offense, but as long as you determine the
> name / type of the fields after the select * it's not so bad.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: RE : full featured alter table?

From
weigelt@metux.de
Date:
On Mon, Jun 16, 2003 at 09:26:12PM +0200, Tino Wildenhain wrote:

<snip>
> I think there are quite more praxis relevant problems to solve :)
ACK.

I'd like to see some features, which can be good for distributed databases:

* user defined OID spaces for serveral classes (and their derived classes)
* automatic mtime field update (but only if data really changed)

At the moment i'm doing this in the application (some not-too-small
php code), but it would be nice if the RDBMS could do this, so this
functionality could be provided directly through an SQL interface.

For many applications i use an object-like table model: each table
is derived from _inode, which defines an (cluster wiede) unique inode_id
and an mtime field. The applications do not make direct queries, but
access the db over an abstraction layer instead. This layer has definitions
for each class (object type, properties, etc) and generates queries for
actions like create, update, query, get,... It also implements some
little caching. On create() the abstraction layer generates an query which
fetches the inode_id from an sequence (this sequence has to be adjusted
for each node in the cluster, so each node has its own inode_id space)
and sets the mtime to current_timestamp(). On update() it also updates
the mtime field. But also there's an raw-store mode, which does _not_
update the mtime field, but writes it as it comes from the user
(this is necessary for syncing to avoid loops, since the post-out
uses the mtime to find out which records to send to another node)

I've tried to achieve this w/ rules, but i couldnt handle this
(perhaps i'm too stupid ? ;-))

cu
--
---------------------------------------------------------------------
 Enrico Weigelt    ==   metux ITS
 Webhosting ab 5 EUR/Monat.          UUCP, rawIP und vieles mehr.

 phone:     +49 36207 519931         www:       http://www.metux.de/
 fax:       +49 36207 519932         email:     contact@metux.de
 cellphone: +49 174 7066481         smsgate:   sms.weigelt@metux.de
---------------------------------------------------------------------
 Diese Mail wurde mit UUCP versandt.      http://www.metux.de/uucp/

Re: RE : full featured alter table?

From
Sven Köhler
Date:
>>I think there are quite more praxis relevant problems to solve :)
>
> ACK.

i cannot agree because there are always more important problems than others.

a mercedes is not such a beautiful car because of it's motor, but
because of the beautiful well thought out features in the cockpit. Of
course it would be a crappy car without a good motor - i hope you see,
that details matter as much as the big problems.


Re: full featured alter table?

From
Jan Wieck
Date:
Greg Stark wrote:
> Bleagh. what a way to create tons of extra work for nothing. So now every time
> I add a new attribute to an existing entity I not only have to create the new
> column in the table, and create the new presentation code to handle the
> attribute, I not only have to check through all the queries that fetch the
> data for presentation and add the columns there, I now have to also check
> through all the views those queries use and add the columns there as well?
>
> The goal here is to avoid having to adjust ten million places every time you
> make a minor change to internal data. It's the whole reason ADTs and objects
> were invented. To avoid having to have every piece of code know the internals
> of every data type. What all the attributes of an entity are is just such an
> internal that should be hidden from code that doesn't need it. Unfortunately
> the best SQL can do on that front is SELECT * and even that doesn't work too
> well. But it's better than hard coding explicit lists of attributes everywhere
> throughout the application.

You don't get the point, Greg. In all "applications" I have seen so far,
the number of places where the code analyzes the attributes actually
returned from a SELECT * for display purposes vs. the number of places
where the application code needs explicit fields from one or more tables
tends to be highly in favor for the latter. You know any example to the
contrary out of the top of your head?

If one needs to add attributes that often and has that many places in
his code that need to look what they got after something else blindly
selected whatever that table looks like today ... IMHO that's an
indicator for two things.

     1. He doesn't know that a developer is supposed to write a concept
        and a specification before dragging and dropping around like a
        berserk in some graphical design toy.

     2. His code prooves that he doesn't remember what he coded last
        week and all the reusability of code, OO was supposed to give
        us, is lost in the growing address space of his applications.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: full featured alter table?

From
Greg Stark
Date:
[This is fairly off-topic now. I just wanted to make it clear that there were
 both pros and cons to "select *" and people shouldn't assume they can just
 dismiss things as bad practice based on simplistic rules-of-thumb. I don't
 think we should continue this thread much longer.]


Jan Wieck <JanWieck@Yahoo.com> writes:

> You don't get the point, Greg. In all "applications" I have seen so far, the
> number of places where the code analyzes the attributes actually returned from
> a SELECT * for display purposes vs. the number of places where the application
> code needs explicit fields from one or more tables tends to be highly in favor
> for the latter. You know any example to the contrary out of the top of your
> head?

In my experience there are usually a handful of crucial attributes that are
integral to the design. These are often used in where clauses and such and
obviously need to be well thought out from the start.

Of the other attributes they mostly tend to evolve with the application and
the use of the database. These are fields that turn out to be needed for some
particular application need that may be a new feature or may be a design
deficiency.

In fact I specifically try to postpone adding such attributes until the
relevant portions of the application is being written. I find when designed in
advance such non-structural database attributes more often than not turn out
to be completely inappropriate and need to be replaced. Or often a lot of work
is spent making them infinitely flexible because the relevant application
needs aren't fully thought out. Or worse, the table structure is delayed for a
long time until every facet of the design is ironed out, preventing progress
on even the basic structure of the code.

You asked if I know of any examples, well, sure. Just the other day I added a
new column to a table to handle a new attribute needed to solve a new client
requirement. It was a simple attribute, just a free-form text field that had
to be displayed in an existing tabular display of data. If the query had been
written using "select *" I would have been able to add the attribute to the
presentation by editing the template data file. No code at all would have had
to be edited.

As it turned out the query hadn't been written this way (and couldn't have
been, because of a detail I haven't mentioned). I wasted about 20 minutes
tracking down the bug that the field wasn't showing up despite it being in the
template. Eventually I was able to track down the query that fed that template
and found the missing column in the select list.

In an ideal world the template data files shouldn't even be under the control
of the same person as the application code and database structure. Adding and
removing attributes from amongst the existing columns should be a simple
operation for a graphic artist to do on the static data files without any
modifications in the database.

This is in the early stage of the project. I would say that as a project ages
more and more of the changes are of this form. In past projects, Over the
course of the life of a project easily more than half of the attributes have
been added long after the initial design.

--
greg

Re: full featured alter table?

From
"Nigel J. Andrews"
Date:

Okay, so say now there's a attlognum column in pg_attribute. How is this going
to interact with rowtype variables in plpgsql?

Just thought I'd raise this seeing as there's already an issue with
attisdropped.


--
Nigel J. Andrews


Re: full featured alter table?

From
"Mattias Kregert"
Date:
> > Presentation order should be done at the application level.

I agree.

Use a VIEW for the presentation!

If you use a VIEW for the presentation, then the presentation code can use SELECT * from that view. The code will never
haveto be changed. The VIEW is the presentation - Change the VIEW, and the presentation is changed. 


If you use SELECT * from a table, then you might have to change the code later if you later decide you want to join in
othertables. 
With a VIEW this is not a problem.

If you want the columns from table "customers" in one order for one report and another order for another report, then a
SELECT* from table will never work. ALTER TABLE ...POSITION.. won't help either. 
With a VIEW this is not a problem.

If you want the rows from table "customers" ordered by customer_name for one report and by sales_limit for another
report,then a SELECT * from table will never work because you don't know (on the application level) what columns there
willbe in the table and what they mean. 
With a VIEW this is not a problem.

If you want the column "very_sensitive_personal_opinions_about_this_customer" not to show up in every report, then a
SELECT* from table will never work. 
With a VIEW this is not a problem.

... and so on...


Sure, you (may) have to update the view when you add or drop a column. However, that is *easy* to do, and you get all
ofthe above mentioned positive effects. 


Conclusion: Use SELECT * FROM <view>

The network is the computer.
The view is the presentation.


/Mattias


Tip 4711: The VIEW is the presentation



Re: full featured alter table?

From
Jan Wieck
Date:
Greg Stark wrote:
>
> [This is fairly off-topic now. I just wanted to make it clear that there were
>  both pros and cons to "select *" and people shouldn't assume they can just
>  dismiss things as bad practice based on simplistic rules-of-thumb. I don't
>  think we should continue this thread much longer.]

I just wanted to make clear that there are much less cases where a
"SELECT *" is a good thing at all and that it should be used carefull.

And sorry, you can't make a technical statement and ask "shut up" in the
same mail just to have the last word.

> In my experience ...

The problem is not that software evolves, the problem is the development
process, or better the lack of that. Why did you need 20 minutes to hunt
down that select? Missing some documentation? Missing some structure in
the code?

The reason why new features are added to PostgreSQL only through release
cycles, with lots of peer review, full BETA test phase and everything,
is not because we all are a bunch of hobby programmers who don't know
how real world software projects work. Fortunately in our case there is
no paying customer pushing a stupid manager around and forcing every day
spec changes to violate good development practices.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

RE : full featured alter table?

From
"Bruno BAGUETTE"
Date:
> > > Presentation order should be done at the application level.
>
> I agree.
>
> Use a VIEW for the presentation!
>

Sorry, but I don't fully agree with you. If I have to add a new column
in a table, this column will appear in the end of the table. What we are
talking about (as I understand) is to have the possibility to order the
columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table>
would use that order to display the columns.

We are not talking of changing columns order for each kind of SQL query.
In that case, I agree that views are usefull and when we want to
display several tables in one "pseudo-table" (a view) and to have less
big queries.

I really think that column ordering (ALTER TABLE ...POSITION..) is very
interesting and will allow users to avoid loosing time when they have to
create a new temporary table each time they have to add a new column
inside (not at the end of) a table, and rename the table after deleting
the old table... :-)

Regards,

---------------------------------------
Bruno BAGUETTE - pgsql-ml@baguette.net


Re: RE : full featured alter table?

From
Sven Köhler
Date:
> Sorry, but I don't fully agree with you. If I have to add a new column
> in a table, this column will appear in the end of the table. What we are
> talking about (as I understand) is to have the possibility to order the
> columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table>
> would use that order to display the columns.

yes. it is a feature that not many DBMS support (i know only of MySQL
which supports inserting a column at a defined splace - and don't blame
me - i don't like MySQL either) - it is a feature that's missing in many
DBMS too.

> We are not talking of changing columns order for each kind of SQL query.
> In that case, I agree that views are usefull and when we want to
> display several tables in one "pseudo-table" (a view) and to have less
> big queries.

I don't know about PostGreSQL, but views can be slower than normal
queries - especially when you don't do a "select * form view" but add a
some where-clauses, joins etc.


Re: full featured alter table?

From
"Mattias Kregert"
Date:
> > > > Presentation order should be done at the application level.
>>
> > I agree.
> > Use a VIEW for the presentation!
>
> Sorry, but I don't fully agree with you. If I have to add a new column
> in a table, this column will appear in the end of the table. What we are
> talking about (as I understand) is to have the possibility to order the
> columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table>
> would use that order to display the columns.

Yes, I understand that, but I don't understand what the benefits would be.

What use is it to have the columns in a defined order when you do (SELECT * FROM table)?

1. In a "normal" app, you would want to know what the data in the column *means*. Adding a column "kexchoklad" to the
"customers"table would not be of any good, regardless of it's position relative to other columns. You would never use
"SELECT*". You would SELECT only the columns that matter to this specific part of the app. 
2. In the case of a report generator: You would probably not want *every* column from "customers" for a report... How
canyou print a report without knowing how many columns you'll get? without knowing what they contain? what they mean?
Whereshould you print "kexchoklad"? After the customer name? Just before last_years_sales? In this case you would never
useSELECT *. You would probably want to join in other tables too. 
3.  In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a SELECT
* could be of any use...!! Do a SELECT *, and print it out just to get an overview of what's in the table. 

In case #3: Ok, this is the relevant case. But do you really want to rearrange the table internally just for this
specialcase?? seems like a lot of programming and potential problems just for one very special case... especially since
itcan be done quick and easy with a view...!  Do you even need a view? If you do a quick and ugly report, do you even
careabout the column position of "kexchoklad"?? 

Can you tell me an example of a situation when the column position really matters?


> We are not talking of changing columns order for each kind of SQL query.
> I really think that column ordering (ALTER TABLE ...POSITION..) is very
> interesting and will allow users to avoid loosing time when they have to
> create a new temporary table each time they have to add a new column
> inside (not at the end of) a table, and rename the table after deleting
> the old table...

But *why* would anyone care about the position of the column? Except from a cosmetical point of view... Why all the
hasslewith temp tables and stuff, just to put the column in a specific position in the table definition? Because it
looksneat when you do "\d table" in psql?? 

I think the original poster was talking about the physical layout of the row, and that it would make some selects more
efficient.Exactly how that would be accomplished was not explained. 
Then someone started talking about the logical layout (in the specific case of SELECT *), and printing reports.
The physical layout should probably be handled by pg internally, without any interference from users.
The logical layout, well... i just can't see why it would matter at all?


/Mattias


Re: full featured alter table?

From
"Shridhar Daithankar"
Date:
On 17 Jun 2003 at 15:16, Mattias Kregert wrote:
> Can you tell me an example of a situation when the column position really matters?

I would like to rephrase that question.

Can you tell me an example of a situation when the column position really
matters given

1) You can specify column order in sql select statement
2) most basic interface to postgresql, libpq supports search on a result tuple
by field number or field name?

If the client library you are using does not support this, you can hack it.
Libpq supports it for sure.

I have been deleting all mails on this thread and really wonder what was in
there in all those mail. IMO the thread is not worth living this long but I may
be wrong, given I haven't read all those mails..

Bye
 Shridhar

--
"Trust me":    Translation of the Latin "caveat emptor."


Re: full featured alter table?

From
Shane Dawalt
Date:

Mattias Kregert wrote:
>>>>>Presentation order should be done at the application level.
>>>>
>>>I agree.
>>>Use a VIEW for the presentation!
>>
>>Sorry, but I don't fully agree with you. If I have to add a new column
>>in a table, this column will appear in the end of the table. What we are
>>talking about (as I understand) is to have the possibility to order the
>>columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table>
>>would use that order to display the columns.
>
>
> 3.  In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a
SELECT * could be of any use...!! 
>

   I have been following this thread with great interesting and
perplexity.  I have yet to understand the reasoning behind this proposed
addition.  It seems useful only for SELECT * yet most posts say that
"SELECT *" is bad in an app.  Others say that if SELECT * is used then
the app has to look for the proper column(s) anyway so ordering is not
important.  As stated in the parent post from Mattias Kregert (with whom
I completely agree with), SELECT * is generally always a quick-n-ugly
check of the table.  Surely us humans can adapt to the column positions
for checking tables once in a while.  And what if an application,
expecting a pre-defined order, receives a column in a position that it
doesn't expect?  Wouldn't it still be better to define the column order
in the SELECT statement or just look for the column it wants in the
table information?

   Insofaras rearranging the internal table is concerned, I don't
believe the pg people had that intent in mind at all. It may have been
the intent of the original poster, but I think most everyone agrees that
the back-end knows far better than us humans what is more optimal for
table layout.

   It just seems that this is extra work for little benefit.
Applications that allow people to move columns for cosmetics should deal
with the storage of that application specific configuration data.  If
you use multiple applications that permit customization of column
positions then it falls upon each application to store the configuration
data as it sees fit.

   Shane


Re: full featured alter table?

From
Sven Köhler
Date:
>   I have been following this thread with great interesting and
> perplexity.  I have yet to understand the reasoning behind this proposed
> addition.  It seems useful only for SELECT * yet most posts say that
> "SELECT *" is bad in an app.  Others say that if SELECT * is used then
> the app has to look for the proper column(s) anyway so ordering is not
> important.  As stated in the parent post from Mattias Kregert (with whom
> I completely agree with), SELECT * is generally always a quick-n-ugly
> check of the table.  Surely us humans can adapt to the column positions
> for checking tables once in a while.  And what if an application,
> expecting a pre-defined order, receives a column in a position that it
> doesn't expect?  Wouldn't it still be better to define the column order
> in the SELECT statement or just look for the column it wants in the
> table information?

I don't want to abled to define the column-order just because my "select
*" would look better - it's just that a "select *" should also show the
defined column-order if there is any.

Defining the column-order is just an organisational task.
It is just like having good identifier names in your program-code or
like tidying up your desk - i don't tidy up my desk that often, but i
want a certain tidiness in my database.

In addition, postgresql doesn't offer anything to change a
column-definition. So although your columns are in the logical order you
like when you create a table, your logical order will be broken if you
add a column that you've forgotton or have to change a columns type by
copying the data to a new column.

In order to do something equivalent to a column definition change (the
stuff this thread was about initially) you have to create a new column
with the desired type, copy data, delete the old column _and_ move the
new column to the place the old column was.

In addition, beeing abled to define the column order is a step into the
direction of a more complete ALTER TABLE command - something the most
DBMS are lacking.
MySQL is abled to insert a column at a certain position, but isn't abled
to re-arange columns - this might be due to the fact, that MySQL only
knows the physical order of the columns. This is something we are not
expecting from postgresql - since physical order doesn't matter much
from the user perspective and might be optimized by postgresql internally.
Having a defined column-order is a good thing (would be a basic
requirement to optimize the physical column-order without modifying the
table layout) and to be abled to modify that defined column ordering is
some kind of service for the user.


Re: full featured alter table?

From
Dennis Gearon
Date:
I personally agree with this.

I put the fields in the create statement in a particular order to help with understanding the design. Now, if the DB
wantsto put them in whatever STORAGE order it wants, fine, as long as it displays in the same order I created it. 

A possible, probably completely non standard solution to both problems would be a COMBINATION of a select list and '*':

SELECT (col1, col2, * ) from TABLE1;

Certain tables are ordered, the rest are just appended in order of definition behind it.

Mattias Kregert wrote:

>>>>>Presentation order should be done at the application level.
>>>
>>>I agree.
>>>Use a VIEW for the presentation!
>>
>>Sorry, but I don't fully agree with you. If I have to add a new column
>>in a table, this column will appear in the end of the table. What we are
>>talking about (as I understand) is to have the possibility to order the
>>columns table (via ALTER TABLE ...POSITION..). SELECT * FROM <table>
>>would use that order to display the columns.
>
>
> Yes, I understand that, but I don't understand what the benefits would be.
>
> What use is it to have the columns in a defined order when you do (SELECT * FROM table)?
>
> 1. In a "normal" app, you would want to know what the data in the column *means*. Adding a column "kexchoklad" to the
"customers"table would not be of any good, regardless of it's position relative to other columns. You would never use
"SELECT*". You would SELECT only the columns that matter to this specific part of the app. 
> 2. In the case of a report generator: You would probably not want *every* column from "customers" for a report... How
canyou print a report without knowing how many columns you'll get? without knowing what they contain? what they mean?
Whereshould you print "kexchoklad"? After the customer name? Just before last_years_sales? In this case you would never
useSELECT *. You would probably want to join in other tables too. 
> 3.  In the case of an quick-and-ugly "just dump out all data" report: Ok, here we have the only situation when a
SELECT * could be of any use...!! Do a SELECT *, and print it out just to get an overview of what's in the table. 
>
> In case #3: Ok, this is the relevant case. But do you really want to rearrange the table internally just for this
specialcase?? seems like a lot of programming and potential problems just for one very special case... especially since
itcan be done quick and easy with a view...!  Do you even need a view? If you do a quick and ugly report, do you even
careabout the column position of "kexchoklad"?? 
>
> Can you tell me an example of a situation when the column position really matters?
>
>
>
>>We are not talking of changing columns order for each kind of SQL query.
>>I really think that column ordering (ALTER TABLE ...POSITION..) is very
>>interesting and will allow users to avoid loosing time when they have to
>>create a new temporary table each time they have to add a new column
>>inside (not at the end of) a table, and rename the table after deleting
>>the old table...
>
>
> But *why* would anyone care about the position of the column? Except from a cosmetical point of view... Why all the
hasslewith temp tables and stuff, just to put the column in a specific position in the table definition? Because it
looksneat when you do "\d table" in psql?? 
>
> I think the original poster was talking about the physical layout of the row, and that it would make some selects
moreefficient. Exactly how that would be accomplished was not explained. 
> Then someone started talking about the logical layout (in the specific case of SELECT *), and printing reports.
> The physical layout should probably be handled by pg internally, without any interference from users.
> The logical layout, well... i just can't see why it would matter at all?
>
>
> /Mattias
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Re: full featured alter table?

From
Tom Lane
Date:
Shane Dawalt <shane.dawalt@wright.edu> writes:
>    I have been following this thread with great interesting and
> perplexity.  I have yet to understand the reasoning behind this proposed
> addition.

The original takeoff point was the observation that you couldn't build
"ALTER COLUMN TYPE" out of the existing spare parts: you can make a new
column, load it with the old data, and drop the old column (with a
rename somewhere along the line) ... but this leaves you with the new
column at the end, so it's not a fully transparent substitution.

>    Insofaras rearranging the internal table is concerned, I don't
> believe the pg people had that intent in mind at all. It may have been
> the intent of the original poster, but I think most everyone agrees that
> the back-end knows far better than us humans what is more optimal for
> table layout.

Indeed, but under the constraints of the SQL spec, the backend is not
free to change the visible column order for implementation reasons.
If we were to decouple logical and physical order as this thread is
suggesting, then the door *would* be open to perform that sort of
optimization.

            regards, tom lane

Re: full featured alter table?

From
Sven Köhler
Date:
>   Insofaras rearranging the internal table is concerned, I don't believe
> the pg people had that intent in mind at all. It may have been the
> intent of the original poster, but I think most everyone agrees that the
> back-end knows far better than us humans what is more optimal for table
> layout.

BTW: i never had that in mind. i don't even care about the physical or
internal column-order.


Re: full featured alter table?

From
"Jim C. Nasby"
Date:
On Tue, Jun 17, 2003 at 10:05:48AM -0400, Shane Dawalt wrote:
>   Insofaras rearranging the internal table is concerned, I don't
> believe the pg people had that intent in mind at all. It may have been
> the intent of the original poster, but I think most everyone agrees that
> the back-end knows far better than us humans what is more optimal for
> table layout.

Again, yes, it would be nice if pgsql would order things most
efficiently in the back-end, but it doesn't. I don't care at all about
how stuff comes out in SELECT *. I DO care about how it's stored in the
tuple.

Much, if not most, of the work required to make this change happen would
be required to de-couple presentation from tuple ordering anyway, and
that seems to be one feature everyone agrees would be good. If the
de-coupling is going to be added, might as well add a nice feature that
won't really hurt anything at the same time.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

full featured alter table/column ordering - a summary

From
Sven Koehler
Date:
hi,

so here's a small summery of all the we wrote about changing a column's
definition and/or column-ordering.

column definition change:
- changing a column's defintion is possible in a few situations, but the
main problem is the conversion of the data from one type into another.
- chaning a column's definition is not part of the the SQL-Standard an
therfor is not well defined - even if other DBMS implement such a
feature, they all do it in a different way - so there is no practical
standard
- this feature will not be implemented in near future, and a macro or
something within a tool would also be sufficient (i asked the
pgadmin-team and they promised to think about it - what ever that means)
- still there is some problem:
even if there is a macro within a tool to change a columns definition,
it needs to create a new column which will appear at the end of the
column-list. so one small feature is still needed: user-defined column order

user defined column order:

let's define two things:
- physical column order
the column order with which the column-data is ordered within a table
row when writing it to disk
- logical column order
the column order that postgresql presents to it's clients
it does not need to match te physical order. it is currently equal to
the creation order and also equal the physical order (as far as i know).

WHY?
- defining the column order of a table is part of the creation of the
table. after that, the column order can only be changed by deleting and
creating new columns or by creating new complete new table
- in order to have a full equivalent of changing a column definition,
setting a columns position is required
- many users create a table with a certain column order to help
themselfs or just to keep a certain tidiness, adding a column or
changing it's type would break that order

WHY NOT?
- having a a logical order different from the physical order only
affects "select *" and "select *" should not be used.
- views could be uses to achieve a certain column order
- it's not an important feature and needs a new statement, because users
cannot update the pg_attribute table directly
- any admin-tool could store the its own column ordering in separate tables

WHY ANYWAY?
- having a logical column order is an advantage over other DBMS
- the logical column order should affect "select *" to avoid confusion
("select *" is not the reason the have a logical column order)
- beeing unabled to define the column order makes it look like chaos
after some years. table recreation is needed to compensate that. that's
unacceptable
- the tools will never agree how they store the column order information
if postgresql doesn't provide any way to do that.

SUGGESTIONS:
- add a column to the pg_attribute table to store the logical order
- make "select *" use that new column instead of the old physical order
column
- add a new command to the query interpreter

1. ALTER TABLE <table> ALTER COLUMN <column> POSITION <i>
2. ALTER TABLE <table> POSITIONS <i> <column>,<column>,...

the first statement would be sufficient to do all things, the second is
just a more powerfull shortcut.

ADVANTAGES:
- if there is any advantage in having this or that physical order,
postgresql could optimize the physical order and keep the logical
- MySQL supports inserting a new column at a given position. MySQL
changes the physical order of the columns. Postgresql can do better by
optimizing the physical order and maintaining the logical order separatly.


Re: RE : full featured alter table?

From
Sven Köhler
Date:
 > You could invent a syntax that supports both use cases, along the
 > lines of
 >
 > ALTER ... POSITION <i> <column1> [ , <column2> ... ]

This idea is great, although the statement

ALTER TABLE <table> POSITION <i> <column>,<column>,...

might make the task to maintain the pg_attribute table more complicated
than the simple statement

ALTER TABLE <table> ALTER COLUMN <column> POSITION <i>

which can be transformed into 2 update-statements i think.
perhaps it would be simpler to define a statement like

ALTER TABLE <table> POSITIONS <column1> <i1>, <column2> <i2>, ...

which just means the following:

ALTER TABLE <table> ALTER COLUMN <oclumn1> POSITION <i1>
ALTER TABLE <table> ALTER COLUMN <oclumn2> POSITION <i2>

we wouldn't have such strong/complicated contraints for each <i>,
because each <i> can be >=1 and <= the column-count.

i don't know what i'd like most, but although your last suggestion looks
great, it makes it hard to estimate what's the result.



Re: RE : full featured alter table?

From
Bruce Momjian
Date:
Added to TODO:

   o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
     have SELECT * and INSERT honor such ordering



---------------------------------------------------------------------------

Tom Lane wrote:
> =?ISO-8859-1?Q?Sven_K=F6hler?= <skoehler@upb.de> writes:
> > perhaps we could also think about a
> > ALTER TABLE <table> POSITIONS <column1>,<column2>,...
>
> You could invent a syntax that supports both use cases, along the lines
> of
>
> ALTER ... POSITION <i> <column1> [ , <column2> ... ]
>
> with the meaning that the named columns are inserted sequentially between
> positions i-1 and i, moving them from wherever they were, and leaving
> all not-mentioned columns in their existing relative order.  This
> degenerates to the same as your first proposal if one column is named,
> and at the other extreme allows all the columns to be re-ordered in one
> command.
>
> It could get a little confusing if some of the named columns previously
> occupied positions less than <i>.  I'd suggest the following
> more-concrete specification:
>
> 1. <i> must be in the range 1 to (<number of columns in table> -
>    <number of columns named in statement> + 1).
> 2. After the ALTER, the named columns have ordinal positions <i>, <i+1>,
>    etc.
> 3. Any columns not named are placed into the remaining slots (1..i-1
>    and i+nnamedcols..ntablecols) in the same relative order they had
>    before.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: RE : full featured alter table?

From
Sven Köhler
Date:
> Added to TODO:
>
>    o Allow columns to be reordered using ALTER ... POSITION i col1 [,col2];
>      have SELECT * and INSERT honor such ordering

Great to hear that.
Thx.


Re: full featured alter table?

From
Andrew Sullivan
Date:
On Sat, Jun 14, 2003 at 07:33:19PM +0200, Sven K?hler wrote:
>
> "select *" should refelect the cosmetical order of the columns.

Why?  You asked for everything, and specified no order.

> "select *" could be tranformed into something like "select col1, col2,
> ..." according to the cosmetical order that's defined.

What's wrong with CREATE VIEW if this is so important?

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Re: full featured alter table?

From
Manfred Koizar
Date:
On Wed, 25 Jun 2003 15:29:20 -0400, Andrew Sullivan
<andrew@libertyrms.info> wrote:
>On Sat, Jun 14, 2003 at 07:33:19PM +0200, Sven K?hler wrote:
>>
>> "select *" should refelect the cosmetical order of the columns.
>
>Why?  You asked for everything, and specified no order.

AFAICS it's a matter of standard conformance.  SLQ92 says

in  4.8 Columns:

   A column is described by a column descriptor. A column descriptor
   includes:
   [...]
   -  the ordinal position of the column within the table that con-
      tains the column.

and in  7.9  <query specification>:

   3) Case:

      a) [deals with EXISTS]

      b) Otherwise, the <select list> "*" is equivalent to a <value
        expression> sequence in which each <value expression> is a
        <column reference> that references a column of T and each
        column of T is referenced exactly once. The columns are ref-
        erenced in the ascending sequence of their ordinal position
        within T.

   4) The <select sublist> "<qualifier>.*" for some <qualifier> Q is
      [similar to 3b]

Servus
 Manfred