PostgreSQL example of self-contained stored procedures • Hacker News Board

Re: PostgreSQL example of self-contained stored procedures

#51

Post by 1996 » Sun, Oct 27, 2019, 6:53 PM UTC

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).

Personally I have update scripts for 'version control' of the MV and its dependancies, it is not very painful to do:

begin; drop ... cascade; create ...; commit;

Re: PostgreSQL example of self-contained stored procedures

#52

Post by mslot » Sun, Oct 27, 2019, 6:57 PM UTC

Stored procedures can also be a great way to scale out transactional workloads.

In Citus (a sharding extension for Postgres by Microsoft) we recently introduced a stored procedure call delegation feature.

If your tables are distributed by person_id and you have a stored procedure that takes person_id as a parameter, you can run e.g. "SELECT create_distributed_function('lineitem_add', 'person_id')". The procedure calls for a given person_id value will then be fully delegated to the PostgreSQL server that stores the data for that person_id without additional round-trips between statements or distributed planning overhead. It'll "magically" take care of procedures that access data from other servers as well.

I realize we're basically going full circle to PL/Proxy, except in Citus almost everything else (distributed queries, DML, DDL, transactions) works as well.

Re: PostgreSQL example of self-contained stored procedures

#53

Post by Merad » Sun, Oct 27, 2019, 7:12 PM UTC

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…

Ive worked on several apps that tried this approach and each of them was a mess. I’ve seen two fundamental problems with building your app on top of stored procs:

* Tooling around sql is generally inferior to what’s available for . I’ve yet to see a company with effective automated tests around their database... it’s far more common to have _no_ tests around the database. Even if you’re the unicorn that does have all of that figured out, it still tends to be more difficult for your devs to write and test code.

* Most devs are poor to mediocre when it comes to sql. You’re either going to have to hire more specifically for sql, or force your devs to do complex work (your business logic) in a toolset that they aren’t that good at.

IMO this is a case where a few applications may have significant concurrency issues that warrant the database business logic approach... but for your average app, it’s unnecessary and makes life more difficult for your team.

Re: PostgreSQL example of self-contained stored procedures

#54

Post by no-s » Sun, Oct 27, 2019, 7:22 PM UTC

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…

im curious - why not insert from table value list instead of multiple insert statements in your fixtures population? Does this not work well in Postgres?

Re: PostgreSQL example of self-contained stored procedures

#55

Post by jimktrains2 » Sun, Oct 27, 2019, 7:43 PM UTC

I've been working on some projects and offloading as much as I can to the database. https://github.com/jimktrains/jskerp and https://github.com/jimktrains/jskplan being the two I'd like to get further along with.

Working in the DB like this is both very nice and ... very odd? It doesn't feel like most development environments and feels like going back to C or pre-object Ada (I believe Pl/SQL is based on Ada syntax-wise) or Pascal where you're writing lots of global functions that operate on structs.

Once I gain a little more experience developing like this, I'd love to think about that and see if there'd be a way to build a language that feels more "modern" and that can be transpiled to SQL (including building diffs from a current database).

Re: PostgreSQL example of self-contained stored procedures

#56

Post by vidarh » Sun, Oct 27, 2019, 7:48 PM UTC

pmontra wrote: post #23

BubRoss wrote: post #18

Earlier quoted context omitted.

How is that not how databases are typically used?

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…

Returning JSON might be recent, but multi-tier apps where one tier talks to the data storage and applies business logic served up to a presentation tier dates back to long before the web. The rise of the web caused a lot of developers without any history with multi-tier applications to push "flatter" apps, that is true, but in terms of software development in general it's not new.

Even in terms of web dev, well over a decade ago I worked on a system where we had a middleware layer that consisted of a service that handled the business logic and talked to the database. This middleware layer took requests from a web frontend layer did most of the presentation and served up in various formats. The web frontend never talked directly to the databases. It gave us flexibility in how we sharded things, and in applying different extents of access control (e.g. we could put different frontend services in front of the middleware) as well as use it to enforce constraints that were impractical to express in the database. How important that is certainly depends on the database you use.

It wasn't a new thing then either - I took inspiration for that system from much older systems.

Re: PostgreSQL example of self-contained stored procedures

#57

Post by AmericanChopper » Sun, Oct 27, 2019, 8:25 PM UTC

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…

This used to be the standard architecture for “enterprise” apps. I’ve worked on lots of Java/Oracle apps like this, and there’s some pretty good reasons this isn’t widely used anymore. It makes your DB more of a bottleneck, which is the hardest part of your architecture to scale. You can’t use SQL for all of your business logic, so you end up fragmenting it across two codebases, which sucks, especially at scale. Databases are optimised for reading, writing and comparing large sets of data, and a typical app will have business logic that simply isn’t performant in a DB. This architecture is basically guaranteed to scale into performance bottlenecks and enormous amounts of complexity.

On the other hand, I don’t really buy the “most engineers aren’t familiar with SQL” line of reasoning. Maybe that’s true, but SQL is incredibly simple, I think any engineer could pick it up quite easily, and that what you learn from doing so will just make you a better engineer in general.

Re: PostgreSQL example of self-contained stored procedures

#58

Post by sivers » Sun, Oct 27, 2019, 8:39 PM UTC

no-s wrote: post #54

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…

im curious - why not insert from table value list instead of multiple insert statements in your fixtures population? Does this not work well in Postgres?

Only because sometimes I edit them directly, and remove columns or data there with vim.

Re: PostgreSQL example of self-contained stored procedures

#59

Post by ris » Sun, Oct 27, 2019, 9:03 PM UTC

segmondy wrote: post #33

ris wrote: post #30

As neat as this is, I'm not sure I'd recommend this - I'm not sure what the plan would be when it came to scaling.

Tell us about your scaling problem. Do you really have a scaling problem?

Ok, where does this go when it needs to grow beyond a single machine? Is it time to set up replication? Partitioning?

Re: PostgreSQL example of self-contained stored procedures

#60

Post by steve-chavez » Sun, Oct 27, 2019, 9:21 PM UTC

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…

Hey Derek. For tests, have you tried pgTAP[1]?

Having all the db tests in SQL is very convenient and I think pgTAP could simplify your API tests.

Here are some examples of how testing with pgTAP looks like:

https://github.com/steve-chavez/socnet/blob/master/tests/fri...

(Loading schema/fixtures can be done with a `\i file.sql`)

[1]: https://pgtap.org/