5 ms·

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

• 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.
    • 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.
  • heavenlyblue 7y ago
    Why is it not ideal? Literally every single other language solves it this way.
    • setr 7y ago
      Highly manual, error-prone, lack of consistency means lack of tooling support, etc. Most languages simply don't handle the scenario; most languages also didn't handle code-testing, or building, or etc, but that hardly makes it an ideal situation. Additionally, in the case of DBs, it's strange to call it a "language"; at best it's a language with a supremely massive runtime, but more appropriately, it's a platform. And being a platform, it would be fairly reasonable and unsurprising to consider things like version control and function-versioning to be in scope, even though most DB platforms don't handle it either. But I'm also consistently surprised by how lacking the DB tooling space is compared to programming languages; eg the formatters are generally lacking/incomplete (foolishly imagining the SQL standard to be sufficient to claim support for all DBs), the general lack of any version control, the general lack of SQL transpilers (again, probably due to the misunderstanding that the standard standardizes anything; specifically the lack of a 80-90÷ OS automatic translation for MySQL -> PGSQL is astounding), the general lack of IDE support (Most can't even assist in maintaining datatypes when writing SQL, despite the all-knowing schema available to it; you basically get notepad + a pretty view of the output), etc. DBAs are willing to put up with quite a bit, and I can't figure out why. The engines themselves are phenomenal works of engineering; the usage of it is utterly pathetic
      • paulddraper 7y ago
        > DBAs are willing to put up with quite a bit, and I can't figure out why. The engines themselves are phenomenal works of engineering; the usage of it is utterly pathetic Perhaps they don't want their programs to bloat until they can read email.
        • setr 7y ago
          Enhancing management of your relational database seems to me quite in scope of your relational database management system. Whether the functionality exists as an arbitrary other program, in whatever miniscule chunk that fits your preference, or as part of an additional arbitrary SQL extension (we would all certainly love more random keywords/syntax), is unimportant. The functionality/tooling is missing, or at best, lacking, and has nothing to do with a preference for no-bloat. It simply means that we have arbitrary, adhoc implementations to enhance the management of our failing management system. That the database must be babied is not a positive, and RDBMS are meant to remedy 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.”)