Re: [INTERFACES] [pgaccess-users] RE: bugzilla.pgaccess.org - Mailing list pgsql-hackers

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