// Fills Vulnerability.LayersIntroducingVulnerability. // limit -1: won't do anything // limit 0: will just get the startID of the second page func (pgSQL *pgSQL) loadLayerIntroducingVulnerability(vulnerability *database.Vulnerability, limit, startID int) (int, error) { tf := time.Now() if vulnerability == nil { return -1, nil } // A startID equals to -1 means that we reached the end already. if startID == -1 || limit == -1 { return -1, nil } // We do `defer observeQueryTime` here because we don't want to observe invalid calls. defer observeQueryTime("loadLayerIntroducingVulnerability", "all", tf) // Query with limit + 1, the last item will be used to know the next starting ID. rows, err := pgSQL.Query(searchNotificationLayerIntroducingVulnerability, vulnerability.ID, startID, limit+1) if err != nil { return 0, handleError("searchNotificationLayerIntroducingVulnerability", err) } defer rows.Close() var layers []database.Layer for rows.Next() { var layer database.Layer if err := rows.Scan(&layer.ID, &layer.Name); err != nil { return -1, handleError("searchNotificationLayerIntroducingVulnerability.Scan()", err) } layers = append(layers, layer) } if err = rows.Err(); err != nil { return -1, handleError("searchNotificationLayerIntroducingVulnerability.Rows()", err) } size := limit if len(layers) < limit { size = len(layers) } vulnerability.LayersIntroducingVulnerability = layers[:size] nextID := -1 if len(layers) > limit { nextID = layers[limit].ID } return nextID, nil }
// Fills Vulnerability.LayersIntroducingVulnerability. // limit -1: won't do anything // limit 0: will just get the startID of the second page func (pgSQL *pgSQL) loadLayerIntroducingVulnerability(vulnerability *database.Vulnerability, limit, startID int) (int, error) { tf := time.Now() if vulnerability == nil { return -1, nil } // A startID equals to -1 means that we reached the end already. if startID == -1 || limit == -1 { return -1, nil } // Create a transaction to disable hash joins as our experience shows that // PostgreSQL plans in certain cases a sequential scan and a hash on // Layer_diff_FeatureVersion for the condition `ldfv.layer_id >= $2 AND // ldfv.modification = 'add'` before realizing a hash inner join with // Vulnerability_Affects_FeatureVersion. By disabling explictly hash joins, // we force PostgreSQL to perform a bitmap index scan with // `ldfv.featureversion_id = fv.id` on Layer_diff_FeatureVersion, followed by // a bitmap heap scan on `ldfv.layer_id >= $2 AND ldfv.modification = 'add'`, // thus avoiding a sequential scan on the biggest database table and // allowing a small nested loop join instead. tx, err := pgSQL.Begin() if err != nil { return -1, handleError("searchNotificationLayerIntroducingVulnerability.Begin()", err) } defer tx.Commit() _, err = tx.Exec(disableHashJoin) if err != nil { log.Warningf("searchNotificationLayerIntroducingVulnerability: could not disable hash join: %s", err) } // We do `defer observeQueryTime` here because we don't want to observe invalid calls. defer observeQueryTime("loadLayerIntroducingVulnerability", "all", tf) // Query with limit + 1, the last item will be used to know the next starting ID. rows, err := tx.Query(searchNotificationLayerIntroducingVulnerability, vulnerability.ID, startID, limit+1) if err != nil { return 0, handleError("searchNotificationLayerIntroducingVulnerability", err) } defer rows.Close() var layers []database.Layer for rows.Next() { var layer database.Layer if err := rows.Scan(&layer.ID, &layer.Name); err != nil { return -1, handleError("searchNotificationLayerIntroducingVulnerability.Scan()", err) } layers = append(layers, layer) } if err = rows.Err(); err != nil { return -1, handleError("searchNotificationLayerIntroducingVulnerability.Rows()", err) } size := limit if len(layers) < limit { size = len(layers) } vulnerability.LayersIntroducingVulnerability = layers[:size] nextID := -1 if len(layers) > limit { nextID = layers[limit].ID } return nextID, nil }