GDA: empty PostgreSQL table failed workaround

[Mark Johnson]

Phil Longstaff wrote:
> Mark Johnson wrote:
>   
>> Previously, I had suggested a workaround for the empty PostgreSQL slots 
>> table problem.
>>
>> I have now tried the work-around for the empty slots table problem with 
>> PostgreSQL.  The type SERIAL is simply a convenience, and not a real 
>> type.  I had hoped the convenience extended to ALTER TABLE, but it 
>> doesn't.  The following did not work:
>>
>> gnucash_db=# alter table slots alter column slot_id type serial;
>> ERROR:  type "serial" does not exist
>>
>> However, manually creating the sequence and setting a default value 
>> appear to work:
>> gnucash_db=# create sequence seq_slot_id;
>> CREATE SEQUENCE
>> gnucash_db=# alter table slots alter column slot_id set default 
>> nextval('seq_slot_id');
>> ALTER TABLE
>>
>> and confirmed by:
>> gnucash_db=# \d+ slots
>>
>> However, this did not work.  The logfile still indicates:
>> 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 
>> ('391b8b5ede0a168f59664a8de0b4587f', 'sched-xaction/account', 5, NULL, 
>> NULL, NULL, '1969-12-31', 'ac336ec951601638920d70c6fd4b7119', 0, 1)
>>
>> Issuing the following with psql:
>> select nextval('seq_slot_id');
>> confirms that the sequence works and counts up.
>>
>> So I checked the table structure.  My default value has disappeared.  It 
>> appears that this was done when I first selected to save to this db.  
>> The following appears in the log file:
>> NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
>> "slots_pkey" for table "slots"
>> There are similar notices for all the other tables.
>>
>> Additionally, this problem now shows with another table:
>> ERROR:  null value in column "recurrence_id" violates not-null 
>> constraint                            STATEMENT:  INSERT INTO 
>> recurrences (obj_guid, recurrence_mult, recurrence_period_type, 
>> recurrence_period_start) VALUES ('00000000002000e00000000000a89408', 1, 
>> 'month', '2005-01-24')
>>   
>>     
>
> Saving to the db deletes all tables and then recreates them.  It 
> therefore deleted the slot_id without the default value.  It wouldn't 
> have touched the sequence.  I'll need to build your workaround into a 
> rev and then let you try it.  There's also an argument to be made that I 
> should create some test cases and set up automated tests for sqlite, 
> mysql and psql.  Maybe once I'm over these initial hurdles and some 
> people are working with the code.
>   
I thought it was doing something like this.  I would strongly suggest 
that some error checking is needed here.  i.e. One must ensure that the 
db is empty before doing this.

In general I think it is good to start the new db from a known state.  
You don't want users like me changing something and possibly breaking 
the db.  However, you really do need to take care that it is empty prior 
to the wipe.

Another piece of error checking that should be present is to check the 
return value to ensure that the INSERT succeeded.  That way gnucash 
itself would have detected the problem with the slots rather than me 
examining the PostgreSQL log.

For your workaround, are you thinking of a post-create fixup as I tried 
above, or of simply requesting the type SERIAL upon table creation.
> Yes, the recurrence table would have the same problem.  All of the other 
> tables are for objects which have a guid as the main reference (and 
> therefore as the table's primary key).  Slots and recurrences attach to 
> an object but there can be more than one attached to any object.
>
> Phil
>   
Mark