As someone who lived through the Stored Procedure hell of the late 90's and early 00's, can someone explain to me why this shouldn't scare the bejeezus out of me?
I have written quite a bit on my blog (http://ledgersmbdev.blogspot.com) on how to avoid sproc hell when porting a lot of data access logic to the database. LedgerSMB more or less is moving to a model where all non-service-locator queries (which look up stored procedures) are stored procedures and we believe we have solved most of these problems.
There are significant problems with stored procedures if they aren't used right. VARIADIC stored procedures help, but they don't entirely solve the problem. Fortunately PostgreSQL offers some rich tools for solving some of these problems. For example we make extensive use of named arguments.
But there is a second use here as well, and this is to extend what you can do inside a SQL query and when you combine this with JSON types, you can do quite a lot.
For example, suppose you do something like:
SELECT extract(myjsonfld, 'property1') FROM mytable WHERE id = 123;
There is no extract function in PostgreSQL 9.1-2 (there will be in 9.3 but not sure what the semantics will be), so you could write such a function in Javascript, such that it takes a JSON and string input and outputs a string or JSON object depending on what you want.
This allows you to extend the capabilities of SQL to do whatever you need.
During the afformentioned "Sproc Hell", we were putting application logic in the database. Of course, this made perfect sense: it was secure because of bound params and strict typing, it was fast because it avoided several trips to the database for multi query operations and even for single query statements, query plans were precomputed and cached by the DB. You were also able to tweak application logic without deploying code, which was likely a clumsy process involving more than one team and various manual steps. This is all bollocks, as we've learned many scars and gray hairs later.
Now, the proposal here is entirely different. While yes, you are creating a function in your database, you are doing it to access data in a JSON structure, per the OP. Because in Postgres you can create an index on the result of any expression, including a function, you can now create indexes on functions that parse and access data your JSON docs. And it's fast.
I don't think it was all bollocks. It was just due to the fact that sproc interfaces sucked. Also development of quality sprocs is qualitatively different than upper level app code (among other things, you want a single large query front and center to the extent possible), and so if you write stored procedures the way you write application code they will suck.
Now, what we do with LedgerSMB is build our stored procedures as basically named queries, inspired by web services (both SOAP and REST have been inspirations there). The procedures are intended to be relatively discoverable at runtime, with the aggressive attempts to use what infrastructure exists for this purpose that REST gives for HTTP.
Stored procedures are not a problem. They allow you to encapsulate a database behind an API, and the desire to do that is a major point of Martin Fowler's NoSQL advocacy (arguing for doing this for NoSQL dbs).
> As someone who lived through the Stored Procedure hell of the late 90's and early 00's, can someone explain to me why this shouldn't scare the bejeezus out of me?
Because it doesn't have anything to do with "Stored Procedure hell". Having one more choice of procedural languages (of which there are already many available for Postgres) doesn't do anything to force you to adopt bad practices as to when you use procedural code in the database.
Comments
As someone who lived through the Stored Procedure hell of the late 90's and early 00's, can someone explain to me why this shouldn't scare the bejeezus out of me?
I have written quite a bit on my blog (http://ledgersmbdev.blogspot.com) on how to avoid sproc hell when porting a lot of data access logic to the database. LedgerSMB more or less is moving to a model where all non-service-locator queries (which look up stored procedures) are stored procedures and we believe we have solved most of these problems.
There are significant problems with stored procedures if they aren't used right. VARIADIC stored procedures help, but they don't entirely solve the problem. Fortunately PostgreSQL offers some rich tools for solving some of these problems. For example we make extensive use of named arguments.
But there is a second use here as well, and this is to extend what you can do inside a SQL query and when you combine this with JSON types, you can do quite a lot.
For example, suppose you do something like:
SELECT extract(myjsonfld, 'property1') FROM mytable WHERE id = 123;
There is no extract function in PostgreSQL 9.1-2 (there will be in 9.3 but not sure what the semantics will be), so you could write such a function in Javascript, such that it takes a JSON and string input and outputs a string or JSON object depending on what you want.
This allows you to extend the capabilities of SQL to do whatever you need.
(I work on Heroku Postgres)
During the afformentioned "Sproc Hell", we were putting application logic in the database. Of course, this made perfect sense: it was secure because of bound params and strict typing, it was fast because it avoided several trips to the database for multi query operations and even for single query statements, query plans were precomputed and cached by the DB. You were also able to tweak application logic without deploying code, which was likely a clumsy process involving more than one team and various manual steps. This is all bollocks, as we've learned many scars and gray hairs later.
Now, the proposal here is entirely different. While yes, you are creating a function in your database, you are doing it to access data in a JSON structure, per the OP. Because in Postgres you can create an index on the result of any expression, including a function, you can now create indexes on functions that parse and access data your JSON docs. And it's fast.
I don't think it was all bollocks. It was just due to the fact that sproc interfaces sucked. Also development of quality sprocs is qualitatively different than upper level app code (among other things, you want a single large query front and center to the extent possible), and so if you write stored procedures the way you write application code they will suck.
Now, what we do with LedgerSMB is build our stored procedures as basically named queries, inspired by web services (both SOAP and REST have been inspirations there). The procedures are intended to be relatively discoverable at runtime, with the aggressive attempts to use what infrastructure exists for this purpose that REST gives for HTTP.
Stored procedures are not a problem. They allow you to encapsulate a database behind an API, and the desire to do that is a major point of Martin Fowler's NoSQL advocacy (arguing for doing this for NoSQL dbs).
> As someone who lived through the Stored Procedure hell of the late 90's and early 00's, can someone explain to me why this shouldn't scare the bejeezus out of me?
Because it doesn't have anything to do with "Stored Procedure hell". Having one more choice of procedural languages (of which there are already many available for Postgres) doesn't do anything to force you to adopt bad practices as to when you use procedural code in the database.
The thing I hated most about stored procedures was all the logic and dynamic SQL that creeped in.
The example in this link and what a lot of us hope to use it for is schema-less data storage.
I'm not going to put any logic into my v8 functions except for accessing data.
JavaScript is a real language.