3 ms·

You can't use `CREATE OR REPLACE FUNCTION` if function's signature changed.

by _pctq 10y ago

pritambaral 10y ago
Would `DROP FUNCTION IF EXISTS ...; CREATE FUNCTION ...;` be satisfactory?
- oelmekki 10y ago
  It is, provided you pass to `DROP FUNCTION` the exact previous signature of your function. Then, if you want to be able to revert your change, you also need to write a down migration, having a `DROP FUNCTION` with the exact same signature than your new function, and a `CREATE FUNCTION` that recreates the old one. This is exactly the pain point my root comment describes and which pgrebase solves :)
  - oelmekki 10y ago
    It's clear from this thread that the point of pgrebase may not be understood if you don't play often with functions, so let me provide a bit of context about what comes before pgrebase. Why do we use migration tools? Data is the most important thing for a business. You can loose your code and recreate it. If you loose your data, your business is basically done. Yet, your code often needs change in your database structure. So you write sql in files to describe those changes, because we can't just drop all tables and recreate them on each deploy, we have to preserve data. It is not really efficient to "just drop sql files in your codebase", because database does not live in that codebase, and you can't be sure databases on all your installations, locals and servers, are up to date. Plus, you can't just load those files in any order, there is an order to be respected. For that reason, we use timestamped up migrations files. But then, it means that full code related to a given table does not live in a specific and dedicated file. Should you want to revert the change (especially common in dev env), how would you do? For that reason, next to up migration files, we have down migration files. All of this works just fine, but it has ultimately a single purpose: syncing schema without loosing data. If you want to manage functions in that context, it will be painful. Postgresql won't allow you to replace a function if its signature doesn't exactly match. That is, you can't change a function signature, you have to first drop it, then recreate it. With migrations, this means that when you're migrating up, you need to drop the old function and create the new one ; and when you're migrating down, you have to drop the new function and recreate the old one. Remember of how the purpose of this is to not loose data? Well, you can drop all your functions and recreate them, you won't lose any data. This means you can store them in dedicated files, which means you can revert files individually. So, this is what pgrebase is doing : managing all your non data altering pg code so that it's not needlessly painful to use.
  - pritambaral 10y ago
    Ah, I see.

You can't use `CREATE OR REPLACE FUNCTION` if function's signature changed.

by _pctq 10y ago

You can't use `CREATE OR REPLACE FUNCTION` if function's signature changed.

pritambaral 10y ago

Would `DROP FUNCTION IF EXISTS ...; CREATE FUNCTION ...;` be satisfactory?

oelmekki 10y ago
It is, provided you pass to `DROP FUNCTION` the exact previous signature of your function. Then, if you want to be able to revert your change, you also need to write a down migration, having a `DROP FUNCTION` with the exact same signature than your new function, and a `CREATE FUNCTION` that recreates the old one. This is exactly the pain point my root comment describes and which pgrebase solves :)
- oelmekki 10y ago
  It's clear from this thread that the point of pgrebase may not be understood if you don't play often with functions, so let me provide a bit of context about what comes before pgrebase. Why do we use migration tools? Data is the most important thing for a business. You can loose your code and recreate it. If you loose your data, your business is basically done. Yet, your code often needs change in your database structure. So you write sql in files to describe those changes, because we can't just drop all tables and recreate them on each deploy, we have to preserve data. It is not really efficient to "just drop sql files in your codebase", because database does not live in that codebase, and you can't be sure databases on all your installations, locals and servers, are up to date. Plus, you can't just load those files in any order, there is an order to be respected. For that reason, we use timestamped up migrations files. But then, it means that full code related to a given table does not live in a specific and dedicated file. Should you want to revert the change (especially common in dev env), how would you do? For that reason, next to up migration files, we have down migration files. All of this works just fine, but it has ultimately a single purpose: syncing schema without loosing data. If you want to manage functions in that context, it will be painful. Postgresql won't allow you to replace a function if its signature doesn't exactly match. That is, you can't change a function signature, you have to first drop it, then recreate it. With migrations, this means that when you're migrating up, you need to drop the old function and create the new one ; and when you're migrating down, you have to drop the new function and recreate the old one. Remember of how the purpose of this is to not loose data? Well, you can drop all your functions and recreate them, you won't lose any data. This means you can store them in dedicated files, which means you can revert files individually. So, this is what pgrebase is doing : managing all your non data altering pg code so that it's not needlessly painful to use.
- pritambaral 10y ago
  Ah, I see.