Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL example of self-contained stored procedures

sivers.org

Post reply on HN
Print view View original

71–80 of 81 posts

Re: PostgreSQL example of self-contained stored procedures

#71

Post by mhd »

Twisell wrote: post #49
1996 wrote: post #38

Earlier quoted context omitted.

Thanks a lot for your reply! It seems the main gain for your is to move between languages and allow initial iterations/bugfixes without touching the application code. The database has more maintenance issues (like rolling txids) than the application code. I am not sure I want to add more complexity and potential issues to the database. FYI, a materialized view is a potentially long or complex query whose results are…

Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).

Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle)

I've had good luck with these functions: https://gist.github.com/mateuszwenus/11187288

Allows you to save-and-drop dependent views (materialized or regular) and then restore them after your updates.

Re: PostgreSQL example of self-contained stored procedures

#72

Post by Sophistifunk »

ako wrote: post #40
sivers wrote: post #20

I'm the author. Thanks to Ludwig for posting this here. I've been doing all of my database work like this for 5+ years now, and love it. Many more examples here: https://code.sivers.org/db-api/ It works great when you want to write simple shell scripts, too. They look as simple as this: https://github.com/sivers/store/blob/master/getdb-example.rb... People have been asking how I do unit testing. The main thing is to…

Pretty much how we built client-server applications on Oracle RDBMS 25 years ago. When your application grows, you'll have a large number of stored procedures. Oracle has packages which significantly eases the way you work with a large amount of stored procedures. Packages enable you to group related stored procedures into one object. Packages have an interface (stored procedures and custom types available outside th…

This was my introduction to server-side web programming as a kid; I was helping develop a very early online store (Australia, 94/95) for a local tabletop gaming retailer in Oracle 7 (IIRC). As well as being my first foray into web programming, it was the first time I got my hands on a grown-up RDBMS, Windows NT (still 3.5.1), and Java (I wrote a tetris knockoff as an applet for the site also). I was very surprised to find that my experience writing ADA was actually going to be useful out in the real world :)

Now I feel old :)

Re: PostgreSQL example of self-contained stored procedures

#73

Post by ben509 »

cryptonector wrote: post #62
ben509 wrote: post #39

Earlier quoted context omitted.

It's a bit more complicated than just "upload all my stored procedures" in practice. I think we need a version control / deployment system that is designed for databases to make this work. We want it to regard the database as the source of truth, and understand that while you can blow away a development database, you can't do that to prod. We want it to be able to track which clients are using which stored procedures…

I don't get it. You have a source file. It has your store procedures and what not. You deploy it. You have the server startup load it. Done. What's the problem? Well, you might complain that some schema changes are hard to make this way, and that's true, but for stored procedures it's straightforward, and even for other things you can manage and make the update atomic (though it'd be nicer to have better SQL language…

> You have the server startup load it.

We have to take down a production database to do this?

Or do you mean the app server is going to do it, in which case how do you handle different app servers with different versions of the stored procedure? Especially if we're doing phased deployments?

Re: PostgreSQL example of self-contained stored procedures

#74

Post by cryptonector »

ben509 wrote: post #73
cryptonector wrote: post #62

Earlier quoted context omitted.

I don't get it. You have a source file. It has your store procedures and what not. You deploy it. You have the server startup load it. Done. What's the problem? Well, you might complain that some schema changes are hard to make this way, and that's true, but for stored procedures it's straightforward, and even for other things you can manage and make the update atomic (though it'd be nicer to have better SQL language…

> You have the server startup load it. We have to take down a production database to do this? Or do you mean the app server is going to do it, in which case how do you handle different app servers with different versions of the stored procedure? Especially if we're doing phased deployments?

No, you can load it at any time. Just write (and test) SQL that is safe to load for upgrading a schema.

Re: PostgreSQL example of self-contained stored procedures

#75

Post by pmontra »

ramraj07 wrote: post #66
pmontra wrote: post #23

Earlier quoted context omitted.

Returning JSON to a client is relatively recent. The standard for web apps was to get data from the db and generate HTML on the server. A JSON layer would be a useless overhead. Furthermore about everybody knows how to use an ORM. Not many people can write store procedures. Maybe the question is why is that so. I heard "I know [language], I don't know SQL, so I write queries in [language] " so many times even from pe…

Do you think it's an acceptable excuse for a dev to say they don't know SQL?

No, it's not acceptable. Still it's evident that it doesn't prevent getting a good job.

Re: PostgreSQL example of self-contained stored procedures

#76

Post by Twisell »

mhd wrote: post #71
Twisell wrote: post #49

Earlier quoted context omitted.

Unless it changed very recently be aware that you can't update sql definition of a materialized as easily as you would update a view. There is currently no "CREATE OR REPLACE" option so any dependencie build onto a materialized view can quickly become a real pain (been there). Sometime, it might be easier to stick to the old trick of a table updated by a refresher function (possibly called as a trigger).

Dependencies are a general problem, like changing the type of a table that has dependent views. It's a good idea if your database update scripts/migration software can handle something like this. Other databases don't tend to be as strict as Postgres here (I only found out that some rarely-accessed views never quite worked after migrating from Oracle) I've had good luck with these functions: https://gist.github.com/m…

Deeply agree about dependencies being a general problem.

Thanks for the script that look pretty clever #bookmarking. Like particularly the approach of "drop what you saved, no more no less". DROP CASCADE is simpler but can have undetected side effect, if this script fail to backup all dependency, logically you will get an error when attempting to delete target and that rocks.

Re: PostgreSQL example of self-contained stored procedures

#77

Post by jimktrains2 »

balfirevic wrote: post #50
tashian wrote: post #45

One unmentioned reason this is powerful: It avoids concurrency bugs and inconsistencies introduced by write skew, in cases where the application layer (eg. Ruby/Python) makes business logic decisions based on data that has become stale. These are hardest to detect and avoid in the case where business logic depends on no rows matching (count(*) == 0), eg. meeting room scheduling systems, because there is nothing for a…

> Stored procedures can avoid this entirely, even under the weaker transaction isolation levels, by keeping all the logic in the DB and running it atomically I don't understand. Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer.

> Multiple SQL statements executed inside stored procedures are not run any more atomically than multiple SQL statements executed from application layer.

Yes, but not wrapping those multiple application-generated queries in a transaction is a common bug I've encountered at many of the places I've worked. It also requires a lot more round-trips to the database.

Re: PostgreSQL example of self-contained stored procedures

#78

Post by eternalban »

llimllib wrote: post #3

I'm curious if anybody has practical experience developing a reasonably trafficked website in a similar manner. I wrote apps which consisted mostly of oracle stored procedures when I first got out of college, and it was a pretty awful experience, but it was also at a place where the development knowledge was minimal. I've since been getting closer and closer to writing postgres in this way. My current app (which I ar…

If you have competent or better domain modeling skills, the database route is optimal. In general, Object layer domain modeling (typical in industry) is mainly addressing skill gaps in domain modeling, relational dbs, and SQL.

Of course, if you are writing something like Microsoft Office, where there will be pervasive reuse of object layer components, an Object Domain Model will be a better fit - it makes sense to define the system fundamentally at the object/component level.

In my experience in the industry, the rise of object layer modeling was due to multiple issues, some noted above and also including organizational factors such as the traditional DBA/Dev divide, licensing, etc.

So a pure technical approach to this question is, imo, non-optimal. It is ultimatley a business decision.

Re: PostgreSQL example of self-contained stored procedures

#79

Post by marcus_holmes »

dagss wrote: post #41
thijsvandien wrote: post #17

Earlier quoted context omitted.

Every once in a while I encounter somebody who heavily promotes that way of working. There are a couple of arguments I've heard in favor of it. Often it's a mix of performance, ultimate data integrity, and "simplicity". I just don't buy it. If performance is so critical, whatever you gain by moving everything to the database is lost by the fact that databases are harder to scale. You may be able to get by with a sing…

We write most code in backend (go), but we DEFINITELY spin up a new, real SQL database in for every test in our automated test suite. ("CREATE DATABASE" is rather cheap; use a fresh docker DB instance for each suite run, or run it permanently while developing. Mocking away DB is both an inefficient way to work and lets the tests cover less. I will never write software without running tests against a real DB again. Ou…

agree on the mocking the database - I wrote an entire test suite based on mocking the database responses, and then found a ton of live problems caused by the database - backend interaction (things like structs not aligning with the actual return records from functions, input format for arrays of ids being different from the mockup to the actual driver, and hstores).

Now I have a test database, which I create from a version-controlled schema.sql, and each test creates its own fixtures in the test db. Works way better.

Post reply on HN