On 10 Jul 2002, Rod Taylor wrote: > > However, is there an easy way of obtaining the list of columns (and their > > types/indexes/etc) in a table, so that we can recreate table a with just > > that column missing? One which won't break when the underlying pg_* schema > > changes? > > I see. No, not that I know of. You could take an SQL dump of the DB > and work on that, then restore at the end of the upgrade process -- but > thats not so good :)
:) > > Anyway, I'd *really* like to see PostgreSQL officially supported by > Bugzilla. So would I. I cringe every time I think of the locking issues we have with mysql. There is work being done on that (on a branch), but I don't know what the state of it is. > We may get DROP COLUMN in this release (Christopher?). Yeah, I've been reading the archives. bugzilla's auto-updating schema is probably a bit of an unusual application, but it works for us. > > Changing data types probably won't appear. I don't know of anyone > working on it -- and it can be quite a complex issue to get a good > (resource friendly and transaction safe) version. I'd be happy with a non-resource friendly and non-transaction-safe version over not having the functionality at all... ;) > > That said, if drop column is finished in time would the below be close > enough to do a data type change?: > > alter table <table> rename <column> to <coltemp>; > alter table <table> add column <column> <newtype>; > update table <table> set <column> = <coltemp>; > alter table <table> drop column <coltemp>; > That would work - we'd have to manually recreate the indexes, but most of the type changes are done in combination with other changes which have us doing that anyway. > > Are there any other requirements aside from drop column and altering > data types? > I think the big issues are bugzilla ones, using mysql specific features (enum/timestamp types, REPLACE INTO, etc) Locking is the major one, but the first port to pgsql will almost certainly use heavy locking (ie mysql READ -> pgsql SHARE MODE, mysql WRITE -> ACCESS EXCLUSIVE MODE), because thats the easiest thing to port the mysql-based code over to. Less restrictive locking + select for update & friends can be added later. Thanks, Bradley ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
