5 ms·

I wonder, could you describe your use-case a little more? I'm curious why you couldn't write a stored procedure, then call that from your application? If you

by zxexz 7y ago

I wonder, could you describe your use-case a little more? I'm curious why you couldn't write a stored procedure, then call that from your application? If you need to see if your function exists, you can do something like this [0]. Also using pg_proc, you can get the DDL of your function e.g. [1] SELECT prosrc FROM pg_proc WHERE proname = 'function_name'; And you could then check for equality of the source (or its hash), if you are tied to your current application logic [0] https://stackoverflow.com/questions/24773603/how-to-find-if-a-function-exists-in-postgresql https://stackoverflow.com/questions/24773603/how-to-find-if-... [1] https://stackoverflow.com/questions/3524859/how-to-display-full-stored-procedure-code https://stackoverflow.com/questions/3524859/how-to-display-f...

mjw1007 7y ago
If you do it that way, you also end up having to rename your function every time it changes, if it's possible that older clients are still running. And if you're tidy-minded you're going to spend effort building a system for removing old versions when they're not going to be needed any more. It's all doable, but it's fiddly. Explicit support for this sort of setup would be a good thing.
- gfody 7y ago
  of all the reasons a sproc may need to change I can't imagine one where you want to keep the old version around while also giving the new version the same name as the old
  - setr 7y ago
    The output is slightly different, but the semantic behavior remains the same? Databases and applications update independently, so versioning is required. If the database offers nothing for this, then an adhoc solution will generally be found (eg suffixing with _v1, _v2, etc), but that's not at all ideal
    - 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.
  - derefr 7y ago
    You can't imagine multiple client applications of different versions attempting to install, and then interact with, different versions of the same stored-proc API abstracting away (variously-polyfilled views + triggers upon) a single shared underlying dataset? I.e. a world where "FooClient v1.1.0" wants to interact with "DB schema Core-1/FooClient-v1.1.0", while allowing "FooClient v1.0.0" to still see "DB schema Core-1/FooClient-v1.0.0", and "BarClient v1.0.0" to see an entirely separate "DB schema Core-1/BarClient-v1.0.0"? (I mean, sure, you could have each client construct its "API layer" of sprocs et al in its own client-specific schema, or worse yet, as its own connection-specific temporary objects—but that's pretty dang inefficient when tons of different clients are sharing the same few API versions, and when the different API versions share identical copies of 99% of their sprocs.)
    - _jal 7y ago
      Honestly, I've never worked in a shop what would have let anyone design an application where clients were allowed to write stored procedures or views back to the DB. Similarly, I'd never let a client modify the application logic running on my app servers. I mean, I can imagine it, but it sounds like painting "root me and do what you will with the corpse" on your front door.
      - derefr 7y ago
        I can understand your point of view as a “database supremacist”—i.e. someone who thinks DBAs are priests who determine the canonical schema, where development teams (even within the same org) are supplicants to the DBA to get the schema changed (whereupon the schema is then changed for everyone else, who have to all just suck it up.) It’s a common viewpoint, and even a sensible one when managing DBMSes that aren’t “strong” enough to support the features required for virtualizing the schema (mostly: sprocs, schema-namespacing and search paths, low-overhead writable views that can “symlink” objects from one namespace into another, and user ACLs upon database objects where granting write access to a writable view means granting write access to the underlying object, while protecting the object itself.) The alternative viewpoint, is one where a DBMS isn’t its own Data Warehouse, but is rather a “relational transformation workspace” of a Data Lake. Consider BigQuery as a DBMS. Who determines BigQuery’s schema? Nobody. Each user has their own stuff in their own namespace, and users mount stuff from one-another’s namespaces into their own namespace. The job of the people who run BigQuery (or any other multi-tenant data-warehouse infrastructure of this type) is just to enable users to collaborate with one-another through the DBMS, where Data Definition “APIs” are contracts established between pairs of clients, rather than between a developer and the DBMS’s DBA. That was more the stance I was talking about with my parent post. But even under a Database-supremacist viewpoint, what I was talking about can still happen. Whether clients are pushing objects into the DB or not, as long as there are requirements to not break old clients, but also requirements to evolve the schema for new clients, the DBA will arrive at a set of constraints that can only be solved by doing exactly what these clients would do themselves, if given DDL privileges: to define separate “virtual namespaces” where the same object names refer to mostly-common but some slightly-different versions of the same database objects, in order to allow LegacyClient to see PolyfilledLegacySchema while NewClients see the regular, “true” schema (or vice-versa.) In any organization with a sufficiently-large team structure that must share data (picture e.g. Amazon with their store), there will be a number of teams (clients) with different conflicting constraints upon the same data set. This makes the job of the DBA managing their shared data, very similar to the job of an upstream library software developer: you must keep the library’s API stable so that existing downstream clients won’t break; while also making changes to those APIs such that new downstream clients can get the features they’re demanding. Sure, there are alternate ways of solving this stable+evolving constraint problem for shared data, just like there are alternate ways of solving the stable+evolving constraint problem for a software library. You can just build a database that’s “downstream” of your own, that decorates the data with the new features (or tell the client that needs it to do it themselves!), and which syncs changes back to the upstream DB in the upstream’s canonical format. This is like building one library to wrap another. But this is, in the end, a lot of extra complexity that introduces the chance of data corruption along the way. Much safer to warehouse your data in one system with one MVCC world-clock. It might be a requirement if your upstream DBMS is “weak” (e.g. some legacy mainframe DB2 instance) but modern DBMSes give you every feature you need to provide multiple interfaces from a single instance. Why not take advantage? ——— And, if you can accept that argument—and you also accept the idea that clients should be able to use the DBMS as a scratch workspace for their queries (i.e. that creating temporary tables isn’t a perversion of what DBMSes stand for)—then combining the two results in the idea that each client could, in theory, just tell the DBMS of its particular needs for an API to access a shared dataset, and that this would result in the automatic creation of a virtual namespace perfectly suited to that client’s needs. Sure, let’s pretend the client is specifying this virtual namespace using some sort of abstract specification language, where the DBMS takes it, parses it, and then has the power to satisfy those requirements however it wants, maybe just by directing the client to the nearest matching existing virtual namespace, etc. But think about what exactly clients would need to send in these requirements specifications for a long time, you’ll realize that the specs will still turn out to need exactly the level power of regular SQL DDL statements. You don’t need a shim layer; you just need a sufficiently-granular ACL system, such that the client can create “virtual stuff” for their own use, but can’t create or interfere with “real stuff”, or with other clients. (And then it turns out that, since the client would just be communicating its needs via SQL DDL anyway, it makes perfect sense to let dev-teams avoid Not-Invented-Here by just reusing the perfectly good SQL-DDL-emitter that is “database migration frameworks.”)
- janpot 7y ago
  renaming is the point. I want it to be tightly coupled to the specific version of my application.
  - mjw1007 7y ago
    For what it's worth, I recommend something based on running `CREATE FUNCTION pg_temp.foo()` once per session (and calling it explicitly as `pg_temp.foo()`).
- namibj 7y ago
  Just hash the create statement and use that as the name? Have whatever code hashes these dump these hashes, or just use some last-used-timestamp to cull them every once in a while (though I doubt that's actually necessary, due to how i'd expect them to be stored. And when they accumulate, you cans till start to clean up by generating lists of all hashes used, waiting a week or so until all older versions have finished, and drop all stored procedures that are not in your list.
  - mjw1007 7y ago
    As far as I'm concerned, that comes squarely under the scope of "doable, but fiddly".

by zxexz 7y ago

mjw1007 7y ago

If you do it that way, you also end up having to rename your function every time it changes, if it's possible that older clients are still running. And if you're tidy-minded you're going to spend effort building a system for removing old versions when they're not going to be needed any more. It's all doable, but it's fiddly. Explicit support for this sort of setup would be a good thing.

gfody 7y ago
of all the reasons a sproc may need to change I can't imagine one where you want to keep the old version around while also giving the new version the same name as the old
- setr 7y ago
  The output is slightly different, but the semantic behavior remains the same? Databases and applications update independently, so versioning is required. If the database offers nothing for this, then an adhoc solution will generally be found (eg suffixing with _v1, _v2, etc), but that's not at all ideal
  - 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.
- derefr 7y ago
  You can't imagine multiple client applications of different versions attempting to install, and then interact with, different versions of the same stored-proc API abstracting away (variously-polyfilled views + triggers upon) a single shared underlying dataset? I.e. a world where "FooClient v1.1.0" wants to interact with "DB schema Core-1/FooClient-v1.1.0", while allowing "FooClient v1.0.0" to still see "DB schema Core-1/FooClient-v1.0.0", and "BarClient v1.0.0" to see an entirely separate "DB schema Core-1/BarClient-v1.0.0"? (I mean, sure, you could have each client construct its "API layer" of sprocs et al in its own client-specific schema, or worse yet, as its own connection-specific temporary objects—but that's pretty dang inefficient when tons of different clients are sharing the same few API versions, and when the different API versions share identical copies of 99% of their sprocs.)
  - _jal 7y ago
    Honestly, I've never worked in a shop what would have let anyone design an application where clients were allowed to write stored procedures or views back to the DB. Similarly, I'd never let a client modify the application logic running on my app servers. I mean, I can imagine it, but it sounds like painting "root me and do what you will with the corpse" on your front door.
    - derefr 7y ago
      I can understand your point of view as a “database supremacist”—i.e. someone who thinks DBAs are priests who determine the canonical schema, where development teams (even within the same org) are supplicants to the DBA to get the schema changed (whereupon the schema is then changed for everyone else, who have to all just suck it up.) It’s a common viewpoint, and even a sensible one when managing DBMSes that aren’t “strong” enough to support the features required for virtualizing the schema (mostly: sprocs, schema-namespacing and search paths, low-overhead writable views that can “symlink” objects from one namespace into another, and user ACLs upon database objects where granting write access to a writable view means granting write access to the underlying object, while protecting the object itself.) The alternative viewpoint, is one where a DBMS isn’t its own Data Warehouse, but is rather a “relational transformation workspace” of a Data Lake. Consider BigQuery as a DBMS. Who determines BigQuery’s schema? Nobody. Each user has their own stuff in their own namespace, and users mount stuff from one-another’s namespaces into their own namespace. The job of the people who run BigQuery (or any other multi-tenant data-warehouse infrastructure of this type) is just to enable users to collaborate with one-another through the DBMS, where Data Definition “APIs” are contracts established between pairs of clients, rather than between a developer and the DBMS’s DBA. That was more the stance I was talking about with my parent post. But even under a Database-supremacist viewpoint, what I was talking about can still happen. Whether clients are pushing objects into the DB or not, as long as there are requirements to not break old clients, but also requirements to evolve the schema for new clients, the DBA will arrive at a set of constraints that can only be solved by doing exactly what these clients would do themselves, if given DDL privileges: to define separate “virtual namespaces” where the same object names refer to mostly-common but some slightly-different versions of the same database objects, in order to allow LegacyClient to see PolyfilledLegacySchema while NewClients see the regular, “true” schema (or vice-versa.) In any organization with a sufficiently-large team structure that must share data (picture e.g. Amazon with their store), there will be a number of teams (clients) with different conflicting constraints upon the same data set. This makes the job of the DBA managing their shared data, very similar to the job of an upstream library software developer: you must keep the library’s API stable so that existing downstream clients won’t break; while also making changes to those APIs such that new downstream clients can get the features they’re demanding. Sure, there are alternate ways of solving this stable+evolving constraint problem for shared data, just like there are alternate ways of solving the stable+evolving constraint problem for a software library. You can just build a database that’s “downstream” of your own, that decorates the data with the new features (or tell the client that needs it to do it themselves!), and which syncs changes back to the upstream DB in the upstream’s canonical format. This is like building one library to wrap another. But this is, in the end, a lot of extra complexity that introduces the chance of data corruption along the way. Much safer to warehouse your data in one system with one MVCC world-clock. It might be a requirement if your upstream DBMS is “weak” (e.g. some legacy mainframe DB2 instance) but modern DBMSes give you every feature you need to provide multiple interfaces from a single instance. Why not take advantage? ——— And, if you can accept that argument—and you also accept the idea that clients should be able to use the DBMS as a scratch workspace for their queries (i.e. that creating temporary tables isn’t a perversion of what DBMSes stand for)—then combining the two results in the idea that each client could, in theory, just tell the DBMS of its particular needs for an API to access a shared dataset, and that this would result in the automatic creation of a virtual namespace perfectly suited to that client’s needs. Sure, let’s pretend the client is specifying this virtual namespace using some sort of abstract specification language, where the DBMS takes it, parses it, and then has the power to satisfy those requirements however it wants, maybe just by directing the client to the nearest matching existing virtual namespace, etc. But think about what exactly clients would need to send in these requirements specifications for a long time, you’ll realize that the specs will still turn out to need exactly the level power of regular SQL DDL statements. You don’t need a shim layer; you just need a sufficiently-granular ACL system, such that the client can create “virtual stuff” for their own use, but can’t create or interfere with “real stuff”, or with other clients. (And then it turns out that, since the client would just be communicating its needs via SQL DDL anyway, it makes perfect sense to let dev-teams avoid Not-Invented-Here by just reusing the perfectly good SQL-DDL-emitter that is “database migration frameworks.”)
janpot 7y ago
renaming is the point. I want it to be tightly coupled to the specific version of my application.
- mjw1007 7y ago
  For what it's worth, I recommend something based on running `CREATE FUNCTION pg_temp.foo()` once per session (and calling it explicitly as `pg_temp.foo()`).
namibj 7y ago
Just hash the create statement and use that as the name? Have whatever code hashes these dump these hashes, or just use some last-used-timestamp to cull them every once in a while (though I doubt that's actually necessary, due to how i'd expect them to be stored. And when they accumulate, you cans till start to clean up by generating lists of all hashes used, waiting a week or so until all older versions have finished, and drop all stored procedures that are not in your list.
- mjw1007 7y ago
  As far as I'm concerned, that comes squarely under the scope of "doable, but fiddly".