Thread: Re: [INTERFACES] [pgaccess-users] RE: bugzilla.pgaccess.org
<delurk - reading from the archives, so please cc me on responses> Note that before bugzilla really supports postgresql, we (ie the bugzilla team) are going to need DROP COLUMN support, as well as support for changing a field's type. This is because thats how upgrades are done, when new features change the bz schema. See http://lxr.mozilla.org/mozilla/source/webtools/bugzilla/checksetup.pl#2193 and below for the code. Lots of it is currently mysql specific, and could easily be wrapped in helper functions - some of it already is. That won't help if there isn't an easy way to use the functionality, though. Reclaiming the disk space is also really needed, because upgrading a bugzilla installation could change a table multiple times, and requirng all that extra disk space will probably be an issue with most admins. Bradley
On Wed, 2002-07-10 at 19:44, Bradley Baetz wrote: > <delurk - reading from the archives, so please cc me on responses> > > Note that before bugzilla really supports postgresql, we (ie the bugzilla > team) are going to need DROP COLUMN support, as well as support for > changing a field's type. This is because thats how upgrades are done, when > new features change the bz schema. Agreed it would be nice, but how come upgrades cannot be done with temp tables -- especially since the bugzilla database is simple (no foreign key constraints, etc.) If you're persisting with using ENUM(), a common upgrade script won't work anyway. -- Create a table create table a (col1 varchar(4)); -- Add some data insert into table a values (1); insert into table a values (2); -- Lets change the datatype of col1 begin; create temp table a_tmp as select * from a; drop table a; create table a (col1 integer); insert into a (col1) select cast(col1 as integer) from a_tmp; commit; You've just changed the datatype from a varchar to integer. With the transaction support, you're guaranteed it won't be left mid way through either.
On 10 Jul 2002, Rod Taylor wrote: > On Wed, 2002-07-10 at 19:44, Bradley Baetz wrote: > > <delurk - reading from the archives, so please cc me on responses> > > > > Note that before bugzilla really supports postgresql, we (ie the bugzilla > > team) are going to need DROP COLUMN support, as well as support for > > changing a field's type. This is because thats how upgrades are done, when > > new features change the bz schema. > > Agreed it would be nice, but how come upgrades cannot be done with temp > tables -- especially since the bugzilla database is simple (no foreign > key constraints, etc.) If you're persisting with using ENUM(), a common > upgrade script won't work anyway. We don't plan on persisting in using enum :) <snip> > > You've just changed the datatype from a varchar to integer. With the > transaction support, you're guaranteed it won't be left mid way through > either. > Well, when bugzilla supports a db which supports foreign constraints, we'd like to add those in, too 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? The alternative is to pass the set of columns/indexes/etc into the DropColumn function each time its called, which would get messy quite quickly. Bradley
> 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. We may get DROP COLUMN in this release (Christopher?). 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. 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>; Are there any other requirements aside from drop column and altering data types?
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
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 :) One way to make the application more DB version independent is to hide the system catalog version specific stuff in views. Whatever information you need from the catalog, wrap it into bzdd_* functions and views (BugZillaDataDictionary). If the catalog changes, you just change the functions and views. You finally end up with one .sql file per supported BZ/PG combination. But that's alot better than bunches of if()'s in the application code. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Bradley Baetz wrote: > <delurk - reading from the archives, so please cc me on responses> > > Note that before bugzilla really supports postgresql, we (ie the bugzilla > team) are going to need DROP COLUMN support, as well as support for > changing a field's type. This is because thats how upgrades are done, when > new features change the bz schema. DROP COLUMNS should be in 7.3, due out in the Fall. You can simulate ALTER COLUMN by creating a new column, UPDATING the data to the new column, dropping the old, then renaming the new column to the old name. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
> > Note that before bugzilla really supports postgresql, we (ie > the bugzilla > > team) are going to need DROP COLUMN support, as well as support for > > changing a field's type. This is because thats how upgrades are > done, when > > new features change the bz schema. > > DROP COLUMNS should be in 7.3, due out in the Fall. Assuming, of course, that I can get past these latest problems that Tom brought up - otherwise I guess I could pass it off again :( > You can simulate > ALTER COLUMN by creating a new column, UPDATING the data to the new > column, dropping the old, then renaming the new column to the old name. Well, once DROP COLUMN is natively supported, I intend to implement a SET TYPE or MODIFY function - it should be quite straightforward once DROP COLUMN is done. It will maintain all foreign key and index references properly... Chris
> > 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... ;)
For me, I'd have to buy / install harddrives if I wanted to change data
types in some of the larger tables. I've done a number of silly things
like store an Apache hitlog in the DB for pattern analysis. Lots and
lots of rows ;)
> > 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.
Okay, if thats all it truly takes, I'll see if I can help get it done.
> > 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
enum(A,B,C) -> column char(1) check (column IN ('A', 'B', 'C'))
timestamp? Output pattern may be different, but PostgreSQL 7.3 will
accept any timestamp I've thrown at it. Lots of weird and wonderful
forms.
Anyway, I think there is a way to coerce MySQL into outputting an ISO
style timestamp, which would probably be the best way to move as it'll
make adding other DBs easier in the future.
REPLACE INTO: Have an ON INSERT TRIGGER on all tables which will update
a row if the primary key already exists -- or catch an INSERT error and
try an update instead.
> > > 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... ;)
I absolutely, definitely agree with this! If I really, really, really need
to change a column type then even if it takes 2 hours, I should have the
option. People can always resort to doing a dump, edit and restore if they
really want...
> For me, I'd have to buy / install harddrives if I wanted to change data
> types in some of the larger tables. I've done a number of silly things
> like store an Apache hitlog in the DB for pattern analysis. Lots and
> lots of rows ;)
Of course, you might have thought about the correct column types in advance,
but hey :) I think that there's no way to have a rollback-able column type
change without temporarily doubling space. Actually, I think Oracle has
some sort of system whereby the column type change is irreversible, and if
it crashes halfway thru, the table is unusable. You can issue a command on
the table to pick up where it left off. You continue to do this until it's
fully complete. However, I think the temporary doubling is probably good
enough for 90% of our users...
> > > 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.
> >
> Okay, if thats all it truly takes, I'll see if I can help get it done.
Well, you're always welcome to help me out with this DROP COLUMN business -
after which MODIFY will be straightforward. Don't forget that maybe foreign
keys, rules, triggers and views might have to be updated?
> > I think the big issues are bugzilla ones, using mysql specific features
> > (enum/timestamp types, REPLACE INTO, etc) Locking is the major one, but
>
> enum(A,B,C) -> column char(1) check (column IN ('A', 'B', 'C'))
>
> timestamp? Output pattern may be different, but PostgreSQL 7.3 will
> accept any timestamp I've thrown at it. Lots of weird and wonderful
> forms.
>
> Anyway, I think there is a way to coerce MySQL into outputting an ISO
> style timestamp, which would probably be the best way to move as it'll
> make adding other DBs easier in the future.
>
> REPLACE INTO: Have an ON INSERT TRIGGER on all tables which will update
> a row if the primary key already exists -- or catch an INSERT error and
> try an update instead.
The main thing I pick up from all of this is that Bugzilla is rather poorly
written for cross-db compatibility. It should be using a database
abstraction layer such as ADODB that will let you do a 'replace' in _any_
database, is type independent, syntax independent, etc.
Chris
On 10 Jul 2002, Rod Taylor wrote:
> enum(A,B,C) -> column char(1) check (column IN ('A', 'B', 'C'))
right.
>
> timestamp? Output pattern may be different, but PostgreSQL 7.3 will
> accept any timestamp I've thrown at it. Lots of weird and wonderful
> forms.
I'm referring to the mysql |timestamp| type, which will update that
column's contents to |now()| when any UPDATE is given for that partcular
row, unless the column was assigned to. I don't know how to handle the
last part in a trigger. Bugzilla's use of that feature is more trouble
than its worth, though, because we keep forgetting to stop the update in
places where it should be, and there are plans to remove it anyway.
> REPLACE INTO: Have an ON INSERT TRIGGER on all tables which will update
> a row if the primary key already exists -- or catch an INSERT error and
> try an update instead.
BZ uses REPLACE in 4 places, and 3 of those check for the row existing
immediately before. :)
These are bugzilla problems, not postgres ones, though.
Bradley
On Thu, 11 Jul 2002, Christopher Kings-Lynne wrote: > Of course, you might have thought about the correct column types in advance, > but hey :) I think that there's no way to have a rollback-able column type > change without temporarily doubling space. Actually, I think Oracle has > some sort of system whereby the column type change is irreversible, and if > it crashes halfway thru, the table is unusable. You can issue a command on > the table to pick up where it left off. You continue to do this until it's > fully complete. However, I think the temporary doubling is probably good > enough for 90% of our users... I don't mind temporarily doubling space - mysql docs say that all its ALTER TABLE stuff (except for renaming) is done by making a copy. > The main thing I pick up from all of this is that Bugzilla is rather poorly > written for cross-db compatibility. It should be using a database > abstraction layer such as ADODB that will let you do a 'replace' in _any_ > database, is type independent, syntax independent, etc. Yep. BZ isn't very portable - it wasn't a design goal at the time, I believe. redhat do have an oracle port though, and are working on a postgres port, so it is possible. ADODB (or a perl equivalent) is possibly overkill once we get the (legacy) column typing stuff worked out. BZ doesn't really use any non-basic SQL functionality, although the query stuff will benefit from subselects. > > Chris > Bradley
Bradley Baetz <bbaetz@student.usyd.edu.au> writes:
> I'm referring to the mysql |timestamp| type, which will update that
> column's contents to |now()| when any UPDATE is given for that partcular
> row, unless the column was assigned to. I don't know how to handle the
> last part in a trigger.
It'd probably be close enough to have an UPDATE trigger that does
if (new.timestamp = old.timestamp) new.timestamp = now();
regards, tom lane
On Wed, 10 Jul 2002, Tom Lane wrote: > Bradley Baetz <bbaetz@student.usyd.edu.au> writes: > > I'm referring to the mysql |timestamp| type, which will update that > > column's contents to |now()| when any UPDATE is given for that partcular > > row, unless the column was assigned to. I don't know how to handle the > > last part in a trigger. > > It'd probably be close enough to have an UPDATE trigger that does > > if (new.timestamp = old.timestamp) > new.timestamp = now(); Nope, because the documented way of making sure that the field doens't change is to use |UPDATE foo SET bar=bar ....|, and thats what bz uses. Don't worry about this, though - we will hpefully be removing this 'feature' soon. Bradley