Thread

Re: full featured alter table?

Reynard Hilman <reynardmh@lightsky.com> — 2003-06-12T10:33:54Z

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.

full featured alter table?

Sven Köhler <skoehler@upb.de> — 2003-06-12T14:20:16Z

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?

Jonathan Bartlett <johnnyb@eskimo.com> — 2003-06-12T14:26:49Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-12T14:33:11Z

> 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?

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-06-12T14:44:13Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-12T14:56:56Z

> 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?

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-06-12T15:24:31Z

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?

Reynard Hilman <reynardmh@lightsky.com> — 2003-06-13T17:05:32Z

>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?

scott.marlowe <scott.marlowe@ihs.com> — 2003-06-13T21:33:04Z

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?

Enrico Weigelt <weigelt@metux.de> — 2003-06-13T22:44:25Z

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?

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2003-06-13T22:48:28Z

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?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-13T22:49:01Z

"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?

Jim C. Nasby <jim@nasby.net> — 2003-06-14T15:31:02Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T15:34:49Z

> 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?

Bruno Wolff III <bruno@wolff.to> — 2003-06-14T15:59:08Z

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?

Bruno BAGUETTE <pgsql-ml@baguette.net> — 2003-06-14T16:55:36Z

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?

Randal L. Schwartz <merlyn@stonehenge.com> — 2003-06-14T17:26:45Z

>>>>> "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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T17:33:19Z

 > 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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T17:35:15Z

 >>> 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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T17:45:23Z

> 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?

Guillaume LELARGE <gleu@wanadoo.fr> — 2003-06-14T17:54:47Z

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?

Bruno Wolff III <bruno@wolff.to> — 2003-06-14T17:57:30Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T17:59:19Z

>>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?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-14T18:13:00Z

Bruno Wolff III <bruno@wolff.to> writes:
>   Sven Khler <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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T18:17:38Z

> 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?

Enrico Weigelt <weigelt@metux.de> — 2003-06-14T20:09:56Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T20:18:30Z

>>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?

Sven Köhler <skoehler@upb.de> — 2003-06-14T20:21:16Z

>>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?

Enrico Weigelt <weigelt@metux.de> — 2003-06-14T20:32:34Z

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?

Enrico Weigelt <weigelt@metux.de> — 2003-06-14T20:45:01Z

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?

Tino Wildenhain <tino@wildenhain.de> — 2003-06-14T21:08:06Z

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?

Jay O'Connor <joconnor@cybermesa.com> — 2003-06-14T21:18:43Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-15T00:03:00Z

>>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?

Sven Köhler <skoehler@upb.de> — 2003-06-15T00:19:22Z

>>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?

Sven Köhler <skoehler@upb.de> — 2003-06-15T00:20:44Z

> 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?

Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-06-15T10:33:28Z

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?

Ernest E Vogelsinger <ernest@vogelsinger.at> — 2003-06-15T11:04:23Z

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?

Mike Mascari <mascarm@mascari.com> — 2003-06-15T11:49:39Z

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?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-15T17:22:46Z

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?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-15T17:25:19Z

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?

Bruno BAGUETTE <pgsql-ml@baguette.net> — 2003-06-15T21:17:01Z

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?

Bruno BAGUETTE <pgsql-ml@baguette.net> — 2003-06-15T21:30:45Z

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?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-15T21:52:01Z

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?

Ernest E Vogelsinger <ernest@vogelsinger.at> — 2003-06-15T22:13:45Z

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?

Tino Wildenhain <tino@wildenhain.de> — 2003-06-15T22:43:32Z

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?

Jim C. Nasby <jim@nasby.net> — 2003-06-16T05:45:57Z

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?

Jim C. Nasby <jim@nasby.net> — 2003-06-16T05:55:39Z

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?

Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-06-16T10:54:14Z

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?

Greg Stark <gsstark@mit.edu> — 2003-06-16T13:26:02Z

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?

Ernest E Vogelsinger <ernest@vogelsinger.at> — 2003-06-16T13:45:03Z

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?

Mike Mascari <mascarm@mascari.com> — 2003-06-16T14:21:14Z

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?

Ernest E Vogelsinger <ernest@vogelsinger.at> — 2003-06-16T14:27:50Z

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: full featured alter table?

Jan Wieck <janwieck@yahoo.com> — 2003-06-16T16:49:44Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-16T16:49:50Z

> "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: RE : full featured alter table?

Jan Wieck <janwieck@yahoo.com> — 2003-06-16T16:56:29Z

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?

Jim C. Nasby <jim@nasby.net> — 2003-06-16T17:00:49Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-16T17:20:51Z

>>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: full featured alter table?

Greg Stark <gsstark@mit.edu> — 2003-06-16T18:13:40Z

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?

Jean-Luc Lachance <jllachan@nsd.ca> — 2003-06-16T19:25:33Z

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: RE : full featured alter table?

Tino Wildenhain <tino@wildenhain.de> — 2003-06-16T19:26:12Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-16T19:30:51Z

> 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?

Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-16T20:20:55Z

=?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?

scott.marlowe <scott.marlowe@ihs.com> — 2003-06-16T21:18:01Z

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?

Dennis Gearon <gearond@cvc.net> — 2003-06-16T22:04:13Z

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?

Enrico Weigelt <weigelt@metux.de> — 2003-06-16T22:15:22Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-16T23:22:37Z

 > 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?

Sven Köhler <skoehler@upb.de> — 2003-06-17T00:21:39Z

>>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?

Jan Wieck <janwieck@yahoo.com> — 2003-06-17T00:32:57Z

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?

Greg Stark <gsstark@mit.edu> — 2003-06-17T04:34:44Z

[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?

Nigel J. Andrews <nandrews@investsystems.co.uk> — 2003-06-17T06:45:28Z


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?

Mattias Kregert <mattias@kregert.se> — 2003-06-17T10:04:16Z

> > 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 have to 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 other tables.
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 will be 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 of the 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?

Jan Wieck <janwieck@yahoo.com> — 2003-06-17T10:51:15Z

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?

Bruno BAGUETTE <pgsql-ml@baguette.net> — 2003-06-17T11:21:08Z

> > > 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?

Sven Köhler <skoehler@upb.de> — 2003-06-17T11:40:40Z

> 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.

full featured alter table/column ordering - a summary

Sven Köhler <skoehler@upb.de> — 2003-06-18T13:50:14Z

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?

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-06-24T01:08:33Z

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?

Sven Köhler <skoehler@upb.de> — 2003-06-25T11:48:57Z

> 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?

Andrew Sullivan <andrew@libertyrms.info> — 2003-06-25T19:29:20Z

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?

Manfred Koizar <mkoi-pg@aon.at> — 2003-06-26T14:59:55Z

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