GDA: PostgreSQL empty slots table

[Mark Johnson]

Phil Longstaff wrote:
> 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.
Interesting.  I had tried something with executing SQL strings a while 
back.  I hadn't tried building the queries through the object model as 
you did.
>
> 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.  
Agreed.  I have tracked this far enough to file a reasonable bug report 
against the PostgreSQL backend.  I just ran out of time to do it.  I'll 
get to it in the next couple of days.

> 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.  
Agreed.  Not having special cases for different backends is a major 
reason to use libgda.  And it has enough backends that supporting 
special cases would be an undesirable amount of work.

> 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.
I wasn't aware of this limitation of SQLite.  I have not had occasion to 
use it.  I've recently looked a little into the manual.  Enough to see 
that it supports sub-queries and only does joins through nested loops.  
That may cause performance issues with my suggested queries, especially 
the last one.
>
> Mark, I am happy if you want to design any of the SQL queries to 
> improve them.
I'm glad.  In the next couple of days, I'm going to try to benchmark 
some of them (with MySQL since their query browser helpfully gives the 
time needed for the query).
>
> Phil
>
>