sql backend failure when deleting an account

[Matthew Vanecek]

--- Matthew Vanecek <mevanecek@yahoo.com> wrote:
> Well, I appear to be mistaken.  Deleting
> transactions
> from accounts does not appear to work, when using
> the
> SQL backend.  Most frustrating.  How do you turn on
> the logging performed by gnc_log?  I cannot find any
> documentation on it.  Surely you can log the PINFO
> messages without hard-coding and recompiling??
> 
> 
> --- Matthew Vanecek <mevanecek@yahoo.com> wrote:
> > Hello all.  I'm trying out the Postgresql backend,
> > and
> > I seem to have been bitten.  Well, not
> > seriously--it's
> > only my test set.  Anyhow, stuff seems to work
> well
> > when creating accounts.  I've also played with
> > creating and deleting transactions.  That also
> seems
> > to work well.
> > 
> > However, when I try to delete an account, it
> fails.
> > Apparently there is an invalid data type in the
> > account delete.  I haven't tracked the issue past
> > SEND_QUERY and FINISH_QUERY.  Here are the errors:
> > 
> > Error: pgendKVPDeleteStr: finish query failed:
> >         ERROR:  Attribute 'ipath' is of type
> 'int4'
> > but expression is of type 'bpchar'
> >         You will need to rewrite or cast the
> > expression
> > 
> > Error: xaccAccountCommitEdit:  backend asked
> engine
> > to
> > rollback, but this isn't handled yet. Return
> code=5
> > Error: pgendEventsPending: consume input failed:
> > PQerrorMessage: conn pointer is  NULL
> > 
> > I'm not sure about the rollback error, but the
> conn
> > pointer is NULL error seems to be becasue
> > FINISH_QUERY
> > follows SEND_QUERY, and both disconnect the
> > connection.
> > 
> > Is anybody aware of this problem, and is anyone
> > working on it?  Or has it already been addressed?
> > 
> > Just as an aside, mightn't it be better to not
> > disconnect from the database just because of an
> > error?
> >  I think a better path might be to discard the
> > change,
> > popup an appropriate error message, and maintain
> the
> > connection so that other changes can be saved. 
> Just
> > my opinion, but everything I do at work is with
> > database interaction, and there's nothing more
> > irritating than getting calls from the field
> because
> > the application disconnected from the database and
> > didn't re-establish.  Of course, in this case, the
> > disconnection was *performed* by the application!!
> > 
> > My point on the last bit is, ok, so I cannot
> delete
> > an
> > account.  Bug noted.  Why not let me continue
> > working
> > with the other pieces that *do* work, instead of
> > rudely blitzing my database connection?
> > 
> > Thanks,
> > Matt

Well, I hate to keep replying to myself, but I kept
digging.  I recompiled gnucash (1.6.4), and hardcode
gnc_should_log() to always return true.  Wasn't sure
how else to get the logging turned on. :(  Anyhow, I
captured the queries that GNUCash is sending to the
SQL backend, and discovered that they are grossly
malformed.  The columns on some "INSERTS" do not
correspond to the actual columns of the tables used in
the INSERTs.  I checked a couple of different INSERTS,
but for brevity I've only included one.

Below you see the definition of the destination
relation of the INSERT, and below that the breakdown
of the INSERT's sub-SELECT.  As you can see, the
columns being inserted do not match the columns in the
tables.  I found this offset on several INSERTs into
different tables.  Hopefully this'll all come through
OK--I gotta use Yahoo mail or another 3rd part mailer
due to me being a soon-ex-Sprint-ION customer. :/  It
looks like objtype is missing from all the INSERTs,
upon cursory inspection.  Well, it's bedtime for me
now--perhaps someone can verify my findings?

accounts=> \d gncKVPValueTrail
              Table "gnckvpvaluetrail"
  Attribute   |           Type           | Modifier 
--------------+--------------------------+----------
 sessionguid  | character(32)            | not null	
-- col. 1
 date_changed | timestamp with time zone | 			-- col.
2
 change       | character(1)             | not null	
-- col. 3
 objtype      | character(1)             | not null	
-- col. 4
 iguid        | integer                  | 			-- col.
5
 ipath        | integer                  | 			-- col.
6
 type         | character(4)             | 			-- col.
7



INSERT INTO gncKVPValueTrail 
	SELECT '3778808a825e89d84c598037d1264492' as
sessionGuid, -- col. 1
		datetime('NOW') as date_changed,  		  -- col. 2
		'd' as change, 					  -- col. 3
		*

--         Table "gnckvpvalue"
-- Attribute |     Type     | Modifier 
-----------+--------------+----------
-- iguid     | integer      | not null				  -- col. 4
-- ipath     | integer      | not null				  -- col. 5
-- type      | character(4) | 					  -- col. 6
--Index: gnckvpvalue_pkey

	from gncKVPValue
	WHERE iguid=120;


__________________________________________________
Do You Yahoo!?
Find the one for you at Yahoo! Personals
http://personals.yahoo.com