Re: Implementing "thick"/"fat" databases - Mailing list pgsql-general

I gave a talk on using postgresql as an application server  at PG East
in March.

Basically, we try to implement all business logic using functions, using
plpythonu when necessary.

For example, we have functions that send email, ftp files, sync remote
databases, etc.


It is important to keep your MVC intact and not "php" your function code
by mixing business logic with SQL statements.


I am currently playing with interactive queries, where the function
stops in the middle, and sends a message to the client asking for input.
This isn't a necessarily a good idea in all cases, but there are some
functions where you don't have the ability to ask a question until it is
mostly finished processing (ie you dont have the information needed to
ask the question until it finishes munging the data, which might be a
long process).


Let me know if you would like a copy of my presentation.


Sim


On 07/22/2011 10:15 PM, Karl Nack wrote:

> I've been following a few blogs
> (http://database-programmer.blogspot.com/,
> http://thehelsinkideclaration.blogspot.com/) that make a very compelling
> argument, in my opinion, to move as much business/transactional logic as
> possible into the database, so that client applications become little
> more than moving data into and out of the database using a well-defined
> API, most commonly (but not necessarily) through the use of stored
> procedures. Although there seems to be a lot of discussion out there of
> the reasons why one might want to do this, I'm really at a loss for
> finding good, concrete examples of how to do it. Consequently, I'm
> hoping that somebody can share their experience(s), or point me to some
> examples, of doing this with PostgreSQL. I'd consider myself fairly
> well-versed in using the various features of PostgreSQL to enforce data
> integrity, but much less so for implementing transactional logic.
>
> To focus on a more concrete example, let's consider adding a financial
> transaction to the database. The "traditional" way to do this, with the
> business logic in the application layer, leaves us with two steps:
> insert the transaction "header", then insert the line items:
>
> BEGIN;
>
> INSERT INTO transaction (id, date, description)
> VALUES (1, CURRENT_DATE, 'Transaction 1');
>
> INSERT INTO line_item (transaction_id, account_id, amount)
> VALUES (1, 1, 50), (1, 2, -50);
>
> END;
>
>
> Now if we start moving this logic to the database, we'd have something
> like:
>
> BEGIN;
> SELECT create_transaction(1, current_date, 'Transaction 1');
> SELECT create_line_item(1, 1, 50);
> SELECT create_line_item(1, 1, -50);
> END;
>
>
> But we've actually taken a step back, since we're making a round-trip to
> the database for each line item. That could be resolved by doing:
>
> BEGIN;
>
> SELECT create_transaction(1, current_date, 'Transaction 1');
>
> SELECT create_line_item(transaction_id, account_id, amount)
> FROM (VALUES (1, 1, 50), (1, 2, -50))
>      AS line_item (transaction_id, account_id, amount);
>
> END;
>
>
> Better, but still not good, since we're invoking the function for each
> individual line item, which ultimately means separate INSERTs for each
> one. What we'd want is something like:
>
> BEGIN;
> SELECT create_transaction(1, current_date, 'Transaction 1');
> SELECT create_line_items(((1, 1, 50), (1, 2, -50)));
> END;
>
>
> But this still falls short, since we're still basically managing the
> transaction in the application layer. The holy grail, so to speak, would
> be:
>
> SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50),
> (2, -50)));
>
>
> Perhaps I just need to spend more time digging through the
> documentation, but I really have no idea how to do something like this,
> or if it's even possible. I'm really hoping someone can provide an
> example, point me to some resources, or even just share their real-world
> experience of doing something like this. It would be very much
> appreciated.
>
> Thanks.
>
>
> Karl Nack
>
> Futurity, Inc
> 5121 N Ravenswood Ave
> Chicago, IL 60640
> 773-506-2007
>