Re: Re: Re: ALTER DROP COLUMN

>So how is this useful for anything beyond a toy database?
>
>This inability to drop a column makes it hard to follow an "extreme
>programming" style of development, where one designs only as much into
>the database schema as is needed at the moment (DTSTTCPW, YAGNI).
>When I do that I often find that I need to drop columns (in favor of
>other columns of different types) as I evolve the schema to support
>ever more complex applications. PostgreSQL makes this a bit harder
>than it needs to be.

I would content that any moderately complex database design that starts at
the keyboard instead of a pad of paper (i.e., DESIGNED) is going to have
more problems than a "toy" database. Postgres is used in many "real"
applications but more than likely they were thought out and designed before
committing to tables.

len morgan

"Len Morgan" <len-morgan(at)crcom(dot)net> writes:

> >So how is this useful for anything beyond a toy database?
> >
> >This inability to drop a column makes it hard to follow an "extreme
> >programming" style of development, where one designs only as much into
> >the database schema as is needed at the moment (DTSTTCPW, YAGNI).
> >When I do that I often find that I need to drop columns (in favor of
> >other columns of different types) as I evolve the schema to support
> >ever more complex applications. PostgreSQL makes this a bit harder
> >than it needs to be.
>
> I would content that any moderately complex database design that starts at
> the keyboard instead of a pad of paper (i.e., DESIGNED) is going to have
> more problems than a "toy" database. Postgres is used in many "real"
> applications but more than likely they were thought out and designed before
> committing to tables.

For me, who has developed a few databases with web interfaces, this
isn't very easy, because the end user isn't always (read: almost
never) very good at explaining exactly what he/she wants, or they come
up with things they want later, after they've used the system for a
while. They, as users, don't realise that what seems to be small
changes to them, might need some redesigning of the database. As a
programmer, I can't tell the users that we can't make the change,
because they should've thought of it from the start.

Tomas

--- Tomas Berndtsson <tomas(at)nocrew(dot)org> wrote:
> "Len Morgan" <len-morgan(at)crcom(dot)net> writes:
>
> > >So how is this useful for anything beyond a toy database?
> > >
> > >This inability to drop a column makes it hard to follow an
> "extreme
> > >programming" style of development, where one designs only
> as much into
> > >the database schema as is needed at the moment (DTSTTCPW,
> YAGNI).
> > >When I do that I often find that I need to drop columns (in
> favor of
> > >other columns of different types) as I evolve the schema to
> support
> > >ever more complex applications. PostgreSQL makes this a
> bit harder
> > >than it needs to be.
> >
> > I would content that any moderately complex database design
> that starts at
> > the keyboard instead of a pad of paper (i.e., DESIGNED) is
> going to have
> > more problems than a "toy" database. Postgres is used in
> many "real"
> > applications but more than likely they were thought out and
> designed before
> > committing to tables.
>
> For me, who has developed a few databases with web interfaces,
> this
> isn't very easy, because the end user isn't always (read:
> almost
> never) very good at explaining exactly what he/she wants, or
> they come
> up with things they want later, after they've used the system
> for a
> while. They, as users, don't realise that what seems to be
> small
> changes to them, might need some redesigning of the database.
> As a
> programmer, I can't tell the users that we can't make the
> change,
> because they should've thought of it from the start.
>
>
> Tomas

You might also try dumping the database, dropping and
re-creating the database, and then hacking the dump to add,
delete, alter columns. That way the original structure should
remain intact.

I have been in the same situation as you many times where users
request alterations that can seriously screw up your original
structure. However, it is your responsibility to inform them
that any change, no matter how small can require a lot of
development time.

If they are requesting very frequent changes then the project
should return to the design (paper) phase that Len spoke of. I
know it's not as fun as poking around in the database, but it
can save you loads of time and headaches.

Brent

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/

On Tue, Feb 13, 2001 at 06:40:30AM -0800, Brent R. Matzelle wrote:
> You might also try dumping the database, dropping and
> re-creating the database, and then hacking the dump to add,
> delete, alter columns. That way the original structure should
> remain intact.

I find that the dump output is a pain to work with. Information that
was concise when first defined ("id_stuff serial primary key") appears
in least common denominator form as separate indexes, sequences,
constraints, etc. And if I'm deleting a column, do I have to write
Perl scripts or the like to strip out the obsolete column data from
the "COPY ... FROM" sections? What a PITA.

A key strategy in Extreme Programming is to make the
system-under-development as amenable to change as possible (while
satisfying current requirements as simply as possible -- an obvious
tension). Some projects may have the luxury of defining the schema
"on paper" once and for all, no changes ever. I've just never seen
such a project in over 20 years professional programming experience.
I'm not advocating _hacking_ a schema into shape, but I do want to
_evolve_ my systems as needed to satisfy my customers' evolving
business needs (and our joint understanding of such).

> I have been in the same situation as you many times where users
> request alterations that can seriously screw up your original
> structure. However, it is your responsibility to inform them
> that any change, no matter how small can require a lot of
> development time.

Or we can improve our tools so that changes aren't so painful to
undertake. PostgreSQL is a damn fine piece of work, and there are
ways that it too could evolve to make it an even more powerful tool
for doing what our customers need done, rather than just what is
convenient for us to do.

[I just got dizzy and fell off my high horse. Oww...]

--
Fred Yankowski fred(at)OntoSys(dot)com tel: +1.630.879.1312
Principal Consultant www.OntoSys.com fax: +1.630.879.1370
OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA

Fred Yankowski <fred(at)ontosys(dot)com> writes:

> On Tue, Feb 13, 2001 at 06:40:30AM -0800, Brent R. Matzelle wrote:
> > You might also try dumping the database, dropping and
> > re-creating the database, and then hacking the dump to add,
> > delete, alter columns. That way the original structure should
> > remain intact.
>
[...]
>
> Or we can improve our tools so that changes aren't so painful to
> undertake. PostgreSQL is a damn fine piece of work, and there are
> ways that it too could evolve to make it an even more powerful tool
> for doing what our customers need done, rather than just what is
> convenient for us to do.

I agree with you; it would be very nice to be able to easily change
the schema in a running database. However, the developers of PG have
limited time, and they typically devote their efforts more towards
production issues than to development issues. In other words,
changing a schema is something the developer is going to want to do,
but it's not something you'll do much once your product is in
production.

Given the choice between having rock-solid production support and
having less solid production support, but better development support,
I'd still vote on the side of production. If somebody like you or me
wants to add ALTER TABLE DROP COLUMN, I'm sure the PG developers would
gladly accept a patch.

In the meantime, what works well for me is to build my schema by way
of an SQL script or three. When I change the schema, I just change
the script. If the database is in development, I can frequently get
away with dropping the whole thing, re-creating it, and re-running the
script. Even if not, I can run pg_dump, drop and re-create the
database, run the schema script, and then restore just the data (no
structure) from dump. It's a pain, but it does work. And it has the
added benefit of giving me a text record of the schema that I can
check into CVS and examine previous versions of.

Chris

--
chris(at)mt(dot)sri(dot)com -----------------------------------------------------
Chris Jones SRI International, Inc.
www.sri.com

--- Fred Yankowski <fred(at)ontosys(dot)com> wrote:
> On Tue, Feb 13, 2001 at 06:40:30AM -0800, Brent R. Matzelle
> wrote:
> > You might also try dumping the database, dropping and
> > re-creating the database, and then hacking the dump to add,
> > delete, alter columns. That way the original structure
> should
> > remain intact.
>
> I find that the dump output is a pain to work with.
> Information that
> was concise when first defined ("id_stuff serial primary key")
> appears
> in least common denominator form as separate indexes,
> sequences,
> constraints, etc. And if I'm deleting a column, do I have to
> write
> Perl scripts or the like to strip out the obsolete column data
> from
> the "COPY ... FROM" sections? What a PITA.

Hey, you don't have to tell me that it's a pain. I was just
giving you another way to do the same thing.

<snip>

> [I just got dizzy and fell off my high horse. Oww...]
>

Everyone has to rant once and a while ;) Anyway, there is hope
in all of this. The PostgreSQL development team could build
support for it in some newer versions. And it's open source so
even you could hack the functionality it if you want. Hey, I'd
do it myself if I had the time and motivation. In the meantime
we'll just have to wait I guess.

Brent

__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/

> I would content that any moderately complex database design that starts at
> the keyboard instead of a pad of paper (i.e., DESIGNED) is going to have
> more problems than a "toy" database. Postgres is used in many "real"
> applications but more than likely they were thought out and designed
before
> committing to tables.

So you are able to design your "real" applications completely without any
problems, huh? Never have a missing attribute; never an extra attribute;
never an attribute in the wrong table; never having to
split/normalize/denormalize for performance or other reasons, huh? You are
amazing -- and a liar.

David

On Tue, 13 Feb 2001, Fred Yankowski wrote:

> Or we can improve our tools so that changes aren't so painful to
> undertake. PostgreSQL is a damn fine piece of work, and there are
> ways that it too could evolve to make it an even more powerful tool
> for doing what our customers need done, rather than just what is
> convenient for us to do.

There have been some questions about how to best do a drop column
which is a big reason there isn't a final implementation yet
(although there is some implementation in the code I believe
ifdefed out with _DROP_COLUMN_HACK__). There are discussions about
this on -hackers a while back (sorry don't remember when).

> So you are able to design your "real" applications completely without any
> problems, huh? Never have a missing attribute; never an extra attribute;
> never an attribute in the wrong table; never having to
> split/normalize/denormalize for performance or other reasons,
> huh?

Yes, it does occasionally happen that you want to drop a column. It can be
worked around by dropping/reloading the table.. I personally would find it
useful to have the ability to do it in a single command, no matter how
slow/innefficient the implementation is. Just a matter of someone getting
around to writing it, I think...

- Andrew