Minor PostgreSQL issues

[Dean Gibson]

On 2012-08-27 06:14, Derek Atkins wrote:
> Hi,
>
> Dean Gibson <gnucash at ultimeth.com> writes:
> ...
>> 2. If there are any other tables in OTHER SCHEMAS, a GnuCash save
>>     deletes them (ugh).
> I have no idea what you mean by "Other Schema" here.  You cannot share
> the gnucash database with other apps, and I don't know why you would try
> to put multiple applications into the same database.  (Again, I'm using
> MySQL terminology here).

There are LOTS of reasons to share the data.  That's the whole purpose 
of using a database -- to share the data and make it "extensible".  Eg, 
doing custom reports with complex filters, or other reporting and 
alerting functions.  In order to KEEP SEPARATE the DB data in one 
application from another, and still allow that sharing, SCHEMAs (been in 
the SQL standard for almost a decade) are used.  As a test before saving 
my GnuCash data to PostgreSQL, I created an empty DB with both a public 
SCHEMA and a separate named SCHEMA, and created a token TABLE within it 
the latter.  The initial save wiped the separate SCHEMA and everything 
within it.

Put bluntly, MySQL is a toy database, and has been for a decade.  It 
doesn't have SCHEMAs (as a distinct capability from DATABASE), and it 
didn't have VIEWs (a requirement for any serious DB use) until fairly 
recently.  MySQL's main claim to fame is that it's fast and simple.


>
>> 3. The name of the PostgreSQL database must be all lower case -- I
>>     couldn't find a way to specify/use a mixed case name.
> This is correct, and is done on purpose, because there are cases where
> databases automatically downcase the name and later mixed-case names
> cause problems.  As a result of bugs with allowing mixed case GnuCash
> will always downcase the database name.  Sorry.

Most databases downcase all names unless they are in double quotes. I do 
understand that the work required to preserve case in a consistent 
manner, may not be worth the effort.  However, (for example) phpBB (a 
message board system that stores messages in a DB) bit the bullet on 
this a number of years ago, and supports preserving DB names that are in 
double quotes (phpBB also supports SCHEMAs).


>
>> 4. It's slower to open than the XML-format files, and I believe I
>>     recall that the close is equally slow.  From this I got the
>>     impression that GnuCash did not make incremental changes to the DB,
>>     but did a complete wipe/save, but I may be wrong on this.
> This is where you are wrong.  GnuCash does indeed make incremental
> changes to the DB.

Good!  I'm glad to both be wrong (to clear up my misunderstanding), and 
that it does make immediate posts.

The only "deal-breaker" in my list of issues is the lack of SCHEMA 
support.  That's only a "deal-breaker" for using PostgreSQL as a backend 
store, not a "deal-breaker" for using GnuCash.  I'm perfectly content to 
use the compressed XML file format for the foreseeable future.

I see you are associated w/ MIT.  I applied to both MIT and CalTech (and 
Stanford), and was accepted at all three.  I went to CalTech, mainly 
because I happened to live in the same town (Pasadena), despite the fact 
that MIT offered me a partial scholarship.  That's not a dig at MIT;  I 
seriously considered MIT as well.