GDA: PostgreSQL empty slots table

[Phil Longstaff]

Mark Johnson wrote:
> 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.

I just saw an e-mail on the libgda mailing list which said that GdaQuery 
might be deprecated in the 4.0 series.
>>
>> 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.

Unfortunately, any fix for this will take time to propagate to the 
distributions and make it out to people who want to use the GDA backend 
with postgresql.  We might need a workaround.

>
>> 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.

Well, we could limit support to sqlite, mysql and postgresql.  They are 
the 3 backends for which the provider status is "fully functional".
>
>> 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.

Would the performance be any worse than "SELECT DISTINCT tx_guid FROM 
splits..." followed by "SELECT * from SPLIT where tx_guid IN (...)" 
which is a nested loop broken up into 2 pieces?

Hmmm... I just found there is a way to add a batch of rows to a 
GdaDataModelQuery.  I wonder if this would then allow the mysql and 
postgresql providers to save all the new rows at once, and require the 
sqlite provider to loop and add 1 row at a time.  This would keep the 
source common but still allow the performance improvement when saving.  
I'm not sure I trust libgda to handle anything that fancy.

>>
>> 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