GDA: empty PostgreSQL table failed workaround

[Derek Atkins]

Quoting Daniel Espinosa <esodan at gmail.com>:

> 2008/2/13, Mark Johnson <mrj001 at shaw.ca>:
>> Phil Longstaff wrote:
>> > The slot_id is used to provide a unique primary key.  I don't know if it
>> > would work to have the slots table have *no* key, but have an index on
>> > the obj_guid field.  The obj_guid field can't be the primary key because
>> > I believe a primary key needs to be unique.  Mark?
>> >
>> >
>> Yes, a primary key does have to be unique.  I can't think of any
>> requirement that a table have a primary key though.  It is generally
>> good practice.  You could have an index on the guid field.
>>
>
> Derek can confirm this: GUID is unique for any object using in QOF,
> then you can use it as the primary key, the control of unique GUID
> will be responsability of GnuCash/QOF and that GUID is almost
> imposible to  be duplicated in other instance of GnuCash accessing to
> the DB, even if it is simultaneous.

Unfortunately in this particular case the slot entries and Recurrences
are NOT first-class QOF objects, so each entry does NOT have a unique
GUID.  The GUIDs here are references to the containing object, but because
you could have multiple slot entries or multiple recurrences this means you
might have multiple rows in the slots or recurrences tables that contain
the same GUID.   So in this case you cannot use it as the primary key.

> Just take in acount that GUID is a string, then it will represent more
> work for the DBMS to check for the unique constraint.
>
>> In my benchmarking, I was thinking I might create such an index to see
>> if it helped.  I haven't got that far though.  I will be posting my
>> results so far (i.e. without any additional indices) soon (hopefully
>> later today).  Obviously, they can't include the complete series of
>> queries for PostgreSQL.  I have done MySql and SQLite, but need to run
>> the partial series for PostgreSQL.
>>
>> Mark

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available