3 ms·

same situation as with any interface, you either break backwards compatibility or you don't - this is not a problem for databases to solve

by gfody 7y ago

mjw1007 7y ago
The situation I'm thinking of (and I think janpot is) is where your stored procedure or function isn't intended to be a shared interface, but a part of what is notionally your client's code that you happen to want to run server-side. DO can be good for this, but it doesn't cover all cases: sometimes you want a named function to use in an expression, and sometimes you want multiple procedures which can call each other. Just having CREATE [OR REPLACE] TEMPORARY FUNCTION would be pleasant.
- jeltz 7y ago
  You can create temporary functions in PostgreSQL, but I have not checked the performance of using them (I think they could theoretically bloat the catalog, but I only use them in migrations). Just do something like the below: CREATE FUNCTION pg_temp.f(int, int) RETURNS int LANGUAGE sql AS $$ SELECT $1 + $2 $$; SELECT $1 + $2 $$;SELECT pg_temp.f(1 , 3); f --- 4 (1 row)
  - mjw1007 7y ago
    This is in fact what I do. I'm agreeing with janpot that a richer model would be pleasant. (In practice, when I've wanted this sort of thing it's been good enough to either install all the functions the client might want to use whenever it makes a connection, or else to use CREATE OR REPLACE FUNCTION every time it's going to run a query needing that function. But I can imagine cases where neither of those would be efficient enough and I'd end up having the client track what it's already loaded. Some help from the server would be good there.)
  - janpot 7y ago
    Yep, I just found out about temporary functions today. Gonna start experimenting with that.
setr 7y ago
and when you do break backwards compatibility, you version. Now how is versioning managed? The database can easily fill in here with a standardized approach, with supporting tooling. Or it can not, in which case you come up with an adhoc versioning scheme that you maintain manually. Same way you do in all computing scenarios when you don't have any versioning tool available to you: AGREEMENT_v1.doc AGREEMENT_v2.doc AGREEMENT_v2_revised.doc AGREMEENT_v2_Revision2.doc AGREEMENT_v3.doc AGREEMENT_v3_fixed.doc or perhaps you start doing some strange behaviors to force other tools to fit in, like attempting to version your schema/functions with git, by tying it to a single application's ORM or perhaps you stop touching the database directly, and try to make it all work with migration-scripts, and it all falls apart when a second application comes into play or perhaps you try to force coordination between all dependent applications and your database, and make this update simultaneously or perhaps you convince the RDB management system to manage your system
- sixbrx 7y ago
  Putting the functions in a separate schema (ie namespace) per version will prevent having to name mangle the function names themselves. Then you just manage the schema names for versioning and adjusting the search path can make it work implicitly with the proper version if that's wanted.

same situation as with any interface, you either break backwards compatibility or you don't - this is not a problem for databases to solve

by gfody 7y ago

same situation as with any interface, you either break backwards compatibility or you don't - this is not a problem for databases to solve

mjw1007 7y ago

The situation I'm thinking of (and I think janpot is) is where your stored procedure or function isn't intended to be a shared interface, but a part of what is notionally your client's code that you happen to want to run server-side. DO can be good for this, but it doesn't cover all cases: sometimes you want a named function to use in an expression, and sometimes you want multiple procedures which can call each other. Just having CREATE [OR REPLACE] TEMPORARY FUNCTION would be pleasant.

jeltz 7y ago
You can create temporary functions in PostgreSQL, but I have not checked the performance of using them (I think they could theoretically bloat the catalog, but I only use them in migrations). Just do something like the below: CREATE FUNCTION pg_temp.f(int, int) RETURNS int LANGUAGE sql AS $$ SELECT $1 + $2 $$; SELECT $1 + $2 $$;SELECT pg_temp.f(1 , 3); f --- 4 (1 row)
- mjw1007 7y ago
  This is in fact what I do. I'm agreeing with janpot that a richer model would be pleasant. (In practice, when I've wanted this sort of thing it's been good enough to either install all the functions the client might want to use whenever it makes a connection, or else to use CREATE OR REPLACE FUNCTION every time it's going to run a query needing that function. But I can imagine cases where neither of those would be efficient enough and I'd end up having the client track what it's already loaded. Some help from the server would be good there.)
- janpot 7y ago
  Yep, I just found out about temporary functions today. Gonna start experimenting with that.

setr 7y ago

and when you do break backwards compatibility, you version. Now how is versioning managed? The database can easily fill in here with a standardized approach, with supporting tooling. Or it can not, in which case you come up with an adhoc versioning scheme that you maintain manually. Same way you do in all computing scenarios when you don't have any versioning tool available to you: AGREEMENT_v1.doc AGREEMENT_v2.doc AGREEMENT_v2_revised.doc AGREMEENT_v2_Revision2.doc AGREEMENT_v3.doc AGREEMENT_v3_fixed.doc or perhaps you start doing some strange behaviors to force other tools to fit in, like attempting to version your schema/functions with git, by tying it to a single application's ORM or perhaps you stop touching the database directly, and try to make it all work with migration-scripts, and it all falls apart when a second application comes into play or perhaps you try to force coordination between all dependent applications and your database, and make this update simultaneously or perhaps you convince the RDB management system to manage your system

sixbrx 7y ago
Putting the functions in a separate schema (ie namespace) per version will prevent having to name mangle the function names themselves. Then you just manage the schema names for versioning and adjusting the search path can make it work implicitly with the proper version if that's wanted.