数据库逻辑VS应用逻辑

Sorry if this is a dumb question. I've been out of the game for a while.

I am deciding whether to use PostgresSQL for a web app. I have never used it before. Having done my research, I really like the PGSQL language it has for stored functions.

Often in my web-app code I am saving a record. This consists of checking whether the record already exists - and if so UPDATE it, else INSERT new record.

With PGSQL - I can do all this in one query ( as opposed MysqL which im pretty sure you can't)

Is their an advantage to this? OR should this logic remain in the web-app layer and not in a stored function on the database.

Heres a rough example in PGSQL. Its only illustrative and not meant to be secure or good code.

CREATE OR REPLACE FUNCTION save_client(IN strforename character varying, IN strnew character varying)
  RETURNS TABLE(forename character varying, surname character varying) AS
$BODY$

    DECLARE myrec int;

    BEGIN   

        SELECT idx_clients INTO myrec from "Clients" WHERE LOWER("Clients".forename)=$1;


        IF NOT FOUND THEN
            RAISE NOTICE 'No results found.%',myrec;

            INSERT INTO "Clients" (forename,surname) VALUES (strnew,strforename);
        ELSE
            RAISE NOTICE 'YES results found.%',myrec;

            IF myrec NOTNULL THEN
                UPDATE "Clients" SET forename=$2 WHERE idx_clients=myrec;
            END IF;

        END IF;

    END;

$BODY$

I'd usually rather have this kind of functionality in the application, for a few reasons:

  1. Server Load Your database server will often be under heavy load anyway, application server usually less so.
  2. Technology It leaves your implementation database agnostic, so if you later decide to move to MySQL or Oracle you won't lose the functions or have to re-design them.
  3. Maintainability Having your code abstracted into the database will be less intuitive if anyone else has to maintain it apart from yourself. They will spend time looking for functions that aren't there.

Your main considerations may differ from mine. I expect there would be some performance gains from doing it all in the database, so that may factor into it, but I always try to keep my implementation as tech-agnostic as possible.

I prefer to put this in the database for a few reasons:

  1. It allows you to encapsulate your database behind an API. You can even make your API discoverable (see a number of posts on http://ledgersmbdev.blogspot.com for one approach) so that applications can, within reason, discover the call syntax at run-time. Again encapsulation helps ensure multiple apps can safely access the same db since the rdbms ends up becoming a server with a well-defined API.

  2. It in essence ensures a sort of dependency inversion you can use to create more stable interfaces.

  3. It allows you for the most part to keep SQL out of your application files (since the call interface itself can be abstracted to a single API).

  4. Better control over transactional logic and performance (but see below)

This being said there are a few traps to be aware of:

  1. Stored procs are most maintainable where they are a single large query with some minor supporting logic.

  2. Beware of locks

  3. Do not mix transactional and non-transactional logic. Transactional logic belongs in the db. Anything non-transactional belongs outside it. For example do not send emails from stored procedures. Do not pause database transactions to try to make a network connection to the user asking if he or she wants to continue. Do not hook these into scripts which have real-world impacts directly from within stored procedures.....

  4. Be careful about contracts. One of the big issues people run into in stored procedure development has to do with schema change. You add an additional column that you need to gather and instead of two places your code changes, you have at least three. This is one reason I put a lot of emphasis on discoverability, because this allows you to build more flexible contracts and change your code only where it needs to change. In other words things can fail gracefully.

I say most of this as the author of tens of thousands of lines of PostgreSQL stored procedures, mostly in sql and pl/pgsql. There are management challenges that come with stored procedures but once you tame them, they are well worth the effort.