xataio / pgroll

PostgreSQL zero-downtime migrations made easy
https://www.xata.io
Apache License 2.0
3.56k stars 67 forks source link

Fix duplicate inferred migrations when dropping columns outside of a migration #305

Closed andrew-farries closed 8 months ago

andrew-farries commented 8 months ago

Ensure that only one inferred migration is created in the pgroll.migrations table when a column is dropped outside of a migration.

From the Postgres docs:

The sql_drop event occurs just before the ddl_command_end event trigger for any operation that drops database objects

This means that when the raw_migration function is run in response to sql_drop and ddl_command_end, duplicate entries will be created in pgroll.migrations; once as the function is run for sql_drop and again when it's run for ddl_command_end.

Change the definition of the pg_roll_handle_drop event trigger to only run on those kinds of drops that won't result in duplicates when the pg_roll_handle_ddl trigger runs for the same change. DROP TABLE and DROP VIEW won't result in duplicate migrations because their schema can't be inferred by the ddl_command_event trigger because the object has already been dropped when the trigger runs.

Update the inferred migration tests with two new testcases covering dropping tables and columns.

Fixes #304