• Jérome Perrin's avatar
    core: repair ERP5Site_reindexLatestIndexedObjects · e83e7960
    Jérome Perrin authored
    This script stopped working since Catalog changed to be ERP5 document,
    because it was calling the unindex method of the catalog (which
    itself is indexable like any other ERP5 document). Update to use the
    uncatalogObject which is the method to unindex a document by uid.
    
    Even though it was somehow working before, it was not really correct
    in selecting objects, because it was using - operator on TIMESTAMP
    column, which is not computing a difference in seconds as this script
    was expecting. See for example https://stackoverflow.com/a/24504132/7607763
    or the example below for an explanation. Instead, use TIMESTAMPADD
    to compute the start timestamp only once and use >= operator, which
    works as expected.
    
    This query was also sorting by indexation_timestamp, which does not
    use an index. Remove the sort because it's not really needed.
    
    Excluding reserved path was also not needed, we no longer use these
    since 69aefdff (ZSQLCatalog: Drop support for "reserved" path.,
    2017-09-18)
    
    ---
    
    Another reproduction of the timestamp arithmetic problem
    
    select
       TIMESTAMP('2021-01-02 00:00:00') - TIMESTAMP('2021-01-01 00:00:00') a,
       20210102000000 - 20210101000000 aa,
       TIMESTAMP('2021-06-01 00:00:00') - TIMESTAMP('2021-05-31 00:00:00') b,
       20210601000000 - 20210531000000 bb,
       TIMESTAMPDIFF(second, TIMESTAMP('2021-05-31 00:00:00'), TIMESTAMP('2021-06-01 00:00:00')) c
    
    | a | aa | b | bb | c |
    | ------ | ------ | ------ | ------ | ------ |
    | 1000000 | 1000000 | 70000000 | 70000000 | 86400 |
    e83e7960
ERP5Site_zGetLatestIndexedObjectList.sql 140 Bytes