Skip to content

Normalize constraint options to prevent differences between pgquery and generic parser#1142

Closed
wreulicke wants to merge 4 commits intosqldef:masterfrom
wreulicke:normalize-constraint-options
Closed

Normalize constraint options to prevent differences between pgquery and generic parser#1142
wreulicke wants to merge 4 commits intosqldef:masterfrom
wreulicke:normalize-constraint-options

Conversation

@wreulicke
Copy link
Contributor

@wreulicke wreulicke commented Feb 26, 2026

There are trivial parse result differences between pgquery and generic parser.
This causes unnecessary migrations around constraints by psqldef.
This PR fixes this behavior.

The generic parser does not support CREATE INDEX IF NOT EXISTS because it is not supported in MySQL.
Therefore, we should not change parser.y I think.

Reproduction

psqdef 3.5.0 or higher can reproduce this inconsistency.
Just apply psqldef with following DDL twice.

    CREATE TABLE users (
        user_id uuid PRIMARY KEY
    );
    CREATE TABLE user_profiles (
        profile_id uuid PRIMARY KEY,
        user_id uuid,
        status VARCHAR(255)
    );
    CREATE INDEX IF NOT EXISTS idx_user_profiles_status ON user_profiles (status); -- trigger pgquery fallback by `IF NOT EXISTS`
    ALTER TABLE user_profiles ADD CONSTRAINT fk_user_profiles_user
            FOREIGN KEY(user_id) REFERENCES users(user_id) ON UPDATE NO ACTION ON DELETE RESTRICT;

current behavior

psqdef will apply following changes

            ALTER TABLE "public"."user_profiles" DROP CONSTRAINT "fk_user_profiles_user";
            ALTER TABLE user_profiles ADD CONSTRAINT fk_user_profiles_user
                    FOREIGN KEY(user_id) REFERENCES users(user_id) ON UPDATE NO ACTION ON DELETE RESTRICT;

expected behavior

even if contains IF NOT EXISTS
psqldef will apply no changes.

wreulicke
wreulicke commented Feb 26, 2026
View reviewed changes
schema/generator.go
Comment on lines +5304 to +5306
if constraintOptions == nil {
return false, false
}
Copy link
Contributor Author

@wreulicke wreulicke Feb 26, 2026 •
edited
Loading

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Sorry, I am not sure that this assumption is correct.

@codecov
Copy link

codecov bot commented Feb 26, 2026 •
edited
Loading

Codecov Report

❌ Patch coverage is 85.71429% with 2 lines in your changes missing coverage. Please review.

Files with missing lines Patch % Lines
schema/generator.go 85.71% 1 Missing and 1 partial ⚠️
Files with missing lines Coverage Δ
schema/generator.go 81.15% <85.71%> (+0.13%) ⬆️
🚀 New features to boost your workflow:
  • ❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.

wreulicke
wreulicke commented Feb 26, 2026
View reviewed changes
cmd/psqldef/psqldef_test.go
})
}

func TestPsqldefConstraintWithPgqueryFallback(t *testing.T) {
Copy link
Contributor Author

@wreulicke wreulicke Feb 26, 2026

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I cannot add a test that contains pgquery fallback in tests.yaml.
I moved this from yaml.
ed86b23

@wreulicke
Copy link
Contributor Author

wreulicke commented Mar 2, 2026 •
edited
Loading

@gfx @k0kubun
Could you check this PR?

gfx added a commit that referenced this pull request Mar 4, 2026
@gfx @claude
Support CREATE INDEX IF NOT EXISTS in generic parser (#1142)
eb9e5db 
The generic parser didn't support CREATE INDEX IF NOT EXISTS, causing
psqldef to fall back to the pgquery parser. This led to unnecessary
foreign key constraint migrations due to differing constraintOptions
representations between the two parsers (nil vs {false, false}).

Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
@gfx gfx mentioned this pull request Mar 4, 2026
Merged
github-merge-queue bot pushed a commit that referenced this pull request Mar 4, 2026
@gfx @claude
Support CREATE INDEX IF NOT EXISTS in generic parser (#1142) (#1146)
0bb0d54 
The generic parser didn't support CREATE INDEX IF NOT EXISTS, causing
psqldef to fall back to the pgquery parser. This led to unnecessary
foreign key constraint migrations due to differing constraintOptions
representations between the two parsers (nil vs {false, false}).

Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
@gfx gfx closed this in #1146 Mar 4, 2026
@sqldef-bot sqldef-bot bot mentioned this pull request Mar 4, 2026
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

1 participant

@wreulicke