Desktop apps and Web - what makes sense?

Alex Clay aclay at mac.com
Thu Aug 12 18:02:54 UTC 2021


> On Aug 12, 2021, at 12:31, Doug Easterbrook <doug at artsman.com> wrote:
> 
>> One thing that I am not sure about is the use of stored procedures.  I see the advantage of having multiple parties (including legacy Omnis desktop app) using them, however, the challenge is to maintain them in such a way that nothing gets broken. The debugging of stored procedures where you put biz logic might be another challenge, not a trivial thing.
> 
> true it is harder.   there is a command in pgsql called ‘raise’  
> refer to: https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html
> 
> basically it lets you put out debugging info into the postgres log…. if you need to.   This is no different than a headless server where you have to write to a text file or log to see whats going on.
> 
> 
> solution:  make small, testable routines if you want to have some stuff in pgsql.     to be clear, we DO NOT have all, nor do we have a significant portion of our business logic in postgres.
> 
> we have some.     and the places we chose to do it at the beginning is where we really needed performance for database I/O where many things happen on a single database call.     We do not use it for anything where we are simply reading or updating a large set of records.  thats all in omnis.

We actively push logic to the database. Any kind of processing, data analysis, and certainly enforcing data integrity works extremely well as a server-side function. We interface with external web services and the file system through PostgreSQL. Our SMTP sending system is all server-side in PostgreSQL. Functions are a mix of plpgsql, perl, and pure SQL. Even our backup and database management systems are all server-side.

Server-side functions have zero latency, robust features, and are easy to learn, IMHO. We had a requirement to build an endpoint in our REST API to dump a decent-sized table. In native Rails this was 50+ seconds. We rebuilt this to generate the JSON using PostgreSQL's native functions in a view and the time dropped to under 10 seconds, and a good chunk of that time was interfacing through Rails and the data transfer. You might argue that a view isn't server-side programming. I would argue it's absolutely programming—just declarative instead of imperative. 

Function development is one area that all of our developers can share, whether they are desktop, web, or mobile. That is hugely intentional since all those platforms converge at the PostgreSQL layer, and new devs grok server-side programming easily.

Could some of this move to a middle tier? Sure, but we don't have a consistent middle tier since Omnis makes direct connections and we allow clients to self-host (ain't no way I'm going to try a turn-key installer for a Ruby + Rails + Nginx stack on Windows).

Current function count is just shy of 5,000 in an application database. About 33% of these are testing functions. We use pgTAP (https://pgtap.org <https://pgtap.org/>) to add automated testing in PostgreSQL and it's a fantastic tool for building a test harness, refactoring, and scaling code. 

One downside is that we're all in on PostgreSQL. That's an issue in theory only...I have ZERO reason to even think about leaving PostgreSQL. If you want to talk about software that can be glue, PostgreSQL should be high on that list. :)

Debugging can be a little challenging, but there are some good techniques to help. I don't have any experience with it but it appears pgAdmin can offer a more traditional debugger: https://stackoverflow.com/questions/20190406/how-to-debug-postgresql-stored-procedures

Alex


More information about the omnisdev-en mailing list