Thursday, September 7, 2017

The Lino Welfare test suite revealed some more consequences of ticket #2043.

Here are some examples of tables whose behaviour is affected:

For two table I wrote an insert_layout:

  • lino_welfare.modlib.jobs.Jobs (which confirmed that the API change was a good idea because until now it was really ugly when you inserted a new job)

  • lino_xl.lib.humanlinks.Links (where it was important to add it because LinksByHuman needs an insert_action for the part “Create relationship as Father/Son …”)

How to analyze performance issues of a query

Tonis and I opened #2044.

I wrote a function show_sql_queries and a document which uses it in order to show why Jane is so slow: Exploring SQL activity in Lino Noi.

Ideas for optimizing:

  • it seems to me that the slave summary panels are being returned as well.

  • have users not inherit from person

Sencha Acquired by IDERA, Inc.

I read the news that Sencha Acquired by IDERA, Inc.. And Arthur Kay’s comment A Fond Farewell to Sencha.

I changed the summary of #148 to “Dialog with ExtJS/Sencha/Idera”.

Optimizing invoices entry in Lino Tera

Meeting with Lydia (#2039). We tested the new MakeCopy action. Lydia was amazed. But after about three invoices we had a problem:

IntegrityError: duplicate key value violates unique constraint "ledger_voucher_pkey"
DETAIL:  Key (id)=(33) already exists.

And it is not just MakeCopy. There is no way to create any voucher at all.

I guess that this is related to our recent move from MySQL to PostgreSQL. Here are some related links:

Best quote from one of above:

It appears to be a known difference of behaviour between the MySQL and SQLite (they update the next available primary key even when inserting an object with an explicit id) backends, and other backends like Postgres, Oracle, … (they do not).

So it seems that I have been creating objects with an explicit id somewhere. My problem is that AFAICS I do not manually specify the id of the voucher…

… but wait, now I see what happened: restore.py does explicitly specify the id of every row! And PostgreSQL does not update the sequence counter automatically in that case. The voucher ids until 33 were unused, that’s why Lydia had no problems so far. And just coincidentally we reached number 33 when we were testing the new MakeCopy action.

The following did not update the sequence:

from lino.api.shell import *
ledger.Voucher.objects.first().save(force_update=True)

Some research:

$ python manage.py sqlsequencereset ledger
BEGIN;
SELECT setval(pg_get_serial_sequence('"ledger_journal"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "ledger_journal";
SELECT setval(pg_get_serial_sequence('"ledger_accountingperiod"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "ledger_accountingperiod";
SELECT setval(pg_get_serial_sequence('"ledger_paymentterm"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "ledger_paymentterm";
SELECT setval(pg_get_serial_sequence('"ledger_voucher"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "ledger_voucher";
SELECT setval(pg_get_serial_sequence('"ledger_movement"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "ledger_movement";
SELECT setval(pg_get_serial_sequence('"ledger_matchrule"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "ledger_matchrule";
COMMIT;

Here is how I checked the current value without changing it:

# select pg_get_serial_sequence('ledger_voucher','id');
    pg_get_serial_sequence
------------------------------
 public.accounting_voucher_id_seq
(1 row)

# select * from ledger_voucher_id_seq;
     sequence_name     | last_value | start_value | increment_by |      max_value      | min_value | cache_value | log_cnt | is_cycled | is_called
-----------------------+------------+-------------+--------------+---------------------+-----------+-------------+---------+-----------+-----------
 ledger_voucher_id_seq |         36 |           1 |            1 | 9223372036854775807 |         1 |           1 |      32 | f         | t
(1 row)

The only solution seems to run Django’s sqlsequencereset command after a restore.py. One problem here is that I want to reset all sequences, and the sqlsequencereset command requires us to specify all plugins as arguments.

So I created a variant which is more admin-friendly, the resetsequences command.

Release to their production site.

Miscellaneous : there was another minor bug causing an “AssertionError: None is not a date” when creating a sales invoice on a partner without payment term.