GDA: PostgreSQL empty slots table

Phil Longstaff plongstaff at rogers.com
Thu Feb 7 21:50:54 EST 2008


Mark Johnson wrote:
> The slots table includes an id field which is "auto_increment".  
> PostgreSQL does not implement that keyword.  Instead, it appears to 
> accept it, but ignore it when creating the table.  (This may actually be 
> libgda's PostgreSQL provider doing that.)  Gnucash-gda relies upon that 
> field auto-incrementing when inserts are done to the slots table.  The 
> result is similar to this for every insert to the slots table:
> ERROR:  null value in column "slot_id" violates not-null constraint
> STATEMENT:  INSERT INTO slots (obj_guid, name, slot_type, int64_val, 
> string_val, double_val, timespec_val, guid_val, numeric_val_num, 
> numeric_val_denom) VALUES ('77889f8da5fb434ae68891da19bef5ad', 
> 'reconcile-info/last-date', 1, 1199170799, NULL, NULL, '1969-12-31', 
> NULL, 0, 1)
>
> When saving to the gda backend, both MySql and sqlite have many records 
> in the slots table.  PostgreSQL ends up with none.
>   

GDA provides 1) numerous backends and 2) an object model to build 
queries and get results.  From what I have seen so far, (1) is more 
mature than (2).  For example, some of the queries you wrote in your 
other e-mail (SELECT * from t1 where t1.guid in (SELECT * from ...)) 
cannot be represented in the query model.  However, I should be able to 
create a string and execute it.

It looks as though the postgresql backend has a bug.  Using the object 
model, I create an XML-based representation of the table.  I create the 
'slot_id' column with type integer and add the 'AUTOINC' flag to it.  
For mysql and sqlite, this is an INTEGER AUTO_INCREMENT column, while 
for postgresql, it should be a SERIAL column.  I don't really want to 
special-case the code for various backends since libgda is supposed to 
remove that need.  There are other cases where it might be required.  
SQLite, for example, does not allow more than one row to be added in an 
INSERT statement, whereas mysql and postgresql do.  I could speed up 
initial save to a blank db by writing all accounts at once, for example, 
but sqlite couldn't handle it and would need them to be written one at a 
time.  Note that the libgda object model does not allow more than 1 row 
to be written either, so that would need to be done with SQL directly.

Mark, I am happy if you want to design any of the SQL queries to improve 
them.

Phil



More information about the gnucash-devel mailing list