The problem
Because of session settings, migrations do not compose well. Suppose there are two migrations:
Mig A:
Mig B:
The ownership of sometbl will differ if migration B is applied in the same transaction as Mig A, compared to them being applied in two distinct batches.
This is a huge risk, since deployments to other environments can in some cases have both migrations and in others not, leading to different schemas.
The same thing is true of any session or transaction level setting; it's not just roles.
A possible solution
One possible solution is for codd to run RESET ALL; RESET ROLE after each migration. Does this reset SET SESSION AUTHORIZATION too? That should also be reset.
Does it work, though?
- Does it matter if we're in a transaction or not?
- Are there other things that can affect migration composability other than session settings?
- Is it possible, for instance, to update
pg_settings and change reset values? Or maybe just ALTER DATABASE ... SET .. is sufficient for that?
- What is the expectation of users? What mental model do they apply instinctively when looking at codd?
- This might not matter much unless it's a landslide victory for the mental model where users expect
SET .. to have effects on other migrations. Making sure different developers working in the same codebase write migrations without fear of affecting others or even accidentally forgetting SET .. can affect others seems more important.
I need to read https://www.postgresql.org/docs/13/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION and run experiments before deciding.
The problem
Because of session settings, migrations do not compose well. Suppose there are two migrations:
Mig A:
SET ROLE otherrole;Mig B:
The ownership of
sometblwill differ if migration B is applied in the same transaction as Mig A, compared to them being applied in two distinct batches.This is a huge risk, since deployments to other environments can in some cases have both migrations and in others not, leading to different schemas.
The same thing is true of any session or transaction level setting; it's not just roles.
A possible solution
One possible solution is for codd to run
RESET ALL; RESET ROLEafter each migration. Does this resetSET SESSION AUTHORIZATIONtoo? That should also be reset.Does it work, though?
pg_settingsand change reset values? Or maybe justALTER DATABASE ... SET ..is sufficient for that?SET ..to have effects on other migrations. Making sure different developers working in the same codebase write migrations without fear of affecting others or even accidentally forgettingSET ..can affect others seems more important.I need to read https://www.postgresql.org/docs/13/config-setting.html#CONFIG-SETTING-SQL-COMMAND-INTERACTION and run experiments before deciding.