GnuCash Daily Source Diff

[Linas Vepstas]

On Fri, Oct 19, 2001 at 08:04:29AM -0500, Dave Peticolas was heard to remark:
> Index: src/backend/postgres/PostgresBackend.c

I notice you've been working on the pg backend a lot, and at least some
of your changes help performance. 

I suspect, am not sure, that my biggest design mistake was making the
guid the 'primary key'.  I beleive that there might be a big performance
boost are (or at least, a more consistent behaviour) if the iguid was
made primary key.

sql db's use the 'primary key' is the primary index into whatever
hash-table/b-tree they use to index the data. Clearly, an 'int' compare
would be much faster than a string compare.  I assumed, falsely, I
surmise, that postgres would be smart enough to replace my string
primary key with an int, but that doesn't exactly seem to be the case.
So I assume that using iguid as the primary key should improve
performance.

The other thing I noticed was that seemingly inconsequential changes
made big differences in performance, sometimes by 20% or so, sometimes
by factors of 3 or 5.  This seemed a rather unstable situation, and 
... well, I'm not sure what to recommend.  Just be careful; seemingly
idempotent changes to the sql queries can make a big impact on
performance.

If you find you need to change the structure of the sql tables, then
note that I added a fully general automatic upgrade mechanism.  Its
in upgrade.c, upgrade.h.  It looks like its not documented .... hmmm.
I thought I'd written documentation.  

Upgrades are treated like patches; the sql db is 'patched'.  Each patch
carries three version numbers: major, minor, rev.  A client can work
with a database only if the clients & databases major verion numbers are
equal, and the clients minor number is equal or newer than the db.
The third number, the rev number, is irrelevant if the above condition
is met.  The rev number is handy only for making sure that patches are
applied in a specified order.

The gncVersion table stores these three numbers.  There's also a
human-readable text string, so that the different upgrades can be
labelled.

The actual upgrade process is 'ad hoc'.  The client pops up a dialog
asking the user if they want to upgrade the DB.  If so, then the
pgendUpgradeDB() routine is called.  This routine is a set of nested
case statements that compare version numbers, and apply patches as
needed.  As of this writing, there's only one upgrade:
'put_iguid_in_tables()'.  Note its ad-hoc manner.  Everything else 
in upgrade.c is focused on trying to figure out when its appropriate to
call 'put_iguid_in_tables()'.  Other upgrades should follow this same
pattern: create the adhoc routine, and plug it into a case statement 
in the pgendUpgradeDB() call.  Everything else is automatic.


--linas


-- 
pub  1024D/01045933 2001-02-01 Linas Vepstas (Labas!) <linas@linas.org>
PGP Key fingerprint = 8305 2521 6000 0B5E 8984  3F54 64A9 9A82 0104 5933