internal/postgres: set modules.status when updating module_version_states

Populate the new modules.status column whenever we insert or update
module_version_states.status, in the same transaction.

After this code is deployed, we should manually update NULL
modules.status values to match module_version_states.status.

This CL implements part of https://golang.org/cl/c/pkgsite/+/249447.

For golang/go#40807

Change-Id: I8623fd71207b6578ff03de09ee12d292b36465b5
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/253080
Reviewed-by: Julie Qiu <julie@golang.org>

1 files changed, 25 insertions, 0 deletions

diff --git a/internal/postgres/versionstate.go b/internal/postgres/versionstate.go
index 69178f7f..2b60f47a 100644
--- a/internal/postgres/versionstate.go
+++ b/internal/postgres/versionstate.go
@@ -61,6 +61,10 @@ func (db *DB) UpsertModuleVersionState(ctx context.Context, modulePath, vers, ap
 		if err := upsertModuleVersionState(ctx, tx, modulePath, vers, appVersion, numPackages, timestamp, status, goModPath, fetchErr); err != nil {
 			return err
 		}
+		// Sync modules.status if the module exists in the modules table.
+		if err := updateModulesStatus(ctx, tx, modulePath, vers, status); err != nil {
+			return err
+		}
 		if len(packageVersionStates) == 0 {
 			return nil
 		}
@@ -122,6 +126,27 @@ func upsertModuleVersionState(ctx context.Context, db *database.DB, modulePath,
 	return nil
 }
 
+// updateModulesStatus updates the status of the module with the given modulePath
+// and version, if it exists, in the modules table.
+func updateModulesStatus(ctx context.Context, db *database.DB, modulePath, version string, status int) (err error) {
+	defer derrors.Wrap(&err, "updateModulesStatus(%q, %q, %d)", modulePath, version, status)
+
+	query := `UPDATE modules
+			SET
+				status = $1
+			WHERE
+				module_path = $2
+				AND version = $3;`
+	affected, err := db.Exec(ctx, query, status, modulePath, version)
+	if err != nil {
+		return err
+	}
+	if affected > 1 {
+		return fmt.Errorf("module status update affected %d rows, expected at most 1", affected)
+	}
+	return nil
+}
+
 func upsertPackageVersionStates(ctx context.Context, db *database.DB, packageVersionStates []*internal.PackageVersionState) (err error) {
 	defer derrors.Wrap(&err, "upsertPackageVersionStates")
 	ctx, span := trace.StartSpan(ctx, "upsertPackageVersionStates")