Re: Implementing "thick"/"fat" databases

On Sat, Jul 23, 2011 at 11:44 PM, Sim Zacks <sim(at)compulab(dot)co(dot)il> wrote:
> 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.

It depends on what you mean by "business logic." In general my view
is that some business logic doesn't belong in stored procedures.

In general where I draw the line is between 'data logic' (i.e. logic
necessary to retrieve, process, and store data in a meaningful and
consistent way meeting inherent requirements including security
requirements) and 'application logic' (i.e. logic as to how you
obtain, display, present, and use the data).

If the goal is to provide a consistent set of business logic to
several applications hitting the database, in general you may not want
your database to make assumptions about how the data is going to be
used, or where it is going to come from.

OTOH, if this is a single application database, then I would ask:
What do you gain by putting it in plpythonu on the db server instead
of on the client?

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

How does this interface work? You have a separate network socket for
this? Something else?

What happens if a user goes to lunch or leaves early and then the
query hangs pending input? How do you handle this? Is there a
timeout on the input request?

Best Wishes,
Chris Travers