gnucash-2.3 + sql backend

[Klaus Dahlke]

On Sat, 6 Jun 2009 13:09:58 -0400
Phil Longstaff <plongstaff at rogers.com> wrote:

> On June 6, 2009 12:38:14 pm Klaus Dahlke wrote:
> > Hi Phil,
> > I digged around a bit and found the following statement in gnucash.trace:
> >
> > * 18:31:18  INFO <qof.session> [qof_session_load_backend]  selected GnuCash
> > Libdbi (POSTGRESQL) Backend * 18:31:18  CRIT <gnc.backend.dbi>
> > [pgsql_error_fn()] DBI error: could not connect to server: Connection
> > refused Is the server running on host "localhost" and accepting
> >         TCP/IP connections on port 0?
> >
> > the server is up and running, started even with the -i option:
> > postgres 22560     1  0 18:30 ?        00:00:00
> > /usr/lib/postgresql-8.3/bin/postgres --silent-mode=true -i
> >
> > The server listens to the default port :5432 and works normal.
> >
> > Any ideas?
> 
> Yes, there's still a problem, and I just fixed it (r18108).
> 
> Phil
Hi Phil,
in meanwhile, I figured out to correct that myself: I changed the line to

gnc-backend-dbi.c:      result = dbi_conn_set_option_numeric( be->conn, "port", 5432 );

which works for me in the moment (having an hard coded port number is probably not the best way).

With that, saving into an existing empty postgres database works fine. But I have a delicate problem when it comes to re-open the database.

a) I used 'save as postres' to save foo.xac into the postgres. Works fine.
b) quitting gnucash
c) starting gnucash without any other command
d) it then loads postgres://localhost/dbname:user:password quit fast 
e) unfortunately, some accounts show wrong balances

If an account shows a wrong balances, then gnucash has calculated a balance less then zero for the time of when the first entry occurs. Example:
received an interest payment for a savings account. The first line the account shows then:
date: 1.1.2002, income=3.67; balance=-44.11
Thus, gnucash has calculated a balance of 47.78 being present on 31.12.2000.

Surprisingly, this is not the case when using sqlite3. The file got saved and after quitting gnucash and reading the sqlite3 database, the values are the same as before and as in the .xac file.

When further investigating the case, I saw that the differences might be linked to having not reconciled entries. On the command line I set all entires to be reconsiled:
gnucash=# update splits set reconcile_date= '20090606120000' where reconcile_date='19700101000000';
UPDATE 9539
gnucash=# update splits set reconcile_state= 'y' where reconcile_state='n';
UPDATE 9539

After reading the so modified gnucash database, the values are correct.

Again, it surprises me that when reading a postgres database the sorting via reconcile_date has an impact and for sqlite3 not.

BTW: there is an entry in gnucash.trace regarding the timestamp:
* 23:11:13  WARN <qof.engine> [gnc_iso8601_to_timespec_gmt()]  mktime failed to handle daylight saving: tm_hour=0 tm_year=70 tm_min=0 tm_sec=0 tm_isdst=-1 for string=1970-01-01 00:00:00

I hope that helps a bit.

Best regards,
Klaus