Give priority to new resources when pivoting

[mmzeeman]

It can take a long time to complete a reindex operation. On our live server it can easily take 6 to 7 hours.

When during that time new resources are added, they are appended to the pivot queue. This means that they are indexed after the whole reindex operation finishes.

I would like to give priority to newly added resources so they can be indexed earlier, instead of 6 or 7 hours later.

[mworrell]

Yes, makes sense.

The current SQL query is:

select rsc_id, serial
from rsc_pivot_queue where due < current_timestamp - '10 second'::interval
order by is_update, due
limit $1

Here we use the is_update flag to set the priority, in this case to prioritize insert statements to have a quicker pivoting.

This flag is set in the pivot trigger:

    CREATE FUNCTION rsc_pivot_update() RETURNS trigger AS $$
    declare
        duetime timestamp;
        do_queue boolean;
    begin
        if (tg_op = 'INSERT') then
            do_queue := true;
        elseif (new.version <> old.version or new.modified <> old.modified) then
            do_queue := true;
        else
            do_queue := false;
        end if;

        if (do_queue) then
            <<insert_update_queue>>
            loop
                update rsc_pivot_queue
                set due = (case when now() < due then now() else due end),
                    serial = serial + 1
                where rsc_id = new.id;

                exit insert_update_queue when found;

                begin
                    insert into rsc_pivot_queue (rsc_id, due, is_update) values (new.id, now(), tg_op = 'UPDATE');
                    exit insert_update_queue;
                exception
                    when unique_violation then
                        -- do nothing
                end;
            end loop insert_update_queue;
        end if;

        if (new.is_protected) then
            begin
                insert into protect (id) values (new.id);
            exception
                when unique_violation then
                    -- do nothing
            end;
        else
            delete from protect where id = new.id;
        end if;
        return null;
    end;
    $$ LANGUAGE plpgsql

What we could do is:

• Add a priority field, default 1
• Add an index on (is_update, priority, due)
• Always set a due and change the field to not null

Then we can have the re-pivot routines insert entries with lower priority (ie. higher number).
(The z_pivot_rsc code always inserts queue entries with is_update set to true)