Convertible Aggregations Are Not Atomic Enough

[AardWolf]

Right now three tables are being built. We saw a new convertible show up between table 1 and table 2. In theory it can happen again with table 3 (which we're not using for anything yet).

Either the building of the tables needs to become atomic or we can skip building table 1 and inline it into the building of table 2.

[AardWolf]

CREATE PROCEDURE aggregate_convertibles()
ac: BEGIN

    drop table if exists aggr_convertibles_shadow;
    drop table if exists aggr_convertible_item_shadow;
    drop table if exists aggr_convertible_item_quantity_shadow;
    drop table if exists aggr_convertibles_old;
    drop table if exists aggr_convertible_item_old;
    drop table if exists aggr_convertible_item_quantity_old;
    create table aggr_convertibles_shadow like aggr_convertibles;
    create table aggr_convertible_item_shadow like aggr_convertible_item;
    create table aggr_convertible_item_quantity_shadow like aggr_convertible_item_quantity;

    insert into aggr_convertibles_shadow(convertible_id, total_convertibles_opened, single_convertibles_opened)
    select convertible_id, sum(convertible_quantity) total_convertibles_opened, 
        sum(case when convertible_quantity = 1 then 1 else 0 end) as single_convertibles_opened
    from (
        select distinct ci.entry_id, ci.convertible_id, ci.convertible_quantity 
        from convertible_item ci
    ) as ci
    group by convertible_id
    ;

    insert into aggr_convertible_item_shadow (convertible_id, item_id, total_convertibles_opened, single_convertibles_opened, total_item_quantity,
            min_item_quantity, max_item_quantity, total_quantity_when_any, times_with_any)
    select ci.convertible_id , ci.item_id , ac.total_convertibles_opened , ac.single_convertibles_opened ,
        sum(ci.item_quantity) as total_item_quantity, min(case when ci.convertible_quantity = 1 then ci.item_quantity else null end) as min_item_quantity,
        max(case when ci.convertible_quantity = 1 then ci.item_quantity else null end) as max_item_quantity,
        sum(case when ci.convertible_quantity = 1 then ci.item_quantity else null end) as total_quantity_when_any,
        sum(case when ci.convertible_quantity = 1 then 1 else 0 end) as times_with_any
    from convertible_item ci 
        inner join (select convertible_id, sum(convertible_quantity) total_convertibles_opened, 
                        sum(case when convertible_quantity = 1 then 1 else 0 end) as single_convertibles_opened
                    from (
                        select distinct ci.entry_id, ci.convertible_id, ci.convertible_quantity 
                        from convertible_item ci
                    ) as ci
                    group by convertible_id) ac 
            on ci.convertible_id = ac.convertible_id 
    -- where ci.convertible_id = 3010
    group by ci.convertible_id , ci.item_id , ac.total_convertibles_opened , ac.single_convertibles_opened 
    ;

    insert into aggr_convertible_item_quantity_shadow
    select ci.convertible_id , ci.item_id , aci.times_with_any, ci.item_quantity, count(*) as times
    from convertible_item ci 
        inner join aggr_convertible_item aci 
            on ci.convertible_id = aci.convertible_id 
            and ci.item_id = aci.item_id 
    where ci.convertible_quantity = 1
    group by ci.convertible_id, ci.item_id , aci.times_with_any, ci.item_quantity 
    ;

    start transaction;
    rename table aggr_convertibles to aggr_convertibles_old;
    rename table aggr_convertibles_shadow to aggr_convertibles;
    rename table aggr_convertible_item to aggr_convertible_item_old;
    rename table aggr_convertible_item_shadow to aggr_convertible_item;
    rename table aggr_convertible_item_quantity to aggr_convertible_item_quantity_old;
    rename table aggr_convertible_item_quantity_shadow to aggr_convertible_item_quantity;
    commit;
    drop table if exists aggr_convertibles_old;
    drop table if exists aggr_convertible_item_old;
    drop table if exists aggr_convertible_item_quantity_old;

END;

Just deployed to address this. aggr_convertible can be dropped but that was not tested.

[AardWolf]

This has been addressed and will continue to be addressed as CPU usage is adjusted.