3 ms·

> In the LedgerSMB project we decided to use stored procedures to accomplish this abstraction. Why? Stored procedures are nothing more than a bolted-on way to

> In the LedgerSMB project we decided to use stored procedures to accomplish this abstraction. Why? Stored procedures are nothing more than a bolted-on way to store SQL queries on the database server. Yes, you've create an "API" and an "abstraction" but you've done nothing you couldn't have done in any other language. > The stored procedures thus create an API I consider stored procedures to be little more than a shitty "API builder" thrown in for free with your database. * The programming language generally sucks * Passing in complex data always sucks * Source control is non-existent (or sucks) You've probably had to write an API layer within your code anyway, to deal with the remaining logic requirements, why not store those queries within that layer? Or put them in a further API layer within your own source code?

saurik 14y ago
With PostgreSQL the programming language can be almost anything you want (I have some stored procedures written in Python), the data structures can be reasonably arbitrary (you can build your own data types easily, the better database libraryes already support arrays and dictionaries, and in 9.2 JSON is being shipped by default), and the source control problem is easily solved by having pg_dump output your stored procedures to a file. The key advantages are then that 1) if you need to take the output of one query and use it in another--especially if they need to be in the same transaction--you can bring the round-trip time down to nothing and 2) this part of your abstraction can now be modified as part of a transaction, making changes to table schemas (which PostgreSQL supports as parts of transactions) something to do painlessly and without downtime on production.
einhverfr 14y ago
Most of our stored procedures are currently in PL/PGSQL. I don't know that I would say the language sucks. In fact where one is doing set operations, the language is wonderfully expressive and flexible, and one can get more done in it faster than any alternative, again where the operations are set operations. SQL has some very serious downsides and these are persent in PL/PGSQL, but these aren't that hard to avoid with good db design. Don't get me wrong, I have seen some really horrible PL/PGSQL stored procs in my day, but it doesn't have to be bad code. As for an additional API layer, yes there is one but it is pretty thin and most of it is pretty heavily automated. I would say that at least 75% of perl module code is just identifying methods to map to stored procedures, so there isn't a lot of code there (figure three lines to map, a sub name, a sproc name, and closing the sub). What this allows us to do is support multiple applications in multiple languages while offering consistent security and database functionality to these applications. The DB isn't just a data store, it's essentially a meeting point potentially of a larger number of applications. We have considered adding PL/Perl and PL/Python stored procedures but since most of these are set operations and so they are things that belong in db queries. As for source control I don't know what really would separate this from a compiled language. You can think of loading stored procedures into the database as essentially like compiling a program. As we move to future versions this will become an even closer comparison, as we start bundling these using PostgreSQL 9.1's extension framework. Edit: Our stored procs are defined in text files that are checked into a source repository. They are also designed to be rebuildable, and there are utilities for rebuilding stored procs included in the software.

saurik 14y ago

With PostgreSQL the programming language can be almost anything you want (I have some stored procedures written in Python), the data structures can be reasonably arbitrary (you can build your own data types easily, the better database libraryes already support arrays and dictionaries, and in 9.2 JSON is being shipped by default), and the source control problem is easily solved by having pg_dump output your stored procedures to a file. The key advantages are then that 1) if you need to take the output of one query and use it in another--especially if they need to be in the same transaction--you can bring the round-trip time down to nothing and 2) this part of your abstraction can now be modified as part of a transaction, making changes to table schemas (which PostgreSQL supports as parts of transactions) something to do painlessly and without downtime on production.

einhverfr 14y ago

Most of our stored procedures are currently in PL/PGSQL. I don't know that I would say the language sucks. In fact where one is doing set operations, the language is wonderfully expressive and flexible, and one can get more done in it faster than any alternative, again where the operations are set operations. SQL has some very serious downsides and these are persent in PL/PGSQL, but these aren't that hard to avoid with good db design. Don't get me wrong, I have seen some really horrible PL/PGSQL stored procs in my day, but it doesn't have to be bad code. As for an additional API layer, yes there is one but it is pretty thin and most of it is pretty heavily automated. I would say that at least 75% of perl module code is just identifying methods to map to stored procedures, so there isn't a lot of code there (figure three lines to map, a sub name, a sproc name, and closing the sub). What this allows us to do is support multiple applications in multiple languages while offering consistent security and database functionality to these applications. The DB isn't just a data store, it's essentially a meeting point potentially of a larger number of applications. We have considered adding PL/Perl and PL/Python stored procedures but since most of these are set operations and so they are things that belong in db queries. As for source control I don't know what really would separate this from a compiled language. You can think of loading stored procedures into the database as essentially like compiling a program. As we move to future versions this will become an even closer comparison, as we start bundling these using PostgreSQL 9.1's extension framework. Edit: Our stored procs are defined in text files that are checked into a source repository. They are also designed to be rebuildable, and there are utilities for rebuilding stored procs included in the software.