Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package pg
import (
"fmt"
"io"
"slices"
"strconv"
"strings"
metadatapb "github.com/bytebase/omni/metadata"
"github.com/pkg/errors"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/plugin/parser/base"
"github.com/bytebase/bytebase/backend/plugin/schema"
"github.com/bytebase/bytebase/backend/store/model"
)
const (
pgTypeCharacterVarying = "character varying"
pgTypeDoublePrecision = "double precision"
viewCommentObjectFormat = "VIEW \"%s\".\"%s\""
)
func pgDiffMetadataMigration(oldSchema, newSchema *model.DatabaseMetadata) (string, error) {
return pgDiffMetadataMigrationForEngine(storepb.Engine_POSTGRES, oldSchema, newSchema)
}
func pgDiffCockroachMetadataMigration(oldSchema, newSchema *model.DatabaseMetadata) (string, error) {
return pgDiffMetadataMigrationForEngine(storepb.Engine_COCKROACHDB, oldSchema, newSchema)
}
func pgDiffMetadataMigrationForEngine(engine storepb.Engine, oldSchema, newSchema *model.DatabaseMetadata) (string, error) {
diff, err := schema.GetDatabaseSchemaDiff(engine, oldSchema, newSchema)
if err != nil {
return "", err
}
augmentMetadataDiffForMigration(diff, oldSchema, newSchema)
if metadataDiffEmpty(diff) {
return "", nil
}
return pgGenerateMetadataMigration(diff)
}
func metadataDiffEmpty(diff *schema.MetadataDiff) bool {
return diff == nil ||
len(diff.SchemaChanges) == 0 &&
len(diff.TableChanges) == 0 &&
len(diff.ViewChanges) == 0 &&
len(diff.MaterializedViewChanges) == 0 &&
len(diff.FunctionChanges) == 0 &&
len(diff.ProcedureChanges) == 0 &&
len(diff.SequenceChanges) == 0 &&
len(diff.EnumTypeChanges) == 0 &&
len(diff.CompositeTypeChanges) == 0 &&
len(diff.ExtensionChanges) == 0 &&
len(diff.EventTriggerChanges) == 0 &&
len(diff.EventChanges) == 0 &&
len(diff.CommentChanges) == 0
}
type affectedTableColumns struct {
columns map[string]bool
}
func augmentMetadataDiffForMigration(diff *schema.MetadataDiff, oldSchema, newSchema *model.DatabaseMetadata) {
if diff == nil || oldSchema == nil || newSchema == nil {
return
}
affectedTables := collectDependencySensitiveTableColumns(diff)
if len(affectedTables) == 0 {
return
}
addUnchangedDependentViews(diff, oldSchema, newSchema, affectedTables)
addUnchangedDependentMaterializedViews(diff, oldSchema, newSchema, affectedTables)
}
func collectDependencySensitiveTableColumns(diff *schema.MetadataDiff) map[string]affectedTableColumns {
affectedTables := make(map[string]affectedTableColumns)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action != schema.MetadataDiffActionAlter {
continue
}
for _, columnDiff := range tableDiff.ColumnChanges {
columnName := dependentObjectBlockingColumnName(columnDiff)
if columnName == "" {
continue
}
tableID := getMigrationObjectID(tableDiff.SchemaName, tableDiff.TableName)
affected := affectedTables[tableID]
if affected.columns == nil {
affected.columns = make(map[string]bool)
}
affected.columns[columnName] = true
affectedTables[tableID] = affected
}
}
return affectedTables
}
func dependentObjectBlockingColumnName(columnDiff *schema.ColumnDiff) string {
switch columnDiff.Action {
case schema.MetadataDiffActionDrop:
return columnDiff.OldColumn.GetName()
case schema.MetadataDiffActionAlter:
if columnDiff.OldColumn.GetType() != columnDiff.NewColumn.GetType() {
return columnDiff.OldColumn.GetName()
}
return ""
default:
return ""
}
}
func addUnchangedDependentViews(diff *schema.MetadataDiff, oldSchema, newSchema *model.DatabaseMetadata, affectedTables map[string]affectedTableColumns) {
changedViews := make(map[string]bool)
for _, viewDiff := range diff.ViewChanges {
changedViews[getMigrationObjectID(viewDiff.SchemaName, viewDiff.ViewName)] = true
}
for _, schemaMeta := range newSchema.GetProto().GetSchemas() {
oldSchemaMeta := oldSchema.GetSchemaMetadata(schemaMeta.GetName())
if oldSchemaMeta == nil {
continue
}
for _, newView := range schemaMeta.GetViews() {
viewID := getMigrationObjectID(schemaMeta.GetName(), newView.GetName())
if changedViews[viewID] || newView.GetSkipDump() {
continue
}
oldView := oldSchemaMeta.GetView(newView.GetName())
if oldView == nil || oldView.GetSkipDump() {
continue
}
if !dependencyColumnsReferenceAffectedTable(oldView.GetDependencyColumns(), affectedTables) && !dependencyColumnsReferenceAffectedTable(newView.GetDependencyColumns(), affectedTables) {
continue
}
diff.ViewChanges = append(diff.ViewChanges, &schema.ViewDiff{
Action: schema.MetadataDiffActionAlter,
SchemaName: schemaMeta.GetName(),
ViewName: newView.GetName(),
OldView: oldView,
NewView: newView,
})
changedViews[viewID] = true
}
}
}
func addUnchangedDependentMaterializedViews(diff *schema.MetadataDiff, oldSchema, newSchema *model.DatabaseMetadata, affectedTables map[string]affectedTableColumns) {
changedViews := make(map[string]bool)
for _, viewDiff := range diff.MaterializedViewChanges {
changedViews[getMigrationObjectID(viewDiff.SchemaName, viewDiff.MaterializedViewName)] = true
}
for _, schemaMeta := range newSchema.GetProto().GetSchemas() {
oldSchemaMeta := oldSchema.GetSchemaMetadata(schemaMeta.GetName())
if oldSchemaMeta == nil {
continue
}
for _, newView := range schemaMeta.GetMaterializedViews() {
viewID := getMigrationObjectID(schemaMeta.GetName(), newView.GetName())
if changedViews[viewID] || newView.GetSkipDump() {
continue
}
oldView := oldSchemaMeta.GetMaterializedView(newView.GetName())
if oldView == nil || oldView.GetSkipDump() {
continue
}
if !dependencyColumnsReferenceAffectedTable(oldView.GetDependencyColumns(), affectedTables) && !dependencyColumnsReferenceAffectedTable(newView.GetDependencyColumns(), affectedTables) {
continue
}
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &schema.MaterializedViewDiff{
Action: schema.MetadataDiffActionAlter,
SchemaName: schemaMeta.GetName(),
MaterializedViewName: newView.GetName(),
OldMaterializedView: oldView,
NewMaterializedView: newView,
})
changedViews[viewID] = true
}
}
}
func dependencyColumnsReferenceAffectedTable(dependencies []*metadatapb.DependencyColumn, affectedTables map[string]affectedTableColumns) bool {
for _, dep := range dependencies {
affected, ok := affectedTables[getMigrationObjectID(dep.GetSchema(), dep.GetTable())]
if !ok {
continue
}
if dep.GetColumn() == "" || affected.columns[dep.GetColumn()] {
return true
}
}
return false
}
func pgGenerateMetadataMigration(diff *schema.MetadataDiff) (string, error) {
if err := validateSupportedMetadataDiff(diff); err != nil {
return "", err
}
var buf strings.Builder
if err := writeDropPhase(&buf, diff); err != nil {
return "", err
}
if err := writeCreateOrAlterPhase(&buf, diff); err != nil {
return "", err
}
if err := writeDeferredDropPhase(&buf, diff); err != nil {
return "", err
}
return buf.String(), nil
}
// postViewCompositeCreates selects created composites whose attributes
// reference a created view or materialized view row type, expanded over
// composite-to-composite references, for emission after the views phase.
func postViewCompositeCreates(diff *schema.MetadataDiff) map[*schema.CompositeTypeDiff]bool {
createdViews := make(map[string]bool)
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate {
createdViews[viewDiff.SchemaName+"\x00"+viewDiff.ViewName] = true
}
}
for _, viewDiff := range diff.MaterializedViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate {
createdViews[viewDiff.SchemaName+"\x00"+viewDiff.MaterializedViewName] = true
}
}
post := make(map[*schema.CompositeTypeDiff]bool)
if len(createdViews) == 0 {
return post
}
postKeys := make(map[string]bool)
for changed := true; changed; {
changed = false
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action != schema.MetadataDiffActionCreate || post[compositeDiff] {
continue
}
for _, attribute := range compositeDiff.NewCompositeType.GetAttributes() {
depSchema, depName, ok := parseQualifiedTypeIdent(attribute.Type)
if !ok {
continue
}
depKey := depSchema + "\x00" + depName
if createdViews[depKey] || postKeys[depKey] {
post[compositeDiff] = true
postKeys[compositeDiff.SchemaName+"\x00"+compositeDiff.CompositeTypeName] = true
changed = true
break
}
}
}
}
return post
}
// postViewCompositeAlters selects altered composites whose added or retyped
// attributes reference a created view or materialized view row type; their
// alter statements run after the views phase.
func postViewCompositeAlters(diff *schema.MetadataDiff) map[*schema.CompositeTypeDiff]bool {
createdViews := make(map[string]bool)
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate {
createdViews[viewDiff.SchemaName+"\x00"+viewDiff.ViewName] = true
}
}
for _, viewDiff := range diff.MaterializedViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate {
createdViews[viewDiff.SchemaName+"\x00"+viewDiff.MaterializedViewName] = true
}
}
post := make(map[*schema.CompositeTypeDiff]bool)
if len(createdViews) == 0 {
return post
}
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action != schema.MetadataDiffActionAlter {
continue
}
oldAttributes := make(map[string]*metadatapb.CompositeTypeAttribute)
for _, attribute := range compositeDiff.OldCompositeType.GetAttributes() {
oldAttributes[attribute.Name] = attribute
}
for _, newAttribute := range compositeDiff.NewCompositeType.GetAttributes() {
oldAttribute := oldAttributes[newAttribute.Name]
if oldAttribute != nil && oldAttribute.Type == newAttribute.Type {
continue
}
if depSchema, depName, ok := parseQualifiedTypeIdent(newAttribute.Type); ok && createdViews[depSchema+"\x00"+depName] {
post[compositeDiff] = true
break
}
}
}
return post
}
// deferredDropSet resolves which drops must wait until the alter phase has
// released the last reference.
type deferredDropSet struct {
composites []*schema.CompositeTypeDiff
enums map[*schema.EnumTypeDiff]bool
schemas map[*schema.SchemaDiff]bool
}
// computeDeferredDrops seeds the set with composites released by a column
// retype and schemas owning such composites, then expands to a fixpoint: a
// dropped schema is also deferred when any pooled deferred composite
// references a type inside it, and every deferred schema contributes its own
// composites to the pool. Finally, dropped enums referenced by any pooled
// composite are deferred.
func computeDeferredDrops(diff *schema.MetadataDiff) *deferredDropSet {
deferred := &deferredDropSet{
enums: make(map[*schema.EnumTypeDiff]bool),
schemas: make(map[*schema.SchemaDiff]bool),
}
var topLevelDrops []*schema.CompositeTypeDiff
deferredComposites := make(map[*schema.CompositeTypeDiff]bool)
var pool []*metadatapb.CompositeTypeMetadata
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action != schema.MetadataDiffActionDrop {
continue
}
topLevelDrops = append(topLevelDrops, compositeDiff)
if compositeDropReleasedByRetype(diff, compositeDiff) {
deferredComposites[compositeDiff] = true
pool = append(pool, compositeDiff.OldCompositeType)
}
}
// Combined fixpoint: pooled composites pull in (a) top-level dropped
// composites they reference and (b) dropped schemas containing types
// they reference; every newly deferred schema contributes its own
// composites to the pool.
for changed := true; changed; {
changed = false
for _, candidate := range topLevelDrops {
if deferredComposites[candidate] {
continue
}
for _, member := range pool {
if compositeReferencesType(member, candidate.SchemaName, candidate.CompositeTypeName) {
deferredComposites[candidate] = true
pool = append(pool, candidate.OldCompositeType)
changed = true
break
}
}
}
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action != schema.MetadataDiffActionDrop || deferred.schemas[schemaDiff] {
continue
}
if !schemaOwnsRetypeReleasedComposite(diff, schemaDiff) && !compositePoolReferencesSchema(pool, schemaDiff.SchemaName) {
continue
}
deferred.schemas[schemaDiff] = true
for _, compositeType := range schemaDiff.OldSchema.GetCompositeTypes() {
if !compositeType.GetSkipDump() {
pool = append(pool, compositeType)
}
}
changed = true
}
}
for _, compositeDiff := range topLevelDrops {
if deferredComposites[compositeDiff] {
deferred.composites = append(deferred.composites, compositeDiff)
}
}
for _, enumDiff := range diff.EnumTypeChanges {
if enumDiff.Action != schema.MetadataDiffActionDrop {
continue
}
if typeReleasedByCompositeAlter(diff, enumDiff.SchemaName, enumDiff.EnumTypeName) {
deferred.enums[enumDiff] = true
continue
}
for _, compositeType := range pool {
if compositeReferencesType(compositeType, enumDiff.SchemaName, enumDiff.EnumTypeName) {
deferred.enums[enumDiff] = true
break
}
}
}
return deferred
}
// writeDeferredDropPhase emits the deferred drops after the alter phase.
// Type drops are globalized across schema boundaries so cross-schema (even
// cyclic) references between deferred schemas cannot produce an invalid
// order: first the deferred schemas' non-type objects, then every deferred
// composite (top-level and schema-owned) in one reverse-dependency pass,
// then every deferred enum — nothing references an enum by that point —
// and finally the bare DROP SCHEMA statements.
func writeDeferredDropPhase(out *strings.Builder, diff *schema.MetadataDiff) error {
deferred := computeDeferredDrops(diff)
deferredSchemas := make([]*schema.SchemaDiff, 0, len(deferred.schemas))
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionDrop && deferred.schemas[schemaDiff] {
deferredSchemas = append(deferredSchemas, schemaDiff)
}
}
slices.SortStableFunc(deferredSchemas, func(a, b *schema.SchemaDiff) int {
return strings.Compare(a.SchemaName, b.SchemaName)
})
for _, schemaDiff := range deferredSchemas {
if skipPostgresSchemaDDL(schemaDiff.SchemaName) {
continue
}
if err := writeDropSchemaNonTypeObjects(out, schemaDiff.SchemaName, schemaDiff.OldSchema); err != nil {
return err
}
}
compositeDrops := slices.Clone(deferred.composites)
for _, schemaDiff := range deferredSchemas {
if skipPostgresSchemaDDL(schemaDiff.SchemaName) {
continue
}
schemaObjects := buildDropSchemaObjectsDiff(schemaDiff.SchemaName, schemaDiff.OldSchema)
compositeDrops = append(compositeDrops, schemaObjects.CompositeTypeChanges...)
}
if err := writeCompositeTypeDropsInReverseDependencyOrder(out, compositeDrops); err != nil {
return err
}
for _, enumDiff := range diff.EnumTypeChanges {
if enumDiff.Action == schema.MetadataDiffActionDrop && deferred.enums[enumDiff] {
if err := writeEnumTypeDiff(out, enumDiff); err != nil {
return err
}
}
}
for _, schemaDiff := range deferredSchemas {
if skipPostgresSchemaDDL(schemaDiff.SchemaName) {
continue
}
schemaObjects := buildDropSchemaObjectsDiff(schemaDiff.SchemaName, schemaDiff.OldSchema)
for _, enumDiff := range schemaObjects.EnumTypeChanges {
if err := writeEnumTypeDiff(out, enumDiff); err != nil {
return err
}
}
}
for _, schemaDiff := range deferredSchemas {
if skipPostgresSchemaDDL(schemaDiff.SchemaName) {
continue
}
if _, err := fmt.Fprintf(out, "DROP SCHEMA IF EXISTS \"%s\";\n\n", schemaDiff.SchemaName); err != nil {
return err
}
}
return nil
}
// compositePoolReferencesSchema reports whether any composite in the pool has
// an attribute whose (always schema-qualified) type lives in the schema.
func compositePoolReferencesSchema(pool []*metadatapb.CompositeTypeMetadata, schemaName string) bool {
for _, compositeType := range pool {
for _, attribute := range compositeType.GetAttributes() {
if depSchema, _, ok := parseQualifiedTypeIdent(attribute.Type); ok && depSchema == schemaName {
return true
}
}
}
return false
}
// schemaOwnsRetypeReleasedComposite reports whether the dropped schema owns a
// composite that a column retype in the alter phase releases.
func schemaOwnsRetypeReleasedComposite(diff *schema.MetadataDiff, schemaDiff *schema.SchemaDiff) bool {
for _, compositeType := range schemaDiff.OldSchema.GetCompositeTypes() {
if compositeType.GetSkipDump() {
continue
}
probe := &schema.CompositeTypeDiff{
SchemaName: schemaDiff.SchemaName,
CompositeTypeName: compositeType.GetName(),
}
if compositeDropReleasedByRetype(diff, probe) {
return true
}
}
return false
}
// compositeReferencesType reports whether any attribute of the composite
// references the given type. Attribute types are stored schema-qualified for
// user-defined types; the bare fallback matches by name alone, which may
// defer a drop unnecessarily — a safe direction.
func compositeReferencesType(composite *metadatapb.CompositeTypeMetadata, typeSchema, typeName string) bool {
for _, attribute := range composite.GetAttributes() {
if typeStringReferencesComposite(attribute.Type, typeSchema, typeName) {
return true
}
}
return false
}
// partitionDroppedCompositeTypes splits composite type drops into those safe
// to run in the drop phase and those that must wait until after the alter
// phase because a column retype releases the last reference to the type.
// immediateCompositeDrops returns the top-level composite drops that run in
// the drop phase — everything not claimed by the deferred set.
func immediateCompositeDrops(diff *schema.MetadataDiff, deferred *deferredDropSet) []*schema.CompositeTypeDiff {
deferredSet := make(map[*schema.CompositeTypeDiff]bool, len(deferred.composites))
for _, compositeDiff := range deferred.composites {
deferredSet[compositeDiff] = true
}
var immediate []*schema.CompositeTypeDiff
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action == schema.MetadataDiffActionDrop && !deferredSet[compositeDiff] {
immediate = append(immediate, compositeDiff)
}
}
return immediate
}
// dropGraphComposites splits the immediate top-level composite drops into
// those ordered inside the dependent-object graph and those held back to the
// post-graph batch because a schema-owned composite (which only drops after
// the graph) references them, directly or transitively. A composite that is
// both held back and a row-type referencer of a dropped table has genuinely
// conflicting constraints; resolving that requires schema-owned objects in
// the same graph.
func dropGraphComposites(diff *schema.MetadataDiff) (map[string]*schema.CompositeTypeDiff, []*schema.CompositeTypeDiff) {
immediate := immediateCompositeDrops(diff, computeDeferredDrops(diff))
var pool []*metadatapb.CompositeTypeMetadata
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action != schema.MetadataDiffActionDrop || skipPostgresSchemaDDL(schemaDiff.SchemaName) {
continue
}
for _, compositeType := range schemaDiff.OldSchema.GetCompositeTypes() {
if !compositeType.GetSkipDump() {
pool = append(pool, compositeType)
}
}
}
held := make(map[*schema.CompositeTypeDiff]bool)
for changed := true; changed; {
changed = false
for _, candidate := range immediate {
if held[candidate] {
continue
}
for _, member := range pool {
if compositeReferencesType(member, candidate.SchemaName, candidate.CompositeTypeName) {
held[candidate] = true
pool = append(pool, candidate.OldCompositeType)
changed = true
break
}
}
}
}
inGraph := make(map[string]*schema.CompositeTypeDiff)
var heldBack []*schema.CompositeTypeDiff
for _, compositeDiff := range immediate {
if held[compositeDiff] {
heldBack = append(heldBack, compositeDiff)
} else {
inGraph[getMigrationObjectID(compositeDiff.SchemaName, compositeDiff.CompositeTypeName)] = compositeDiff
}
}
return inGraph, heldBack
}
// typeReleasedByCompositeAlter reports whether an ALTERED composite's old
// attributes reference the given type while its new version does not — the
// release happens only when the create-phase alter runs.
func typeReleasedByCompositeAlter(diff *schema.MetadataDiff, typeSchema, typeName string) bool {
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action != schema.MetadataDiffActionAlter {
continue
}
for _, oldAttribute := range compositeDiff.OldCompositeType.GetAttributes() {
if typeStringReferencesComposite(oldAttribute.Type, typeSchema, typeName) {
return true
}
}
}
return false
}
func compositeDropReleasedByRetype(diff *schema.MetadataDiff, compositeDiff *schema.CompositeTypeDiff) bool {
if typeReleasedByCompositeAlter(diff, compositeDiff.SchemaName, compositeDiff.CompositeTypeName) {
return true
}
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action != schema.MetadataDiffActionAlter {
continue
}
for _, columnDiff := range tableDiff.ColumnChanges {
var oldType string
switch columnDiff.Action {
case schema.MetadataDiffActionAlter:
// A retype away from the composite releases it in the alter
// phase.
oldType = columnDiff.OldColumn.GetType()
if oldType == columnDiff.NewColumn.GetType() {
continue
}
case schema.MetadataDiffActionDrop:
// A column drop on a surviving table runs in
// writeDropAlterTableObjects, after the dependent-object
// graph — too late for a graph-ordered type drop.
oldType = columnDiff.OldColumn.GetType()
default:
continue
}
if typeStringReferencesComposite(oldType, compositeDiff.SchemaName, compositeDiff.CompositeTypeName) {
return true
}
}
}
return false
}
// columnTypeCompositeIDs resolves a column type string to candidate
// composite IDs in the given map. Qualified references resolve exactly;
// bare references (including the "_name" array form column sync produces)
// match every same-named composite — over-edging only tightens ordering.
func columnTypeCompositeIDs(columnType string, composites map[string]*schema.CompositeTypeDiff) []string {
if depSchema, depName, ok := parseQualifiedTypeIdent(columnType); ok {
id := getMigrationObjectID(depSchema, depName)
if composites[id] != nil {
return []string{id}
}
return nil
}
base := strings.TrimSuffix(strings.TrimSpace(columnType), "[]")
base = strings.TrimPrefix(base, "_")
var ids []string
for id, compositeDiff := range composites {
if compositeDiff.CompositeTypeName == base {
ids = append(ids, id)
}
}
slices.Sort(ids)
return ids
}
// typeStringReferencesComposite matches a column type string against a
// composite type. Column sync renders user-defined column types as
// "schema.name" and array columns as the bare "_name" element type, so both
// forms plus explicit array suffixes are recognized. The bare form matches
// by name alone, which may defer a drop unnecessarily — a safe direction.
func typeStringReferencesComposite(typeStr, schemaName, compositeName string) bool {
if depSchema, depName, ok := parseQualifiedTypeIdent(typeStr); ok {
return depSchema == schemaName && depName == compositeName
}
base := strings.TrimSuffix(strings.TrimSpace(typeStr), "[]")
base = strings.TrimPrefix(base, "_")
return base == compositeName
}
func writeCompositeTypeDropsInReverseDependencyOrder(out *strings.Builder, drops []*schema.CompositeTypeDiff) error {
droppedCompositeDiffs := make(map[string]*schema.CompositeTypeDiff, len(drops))
droppedComposites := make([]qualifiedCompositeType, 0, len(drops))
for _, compositeDiff := range drops {
droppedCompositeDiffs[compositeDiff.SchemaName+"\x00"+compositeDiff.CompositeTypeName] = compositeDiff
droppedComposites = append(droppedComposites, qualifiedCompositeType{Schema: compositeDiff.SchemaName, Composite: compositeDiff.OldCompositeType})
}
orderedDrops := sortCompositeTypesTopologically(droppedComposites)
for i := len(orderedDrops) - 1; i >= 0; i-- {
t := orderedDrops[i]
if err := writeCompositeTypeDiff(out, droppedCompositeDiffs[t.Schema+"\x00"+t.Composite.Name]); err != nil {
return err
}
}
return nil
}
func writeDropPhase(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, eventTriggerDiff := range diff.EventTriggerChanges {
if eventTriggerDiff.Action == schema.MetadataDiffActionDrop || eventTriggerDiff.Action == schema.MetadataDiffActionAlter {
if err := writeEventTriggerDiff(out, eventTriggerDiff); err != nil {
return err
}
}
}
if err := writeDropTableTriggers(out, diff); err != nil {
return err
}
if err := writeDropAlterTableForeignKeys(out, diff); err != nil {
return err
}
if err := writeDropTableForeignKeysForTableDrops(out, diff); err != nil {
return err
}
if err := writeDropSequenceOwnershipBeforeTableDrops(out, diff); err != nil {
return err
}
if err := writeReleasingCompositeAlters(out, diff); err != nil {
return err
}
if err := writeDropDependentObjects(out, diff); err != nil {
return err
}
if err := writeDropAlterTableObjects(out, diff); err != nil {
return err
}
for _, sequenceDiff := range diff.SequenceChanges {
if sequenceDiff.Action == schema.MetadataDiffActionDrop {
if isSequenceOwnedByDroppedTable(diff, sequenceDiff.SchemaName, sequenceDiff.OldSequence) {
continue
}
if err := writeSequenceDiff(out, sequenceDiff); err != nil {
return err
}
}
}
// Non-deferred dropped schemas participate in the same global type-drop
// ordering as top-level drops: a schema-owned composite may reference a
// top-level type (or vice versa), so their non-type objects drop first,
// then every composite in one reverse-dependency pass, then enums, and
// the bare DROP SCHEMA statements last.
deferred := computeDeferredDrops(diff)
var immediateSchemaDrops []*schema.SchemaDiff
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionDrop && !deferred.schemas[schemaDiff] && !skipPostgresSchemaDDL(schemaDiff.SchemaName) {
immediateSchemaDrops = append(immediateSchemaDrops, schemaDiff)
}
}
slices.SortStableFunc(immediateSchemaDrops, func(a, b *schema.SchemaDiff) int {
return strings.Compare(a.SchemaName, b.SchemaName)
})
for _, schemaDiff := range immediateSchemaDrops {
if err := writeDropSchemaNonTypeObjects(out, schemaDiff.SchemaName, schemaDiff.OldSchema); err != nil {
return err
}
}
// Drop composite types before enum types: composite attributes may
// reference enums. Dependents drop before the composites they reference.
// Composites released only by a column retype cannot be dropped yet —
// the retype runs in the later alter phase — so those drops are deferred
// to the end of the migration.
// Top-level immediate composite drops are ordered inside the dependent-
// object graph (writeDropDependentObjects), except those a schema-owned
// composite references — they drop here, merged with the schema-owned
// composites in one reverse-dependency pass. Residual corner: a
// schema-owned composite referencing a top-level dropped table drops
// after it — full generality needs the schema-owned objects in the
// same graph.
_, remainingDrops := dropGraphComposites(diff)
for _, schemaDiff := range immediateSchemaDrops {
schemaObjects := buildDropSchemaObjectsDiff(schemaDiff.SchemaName, schemaDiff.OldSchema)
remainingDrops = append(remainingDrops, schemaObjects.CompositeTypeChanges...)
}
if err := writeCompositeTypeDropsInReverseDependencyOrder(out, remainingDrops); err != nil {
return err
}
// Enums referenced by a deferred composite cannot be dropped until that
// composite is gone; they are deferred alongside it.
for _, enumDiff := range diff.EnumTypeChanges {
if enumDiff.Action == schema.MetadataDiffActionDrop && !deferred.enums[enumDiff] {
if err := writeEnumTypeDiff(out, enumDiff); err != nil {
return err
}
}
}
for _, schemaDiff := range immediateSchemaDrops {
schemaObjects := buildDropSchemaObjectsDiff(schemaDiff.SchemaName, schemaDiff.OldSchema)
for _, enumDiff := range schemaObjects.EnumTypeChanges {
if err := writeEnumTypeDiff(out, enumDiff); err != nil {
return err
}
}
}
for _, extensionDiff := range diff.ExtensionChanges {
if extensionDiff.Action == schema.MetadataDiffActionDrop {
if err := writeExtensionDiff(out, extensionDiff); err != nil {
return err
}
}
}
for _, schemaDiff := range immediateSchemaDrops {
if _, err := fmt.Fprintf(out, "DROP SCHEMA IF EXISTS \"%s\";\n\n", schemaDiff.SchemaName); err != nil {
return err
}
}
return nil
}
func writeCreateOrAlterPhase(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, schemaDiff := range diff.SchemaChanges {
if schemaDiff.Action == schema.MetadataDiffActionCreate {
if err := writeSchemaDiff(out, schemaDiff); err != nil {
return err
}
}
}
for _, extensionDiff := range diff.ExtensionChanges {
if extensionDiff.Action == schema.MetadataDiffActionCreate {
if err := writeExtensionDiff(out, extensionDiff); err != nil {
return err
}
}
}
for _, enumDiff := range diff.EnumTypeChanges {
if enumDiff.Action == schema.MetadataDiffActionCreate || enumDiff.Action == schema.MetadataDiffActionAlter {
if err := writeEnumTypeDiff(out, enumDiff); err != nil {
return err
}
}
}
for _, sequenceDiff := range diff.SequenceChanges {
if sequenceDiff.Action == schema.MetadataDiffActionCreate || sequenceDiff.Action == schema.MetadataDiffActionAlter {
if err := writeSequenceDiff(out, sequenceDiff); err != nil {
return err
}
}
}
if err := writeCreateTables(out, diff); err != nil {
return err
}
// Composite alters run after table creation: an added or retyped
// attribute may reference a row type created in this migration, while
// table creation itself only references the composite by name and never
// depends on its attribute set. Statements that release a dropped
// table's row type were already emitted in the drop phase. Alters that
// gain a created view's row type wait until after the views phase.
released := releasingCompositeAttributes(diff)
postViewAlters := postViewCompositeAlters(diff)
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action == schema.MetadataDiffActionAlter && !postViewAlters[compositeDiff] {
if err := writeCompositeTypeDiffWithReleased(out, compositeDiff, released); err != nil {
return err
}
}
}
if err := writeAlterTables(out, diff); err != nil {
return err
}
if err := writeSequenceOwnershipAfterTables(out, diff); err != nil {
return err
}
if err := writeCreateRoutinesViewsAndMaterializedViews(out, diff); err != nil {
return err
}
// Composites whose attributes use a created view or materialized view
// row type (directly or through another such composite) are created only
// now. Residual: a created table consuming such a composite still emits
// earlier — resolving that needs views in the create graph.
postView := postViewCompositeCreates(diff)
var postViewCreates []qualifiedCompositeType
postViewDiffs := make(map[string]*schema.CompositeTypeDiff)
for compositeDiff := range postView {
postViewDiffs[compositeDiff.SchemaName+"\x00"+compositeDiff.CompositeTypeName] = compositeDiff
postViewCreates = append(postViewCreates, qualifiedCompositeType{Schema: compositeDiff.SchemaName, Composite: compositeDiff.NewCompositeType})
}
for _, t := range sortCompositeTypesTopologically(postViewCreates) {
if err := writeCompositeTypeDiff(out, postViewDiffs[t.Schema+"\x00"+t.Composite.Name]); err != nil {
return err
}
}
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action == schema.MetadataDiffActionAlter && postViewAlters[compositeDiff] {
if err := writeCompositeTypeDiffWithReleased(out, compositeDiff, released); err != nil {
return err
}
}
}
if err := writeCreateTableForeignKeys(out, diff); err != nil {
return err
}
if err := writeCreateTableTriggers(out, diff); err != nil {
return err
}
if err := writeCommentChanges(out, diff); err != nil {
return err
}
for _, eventTriggerDiff := range diff.EventTriggerChanges {
if eventTriggerDiff.Action == schema.MetadataDiffActionCreate || eventTriggerDiff.Action == schema.MetadataDiffActionAlter {
if err := writeEventTriggerDiff(out, eventTriggerDiff); err != nil {
return err
}
}
}
return nil
}
func validateSupportedMetadataDiff(diff *schema.MetadataDiff) error {
if len(diff.EventChanges) > 0 {
return errors.New("unsupported PostgreSQL metadata diff: event")
}
return nil
}
type metadataObjectMaps struct {
tables map[string]*schema.TableDiff
views map[string]*schema.ViewDiff
materializedViews map[string]*schema.MaterializedViewDiff
functions map[string]*schema.FunctionDiff
procedures map[string]*schema.ProcedureDiff
}
func newMetadataObjectMaps() *metadataObjectMaps {
return &metadataObjectMaps{
tables: make(map[string]*schema.TableDiff),
views: make(map[string]*schema.ViewDiff),
materializedViews: make(map[string]*schema.MaterializedViewDiff),
functions: make(map[string]*schema.FunctionDiff),
procedures: make(map[string]*schema.ProcedureDiff),
}
}
func getMigrationObjectID(schemaName, objectName string) string {
return fmt.Sprintf("%s.%s", schemaName, objectName)
}
func buildDropObjectMaps(diff *schema.MetadataDiff) *metadataObjectMaps {
maps := newMetadataObjectMaps()
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop {
maps.tables[getMigrationObjectID(tableDiff.SchemaName, tableDiff.TableName)] = tableDiff
}
}
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop || alteredViewDependsOnTableDDL(viewDiff, diff) {
maps.views[getMigrationObjectID(viewDiff.SchemaName, viewDiff.ViewName)] = viewDiff
}
}
for _, materializedViewDiff := range diff.MaterializedViewChanges {
if materializedViewDiff.Action == schema.MetadataDiffActionDrop || materializedViewDiff.Action == schema.MetadataDiffActionAlter {
maps.materializedViews[getMigrationObjectID(materializedViewDiff.SchemaName, materializedViewDiff.MaterializedViewName)] = materializedViewDiff
}
}
for _, functionDiff := range diff.FunctionChanges {
if functionDiff.Action == schema.MetadataDiffActionDrop {
maps.functions[getMigrationObjectID(functionDiff.SchemaName, functionDiff.FunctionName)] = functionDiff
}
}
for _, procedureDiff := range diff.ProcedureChanges {
if procedureDiff.Action == schema.MetadataDiffActionDrop {
maps.procedures[getMigrationObjectID(procedureDiff.SchemaName, procedureDiff.ProcedureName)] = procedureDiff
}
}
return maps
}
func alteredViewDependsOnTableDDL(viewDiff *schema.ViewDiff, diff *schema.MetadataDiff) bool {
if viewDiff.Action != schema.MetadataDiffActionAlter {
return false
}
tableDDLTargets := make(map[string]bool)
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop || tableDiff.Action == schema.MetadataDiffActionAlter {
tableDDLTargets[getMigrationObjectID(tableDiff.SchemaName, tableDiff.TableName)] = true
}
}
for _, dep := range viewDiff.OldView.GetDependencyColumns() {
if tableDDLTargets[getMigrationObjectID(dep.GetSchema(), dep.GetTable())] {
return true
}
}
return false
}
func buildCreateObjectMaps(diff *schema.MetadataDiff) *metadataObjectMaps {
maps := newMetadataObjectMaps()
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionCreate {
maps.tables[getMigrationObjectID(tableDiff.SchemaName, tableDiff.TableName)] = tableDiff
}
}
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate || viewDiff.Action == schema.MetadataDiffActionAlter {
maps.views[getMigrationObjectID(viewDiff.SchemaName, viewDiff.ViewName)] = viewDiff
}
}
for _, materializedViewDiff := range diff.MaterializedViewChanges {
if materializedViewDiff.Action == schema.MetadataDiffActionCreate || materializedViewDiff.Action == schema.MetadataDiffActionAlter {
maps.materializedViews[getMigrationObjectID(materializedViewDiff.SchemaName, materializedViewDiff.MaterializedViewName)] = materializedViewDiff
}
}
for _, functionDiff := range diff.FunctionChanges {
if functionDiff.Action == schema.MetadataDiffActionCreate || functionDiff.Action == schema.MetadataDiffActionAlter {
maps.functions[getMigrationObjectID(functionDiff.SchemaName, functionDiff.FunctionName)] = functionDiff
}
}
for _, procedureDiff := range diff.ProcedureChanges {
if procedureDiff.Action == schema.MetadataDiffActionCreate || procedureDiff.Action == schema.MetadataDiffActionAlter {
maps.procedures[getMigrationObjectID(procedureDiff.SchemaName, procedureDiff.ProcedureName)] = procedureDiff
}
}
return maps
}
func sortedMapKeys[T any](items map[string]T) []string {
keys := make([]string, 0, len(items))
for key := range items {
keys = append(keys, key)
}
slices.Sort(keys)
return keys
}
func writeDropTableTriggers(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
switch tableDiff.Action {
case schema.MetadataDiffActionDrop:
for _, trigger := range tableDiff.OldTable.GetTriggers() {
if err := writeDropTrigger(out, tableDiff.SchemaName, tableDiff.TableName, trigger.Name); err != nil {
return err
}
}
case schema.MetadataDiffActionAlter:
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionDrop || triggerDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropTrigger(out, tableDiff.SchemaName, tableDiff.TableName, triggerDiff.TriggerName); err != nil {
return err
}
}
}
default:
}
}
return nil
}
func writeDropAlterTableForeignKeys(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action != schema.MetadataDiffActionAlter {
continue
}
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionDrop || fkDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropForeignKey(out, tableDiff.SchemaName, tableDiff.TableName, fkDiff.OldForeignKey.GetName()); err != nil {
return err
}
}
}
}
return nil
}
func writeDropTableForeignKeysForTableDrops(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action != schema.MetadataDiffActionDrop {
continue
}
for _, fk := range tableDiff.OldTable.GetForeignKeys() {
if err := writeDropForeignKey(out, tableDiff.SchemaName, tableDiff.TableName, fk.GetName()); err != nil {
return err
}
}
}
return nil
}
func isSequenceOwnedByDroppedTable(diff *schema.MetadataDiff, schemaName string, sequence *metadatapb.SequenceMetadata) bool {
if sequence.GetOwnerTable() == "" || sequence.GetOwnerColumn() == "" {
return false
}
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop &&
tableDiff.SchemaName == schemaName &&
tableDiff.TableName == sequence.GetOwnerTable() {
return true
}
}
return false
}
func writeDropDependentObjects(out *strings.Builder, diff *schema.MetadataDiff) error {
maps := buildDropObjectMaps(diff)
// Top-level immediate composite drops participate in the same graph:
// tables/views drop before composites their columns use, and composites
// drop before tables whose row types their attributes reference.
composites, _ := dropGraphComposites(diff)
graph := base.NewGraph()
for _, id := range sortedMapKeys(composites) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.tables) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.views) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.materializedViews) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.functions) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.procedures) {
graph.AddNode(id)
}
for _, tableID := range sortedMapKeys(maps.tables) {
for _, id := range sortedMapKeys(maps.views) {
graph.AddEdge(id, tableID)
}
for _, id := range sortedMapKeys(maps.materializedViews) {
graph.AddEdge(id, tableID)
}
for _, id := range sortedMapKeys(maps.procedures) {
graph.AddEdge(id, tableID)
}
for _, id := range sortedMapKeys(maps.functions) {
graph.AddEdge(id, tableID)
}
}
// Views, matviews, and routines drop before every composite type, like
// they do before every table — except when the composite's attributes
// reference that view's row type, where only the composite -> view edge
// applies (the blanket edge would create a cycle and break the order).
compositeRowTypeRefs := make(map[string]map[string]bool)
for compositeID, compositeDiff := range composites {
refs := make(map[string]bool)
for _, attribute := range compositeDiff.OldCompositeType.GetAttributes() {
if depSchema, depName, ok := parseQualifiedTypeIdent(attribute.Type); ok {
refs[getMigrationObjectID(depSchema, depName)] = true
}
}
compositeRowTypeRefs[compositeID] = refs
}
for _, compositeID := range sortedMapKeys(composites) {
refs := compositeRowTypeRefs[compositeID]
for _, id := range sortedMapKeys(maps.views) {
if !refs[id] {
graph.AddEdge(id, compositeID)
}
}
for _, id := range sortedMapKeys(maps.materializedViews) {
if !refs[id] {
graph.AddEdge(id, compositeID)
}
}
for _, id := range sortedMapKeys(maps.procedures) {
graph.AddEdge(id, compositeID)
}
for _, id := range sortedMapKeys(maps.functions) {
graph.AddEdge(id, compositeID)
}
}
hasNode := func(id string) bool {
_, ok := graph.NodeMap[id]
return ok
}
for viewID, viewDiff := range maps.views {
for _, dep := range viewDiff.OldView.GetDependencyColumns() {
depID := getMigrationObjectID(dep.GetSchema(), dep.GetTable())
if hasNode(depID) {
graph.AddEdge(viewID, depID)
}
}
}
for viewID, viewDiff := range maps.materializedViews {
for _, dep := range viewDiff.OldMaterializedView.GetDependencyColumns() {
depID := getMigrationObjectID(dep.GetSchema(), dep.GetTable())
if hasNode(depID) {
graph.AddEdge(viewID, depID)
}
}
}
for functionID, functionDiff := range maps.functions {
for _, dep := range functionDiff.OldFunction.GetDependencyTables() {
depID := getMigrationObjectID(dep.GetSchema(), dep.GetTable())
if hasNode(depID) {
graph.AddEdge(functionID, depID)
}
}
}
for tableID, tableDiff := range maps.tables {
for _, fk := range tableDiff.OldTable.GetForeignKeys() {
depID := getMigrationObjectID(fk.GetReferencedSchema(), fk.GetReferencedTable())
if hasNode(depID) {
graph.AddEdge(tableID, depID)
}
}
// A table whose column uses a dropped composite drops before it.
for _, column := range tableDiff.OldTable.GetColumns() {
for _, depID := range columnTypeCompositeIDs(column.GetType(), composites) {
graph.AddEdge(tableID, depID)
}
}
}
for compositeID, compositeDiff := range composites {
for _, attribute := range compositeDiff.OldCompositeType.GetAttributes() {
depSchema, depName, ok := parseQualifiedTypeIdent(attribute.Type)
if !ok {
continue
}
depID := getMigrationObjectID(depSchema, depName)
// A composite drops before the composite or table row type it
// references.
if depID != compositeID && hasNode(depID) {
graph.AddEdge(compositeID, depID)
}
}
}
orderedIDs, err := graph.TopologicalSort()
if err != nil {
orderedIDs = append(orderedIDs, sortedMapKeys(maps.views)...)
orderedIDs = append(orderedIDs, sortedMapKeys(maps.materializedViews)...)
orderedIDs = append(orderedIDs, sortedMapKeys(maps.procedures)...)
orderedIDs = append(orderedIDs, sortedMapKeys(maps.functions)...)
orderedIDs = append(orderedIDs, sortedMapKeys(composites)...)
orderedIDs = append(orderedIDs, sortedMapKeys(maps.tables)...)
}
for _, id := range orderedIDs {
switch {
case maps.views[id] != nil:
if err := writeDropView(out, maps.views[id].SchemaName, maps.views[id].ViewName); err != nil {
return err
}
case maps.materializedViews[id] != nil:
if err := writeDropMaterializedView(out, maps.materializedViews[id].SchemaName, maps.materializedViews[id].MaterializedViewName); err != nil {
return err
}
case maps.procedures[id] != nil:
if err := writeProcedureDiff(out, maps.procedures[id]); err != nil {
return err
}
case maps.functions[id] != nil:
if err := writeFunctionDiff(out, maps.functions[id]); err != nil {
return err
}
case maps.tables[id] != nil:
if err := writeTableDiff(out, maps.tables[id]); err != nil {
return err
}
case composites[id] != nil:
if err := writeCompositeTypeDiff(out, composites[id]); err != nil {
return err
}
default:
}
}
return nil
}
func writeDropAlterTableObjects(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action != schema.MetadataDiffActionAlter {
continue
}
for _, partitionDiff := range tableDiff.PartitionChanges {
if partitionDiff.Action == schema.MetadataDiffActionDrop {
if err := writeDropPartition(out, tableDiff.SchemaName, partitionDiff.OldPartition); err != nil {
return err
}
}
}
for _, primaryKeyDiff := range tableDiff.PrimaryKeyChanges {
if primaryKeyDiff.Action == schema.MetadataDiffActionDrop || primaryKeyDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropTableConstraint(out, tableDiff.SchemaName, tableDiff.TableName, primaryKeyDiff.OldPrimaryKey.GetName()); err != nil {
return err
}
}
}
for _, uniqueConstraintDiff := range tableDiff.UniqueConstraintChanges {
if uniqueConstraintDiff.Action == schema.MetadataDiffActionDrop || uniqueConstraintDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropTableConstraint(out, tableDiff.SchemaName, tableDiff.TableName, uniqueConstraintDiff.OldUniqueConstraint.GetName()); err != nil {
return err
}
}
}
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionDrop || checkDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropTableConstraint(out, tableDiff.SchemaName, tableDiff.TableName, checkDiff.OldCheckConstraint.GetName()); err != nil {
return err
}
}
}
for _, excludeDiff := range tableDiff.ExcludeConstraintChanges {
if excludeDiff.Action == schema.MetadataDiffActionDrop || excludeDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropTableConstraint(out, tableDiff.SchemaName, tableDiff.TableName, excludeDiff.OldExcludeConstraint.GetName()); err != nil {
return err
}
}
}
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionDrop || indexDiff.Action == schema.MetadataDiffActionAlter {
if err := writeDropIndexDiff(out, tableDiff.SchemaName, tableDiff.TableName, indexDiff.OldIndex); err != nil {
return err
}
}
}
for _, columnDiff := range tableDiff.ColumnChanges {
if columnDiff.Action == schema.MetadataDiffActionDrop {
if err := writeColumnDiff(out, tableDiff.SchemaName, tableDiff.TableName, columnDiff); err != nil {
return err
}
}
}
}
return nil
}
func writeCreateTables(out *strings.Builder, diff *schema.MetadataDiff) error {
maps := buildCreateObjectMaps(diff)
// Created composite types share the graph: a composite referencing a
// created table's row type comes after that table, and a table whose
// column uses a created composite comes after the composite. Composites
// referencing created view row types wait for the views phase instead.
postView := postViewCompositeCreates(diff)
composites := make(map[string]*schema.CompositeTypeDiff)
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action == schema.MetadataDiffActionCreate && !postView[compositeDiff] {
composites[getMigrationObjectID(compositeDiff.SchemaName, compositeDiff.CompositeTypeName)] = compositeDiff
}
}
graph := base.NewGraph()
for _, id := range sortedMapKeys(composites) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.tables) {
graph.AddNode(id)
}
for tableID, tableDiff := range maps.tables {
for _, fk := range tableDiff.NewTable.GetForeignKeys() {
depID := getMigrationObjectID(fk.GetReferencedSchema(), fk.GetReferencedTable())
if _, ok := maps.tables[depID]; ok {
graph.AddEdge(depID, tableID)
}
}
for _, column := range tableDiff.NewTable.GetColumns() {
for _, depID := range columnTypeCompositeIDs(column.GetType(), composites) {
graph.AddEdge(depID, tableID)
}
}
}
for compositeID, compositeDiff := range composites {
for _, attribute := range compositeDiff.NewCompositeType.GetAttributes() {
depSchema, depName, ok := parseQualifiedTypeIdent(attribute.Type)
if !ok {
continue
}
depID := getMigrationObjectID(depSchema, depName)
if depID == compositeID {
continue
}
if composites[depID] != nil || maps.tables[depID] != nil {
graph.AddEdge(depID, compositeID)
}
}
}
orderedIDs, err := graph.TopologicalSort()
if err != nil {
orderedIDs = append(sortedMapKeys(composites), sortedMapKeys(maps.tables)...)
}
for _, id := range orderedIDs {
if compositeDiff := composites[id]; compositeDiff != nil {
if err := writeCompositeTypeDiff(out, compositeDiff); err != nil {
return err
}
continue
}
if err := writeTableDiff(out, maps.tables[id]); err != nil {
return err
}
}
return nil
}
func writeAlterTables(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionAlter {
if err := writeAlterTableDiff(out, tableDiff); err != nil {
return err
}
}
}
return nil
}
func writeDropSequenceOwnershipBeforeTableDrops(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, sequenceDiff := range diff.SequenceChanges {
if sequenceDiff.Action != schema.MetadataDiffActionAlter || !sequenceOwnershipChanged(sequenceDiff.OldSequence, sequenceDiff.NewSequence) {
continue
}
if sequenceDiff.OldSequence.GetOwnerTable() == "" || sequenceDiff.OldSequence.GetOwnerColumn() == "" {
continue
}
if !tableIsDropped(diff, sequenceDiff.SchemaName, sequenceDiff.OldSequence.GetOwnerTable()) {
continue
}
if err := writeAlterSequenceOwnedByNone(out, sequenceDiff.SchemaName, sequenceDiff.SequenceName); err != nil {
return err
}
}
return nil
}
func writeSequenceOwnershipAfterTables(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, sequenceDiff := range diff.SequenceChanges {
switch sequenceDiff.Action {
case schema.MetadataDiffActionCreate:
if sequenceDiff.NewSequence.GetOwnerTable() != "" && sequenceDiff.NewSequence.GetOwnerColumn() != "" {
if err := writeAlterSequenceOwnedBy(out, sequenceDiff.SchemaName, sequenceDiff.NewSequence); err != nil {
return err
}
}
case schema.MetadataDiffActionAlter:
if !sequenceOwnershipChanged(sequenceDiff.OldSequence, sequenceDiff.NewSequence) {
continue
}
if sequenceDiff.NewSequence.GetOwnerTable() != "" && sequenceDiff.NewSequence.GetOwnerColumn() != "" {
if err := writeAlterSequenceOwnedBy(out, sequenceDiff.SchemaName, sequenceDiff.NewSequence); err != nil {
return err
}
} else if !tableIsDropped(diff, sequenceDiff.SchemaName, sequenceDiff.OldSequence.GetOwnerTable()) {
if err := writeAlterSequenceOwnedByNone(out, sequenceDiff.SchemaName, sequenceDiff.SequenceName); err != nil {
return err
}
}
default:
}
}
return nil
}
func sequenceOwnershipChanged(oldSequence, newSequence *metadatapb.SequenceMetadata) bool {
return oldSequence.GetOwnerTable() != newSequence.GetOwnerTable() || oldSequence.GetOwnerColumn() != newSequence.GetOwnerColumn()
}
func tableIsDropped(diff *schema.MetadataDiff, schemaName, tableName string) bool {
for _, tableDiff := range diff.TableChanges {
if tableDiff.Action == schema.MetadataDiffActionDrop && tableDiff.SchemaName == schemaName && tableDiff.TableName == tableName {
return true
}
}
return false
}
func writeCreateRoutinesViewsAndMaterializedViews(out *strings.Builder, diff *schema.MetadataDiff) error {
maps := buildCreateObjectMaps(diff)
dependentFunctions := functionsDependingOnCreatedViewsOrMaterializedViews(maps)
for _, id := range sortedMapKeys(maps.functions) {
if dependentFunctions[id] {
continue
}
if err := writeFunctionDiff(out, maps.functions[id]); err != nil {
return err
}
}
for _, id := range sortedMapKeys(maps.procedures) {
if err := writeProcedureDiff(out, maps.procedures[id]); err != nil {
return err
}
}
graph := base.NewGraph()
for _, id := range sortedMapKeys(maps.views) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(maps.materializedViews) {
graph.AddNode(id)
}
for _, id := range sortedMapKeys(dependentFunctions) {
graph.AddNode(id)
}
hasNode := func(id string) bool {
_, ok := graph.NodeMap[id]
return ok
}
for viewID, viewDiff := range maps.views {
for _, dep := range viewDiff.NewView.GetDependencyColumns() {
depID := getMigrationObjectID(dep.GetSchema(), dep.GetTable())
if hasNode(depID) {
graph.AddEdge(depID, viewID)
}
}
}
for viewID, viewDiff := range maps.materializedViews {
for _, dep := range viewDiff.NewMaterializedView.GetDependencyColumns() {
depID := getMigrationObjectID(dep.GetSchema(), dep.GetTable())
if hasNode(depID) {
graph.AddEdge(depID, viewID)
}
}
}
for functionID := range dependentFunctions {
for _, dep := range maps.functions[functionID].NewFunction.GetDependencyTables() {
depID := getMigrationObjectID(dep.GetSchema(), dep.GetTable())
if hasNode(depID) {
graph.AddEdge(depID, functionID)
}
}
}
orderedIDs, err := graph.TopologicalSort()
if err != nil {
orderedIDs = append(orderedIDs, sortedMapKeys(maps.views)...)
orderedIDs = append(orderedIDs, sortedMapKeys(maps.materializedViews)...)
orderedIDs = append(orderedIDs, sortedMapKeys(dependentFunctions)...)
}
for _, id := range orderedIDs {
switch {
case maps.views[id] != nil:
if err := writeViewDiff(out, maps.views[id]); err != nil {
return err
}
case maps.materializedViews[id] != nil:
if err := writeCreateMaterializedViewDiff(out, maps.materializedViews[id]); err != nil {
return err
}
case maps.functions[id] != nil:
if err := writeFunctionDiff(out, maps.functions[id]); err != nil {
return err
}
default:
}
}
return nil
}
func functionsDependingOnCreatedViewsOrMaterializedViews(maps *metadataObjectMaps) map[string]bool {
viewIDs := make(map[string]bool)
for id := range maps.views {
viewIDs[id] = true
}
for id := range maps.materializedViews {
viewIDs[id] = true
}
dependentFunctions := make(map[string]bool)
for functionID, functionDiff := range maps.functions {
for _, dep := range functionDiff.NewFunction.GetDependencyTables() {
if viewIDs[getMigrationObjectID(dep.GetSchema(), dep.GetTable())] {
dependentFunctions[functionID] = true
break
}
}
}
return dependentFunctions
}
func writeCreateTableForeignKeys(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
switch tableDiff.Action {
case schema.MetadataDiffActionCreate:
for _, fk := range tableDiff.NewTable.GetForeignKeys() {
if err := writeForeignKey(out, tableDiff.SchemaName, tableDiff.TableName, fk); err != nil {
return err
}
}
case schema.MetadataDiffActionAlter:
for _, fkDiff := range tableDiff.ForeignKeyChanges {
if fkDiff.Action == schema.MetadataDiffActionCreate || fkDiff.Action == schema.MetadataDiffActionAlter {
if err := writeForeignKey(out, tableDiff.SchemaName, tableDiff.TableName, fkDiff.NewForeignKey); err != nil {
return err
}
}
}
default:
}
}
return nil
}
func writeCreateTableTriggers(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, tableDiff := range diff.TableChanges {
switch tableDiff.Action {
case schema.MetadataDiffActionCreate:
for _, trigger := range tableDiff.NewTable.GetTriggers() {
if err := writeTrigger(out, tableDiff.SchemaName, tableDiff.TableName, trigger); err != nil {
return err
}
}
case schema.MetadataDiffActionAlter:
for _, triggerDiff := range tableDiff.TriggerChanges {
if triggerDiff.Action == schema.MetadataDiffActionCreate || triggerDiff.Action == schema.MetadataDiffActionAlter {
if err := writeTrigger(out, tableDiff.SchemaName, tableDiff.TableName, triggerDiff.NewTrigger); err != nil {
return err
}
}
}
default:
}
}
return nil
}
func writeCommentChanges(out *strings.Builder, diff *schema.MetadataDiff) error {
for _, commentDiff := range diff.CommentChanges {
if err := writeCommentDiff(out, commentDiff); err != nil {
return err
}
}
return nil
}
func writeTableDiff(out *strings.Builder, tableDiff *schema.TableDiff) error {
switch tableDiff.Action {
case schema.MetadataDiffActionCreate:
return writeTableForMetadataMigration(out, tableDiff.SchemaName, tableDiff.NewTable)
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "DROP TABLE \"%s\".\"%s\";\n\n", tableDiff.SchemaName, tableDiff.TableName)
return err
case schema.MetadataDiffActionAlter:
return writeAlterTableDiff(out, tableDiff)
default:
return nil
}
}
func writeTableForMetadataMigration(out *strings.Builder, schemaName string, table *metadatapb.TableMetadata) error {
if err := writeCreateTableForMetadataMigration(out, schemaName, table); err != nil {
return err
}
if len(table.Partitions) > 0 {
if err := writePartitionClause(out, table.Partitions[0]); err != nil {
return err
}
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if table.GetComment() != "" {
if err := writeTableComment(out, schemaName, table); err != nil {
return err
}
}
for _, column := range table.GetColumns() {
if column.GetComment() != "" {
if err := writeColumnComment(out, schemaName, table.GetName(), column); err != nil {
return err
}
}
}
for _, partition := range table.GetPartitions() {
if err := writePartitionTable(out, schemaName, table.GetColumns(), partition); err != nil {
return err
}
}
for _, partition := range table.GetPartitions() {
if err := writeAttachPartition(out, schemaName, table.GetName(), partition); err != nil {
return err
}
}
for _, index := range table.GetIndexes() {
if index.GetPrimary() {
if err := writePrimaryKey(out, schemaName, table.GetName(), index); err != nil {
return err
}
}
}
for _, partition := range table.GetPartitions() {
if err := writePartitionPrimaryKey(out, schemaName, partition); err != nil {
return err
}
}
for _, index := range table.GetIndexes() {
if index.GetUnique() && !index.GetPrimary() && index.GetIsConstraint() {
if err := writeUniqueKey(out, schemaName, table.GetName(), index); err != nil {
return err
}
}
}
for _, partition := range table.GetPartitions() {
if err := writePartitionUniqueKey(out, schemaName, partition); err != nil {
return err
}
}
for _, index := range table.GetIndexes() {
if !index.GetPrimary() && !index.GetIsConstraint() {
if err := writeCreateRegularIndex(out, schemaName, table.GetName(), index, len(table.GetPartitions()) > 0); err != nil {
return err
}
}
}
for _, partition := range table.GetPartitions() {
if err := writePartitionIndexForMetadataMigration(out, schemaName, partition); err != nil {
return err
}
}
for _, partition := range table.GetPartitions() {
if err := writeAttachPartitionIndex(out, schemaName, partition); err != nil {
return err
}
}
return nil
}
func writeCreateTableForMetadataMigration(out *strings.Builder, schemaName string, table *metadatapb.TableMetadata) error {
if _, err := fmt.Fprintf(out, "CREATE TABLE \"%s\".\"%s\" (", schemaName, table.GetName()); err != nil {
return err
}
for i, column := range table.GetColumns() {
if i > 0 {
if _, err := out.WriteString(","); err != nil {
return err
}
}
if _, err := out.WriteString("\n "); err != nil {
return err
}
if err := writeColumnDefinition(out, column); err != nil {
return err
}
}
for _, check := range table.GetCheckConstraints() {
if _, err := out.WriteString(",\n "); err != nil {
return err
}
if _, err := fmt.Fprintf(out, "CONSTRAINT \"%s\" CHECK %s", check.GetName(), check.GetExpression()); err != nil {
return err
}
}
for _, exclude := range table.GetExcludeConstraints() {
if _, err := out.WriteString(",\n "); err != nil {
return err
}
if _, err := fmt.Fprintf(out, "CONSTRAINT \"%s\" %s", exclude.GetName(), exclude.GetExpression()); err != nil {
return err
}
}
_, err := out.WriteString("\n)")
return err
}
func writeDropPartition(out *strings.Builder, schemaName string, partition *metadatapb.TablePartitionMetadata) error {
if partition == nil {
return nil
}
for _, subpartition := range partition.GetSubpartitions() {
if err := writeDropPartition(out, schemaName, subpartition); err != nil {
return err
}
}
_, err := fmt.Fprintf(out, "DROP TABLE \"%s\".\"%s\";\n\n", schemaName, partition.GetName())
return err
}
func writeCreatePartitionDiff(out *strings.Builder, schemaName, tableName string, columns []*metadatapb.ColumnMetadata, partition *metadatapb.TablePartitionMetadata) error {
if partition == nil {
return nil
}
if err := writePartitionTable(out, schemaName, columns, partition); err != nil {
return err
}
if err := writeAttachPartition(out, schemaName, tableName, partition); err != nil {
return err
}
if err := writePartitionPrimaryKey(out, schemaName, partition); err != nil {
return err
}
if err := writePartitionUniqueKey(out, schemaName, partition); err != nil {
return err
}
if err := writePartitionIndexForMetadataMigration(out, schemaName, partition); err != nil {
return err
}
return writeAttachPartitionIndex(out, schemaName, partition)
}
func writePartitionIndexForMetadataMigration(out *strings.Builder, schemaName string, partition *metadatapb.TablePartitionMetadata) error {
for _, index := range partition.GetIndexes() {
if !index.GetIsConstraint() && !index.GetPrimary() {
if err := writeCreateRegularIndex(out, schemaName, partition.GetName(), index, len(partition.GetSubpartitions()) > 0); err != nil {
return err
}
}
}
for _, subpartition := range partition.GetSubpartitions() {
if err := writePartitionIndexForMetadataMigration(out, schemaName, subpartition); err != nil {
return err
}
}
return nil
}
func writeAlterTableDiff(out *strings.Builder, tableDiff *schema.TableDiff) error {
for _, columnDiff := range tableDiff.ColumnChanges {
if columnDiff.Action == schema.MetadataDiffActionDrop {
continue
}
if err := writeColumnDiff(out, tableDiff.SchemaName, tableDiff.TableName, columnDiff); err != nil {
return err
}
}
for _, partitionDiff := range tableDiff.PartitionChanges {
if partitionDiff.Action == schema.MetadataDiffActionCreate {
if err := writeCreatePartitionDiff(out, tableDiff.SchemaName, tableDiff.TableName, tableDiff.NewTable.GetColumns(), partitionDiff.NewPartition); err != nil {
return err
}
}
}
for _, primaryKeyDiff := range tableDiff.PrimaryKeyChanges {
if primaryKeyDiff.Action == schema.MetadataDiffActionCreate || primaryKeyDiff.Action == schema.MetadataDiffActionAlter {
if err := writePrimaryKey(out, tableDiff.SchemaName, tableDiff.TableName, primaryKeyDiff.NewPrimaryKey); err != nil {
return err
}
}
}
for _, uniqueConstraintDiff := range tableDiff.UniqueConstraintChanges {
if uniqueConstraintDiff.Action == schema.MetadataDiffActionCreate || uniqueConstraintDiff.Action == schema.MetadataDiffActionAlter {
if err := writeUniqueKey(out, tableDiff.SchemaName, tableDiff.TableName, uniqueConstraintDiff.NewUniqueConstraint); err != nil {
return err
}
}
}
for _, checkDiff := range tableDiff.CheckConstraintChanges {
if checkDiff.Action == schema.MetadataDiffActionCreate || checkDiff.Action == schema.MetadataDiffActionAlter {
if err := writeAddCheckConstraint(out, tableDiff.SchemaName, tableDiff.TableName, checkDiff.NewCheckConstraint); err != nil {
return err
}
}
}
for _, excludeDiff := range tableDiff.ExcludeConstraintChanges {
if excludeDiff.Action == schema.MetadataDiffActionCreate || excludeDiff.Action == schema.MetadataDiffActionAlter {
if err := writeAddExcludeConstraint(out, tableDiff.SchemaName, tableDiff.TableName, excludeDiff.NewExcludeConstraint); err != nil {
return err
}
}
}
for _, indexDiff := range tableDiff.IndexChanges {
if indexDiff.Action == schema.MetadataDiffActionCreate || indexDiff.Action == schema.MetadataDiffActionAlter {
if err := writeCreateIndexDiff(out, tableDiff.SchemaName, tableDiff.TableName, indexDiff.NewIndex); err != nil {
return err
}
}
}
return writeTableCommentDiff(out, tableDiff.SchemaName, tableDiff.TableName, tableDiff.OldTable.GetComment(), tableDiff.NewTable.GetComment())
}
func writeColumnDiff(out *strings.Builder, schemaName, tableName string, columnDiff *schema.ColumnDiff) error {
switch columnDiff.Action {
case schema.MetadataDiffActionCreate:
if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ADD COLUMN ", schemaName, tableName); err != nil {
return err
}
if err := writeColumnDefinition(out, columnDiff.NewColumn); err != nil {
return err
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if columnDiff.NewColumn.GetComment() != "" {
return writeColumnComment(out, schemaName, tableName, columnDiff.NewColumn)
}
return nil
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" DROP COLUMN \"%s\";\n\n", schemaName, tableName, columnDiff.OldColumn.Name)
return err
case schema.MetadataDiffActionAlter:
return writeAlterColumnDiff(out, schemaName, tableName, columnDiff)
default:
return nil
}
}
func writeAlterColumnDiff(out *strings.Builder, schemaName, tableName string, columnDiff *schema.ColumnDiff) error {
oldColumn := columnDiff.OldColumn
newColumn := columnDiff.NewColumn
if oldColumn.Type != newColumn.Type {
if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ALTER COLUMN \"%s\" TYPE %s", schemaName, tableName, newColumn.Name, newColumn.Type); err != nil {
return err
}
if requiresExplicitCasting(oldColumn.Type, newColumn.Type) {
if _, err := fmt.Fprintf(out, " USING \"%s\"::%s", newColumn.Name, newColumn.Type); err != nil {
return err
}
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
}
if oldColumn.Default != newColumn.Default {
if newColumn.Default == "" {
if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ALTER COLUMN \"%s\" DROP DEFAULT;\n\n", schemaName, tableName, newColumn.Name); err != nil {
return err
}
} else if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ALTER COLUMN \"%s\" SET DEFAULT %s;\n\n", schemaName, tableName, newColumn.Name, newColumn.Default); err != nil {
return err
}
}
if oldColumn.Nullable != newColumn.Nullable {
nullability := "DROP NOT NULL"
if !newColumn.Nullable {
nullability = "SET NOT NULL"
}
if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ALTER COLUMN \"%s\" %s;\n\n", schemaName, tableName, newColumn.Name, nullability); err != nil {
return err
}
}
if oldColumn.GetComment() != newColumn.GetComment() {
if err := writeColumnCommentDiff(out, schemaName, tableName, newColumn.Name, newColumn.GetComment()); err != nil {
return err
}
}
return nil
}
func requiresExplicitCasting(oldType, newType string) bool {
oldBaseType := extractBaseType(oldType)
newBaseType := extractBaseType(newType)
if oldBaseType == newBaseType {
return requiresUsingForSameType(oldType, newType)
}
incompatibleConversions := map[string][]string{
"text": {"integer", "numeric", "real", pgTypeDoublePrecision, "smallint", "bigint", "boolean"},
pgTypeCharacterVarying: {"integer", "numeric", "real", pgTypeDoublePrecision, "smallint", "bigint", "boolean"},
"character": {"integer", "numeric", "real", pgTypeDoublePrecision, "smallint", "bigint", "boolean"},
"jsonb": {"text", pgTypeCharacterVarying, "character"},
"json": {"text", pgTypeCharacterVarying, "character"},
"integer[]": {"text", pgTypeCharacterVarying, "character"},
"text[]": {"text", pgTypeCharacterVarying, "character"},
"bigint": {"smallint", "integer"},
pgTypeDoublePrecision: {"real"},
"timestamp with time zone": {"date"},
"timestamp without time zone": {"date"},
"numeric": {"integer", "smallint", "bigint", "real", pgTypeDoublePrecision},
}
for _, target := range incompatibleConversions[oldBaseType] {
if target == newBaseType {
return true
}
}
return false
}
func extractBaseType(typeName string) string {
if idx := strings.Index(typeName, "("); idx >= 0 {
return strings.TrimSpace(typeName[:idx])
}
return typeName
}
func requiresUsingForSameType(oldType, newType string) bool {
oldBaseType := extractBaseType(oldType)
newBaseType := extractBaseType(newType)
if oldBaseType != newBaseType {
return false
}
if strings.Contains(oldType, pgTypeCharacterVarying) {
oldLen := extractLength(oldType)
newLen := extractLength(newType)
return oldLen > 0 && newLen > 0 && oldLen > newLen*2
}
return false
}
func extractLength(typeName string) int {
start := strings.Index(typeName, "(")
end := strings.Index(typeName, ")")
if start < 0 || end < 0 || end <= start {
return 0
}
length, err := strconv.Atoi(strings.TrimSpace(typeName[start+1 : end]))
if err != nil {
return 0
}
return length
}
func writeColumnDefinition(out *strings.Builder, column *metadatapb.ColumnMetadata) error {
if _, err := fmt.Fprintf(out, "\"%s\" %s", column.Name, column.Type); err != nil {
return err
}
if column.Default != "" {
if _, err := fmt.Fprintf(out, " DEFAULT %s", column.Default); err != nil {
return err
}
}
switch column.GetIdentityGeneration() {
case metadatapb.ColumnMetadata_ALWAYS:
if _, err := out.WriteString(" GENERATED ALWAYS AS IDENTITY"); err != nil {
return err
}
case metadatapb.ColumnMetadata_BY_DEFAULT:
if _, err := out.WriteString(" GENERATED BY DEFAULT AS IDENTITY"); err != nil {
return err
}
default:
}
if generation := column.GetGeneration(); generation != nil && generation.GetExpression() != "" {
generationType := "STORED"
if generation.GetType() == metadatapb.GenerationMetadata_TYPE_VIRTUAL {
generationType = "VIRTUAL"
}
if _, err := fmt.Fprintf(out, " GENERATED ALWAYS AS (%s) %s", generation.GetExpression(), generationType); err != nil {
return err
}
}
if !column.Nullable {
if _, err := out.WriteString(" NOT NULL"); err != nil {
return err
}
}
return nil
}
func writeDropTrigger(out *strings.Builder, schemaName, tableName, triggerName string) error {
_, err := fmt.Fprintf(out, "DROP TRIGGER \"%s\" ON \"%s\".\"%s\";\n\n", triggerName, schemaName, tableName)
return err
}
func writeDropForeignKey(out *strings.Builder, schemaName, tableName, fkName string) error {
return writeDropTableConstraint(out, schemaName, tableName, fkName)
}
func writeDropTableConstraint(out *strings.Builder, schemaName, tableName, constraintName string) error {
if constraintName == "" {
return nil
}
_, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" DROP CONSTRAINT \"%s\";\n\n", schemaName, tableName, constraintName)
return err
}
func writeDropIndexDiff(out *strings.Builder, schemaName, tableName string, index *metadatapb.IndexMetadata) error {
if index.GetIsConstraint() {
return writeDropTableConstraint(out, schemaName, tableName, index.GetName())
}
if index.GetName() == "" {
return nil
}
_, err := fmt.Fprintf(out, "DROP INDEX \"%s\".\"%s\";\n\n", schemaName, index.GetName())
return err
}
func writeAddCheckConstraint(out *strings.Builder, schemaName, tableName string, check *metadatapb.CheckConstraintMetadata) error {
if check == nil {
return nil
}
if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ADD CONSTRAINT \"%s\" CHECK %s;\n\n", schemaName, tableName, check.GetName(), check.GetExpression()); err != nil {
return err
}
return nil
}
func writeAddExcludeConstraint(out *strings.Builder, schemaName, tableName string, exclude *metadatapb.ExcludeConstraintMetadata) error {
if exclude == nil {
return nil
}
if _, err := fmt.Fprintf(out, "ALTER TABLE \"%s\".\"%s\" ADD CONSTRAINT \"%s\" %s;\n\n", schemaName, tableName, exclude.GetName(), exclude.GetExpression()); err != nil {
return err
}
return nil
}
func writeCreateIndexDiff(out *strings.Builder, schemaName, tableName string, index *metadatapb.IndexMetadata) error {
if index == nil {
return nil
}
switch {
case index.GetPrimary():
return writePrimaryKey(out, schemaName, tableName, index)
case index.GetUnique() && index.GetIsConstraint():
return writeUniqueKey(out, schemaName, tableName, index)
default:
return writeCreateRegularIndex(out, schemaName, tableName, index, false)
}
}
func writeCreateRegularIndex(out *strings.Builder, schemaName, tableName string, index *metadatapb.IndexMetadata, useOnlyClause bool) error {
if index.GetDefinition() != "" {
if err := writeDefinitionStatement(out, index.GetDefinition()); err != nil {
return err
}
if index.GetComment() != "" {
return writeIndexComment(out, schemaName, index)
}
return nil
}
return writeIndex(out, schemaName, tableName, index, useOnlyClause)
}
func writeTableCommentDiff(out *strings.Builder, schemaName, tableName, oldComment, newComment string) error {
if oldComment == newComment {
return nil
}
return writeComment(out, fmt.Sprintf("TABLE \"%s\".\"%s\"", schemaName, tableName), newComment)
}
func writeColumnCommentDiff(out *strings.Builder, schemaName, tableName, columnName, comment string) error {
return writeComment(out, fmt.Sprintf("COLUMN \"%s\".\"%s\".\"%s\"", schemaName, tableName, columnName), comment)
}
func writeCommentDiff(out *strings.Builder, commentDiff *schema.CommentDiff) error {
switch commentDiff.ObjectType {
case schema.CommentObjectTypeSchema:
return writeComment(out, fmt.Sprintf("SCHEMA \"%s\"", commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeTable:
return writeComment(out, fmt.Sprintf("TABLE \"%s\".\"%s\"", commentDiff.SchemaName, commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeColumn:
return writeComment(out, fmt.Sprintf("COLUMN \"%s\".\"%s\".\"%s\"", commentDiff.SchemaName, commentDiff.ObjectName, commentDiff.ColumnName), commentDiff.NewComment)
case schema.CommentObjectTypeView:
return writeComment(out, fmt.Sprintf(viewCommentObjectFormat, commentDiff.SchemaName, commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeMaterializedView:
return writeComment(out, fmt.Sprintf("MATERIALIZED VIEW \"%s\".\"%s\"", commentDiff.SchemaName, commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeFunction:
return writeComment(out, fmt.Sprintf("FUNCTION %s", formatQualifiedRoutineSignature(commentDiff.SchemaName, commentDiff.ObjectName)), commentDiff.NewComment)
case schema.CommentObjectTypeSequence:
return writeComment(out, fmt.Sprintf("SEQUENCE \"%s\".\"%s\"", commentDiff.SchemaName, commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeIndex:
indexName := commentDiff.IndexName
if indexName == "" {
indexName = commentDiff.ObjectName
}
return writeComment(out, fmt.Sprintf("INDEX \"%s\".\"%s\"", commentDiff.SchemaName, indexName), commentDiff.NewComment)
case schema.CommentObjectTypeTrigger:
return writeComment(out, fmt.Sprintf("TRIGGER \"%s\" ON \"%s\".\"%s\"", commentDiff.ObjectName, commentDiff.SchemaName, commentDiff.TableName), commentDiff.NewComment)
case schema.CommentObjectTypeType:
return writeComment(out, fmt.Sprintf("TYPE \"%s\".\"%s\"", commentDiff.SchemaName, commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeExtension:
return writeComment(out, fmt.Sprintf("EXTENSION \"%s\"", commentDiff.ObjectName), commentDiff.NewComment)
case schema.CommentObjectTypeEventTrigger:
return writeComment(out, fmt.Sprintf("EVENT TRIGGER \"%s\"", commentDiff.ObjectName), commentDiff.NewComment)
default:
return nil
}
}
func writeComment(out io.Writer, object, comment string) error {
if comment == "" {
_, err := fmt.Fprintf(out, "COMMENT ON %s IS NULL;\n\n", object)
return err
}
_, err := fmt.Fprintf(out, "COMMENT ON %s IS '%s';\n\n", object, escapeSingleQuote(comment))
return err
}
func writeSchemaDiff(out *strings.Builder, schemaDiff *schema.SchemaDiff) error {
switch schemaDiff.Action {
case schema.MetadataDiffActionCreate:
if skipPostgresSchemaDDL(schemaDiff.SchemaName) {
return nil
}
return writeSchema(out, schemaDiff.NewSchema)
case schema.MetadataDiffActionDrop:
if skipPostgresSchemaDDL(schemaDiff.SchemaName) {
return nil
}
if err := writeDropSchemaObjects(out, schemaDiff.SchemaName, schemaDiff.OldSchema); err != nil {
return err
}
_, err := fmt.Fprintf(out, "DROP SCHEMA IF EXISTS \"%s\";\n\n", schemaDiff.SchemaName)
return err
default:
return nil
}
}
func skipPostgresSchemaDDL(schemaName string) bool {
return schemaName == "pg_catalog" || schemaName == "public"
}
func writeDropSchemaObjects(out *strings.Builder, schemaName string, schemaMeta *metadatapb.SchemaMetadata) error {
if err := writeDropSchemaNonTypeObjects(out, schemaName, schemaMeta); err != nil {
return err
}
return writeDropSchemaTypes(out, schemaName, schemaMeta)
}
func writeDropSchemaNonTypeObjects(out *strings.Builder, schemaName string, schemaMeta *metadatapb.SchemaMetadata) error {
if schemaMeta == nil {
return nil
}
diff := buildDropSchemaObjectsDiff(schemaName, schemaMeta)
// This pass emits only non-type objects; the caller drops the schema's
// types afterwards. Clearing the type changes keeps the shared graph in
// writeDropDependentObjects from emitting them here too.
diff.CompositeTypeChanges = nil
diff.EnumTypeChanges = nil
if err := writeDropTableTriggers(out, diff); err != nil {
return err
}
if err := writeDropTableForeignKeysForTableDrops(out, diff); err != nil {
return err
}
if err := writeDropDependentObjects(out, diff); err != nil {
return err
}
for _, sequenceDiff := range diff.SequenceChanges {
if isSequenceOwnedByDroppedTable(diff, sequenceDiff.SchemaName, sequenceDiff.OldSequence) {
continue
}
if err := writeSequenceDiff(out, sequenceDiff); err != nil {
return err
}
}
return nil
}
func writeDropSchemaTypes(out *strings.Builder, schemaName string, schemaMeta *metadatapb.SchemaMetadata) error {
if schemaMeta == nil {
return nil
}
diff := buildDropSchemaObjectsDiff(schemaName, schemaMeta)
// Composite types drop before enum types they may reference.
if err := writeCompositeTypeDropsInReverseDependencyOrder(out, diff.CompositeTypeChanges); err != nil {
return err
}
for _, enumDiff := range diff.EnumTypeChanges {
if err := writeEnumTypeDiff(out, enumDiff); err != nil {
return err
}
}
return nil
}
func buildDropSchemaObjectsDiff(schemaName string, schemaMeta *metadatapb.SchemaMetadata) *schema.MetadataDiff {
diff := &schema.MetadataDiff{}
for _, table := range schemaMeta.GetTables() {
if table.GetSkipDump() {
continue
}
diff.TableChanges = append(diff.TableChanges, &schema.TableDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
TableName: table.GetName(),
OldTable: table,
})
}
for _, view := range schemaMeta.GetViews() {
if view.GetSkipDump() {
continue
}
diff.ViewChanges = append(diff.ViewChanges, &schema.ViewDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
ViewName: view.GetName(),
OldView: view,
})
}
for _, materializedView := range schemaMeta.GetMaterializedViews() {
if materializedView.GetSkipDump() {
continue
}
diff.MaterializedViewChanges = append(diff.MaterializedViewChanges, &schema.MaterializedViewDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
MaterializedViewName: materializedView.GetName(),
OldMaterializedView: materializedView,
})
}
for _, function := range schemaMeta.GetFunctions() {
if function.GetSkipDump() {
continue
}
functionName := function.GetSignature()
if functionName == "" {
functionName = function.GetName()
}
diff.FunctionChanges = append(diff.FunctionChanges, &schema.FunctionDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
FunctionName: functionName,
OldFunction: function,
})
}
for _, procedure := range schemaMeta.GetProcedures() {
if procedure.GetSkipDump() {
continue
}
procedureName := procedure.GetSignature()
if procedureName == "" {
procedureName = procedure.GetName()
}
diff.ProcedureChanges = append(diff.ProcedureChanges, &schema.ProcedureDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
ProcedureName: procedureName,
OldProcedure: procedure,
})
}
for _, sequence := range schemaMeta.GetSequences() {
if sequence.GetSkipDump() {
continue
}
diff.SequenceChanges = append(diff.SequenceChanges, &schema.SequenceDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
SequenceName: sequence.GetName(),
OldSequence: sequence,
})
}
for _, enumType := range schemaMeta.GetEnumTypes() {
if enumType.GetSkipDump() {
continue
}
diff.EnumTypeChanges = append(diff.EnumTypeChanges, &schema.EnumTypeDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
EnumTypeName: enumType.GetName(),
OldEnumType: enumType,
})
}
for _, compositeType := range schemaMeta.GetCompositeTypes() {
if compositeType.GetSkipDump() {
continue
}
diff.CompositeTypeChanges = append(diff.CompositeTypeChanges, &schema.CompositeTypeDiff{
Action: schema.MetadataDiffActionDrop,
SchemaName: schemaName,
CompositeTypeName: compositeType.GetName(),
OldCompositeType: compositeType,
})
}
return diff
}
func writeExtensionDiff(out *strings.Builder, extensionDiff *schema.ExtensionDiff) error {
switch extensionDiff.Action {
case schema.MetadataDiffActionCreate:
return writeExtension(out, extensionDiff.NewExtension)
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "DROP EXTENSION \"%s\";\n\n", extensionDiff.ExtensionName)
return err
default:
return nil
}
}
func writeEventTriggerDiff(out *strings.Builder, eventTriggerDiff *schema.EventTriggerDiff) error {
switch eventTriggerDiff.Action {
case schema.MetadataDiffActionCreate, schema.MetadataDiffActionAlter:
return writeEventTrigger(out, eventTriggerDiff.NewEventTrigger)
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "DROP EVENT TRIGGER \"%s\";\n\n", eventTriggerDiff.EventTriggerName)
return err
default:
return nil
}
}
func writeSequenceDiff(out *strings.Builder, sequenceDiff *schema.SequenceDiff) error {
switch sequenceDiff.Action {
case schema.MetadataDiffActionCreate:
return writeCreateSequence(out, sequenceDiff.SchemaName, sequenceDiff.NewSequence)
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "DROP SEQUENCE \"%s\".\"%s\";\n\n", sequenceDiff.SchemaName, sequenceDiff.SequenceName)
return err
case schema.MetadataDiffActionAlter:
return writeAlterSequence(out, sequenceDiff.SchemaName, sequenceDiff.OldSequence, sequenceDiff.NewSequence)
default:
return nil
}
}
func writeAlterSequence(out *strings.Builder, schemaName string, oldSequence *metadatapb.SequenceMetadata, sequence *metadatapb.SequenceMetadata) error {
if _, err := fmt.Fprintf(out, "ALTER SEQUENCE \"%s\".\"%s\"", schemaName, sequence.Name); err != nil {
return err
}
if sequence.DataType != "" {
if _, err := fmt.Fprintf(out, "\n AS %s", sequence.DataType); err != nil {
return err
}
}
if sequence.Start != "" {
if _, err := fmt.Fprintf(out, "\n START WITH %s", sequence.Start); err != nil {
return err
}
}
if sequence.Increment != "" {
if _, err := fmt.Fprintf(out, "\n INCREMENT BY %s", sequence.Increment); err != nil {
return err
}
}
if sequence.MinValue != "" {
if _, err := fmt.Fprintf(out, "\n MINVALUE %s", sequence.MinValue); err != nil {
return err
}
}
if sequence.MaxValue != "" {
if _, err := fmt.Fprintf(out, "\n MAXVALUE %s", sequence.MaxValue); err != nil {
return err
}
}
if sequence.CacheSize != "" {
if _, err := fmt.Fprintf(out, "\n CACHE %s", sequence.CacheSize); err != nil {
return err
}
}
if sequence.Cycle {
if _, err := out.WriteString("\n CYCLE"); err != nil {
return err
}
} else if _, err := out.WriteString("\n NO CYCLE"); err != nil {
return err
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if oldSequence.GetComment() != sequence.GetComment() {
return writeComment(out, fmt.Sprintf("SEQUENCE \"%s\".\"%s\"", schemaName, sequence.Name), sequence.GetComment())
}
return nil
}
func writeAlterSequenceOwnedByNone(out *strings.Builder, schemaName, sequenceName string) error {
_, err := fmt.Fprintf(out, "ALTER SEQUENCE \"%s\".\"%s\" OWNED BY NONE;\n\n", schemaName, sequenceName)
return err
}
func writeFunctionDiff(out *strings.Builder, functionDiff *schema.FunctionDiff) error {
switch functionDiff.Action {
case schema.MetadataDiffActionCreate:
return writeFunctionStatement(out, functionDiff.SchemaName, functionDiff.NewFunction, false)
case schema.MetadataDiffActionAlter:
return writeFunctionStatement(out, functionDiff.SchemaName, functionDiff.NewFunction, true)
case schema.MetadataDiffActionDrop:
signature := functionDiff.FunctionName
if functionDiff.OldFunction.GetSignature() != "" {
signature = functionDiff.OldFunction.GetSignature()
}
return writeDropRoutine(out, "FUNCTION", functionDiff.SchemaName, signature)
default:
return nil
}
}
func writeFunctionStatement(out *strings.Builder, schemaName string, function *metadatapb.FunctionMetadata, replace bool) error {
definition := function.GetDefinition()
if replace {
definition = convertCreateFunctionToCreateOrReplace(definition)
}
if err := writeDefinitionStatement(out, definition); err != nil {
return err
}
if function.GetComment() != "" {
return writeFunctionComment(out, schemaName, function)
}
return nil
}
func convertCreateFunctionToCreateOrReplace(definition string) string {
trimmedLeft := strings.TrimLeft(definition, " \t\r\n")
prefixLen := len(definition) - len(trimmedLeft)
upper := strings.ToUpper(trimmedLeft)
if strings.HasPrefix(upper, "CREATE OR REPLACE FUNCTION") {
return definition
}
if strings.HasPrefix(upper, "CREATE FUNCTION") {
return definition[:prefixLen] + "CREATE OR REPLACE FUNCTION" + trimmedLeft[len("CREATE FUNCTION"):]
}
return definition
}
func writeProcedureDiff(out *strings.Builder, procedureDiff *schema.ProcedureDiff) error {
switch procedureDiff.Action {
case schema.MetadataDiffActionCreate:
return writeProcedure(out, procedureDiff.SchemaName, procedureDiff.NewProcedure)
case schema.MetadataDiffActionDrop:
signature := procedureDiff.ProcedureName
if procedureDiff.OldProcedure.GetSignature() != "" {
signature = procedureDiff.OldProcedure.GetSignature()
}
return writeDropRoutine(out, "PROCEDURE", procedureDiff.SchemaName, signature)
case schema.MetadataDiffActionAlter:
signature := procedureDiff.ProcedureName
if procedureDiff.OldProcedure.GetSignature() != "" {
signature = procedureDiff.OldProcedure.GetSignature()
}
if err := writeDropRoutine(out, "PROCEDURE", procedureDiff.SchemaName, signature); err != nil {
return err
}
return writeProcedure(out, procedureDiff.SchemaName, procedureDiff.NewProcedure)
default:
return nil
}
}
func writeProcedure(out *strings.Builder, schemaName string, procedure *metadatapb.ProcedureMetadata) error {
if err := writeDefinitionStatement(out, procedure.Definition); err != nil {
return err
}
if procedure.Comment != "" {
return writeRoutineComment(out, "PROCEDURE", schemaName, procedure.Signature, procedure.Name, procedure.Comment)
}
return nil
}
func writeDefinitionStatement(out *strings.Builder, definition string) error {
definition = strings.TrimSpace(definition)
if _, err := out.WriteString(definition); err != nil {
return err
}
if !strings.HasSuffix(definition, ";") {
if _, err := out.WriteString(";"); err != nil {
return err
}
}
_, err := out.WriteString("\n\n")
return err
}
func writeDropRoutine(out *strings.Builder, routineType, schemaName, signature string) error {
_, err := fmt.Fprintf(out, "DROP %s %s;\n\n", routineType, formatQualifiedRoutineSignature(schemaName, signature))
return err
}
func formatQualifiedRoutineSignature(schemaName, signature string) string {
signature = strings.TrimSpace(signature)
openParen := strings.Index(signature, "(")
if openParen < 0 {
return fmt.Sprintf("\"%s\".\"%s\"()", schemaName, strings.Trim(signature, `"`))
}
name := strings.TrimSpace(signature[:openParen])
if lastDot := strings.LastIndex(name, "."); lastDot >= 0 {
name = name[lastDot+1:]
}
name = strings.Trim(name, `"`)
return fmt.Sprintf("\"%s\".\"%s\"%s", schemaName, name, signature[openParen:])
}
func writeRoutineComment(out *strings.Builder, routineType, schemaName, signature, name, comment string) error {
if signature == "" {
signature = name + "()"
}
if _, err := fmt.Fprintf(out, "COMMENT ON %s %s IS '%s';\n\n", routineType, formatQualifiedRoutineSignature(schemaName, signature), escapeSingleQuote(comment)); err != nil {
return err
}
return nil
}
func writeEnumTypeDiff(out *strings.Builder, enumDiff *schema.EnumTypeDiff) error {
switch enumDiff.Action {
case schema.MetadataDiffActionCreate:
if err := writeEnum(out, enumDiff.SchemaName, enumDiff.NewEnumType); err != nil {
return err
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if enumDiff.NewEnumType.Comment != "" {
return writeEnumComment(out, enumDiff.SchemaName, enumDiff.NewEnumType)
}
return nil
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "DROP TYPE \"%s\".\"%s\";\n\n", enumDiff.SchemaName, enumDiff.EnumTypeName)
return err
case schema.MetadataDiffActionAlter:
return writeAlterEnumTypeAddValues(out, enumDiff.SchemaName, enumDiff.OldEnumType, enumDiff.NewEnumType)
default:
return nil
}
}
func writeAlterEnumTypeAddValues(out *strings.Builder, schemaName string, oldEnum *metadatapb.EnumTypeMetadata, newEnum *metadatapb.EnumTypeMetadata) error {
oldValueSet := make(map[string]bool)
for _, value := range oldEnum.GetValues() {
oldValueSet[value] = true
}
newValueSet := make(map[string]bool)
for _, value := range newEnum.GetValues() {
newValueSet[value] = true
}
var removedValues []string
for _, value := range oldEnum.GetValues() {
if !newValueSet[value] {
removedValues = append(removedValues, value)
}
}
if len(removedValues) > 0 {
if _, err := fmt.Fprintf(out, "-- WARNING: PostgreSQL does not support removing enum values from \"%s\".\"%s\": ", schemaName, newEnum.GetName()); err != nil {
return err
}
for i, value := range removedValues {
if i > 0 {
if _, err := out.WriteString(", "); err != nil {
return err
}
}
if _, err := fmt.Fprintf(out, "'%s'", escapeSingleQuote(value)); err != nil {
return err
}
}
if _, err := out.WriteString("\n\n"); err != nil {
return err
}
}
for i, newValue := range newEnum.GetValues() {
if oldValueSet[newValue] {
continue
}
if _, err := fmt.Fprintf(out, "ALTER TYPE \"%s\".\"%s\" ADD VALUE '%s'", schemaName, newEnum.GetName(), escapeSingleQuote(newValue)); err != nil {
return err
}
for j := i + 1; j < len(newEnum.GetValues()); j++ {
nextValue := newEnum.GetValues()[j]
if oldValueSet[nextValue] {
if _, err := fmt.Fprintf(out, " BEFORE '%s'", escapeSingleQuote(nextValue)); err != nil {
return err
}
break
}
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
}
if oldEnum.GetComment() != newEnum.GetComment() {
return writeComment(out, fmt.Sprintf("TYPE \"%s\".\"%s\"", schemaName, newEnum.GetName()), newEnum.GetComment())
}
return nil
}
// releasedCompositeAttributeKey identifies an old composite attribute whose
// releasing statement (DROP ATTRIBUTE or ALTER ATTRIBUTE ... TYPE) is emitted
// early in the drop phase because its old type references a dropped table's
// row type.
func releasedCompositeAttributeKey(schemaName, typeName, attributeName string) string {
return schemaName + "\x00" + typeName + "\x00" + attributeName
}
// Modes for a released composite attribute.
const (
releasedModeRetypeEarly = iota + 1
releasedModeDropReadd
)
// releasingCompositeAttributes finds old attributes of ALTERED composites
// whose type references a dropped table and which the alter removes or
// retypes — those statements must run before the table drop. When the
// retype's target type is itself created in this migration, the early
// statement is a DROP ATTRIBUTE and the create phase re-adds it.
func releasingCompositeAttributes(diff *schema.MetadataDiff) map[string]int {
droppedTables := make(map[string]bool)
createdTypes := make(map[string]bool)
for _, tableDiff := range diff.TableChanges {
switch tableDiff.Action {
case schema.MetadataDiffActionDrop:
droppedTables[tableDiff.SchemaName+"\x00"+tableDiff.TableName] = true
case schema.MetadataDiffActionCreate:
createdTypes[tableDiff.SchemaName+"\x00"+tableDiff.TableName] = true
default:
}
}
// Dropped views and materialized views provide row types too.
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop {
droppedTables[viewDiff.SchemaName+"\x00"+viewDiff.ViewName] = true
}
}
for _, viewDiff := range diff.MaterializedViewChanges {
if viewDiff.Action == schema.MetadataDiffActionDrop {
droppedTables[viewDiff.SchemaName+"\x00"+viewDiff.MaterializedViewName] = true
}
}
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action == schema.MetadataDiffActionCreate {
createdTypes[compositeDiff.SchemaName+"\x00"+compositeDiff.CompositeTypeName] = true
}
}
for _, viewDiff := range diff.ViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate {
createdTypes[viewDiff.SchemaName+"\x00"+viewDiff.ViewName] = true
}
}
for _, viewDiff := range diff.MaterializedViewChanges {
if viewDiff.Action == schema.MetadataDiffActionCreate {
createdTypes[viewDiff.SchemaName+"\x00"+viewDiff.MaterializedViewName] = true
}
}
released := make(map[string]int)
if len(droppedTables) == 0 {
return released
}
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action != schema.MetadataDiffActionAlter {
continue
}
newAttributes := make(map[string]*metadatapb.CompositeTypeAttribute)
for _, attribute := range compositeDiff.NewCompositeType.GetAttributes() {
newAttributes[attribute.Name] = attribute
}
for _, oldAttribute := range compositeDiff.OldCompositeType.GetAttributes() {
depSchema, depName, ok := parseQualifiedTypeIdent(oldAttribute.Type)
if !ok || !droppedTables[depSchema+"\x00"+depName] {
continue
}
newAttribute := newAttributes[oldAttribute.Name]
if newAttribute == nil || newAttribute.Type == oldAttribute.Type {
if newAttribute == nil {
released[releasedCompositeAttributeKey(compositeDiff.SchemaName, compositeDiff.CompositeTypeName, oldAttribute.Name)] = releasedModeRetypeEarly
}
continue
}
mode := releasedModeRetypeEarly
if newSchema, newName, ok := parseQualifiedTypeIdent(newAttribute.Type); ok && createdTypes[newSchema+"\x00"+newName] {
// The target type does not exist during the drop phase.
mode = releasedModeDropReadd
}
released[releasedCompositeAttributeKey(compositeDiff.SchemaName, compositeDiff.CompositeTypeName, oldAttribute.Name)] = mode
}
}
return released
}
// writeReleasingCompositeAlters emits, before the dependent-object graph,
// the alter statements that release dropped tables' row types. Residual
// corner: a dropped view over such a composite drops later inside the graph.
func writeReleasingCompositeAlters(out *strings.Builder, diff *schema.MetadataDiff) error {
released := releasingCompositeAttributes(diff)
if len(released) == 0 {
return nil
}
for _, compositeDiff := range diff.CompositeTypeChanges {
if compositeDiff.Action != schema.MetadataDiffActionAlter {
continue
}
newAttributes := make(map[string]*metadatapb.CompositeTypeAttribute)
for _, attribute := range compositeDiff.NewCompositeType.GetAttributes() {
newAttributes[attribute.Name] = attribute
}
for _, oldAttribute := range compositeDiff.OldCompositeType.GetAttributes() {
mode := released[releasedCompositeAttributeKey(compositeDiff.SchemaName, compositeDiff.CompositeTypeName, oldAttribute.Name)]
if mode == 0 {
continue
}
newAttribute := newAttributes[oldAttribute.Name]
if mode == releasedModeRetypeEarly && newAttribute != nil {
if err := writeAlterCompositeAttribute(out, compositeDiff.SchemaName, compositeDiff.CompositeTypeName, alterAttributeAction, newAttribute); err != nil {
return err
}
continue
}
// Attribute dropped outright, or its retype target is created
// only later in this migration: drop now, re-add in the create
// phase.
if _, err := fmt.Fprintf(out, "ALTER TYPE \"%s\".\"%s\" DROP ATTRIBUTE \"%s\";\n\n", compositeDiff.SchemaName, compositeDiff.CompositeTypeName, oldAttribute.Name); err != nil {
return err
}
}
}
return nil
}
func writeCompositeTypeDiff(out *strings.Builder, compositeDiff *schema.CompositeTypeDiff) error {
return writeCompositeTypeDiffWithReleased(out, compositeDiff, nil)
}
// writeCompositeTypeDiffWithReleased is writeCompositeTypeDiff for the alter
// path, skipping releasing statements already emitted in the drop phase.
func writeCompositeTypeDiffWithReleased(out *strings.Builder, compositeDiff *schema.CompositeTypeDiff, released map[string]int) error {
switch compositeDiff.Action {
case schema.MetadataDiffActionCreate:
if err := writeCompositeType(out, compositeDiff.SchemaName, compositeDiff.NewCompositeType); err != nil {
return err
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if compositeTypeHasComments(compositeDiff.NewCompositeType) {
return writeCompositeTypeComments(out, compositeDiff.SchemaName, compositeDiff.NewCompositeType)
}
return nil
case schema.MetadataDiffActionDrop:
_, err := fmt.Fprintf(out, "DROP TYPE \"%s\".\"%s\";\n\n", compositeDiff.SchemaName, compositeDiff.CompositeTypeName)
return err
case schema.MetadataDiffActionAlter:
return writeAlterCompositeType(out, compositeDiff.SchemaName, compositeDiff.OldCompositeType, compositeDiff.NewCompositeType, released)
default:
return nil
}
}
// alterAttributeAction is the writeAlterCompositeAttribute action that emits
// ALTER ATTRIBUTE ... TYPE rather than ADD ATTRIBUTE.
const alterAttributeAction = "ALTER ATTRIBUTE"
// writeAlterCompositeType diffs attributes by name and emits real DDL.
// ALTER ATTRIBUTE ... TYPE fails at execution when any table column uses the
// type — PostgreSQL has no online path for that change; the reviewer sees the
// statement and the executor surfaces the error. Attribute reordering has no
// DDL at all and only produces a warning comment.
func writeAlterCompositeType(out *strings.Builder, schemaName string, oldComposite, newComposite *metadatapb.CompositeTypeMetadata, released map[string]int) error {
oldAttributeMap := make(map[string]*metadatapb.CompositeTypeAttribute)
for _, attribute := range oldComposite.GetAttributes() {
oldAttributeMap[attribute.Name] = attribute
}
newAttributeMap := make(map[string]*metadatapb.CompositeTypeAttribute)
for _, attribute := range newComposite.GetAttributes() {
newAttributeMap[attribute.Name] = attribute
}
// Dropped attributes, in old declaration order. Dropping an attribute
// discards its data in every value of the type.
for _, oldAttribute := range oldComposite.GetAttributes() {
if released[releasedCompositeAttributeKey(schemaName, newComposite.GetName(), oldAttribute.Name)] != 0 {
continue
}
if _, exists := newAttributeMap[oldAttribute.Name]; !exists {
if _, err := fmt.Fprintf(out, "ALTER TYPE \"%s\".\"%s\" DROP ATTRIBUTE \"%s\";\n\n", schemaName, newComposite.GetName(), oldAttribute.Name); err != nil {
return err
}
}
}
// Added attributes, in new declaration order.
for _, newAttribute := range newComposite.GetAttributes() {
if _, exists := oldAttributeMap[newAttribute.Name]; !exists {
if err := writeAlterCompositeAttribute(out, schemaName, newComposite.GetName(), "ADD ATTRIBUTE", newAttribute); err != nil {
return err
}
}
}
// Attributes whose type or collation changed.
for _, newAttribute := range newComposite.GetAttributes() {
oldAttribute, exists := oldAttributeMap[newAttribute.Name]
if !exists {
continue
}
mode := released[releasedCompositeAttributeKey(schemaName, newComposite.GetName(), newAttribute.Name)]
if mode == releasedModeDropReadd {
// The early drop-phase statement removed the attribute; re-add
// it now that its target type exists.
if err := writeAlterCompositeAttribute(out, schemaName, newComposite.GetName(), "ADD ATTRIBUTE", newAttribute); err != nil {
return err
}
continue
}
if mode != 0 {
continue
}
if oldAttribute.Type != newAttribute.Type || oldAttribute.Collation != newAttribute.Collation {
if err := writeAlterCompositeAttribute(out, schemaName, newComposite.GetName(), alterAttributeAction, newAttribute); err != nil {
return err
}
}
}
if compositeAttributesReordered(oldComposite, newComposite) {
if _, err := fmt.Fprintf(out, "-- WARNING: PostgreSQL does not support reordering the attributes of \"%s\".\"%s\"\n\n", schemaName, newComposite.GetName()); err != nil {
return err
}
}
if oldComposite.GetComment() != newComposite.GetComment() {
if err := writeComment(out, fmt.Sprintf("TYPE \"%s\".\"%s\"", schemaName, newComposite.GetName()), newComposite.GetComment()); err != nil {
return err
}
}
for _, newAttribute := range newComposite.GetAttributes() {
oldAttribute, exists := oldAttributeMap[newAttribute.Name]
oldComment := ""
if exists {
oldComment = oldAttribute.Comment
}
// Comments of added attributes are covered here as well: ADD ATTRIBUTE
// carries no comment syntax.
if oldComment != newAttribute.Comment {
if err := writeComment(out, fmt.Sprintf("COLUMN \"%s\".\"%s\".\"%s\"", schemaName, newComposite.GetName(), newAttribute.Name), newAttribute.Comment); err != nil {
return err
}
}
}
return nil
}
func writeAlterCompositeAttribute(out *strings.Builder, schemaName, typeName, action string, attribute *metadatapb.CompositeTypeAttribute) error {
typeClause := attribute.Type
if action == alterAttributeAction {
typeClause = "TYPE " + attribute.Type
}
if _, err := fmt.Fprintf(out, "ALTER TYPE \"%s\".\"%s\" %s \"%s\" %s", schemaName, typeName, action, attribute.Name, typeClause); err != nil {
return err
}
// The collation is stored as an emit-ready identifier reference
// (quoted as needed, schema-qualified outside pg_catalog).
if attribute.Collation != "" {
if _, err := fmt.Fprintf(out, " COLLATE %s", attribute.Collation); err != nil {
return err
}
}
_, err := out.WriteString(";\n\n")
return err
}
// compositeAttributesReordered reports whether the attribute order ALTER TYPE
// can achieve differs from the target order. Surviving attributes keep their
// old relative positions and ADD ATTRIBUTE always appends at the end, so any
// target that deviates from that achievable order — including an attribute
// inserted in the middle — is unreachable and warrants the warning.
func compositeAttributesReordered(oldComposite, newComposite *metadatapb.CompositeTypeMetadata) bool {
oldNames := make(map[string]bool)
for _, attribute := range oldComposite.GetAttributes() {
oldNames[attribute.Name] = true
}
newNames := make(map[string]bool)
for _, attribute := range newComposite.GetAttributes() {
newNames[attribute.Name] = true
}
var achieved []string
for _, attribute := range oldComposite.GetAttributes() {
if newNames[attribute.Name] {
achieved = append(achieved, attribute.Name)
}
}
for _, attribute := range newComposite.GetAttributes() {
if !oldNames[attribute.Name] {
achieved = append(achieved, attribute.Name)
}
}
var target []string
for _, attribute := range newComposite.GetAttributes() {
target = append(target, attribute.Name)
}
return !slices.Equal(achieved, target)
}
func writeViewDiff(out *strings.Builder, viewDiff *schema.ViewDiff) error {
switch viewDiff.Action {
case schema.MetadataDiffActionCreate:
if err := writeViewSDL(out, viewDiff.SchemaName, viewDiff.NewView); err != nil {
return err
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if viewDiff.NewView.GetComment() != "" {
return writeComment(out, fmt.Sprintf(viewCommentObjectFormat, viewDiff.SchemaName, viewDiff.ViewName), viewDiff.NewView.GetComment())
}
return nil
case schema.MetadataDiffActionAlter:
definition := strings.TrimSuffix(strings.TrimSpace(viewDiff.NewView.Definition), ";")
if _, err := fmt.Fprintf(out, "CREATE OR REPLACE VIEW \"%s\".\"%s\" AS %s", viewDiff.SchemaName, viewDiff.ViewName, definition); err != nil {
return err
}
if _, err := out.WriteString(";\n\n"); err != nil {
return err
}
if viewDiff.NewView.GetComment() != "" {
return writeComment(out, fmt.Sprintf(viewCommentObjectFormat, viewDiff.SchemaName, viewDiff.ViewName), viewDiff.NewView.GetComment())
}
return nil
case schema.MetadataDiffActionDrop:
return writeDropView(out, viewDiff.SchemaName, viewDiff.ViewName)
default:
return nil
}
}
func writeDropView(out *strings.Builder, schemaName, viewName string) error {
_, err := fmt.Fprintf(out, "DROP VIEW \"%s\".\"%s\";\n\n", schemaName, viewName)
return err
}
func writeCreateMaterializedViewDiff(out *strings.Builder, viewDiff *schema.MaterializedViewDiff) error {
return writeMaterializedView(out, viewDiff.SchemaName, viewDiff.NewMaterializedView)
}
func writeDropMaterializedView(out *strings.Builder, schemaName, viewName string) error {
_, err := fmt.Fprintf(out, "DROP MATERIALIZED VIEW \"%s\".\"%s\";\n\n", schemaName, viewName)
return err
}