search

xataio / pgroll

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

Support for migrating triggers #352

Open hillac opened 5 months ago

hillac commented 5 months ago

Will there be support for migrating triggers? Also, can pre-existing or new triggers interfere at all with the migration process?

andrew-farries commented 5 months ago

pgroll doesn't currently support migrating triggers. It's not something that we currently have plans to add. The full list of supported operations is in the docs. There is always the 'escape hatch' of using a raw SQL migration however, which can be used to create whatever kind of database object you need.

During a migration that alters an existing column or adds a new one, pgroll installs up to two triggers on the affected table, named according to this pattern:

 _pgroll_trigger_<table-name>_<column-name> BEFORE INSERT OR UPDATE ON <table> FOR EACH ROW EXECUTE FUNCTION _pgroll_trigger_<table-name>_<column-name>()

In the presence of another BEFORE INSERT OR UPDATE trigger on the same table, Postgres will run the triggers in alphabetical order by trigger name, so interaction between triggers on the same table is possible. If this becomes an issue we may need a way to control the naming of pgroll triggers.