[lug] Postgres Alter table

[Ryan Kirkpatrick]

On Fri, 13 Jul 2001 rm at mamma.varadinet.de wrote:

> Hmm, as far as i understand the storage manager of postgresql this doesn't
> seem trivial. Taken the fact that altering the data type of a column hope-
> fully happens rather seldom i can easily live without it. 

	Yea, I can live without it as well. If you are dropping a column,
you are probably making pretty large schema changes. And in that case, a
full dump, parse (to remove, add, or convert columns with a perl script),
and load would be in order anyway. 
	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.

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

---------------------------------------------------------------------------
|   "For to me to live is Christ, and to die is gain."                    |
|                                            --- Philippians 1:21 (KJV)   |
---------------------------------------------------------------------------
|   Ryan Kirkpatrick  |  Boulder, Colorado  |  http://www.rkirkpat.net/   |
---------------------------------------------------------------------------