func (pgSQL *pgSQL) FindLayer(name string, withFeatures, withVulnerabilities bool) (database.Layer, error) { subquery := "all" if withFeatures { subquery += "/features" } else if withVulnerabilities { subquery += "/features+vulnerabilities" } defer observeQueryTime("FindLayer", subquery, time.Now()) // Find the layer var layer database.Layer var parentID zero.Int var parentName zero.String var namespaceID zero.Int var namespaceName sql.NullString t := time.Now() err := pgSQL.QueryRow(searchLayer, name). Scan(&layer.ID, &layer.Name, &layer.EngineVersion, &parentID, &parentName, &namespaceID, &namespaceName) observeQueryTime("FindLayer", "searchLayer", t) if err != nil { return layer, handleError("searchLayer", err) } if !parentID.IsZero() { layer.Parent = &database.Layer{ Model: database.Model{ID: int(parentID.Int64)}, Name: parentName.String, } } if !namespaceID.IsZero() { layer.Namespace = &database.Namespace{ Model: database.Model{ID: int(namespaceID.Int64)}, Name: namespaceName.String, } } // Find its features if withFeatures || withVulnerabilities { // Create a transaction to disable hash/merge joins as our experiments have shown that // PostgreSQL 9.4 makes bad planning decisions about: // - joining the layer tree to feature versions and feature // - joining the feature versions to affected/fixed feature version and vulnerabilities // It would for instance do a merge join between affected feature versions (300 rows, estimated // 3000 rows) and fixed in feature version (100k rows). In this case, it is much more // preferred to use a nested loop. tx, err := pgSQL.Begin() if err != nil { return layer, handleError("FindLayer.Begin()", err) } defer tx.Commit() _, err = tx.Exec(disableHashJoin) if err != nil { log.Warningf("FindLayer: could not disable hash join: %s", err) } _, err = tx.Exec(disableMergeJoin) if err != nil { log.Warningf("FindLayer: could not disable merge join: %s", err) } t = time.Now() featureVersions, err := getLayerFeatureVersions(tx, layer.ID) observeQueryTime("FindLayer", "getLayerFeatureVersions", t) if err != nil { return layer, err } layer.Features = featureVersions if withVulnerabilities { // Load the vulnerabilities that affect the FeatureVersions. t = time.Now() err := loadAffectedBy(tx, layer.Features) observeQueryTime("FindLayer", "loadAffectedBy", t) if err != nil { return layer, err } } } return layer, nil }
func scanVulnerability(queryer Queryer, queryName string, vulnerabilityRow *sql.Row) (database.Vulnerability, error) { var vulnerability database.Vulnerability err := vulnerabilityRow.Scan( &vulnerability.ID, &vulnerability.Name, &vulnerability.Namespace.ID, &vulnerability.Namespace.Name, &vulnerability.Description, &vulnerability.Link, &vulnerability.Severity, &vulnerability.Metadata, ) if err != nil { return vulnerability, handleError(queryName+".Scan()", err) } if vulnerability.ID == 0 { return vulnerability, cerrors.ErrNotFound } // Query the FixedIn FeatureVersion now. rows, err := queryer.Query(searchVulnerabilityFixedIn, vulnerability.ID) if err != nil { return vulnerability, handleError("searchVulnerabilityFixedIn.Scan()", err) } defer rows.Close() for rows.Next() { var featureVersionID zero.Int var featureVersionVersion zero.String var featureVersionFeatureName zero.String err := rows.Scan( &featureVersionVersion, &featureVersionID, &featureVersionFeatureName, ) if err != nil { return vulnerability, handleError("searchVulnerabilityFixedIn.Scan()", err) } if !featureVersionID.IsZero() { // Note that the ID we fill in featureVersion is actually a Feature ID, and not // a FeatureVersion ID. featureVersion := database.FeatureVersion{ Model: database.Model{ID: int(featureVersionID.Int64)}, Feature: database.Feature{ Model: database.Model{ID: int(featureVersionID.Int64)}, Namespace: vulnerability.Namespace, Name: featureVersionFeatureName.String, }, Version: types.NewVersionUnsafe(featureVersionVersion.String), } vulnerability.FixedIn = append(vulnerability.FixedIn, featureVersion) } } if err := rows.Err(); err != nil { return vulnerability, handleError("searchVulnerabilityFixedIn.Rows()", err) } return vulnerability, nil }