Closes: #7845 - Direct relationship between IP objects and Prefixes

[DanSheps]

Fixes: #7845 - Direct relationship between IP objects and Prefixes

• Add FK from IPAddress to Prefix
• Add FK from IPRange to Prefix
• Add FK from Prefix to (Parent) Prefix
• Add FK from Prefix to Aggregate
• Adjusted Search Indicies
• Adjusted Filtersets
  • Added filter for Prefix to IPAddress and IPRange Filterset
  • Added filter for parent and Aggregate to Prefix Filterset

[jeremystretch]

@DanSheps have you explored how PostgreSQL triggers might be employed to achieve automatic reassignment of parents at the database level? IMO relying on application level signal handlers seems like it might introduce performance issues, especially when dealing with very large hierarchies.

[DanSheps]

Nope, but I can take a look

[github-actions[bot]]

This PR has been automatically marked as stale because it has not had recent activity. It will be closed automatically if no further action is taken.

[DanSheps]

Just to provide an update. I have been looking into the trigger route. It is taking a lot longer then expected as the triggers are rather complex and I want to ensure we craft them correctly.

Example (AI created but seems to work, will require more extensive testing):

    -- First, handle children that may fall out of scope
    -- These are children that were under OLD but shouldn't be under NEW
    UPDATE prefix
    SET parent_id = (
        -- Find the new best parent for this prefix
        SELECT p.id
        FROM prefix p
        WHERE 
            -- p must contain this prefix
            p.prefix >> prefix.prefix
            
            -- VRF matching
            AND (
                (p.vrf_id = prefix.vrf_id OR (p.vrf_id IS NULL AND prefix.vrf_id IS NULL))
                OR
                (p.vrf_id IS NULL AND p.role = 'container')
            )
            
            -- Not the NEW prefix itself
            AND p.id != NEW.id
            
            -- Get the most specific one (no more specific prefix exists)
            AND NOT EXISTS (
                SELECT 1
                FROM prefix p2
                WHERE 
                    p2.prefix >> prefix.prefix
                    AND p2.prefix << p.prefix
                    AND (
                        (p2.vrf_id = prefix.vrf_id OR (p2.vrf_id IS NULL AND prefix.vrf_id IS NULL))
                        OR
                        (p2.vrf_id IS NULL AND p2.role = 'container')
                    )
                    AND p2.id != NEW.id
            )
        ORDER BY masklen(p.prefix) DESC
        LIMIT 1
    )
    WHERE 
        parent_id = NEW.id
        AND id != NEW.id
        
        -- Child no longer qualifies under NEW prefix
        AND NOT (
            -- Still contained in NEW prefix
            prefix << NEW.prefix
            
            -- AND VRF still matches
            AND (
                (vrf_id = NEW.vrf_id OR (vrf_id IS NULL AND NEW.vrf_id IS NULL))
                OR
                (NEW.vrf_id IS NULL AND NEW.role = 'container')
            )
        );
    
    -- Second, update prefixes that should now have NEW as their parent
    UPDATE prefix
    SET parent_id = NEW.id
    WHERE 
        -- The existing prefix must be more specific (contained in) the new prefix
        prefix << NEW.prefix
        
        -- VRF matching logic
        AND (
            (vrf_id = NEW.vrf_id OR (vrf_id IS NULL AND NEW.vrf_id IS NULL))
            OR
            (NEW.vrf_id IS NULL AND NEW.role = 'container')
        )
        
        -- Exclude the new prefix itself
        AND id != NEW.id
        
        -- Only update if there's no more specific prefix between them
        AND NOT EXISTS (
            SELECT 1
            FROM prefix p
            WHERE 
                p.prefix >> prefix.prefix
                AND p.prefix << NEW.prefix
                
                AND (
                    (p.vrf_id = prefix.vrf_id OR (p.vrf_id IS NULL AND prefix.vrf_id IS NULL))
                    OR
                    (p.vrf_id IS NULL AND p.role = 'container')
                )
                
                AND p.id != NEW.id
        );
    
    RETURN NEW;

There is definitely a performance impact with bulk_update vs triggers (Altering 65535 prefixes (Entire 192.168.0.0/16 of /32's):

Bulk Create: ~30 seconds for 65535 prefixes Bulk Update when inserting a /17: ~75 seconds Bulk Update when resizing to a /18: ~82 seconds Bulk Delete (/16): ~101 seconds

I think some of these might be inflated due to some memory thrashing, but a bulk update (UPDATE ipam_prefix SET parent=x WHERE y) in raw SQL takes ~3 seconds.

[elipsion]

(p.vrf_id = prefix.vrf_id OR (p.vrf_id IS NULL AND prefix.vrf_id IS NULL))

I don't know if the query planner catches this, but you could simplify it to

p.vrf_id IS NOT DISTINCT FROM prefix.vrf_id

[DanSheps]

(p.vrf_id = prefix.vrf_id OR (p.vrf_id IS NULL AND prefix.vrf_id IS NULL))

I don't know if the query planner catches this, but you could simplify it to

p.vrf_id IS NOT DISTINCT FROM prefix.vrf_id

Thanks, I will have to double check that that gets me what we need.

This was a very quick and dirty "what does AI say to do here" without much thought. I have further refined it in some spots but I don't know enough about the IS NOT DISTINCT get me further.