[lug] Postgres Alter table

[rm at mamma.varadinet.de]

On Sun, Jul 15, 2001 at 07:48:49AM -0600, Ryan Kirkpatrick wrote:
[...]

> 	Apparently MySQL can do this sort of thing, one of the reasons
> people state it is better than PgSQL. In my opinion it is just a crutch
> for poor database design in the first place. I could say more about MySQL,
> but I don't want to start a flame war, so I will stop with that.

I actually consider the capability of changing the data type (or better, the
domain) of a column during runtime a mistake. Not only introduce most
typecasts conversion/rounding errors, changing a column type also changes the
semantic behind a table's design. But, yes, no flame wars ;-)

> 
> > You're right, indices and constraints will be dropped (but not recreated).
> > What about triggers and rules? (i never checked this).
> 
> 	Looking at the PgSQL manuals (7.1), it looks like views must be
> explicitly dropped. As for rules and triggers, they appear to fall in the
> same category as views. They all reference (potentially) multiple tables,
> and therefore the semantics of when to drop a trigger/rule/view with a
> table drop are not simple. Hence, they are not dropped.
> 	I don't know how tightly a trigger or rule binds itself to a
> table. If it just references the name, then one could drop a table and
> recreate it with the same name and be just fine. Views I know bind pretty
> tightly, basically storing a precompiled SQL statement, so they must be
> recreated along with a table.
> 	To know for sure, one would have to experiment...

With psql, a simple '\d pg_trigger` is enough:

| 
|    Attribute    |    Type    | Modifier 
| ----------------+------------+----------
|  tgrelid        | oid        | 
|  tgname         | name       | 
|  tgfoid         | oid        | 
|  tgtype         | smallint   | 
|  tgenabled      | boolean    | 
|  tgisconstraint | boolean    | 
|  ...
|

seems like triggers point to the oid of the relevant table. So triggers need to
be recreated. Rules otoh seem to be stored by relation _name_ ....
BTW, there's actually a good reason for this. Iff one alters a column some of
the fields the trigger/rule refer to might either not be present any more or have
changed their data type (and hence certain rules might work in unexpected ways.
Equality test of floats come to mind ...).

  Ralf