[Rt-devel] [rt-users] PostgreSQL schema discrepancies

[Jesse Vincent]

[NOTE: Moved from rt-users]

For RT 3.8.0, we had a major fixup pass of historical mysql schemas for
RT. We've been able to avoid that for Pg up to now, as the upgrade
changes made over the years are technically backwards compatible and
predated the infrastructure needed to do minor schema updates for Pg.

It may make sense for 3.8.3 to do a postgres schema normalizer modeled on
an improved version of the MySQL updater we did for 3.8.0.

Does anyone feel like volunteering?

On Fri, Dec 19, 2008 at 12:00:58PM -0500, Kevin Murphy wrote:
> This message is for RT database hackers, in case 3.8 database upgrade 
> scripts do not already address some long-standing discrepancies between 
> fresh PostgreSQL schemas and vintage 3.X schemas.  I haven't checked 
> MySQL because I don't use it.
> 
> I wrote a script to compare my live ("old" below) PostgreSQL schema with 
> the 3.6.7 etc/schema.Pg ("new" below) and found a few differences.  I'm 
> currently at RT 3.6.7 using PostgreSQL 8.3.5, and I've been upgrading 
> both steadily since the summer of 2003 (RT 3.0.3).
> 
> Most of the differences are benign, but a couple are not.  The 
> differences are not the result of my skipping a database upgrade script 
> -- the changes just don't occur in etc/upgrade/*/*.
> 
> QUESTIONABLE DIFFERENCES:
> 
> *) Old (live) transactions.data is much smaller (100) than new (255):
> NEW: data character varying(255)
> OLD: data character varying(100)
> This problem was discussed here in 2006: 
> http://www.gossamer-threads.com/lists/rt/users/55087#55087
> I checked, and my users have experienced problems because of this.
> 
> *) Old (live) transactions.objecttype is much smaller (64) than new (255):
> NEW: objecttype character varying(255) NOT NULL
> OLD: objecttype character varying(64) NOT NULL
> 
> *) Old (live) customfields.maxvalues lacks default value and NOT NULL 
> constraint:
> NEW: maxvalues integer DEFAULT 0 NOT NULL
> OLD: maxvalues integer
> 
> *) Old (live) objectcustomfieldvalues.objecttype has NOT NULL 
> constraint, new does not:
> NEW: objecttype character varying(255)
> OLD: objecttype character varying(255) NOT NULL
> 
> *) Old (live) has no index on groups:
> NEW: CREATE INDEX groups2 ON groups USING btree (type, instance, domain);
> OLD: n/a
> 
> *) Old (live) has a couple of extra indexes on objectcustomfieldvalues:
> NEW: n/a
> OLD: CREATE INDEX ticketcustomfieldvalues1 ON objectcustomfieldvalues 
> USING btree (customfield, objectid, content);
> OLD: CREATE INDEX ticketcustomfieldvalues2 ON objectcustomfieldvalues 
> USING btree (customfield, objectid);
> 
> BENIGN DIFFERENCES:
> 
> *) Integer booleans for, e.g., 'disabled' columns:
> NEW: disabled integer DEFAULT 0 NOT NULL
> OLD: disabled smallint DEFAULT 0 NOT NULL
> I assume these smallint fields are all used as booleans, so this seems 
> benign.
> 
> *) id columns have different internal PostgreSQL definition:
> NEW: id integer DEFAULT nextval('attributes_id_seq'::regclass) NOT NULL
> OLD: id integer DEFAULT nextval(('attributes_id_seq'::text)::regclass) 
> NOT NULL
> It's interesting that PostgreSQL has propagated a differing internal 
> translation of "id INTEGER DEFAULT nextval('attributes_id_seq')" through 
> the ages.  Presumably this is benign.
> 
> -Kevin Murphy
> 
> 
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
> 
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
> 
> 
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media. 
> Buy a copy at http://rtbook.bestpractical.com
> 

--