Re: full featured alter table? - Mailing list pgsql-general

[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