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

     1  package migrations
     2  
     3  import (
     4  	"context"
     5  	"fmt"
     6  
     7  	"github.com/jackc/pgx/v5"
     8  )
     9  
    10  const (
    11  	getNSConfigPkeyName = `
    12  	SELECT constraint_name FROM information_schema.table_constraints
    13  		WHERE table_schema = current_schema()
    14        	AND table_name = 'namespace_config'
    15        	AND constraint_type = 'PRIMARY KEY';`
    16  
    17  	getNSConfigPkeyNameAlternate = `
    18  	SELECT relname from pg_index, pg_class
    19  		WHERE pg_index.indisprimary
    20  		AND pg_index.indrelid = 'namespace_config'::regclass
    21  		AND pg_class.oid = pg_index.indexrelid;`
    22  
    23  	defaultNSConfigPkeyName = "namespace_config_pkey"
    24  
    25  	dropNSConfigIDPkey = "ALTER TABLE namespace_config DROP CONSTRAINT %s;"
    26  )
    27  
    28  var addXIDConstraints = []string{
    29  	`ALTER TABLE relation_tuple_transaction ALTER COLUMN snapshot SET NOT NULL;`,
    30  	`ALTER TABLE relation_tuple ALTER COLUMN created_xid SET NOT NULL;`,
    31  	`ALTER TABLE namespace_config ALTER COLUMN created_xid SET NOT NULL;`,
    32  	`ALTER TABLE caveat ALTER COLUMN created_xid SET NOT NULL;`,
    33  	`ALTER TABLE relation_tuple_transaction
    34  		DROP CONSTRAINT pk_rttx,
    35  		ADD CONSTRAINT pk_rttx PRIMARY KEY USING INDEX ix_rttx_pk;`,
    36  	`ALTER TABLE namespace_config
    37  		ADD CONSTRAINT pk_namespace_config PRIMARY KEY USING INDEX ix_namespace_config_pk,
    38  		ADD CONSTRAINT uq_namespace_living_xid UNIQUE USING INDEX ix_namespace_config_living;`,
    39  	`ALTER TABLE relation_tuple
    40  		DROP CONSTRAINT pk_relation_tuple,
    41  		ADD CONSTRAINT pk_relation_tuple PRIMARY KEY USING INDEX ix_relation_tuple_pk,
    42  		ADD CONSTRAINT uq_relation_tuple_living_xid UNIQUE USING INDEX ix_relation_tuple_living;`,
    43  	`ALTER TABLE caveat
    44  		DROP CONSTRAINT pk_caveat_v1,
    45  		ADD CONSTRAINT pk_caveat_v2 PRIMARY KEY USING INDEX ix_caveat_living,
    46  		ADD CONSTRAINT uq_caveat_v2 UNIQUE USING INDEX ix_caveat_unique;`,
    47  }
    48  
    49  func init() {
    50  	if err := DatabaseMigrations.Register("add-xid-constraints", "backfill-xid-add-indices",
    51  		noNonatomicMigration,
    52  		func(ctx context.Context, tx pgx.Tx) error {
    53  			var constraintName string
    54  			if err := tx.QueryRow(ctx, getNSConfigPkeyName).Scan(&constraintName); err != nil {
    55  				// Try the backup query
    56  				if err := tx.QueryRow(ctx, getNSConfigPkeyNameAlternate).Scan(&constraintName); err != nil {
    57  					// Just use the default, if it's wrong we will fail to drop it below
    58  					constraintName = defaultNSConfigPkeyName
    59  				}
    60  			}
    61  
    62  			if _, err := tx.Exec(ctx, fmt.Sprintf(dropNSConfigIDPkey, constraintName)); err != nil {
    63  				return err
    64  			}
    65  
    66  			for _, stmt := range addXIDConstraints {
    67  				if _, err := tx.Exec(ctx, stmt); err != nil {
    68  					return err
    69  				}
    70  			}
    71  
    72  			return nil
    73  		}); err != nil {
    74  		panic("failed to register migration: " + err.Error())
    75  	}
    76  }