github.com/authzed/spicedb@v1.32.1-0.20240520085336-ebda56537386/internal/datastore/postgres/migrations/zz_migration.0003_add_unique_living_ns.go (about)

     1  package migrations
     2  
     3  import (
     4  	"context"
     5  
     6  	"github.com/jackc/pgx/v5"
     7  )
     8  
     9  const createUniqueLivingNamespaceConstraint = `
    10  	ALTER TABLE namespace_config
    11  	ADD CONSTRAINT uq_namespace_living UNIQUE (namespace, deleted_transaction);
    12  `
    13  
    14  const deleteAllButNewestNamespace = `
    15  	DELETE FROM namespace_config WHERE namespace IN (
    16  		SELECT namespace FROM namespace_config WHERE deleted_transaction = 9223372036854775807 GROUP BY namespace HAVING COUNT(created_transaction) > 1
    17  	) AND (namespace, created_transaction) NOT IN (
    18  		SELECT namespace, max(created_transaction) from namespace_config where deleted_transaction = 9223372036854775807 GROUP BY namespace HAVING COUNT(created_transaction) > 1);`
    19  
    20  func init() {
    21  	if err := DatabaseMigrations.Register("add-unique-living-ns", "add-reverse-index", noNonatomicMigration, func(ctx context.Context, tx pgx.Tx) error {
    22  		if _, err := tx.Exec(ctx, deleteAllButNewestNamespace); err != nil {
    23  			return err
    24  		}
    25  
    26  		if _, err := tx.Exec(ctx, createUniqueLivingNamespaceConstraint); err != nil {
    27  			return err
    28  		}
    29  		return nil
    30  	}); err != nil {
    31  		panic("failed to register migration: " + err.Error())
    32  	}
    33  }