3 ms·

I think this is interesting and starting down a better path than migrations; there are other tools which similarly keep schema files organized in a structural w

I think this is interesting and starting down a better path than migrations; there are other tools which similarly keep schema files organized in a structural way rather than as a timeline... a much superior approach. Having said that, the dependency management here will have to get better before I could consider it. You can specify dependencies for files using require statement, provided those files are of the same kind. That is, function files can specify dependencies on other function files, type files can define dependencies on other type files, etc. This alone is a show stopper for me. Say I have a table (ignore formatting and other formalities): CREATE TABLE test_table (id uuid primary key, test_val text, test_val_num numeric); Now I need a PL/pgSQL function as so.... CREATE FUNCTION testfunc() RETURNS VOID AS $BODY$ DECLARE myTestRec test_table; BEGIN [do stuff] END; $BODY$ My function declares the myTestRec variable to be of type test_table which means for the create function statement to work, the test_table table must exist first. But I cannot, according to the documentation, express or depend that the dependency will be recognized and enforced. Lack of more fluid dependency management would be a show stopper for me. I like that this tool is implemented in Go; the tool that I use which has this sort of approach is Sqitch (http://sqitch.org/ http://sqitch.org/) and I hate the perl dependencies that need to be fulfilled to get it to install. But couldn't leave it for something like this until a greater number of scenarios are supported. [Edit for clarity]

oelmekki 10y ago
Hi sbuttgereit, You're totally right that the dependency graph handler is extremely crude, I wrote it in one evening because I had functions depending on other functions and this caused my import to fail. The dependency statements only exist to fix that, for now, and that's indeed where I think there'll be the most work to do. You're also right that you still need a migration tool to manage tables schema. I did not intend to replace migration tools, actually, I'm satisfied with them as far as dropping/creating table and adding/removing columns are concerned. The focus of PgRebase is to manage pure non data altering code, which you may edit a lot and this is what migration tools are not good at. The codebase managed is flushed and recreated at will, I didn't want to manage anything that would risk data loss. During deployment, pgrebase is expected to be called after migrations: first you migrate your data, then pgrebase regenerate the non data code. Something that I probably should add in the documentation: the fact that a code kind can only require an other code kind should not be a problem because of the order they are processed. First, pgrebase processes all custom types, then views, then functions, then triggers. Usually, triggers depends on functions (thus, they're already there), functions can use views or types (already there), etc. The rule of thumb is: if it's something you're editing a lot and that can be dropped and created without data loss, then its place is in pgrebase.
- sbuttgereit 10y ago
  Check out the Sqitch tool I mentioned: it's not a migrations tool, it's very much the sort of approach you're taking, but it is more complete and mature; it's solved some problems you haven't yet. Yes, tables require special handling, but still doable in an idempotent fashion in a single "table" script using anonymous blocks. Also remember in PostgreSQL, views and tables are de facto custom types which are created at table/view create time implicitly; not at custom type create time. If PgRebase processes object kinds strictly in the order you say: what you are really saying is that PgRebase can only support a certain subset of PostgreSQL database designs. An example would be if I have a table where a column is DEFAULT myfunction('default_cust_type'), there I need the function before the table... if I have a view which retrieves a the output of a function (maybe at the top in a CTE)... now I need the function before the view... if that function also relies on table_a as a type, I need table_a -> function -> view... you can't just assume that the object types are always in a certain order and it will be all OK. As a design decision... that won't work for a number of databases. I would strongly advise you to build your dependency model on file references rather than on PostgreSQL object kinds. This way you don't need to worry about what objects are permitted to depend on another (since this can be quite fluid) and rely on the user of your tool to manage the dependencies. With that approach you don't loose the structural file organization that you are aiming for now while allowing database developers to be in control of the deployment process.
  - oelmekki 10y ago
    Yes, having a better dependency resolver is something that will happen, eventually. Edge cases will have to wait for that :) (the workaround, obviously, is to use classic migration tools for those, since you still need a migration tool anyway).

oelmekki 10y ago

Hi sbuttgereit, You're totally right that the dependency graph handler is extremely crude, I wrote it in one evening because I had functions depending on other functions and this caused my import to fail. The dependency statements only exist to fix that, for now, and that's indeed where I think there'll be the most work to do. You're also right that you still need a migration tool to manage tables schema. I did not intend to replace migration tools, actually, I'm satisfied with them as far as dropping/creating table and adding/removing columns are concerned. The focus of PgRebase is to manage pure non data altering code, which you may edit a lot and this is what migration tools are not good at. The codebase managed is flushed and recreated at will, I didn't want to manage anything that would risk data loss. During deployment, pgrebase is expected to be called after migrations: first you migrate your data, then pgrebase regenerate the non data code. Something that I probably should add in the documentation: the fact that a code kind can only require an other code kind should not be a problem because of the order they are processed. First, pgrebase processes all custom types, then views, then functions, then triggers. Usually, triggers depends on functions (thus, they're already there), functions can use views or types (already there), etc. The rule of thumb is: if it's something you're editing a lot and that can be dropped and created without data loss, then its place is in pgrebase.

sbuttgereit 10y ago
Check out the Sqitch tool I mentioned: it's not a migrations tool, it's very much the sort of approach you're taking, but it is more complete and mature; it's solved some problems you haven't yet. Yes, tables require special handling, but still doable in an idempotent fashion in a single "table" script using anonymous blocks. Also remember in PostgreSQL, views and tables are de facto custom types which are created at table/view create time implicitly; not at custom type create time. If PgRebase processes object kinds strictly in the order you say: what you are really saying is that PgRebase can only support a certain subset of PostgreSQL database designs. An example would be if I have a table where a column is DEFAULT myfunction('default_cust_type'), there I need the function before the table... if I have a view which retrieves a the output of a function (maybe at the top in a CTE)... now I need the function before the view... if that function also relies on table_a as a type, I need table_a -> function -> view... you can't just assume that the object types are always in a certain order and it will be all OK. As a design decision... that won't work for a number of databases. I would strongly advise you to build your dependency model on file references rather than on PostgreSQL object kinds. This way you don't need to worry about what objects are permitted to depend on another (since this can be quite fluid) and rely on the user of your tool to manage the dependencies. With that approach you don't loose the structural file organization that you are aiming for now while allowing database developers to be in control of the deployment process.
- oelmekki 10y ago
  Yes, having a better dependency resolver is something that will happen, eventually. Edge cases will have to wait for that :) (the workaround, obviously, is to use classic migration tools for those, since you still need a migration tool anyway).