From: | Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu> |
---|---|
To: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
Cc: | jm(dot)poure(at)freesurf(dot)fr, Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>, dpage(at)pgadmin(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: alter table drop column status |
Date: | 2002-02-15 06:37:04 |
Message-ID: | Pine.LNX.4.21.0202150712540.24342-100000@pc10.radnoti-szeged.sulinet.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> select drop_column(mytable, mycolumn);
IMHO first at least a LOCK should be executed on all tables which are in
any reference with "mytable". If LOCK is not enough, the entire database
should be locked (in pg_hba.conf) for all users except for the maintainer.
> > 1) Mark objects for deletion
> > * mark columns in "table_from" for deletion,
> > * mark primary keys in "table_from" for deletion,
> > * mark foreign keys in "table_from" for deletion,
* check all other tables if they have any references to the columns
of "table_from" marked to be deleted; if check fails, STOP
* lock all tables which appear in FOREIGN KEYS of "table_from" and
all tables which have FOREIGN KEYS references to "table_from"
> > 2) Copy schema and data
> > * copy "table_to" structure out of "table_from" keeing only
> > marked objects,
> > * copy data from "table_from" to "table_to",
> >
> > 3) Add rules and triggers, rename
> > * add "table_from" triggers to "table_to",
> > * add "table_from" rules to "table_to",
> > * drop table "table_from",
* (postgres will automatically drop referential integrity triggers from
all tables referencing the the dropped table "table_from")
> > * rename "table_to".
* recreate referential integrity triggers in all tables described above
* unlock all locked tables
I'm afraid LOCK is not available inside a PLPGSQL function (I write almost
everything in PLPGSQL). However, a shell script should do this easily, but
it's no so smart to call a shell script from a PLPGSQL function (although
I do this some time), if Cristopher would like to use it with a single
SELECT.
Regards, Zoltan
Kov\'acs, Zolt\'an
kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Kings-Lynne | 2002-02-15 06:49:21 | Re: changing the nulability of columns |
Previous Message | Christopher Kings-Lynne | 2002-02-15 06:32:14 | changing the nulability of columns |