gis Database

Parameters

Name	Type	Mode
ids	ARRAY	OUT
min_event_id	bigint	OUT
max_event_id	bigint	OUT
max_action_tstamp_tx	timestamp with time zone	OUT

Definition

DECLARE
    sqltemplate text;
    central_count integer;
    p_ids bigint[];
    p_min_event_id bigint;
    p_max_event_id bigint;
    p_max_action_tstamp_tx timestamp with time zone;
    central_ids_to_keep integer[];
    clone_ids_to_keep integer[];
BEGIN

-- Store all central ids before the analyse and removal of rejected logs
    -- Also store min and max event id
    -- Not needed if there is nothing in the central log
    -- If so, we return NULL to let the function replay_central_logs_to_clone return (do nothing)
    SELECT INTO central_count
    count(*) FROM temp_central_audit
    ;
    RAISE NOTICE 'Central modifications count since last sync: %', central_count;
    IF central_count > 0 THEN
        SELECT INTO p_ids, p_min_event_id, p_max_event_id, p_max_action_tstamp_tx
        array_agg(DISTINCT event_id), min(event_id), max(event_id), max(action_tstamp_tx)
        FROM temp_central_audit
        ;
    ELSE
        SELECT INTO p_ids, p_min_event_id, p_max_event_id, p_max_action_tstamp_tx
        NULL, NULL, NULL, NULL
        FROM temp_central_audit
        ;
    END IF;

-- Get the list of ids to keep from each log
    -- We use DISTINCT ON to remove consecutive UPDATE on the same ident, uid and field for each source.
    -- Last is kept
    -- central
    IF central_count > 0 THEN
        WITH
        ce AS (
            SELECT
            DISTINCT ON (ident, action_type, updated_field, uid)
            tid
            FROM temp_central_audit
            ORDER BY ident, action_type, updated_field, uid, event_id DESC
        )
        SELECT array_agg(tid)
        FROM ce
        INTO central_ids_to_keep
        ;
    END IF;

-- clone
    WITH
    cl AS (
        SELECT
        DISTINCT ON (ident, action_type, updated_field, uid)
        tid
        FROM temp_clone_audit
        ORDER BY ident, action_type, updated_field, uid, event_id DESC
    )
    SELECT array_agg(tid)
    FROM cl
    INTO clone_ids_to_keep
    ;

-- DELETE removed lines from temp audit tables
    IF central_count > 0 THEN
        DELETE FROM temp_central_audit
        WHERE tid != ALL (central_ids_to_keep)
        ;
    END IF;

DELETE FROM temp_clone_audit
    WHERE tid != ALL (clone_ids_to_keep)
    ;

-- Compare logs
    -- And get conflicts
    -- Last modified is kept, older is rejected
    INSERT INTO temp_conflicts
    (
        conflict_time,
        object_table, object_uid,
        central_tid, clone_tid,
        central_event_id, central_event_timestamp,
        central_sql, clone_sql,
        rejected,
        rule_applied
    )
    SELECT
        now(),
        ce.ident, ce.uid::uuid,
        ce.tid AS cetid, cl.tid AS cltid,
        ce.event_id, ce.action_tstamp_tx,
        ce.action AS ceaction, cl.action AS claction,
        -- last modified wins
        CASE
            WHEN cl.original_action_tstamp_tx <= ce.original_action_tstamp_tx THEN 'clone'
            ELSE 'central'
        END AS rejected,
        'last_modified' AS rule_applied
    FROM temp_central_audit AS ce
    INNER JOIN temp_clone_audit AS cl
        ON ce.ident = cl.ident
        AND ce.uid = cl.uid
        AND ce.action_type = 'U'
        AND cl.action_type = 'U'
        AND ce.updated_field = cl.updated_field
    ORDER BY ce.event_id
    ;

-- DELETE rejected tid from audit temp tables
    -- central
    DELETE FROM temp_central_audit
    WHERE tid IN (
        SELECT central_tid
        FROM temp_conflicts
        WHERE rejected = 'central'
    )
    ;
    -- clone
    DELETE FROM temp_clone_audit
    WHERE tid IN (
        SELECT clone_tid
        FROM temp_conflicts
        WHERE rejected = 'clone'
    )
    ;

-- Return data
    RETURN QUERY
    SELECT p_ids, p_min_event_id, p_max_event_id, p_max_action_tstamp_tx;
END;

analyse_audit_logs

Description

Parameters

Definition