Re: Implementing "thick"/"fat" databases

On 22 Jul 2011, at 21:15, Karl Nack wrote:

> But this still falls short, since we're still basically managing the
> transaction in the application layer.

The problem you're facing here is that database statements work with records, while your example has a need to handle a set of (different types of) records in one go.

> The holy grail, so to speak, would be:
>
> SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50),
> (2, -50)));

Now imagine someone not familiar with your design reading this query...
To start with, they're going to assume this query SELECTs data, while it actually inserts it. Secondly, it's impossible to see what the different data-values are supposed to represent without looking up the function - and quite possibly, it's implementation. They're going to wonder what (1,50) and (2, -50) mean, what kind of date current_date gets assigned to, etc.

Having to write queries like these is even worse, even if you designed the function. You'll be looking at your own documentation a lot while writing these.

It would seem to me that the API you would provide for business logic like this should provide the users of said API with enough context to create valid statements. For example, you could use XML to describe the data (I'm no fan of XML, but it does suit a need here and allows validation of the provided data), especially as Postgres has XML parsing functionality.
Or you could use a more sophisticated procedural language (plpython or plphp, for example) that's capable of marshalling and unmarshalling data structures to strings and vice versa (eg. '{foo:1,bar:2}').

You would still have a SELECT statement that INSERTs data, which is semantically a bad thing to do IMHO.

Perhaps the better solution is (as others mentioned already) to move the data interpretation to a (web)service/application server and have that perform the actual database operations.
With that in mind, you would put business logic ("process an invoice") into an "application server", while you put data integrity logic ("don't allow transactions with no line_items") into the database.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

!DSPAM:737,4e2a9c2112098024710106!