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

On 07/24/2011 06:58 PM, Chris Travers wrote:

> On Sat, Jul 23, 2011 at 11:44 PM, Sim Zacks<sim@compulab.co.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?
The goal is to make our system client agnostic, Most of our GUI is
written in wxpython, we also have some web functions and even a barcode
terminal function, written in C#. We would like to use an application
server, so that all the code is run on the server but we don't want to
be web-based. I don't want to have the same business logic written into
various clients as that causes maintenance headaches.
The way that postgresql works is that each session is its own process.
That means that from the server perspective (at least in most ways), my
plpython function is not connected to any other call on the database. My
goal is to allow any client to request functionality and have it execute
the same way every time.

Data logic is built in by use of constraints and triggers and some
functions, business logic is built only into functions.

In other words, our postgresql server is a hybrid database/application
server.

>>
>> 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
As I mentioned, I am still playing around and testing this, and have
The concept is that each client has a SocketServer thread that listens
on a specific port (8080 in this case).
My database function is called python_prompt pasted below.
It has a timeout of 2 minutes after which the function errors out with a
timeout. The user will get a message that the function did not complete.
The important thing is that it has a timeout. If the timeout is 20
minutes, that is also ok. Depending on the query that calls this
function, there do not have to be any locks being used and as this is
its own process and it is basically sleeping while waiting for the
answer, it doesn't use resources (aside from a connection and the work
mem in the calling query, of course).

Using this, a plpgsql query can prompt the user, "You are about to use
more stock then you have, are you sure you want to do this?"

CREATE OR REPLACE FUNCTION python_prompt(v_question text)
   RETURNS boolean AS
$BODY$
import time
import socket
ipaddr=plpy.execute("select inet_client_addr()")[0]["inet_client_addr"]
HOST, PORT = str(ipaddr), 8080
sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
sock.settimeout(120)
sock.connect((HOST, PORT))
sock.send(v_question + "\n")
ans=sock.recv(1024)
sock.close()
if ans=="yes":
     return true
else:
     return false
$BODY$
   LANGUAGE plpythonu VOLATILE;

I haven't fully decided to use this yet, but one of my goals is to make
my GUI as dumb as possible.