[rt-users] PostgreSQL schema discrepancies

[Kevin Murphy]

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