Re: [INTERFACES] [pgaccess-users] RE: bugzilla.pgaccess.org

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