Joel on SQL

When you mention stored procedures, I see horror and disgust in the eyes of many developers.

“Stored Procedures are like crack, they are addictive and harmful.”

I agree they are addictive, but harmful?

“They are difficult to version control, test and deploy.”

This is of course not true.

Deployments can be tricky though. How can you be sure none of the functions in the production
database have been locally modified by some evil consultant?
In a hurry, he did a \ef thinking “I’ll commit this change to the repo later, I won’t forget!”.

Months later, the function needs to be modified again, this time by someone else, who makes
the change to the function source code file in the VCS, commits, tests and deploys the function.

Whops! The local modification made by the evil consultant is gone! Alarms goes off, people are
screaming “the system is down!”, people are running around in circles, you revert your change,
back to the previous version in the VCS, but people are screaming even higher “the system is down!”.

But all the tests passed! How could this happened!

To prevent this scenario from happening, I’ve made a simple tool which I always use when deploying functions.

It’s called PgDeploy. I’ve been using it in production successfully since 2008.

It gives you a second chance to review the diff your deployment would actually cause in the
production database, by comparing the new source code with what’s already in the database.

-- SYNOPSIS:
SELECT deploy(SQL text, MD5 char(32));

-- Steg 1, preview:
-- Watch the diff. Get MD5 hash.

SELECT deploy($SQL$
    CREATE OR REPLACE FUNCTION myfunc() RETURNS BOOLEAN AS $$
    DECLARE
    BEGIN
       -- source code of function
       RETURN TRUE;
    END;
    $$ LANGUAGE plpgsql;
$SQL$, NULL);

-- The output returned is a diff showing what functions were removed,
-- created, replaced or updated.

-- Steg 2, deploy:
-- Pass MD5 from step 1 as second argument to confirm deployment.

SELECT deploy($SQL$
    CREATE OR REPLACE FUNCTION myfunc() RETURNS BOOLEAN AS $$
    DECLARE
    BEGIN
       -- source code of function
       RETURN TRUE;
    END;
    $$ LANGUAGE plpgsql;
$SQL$,'3b8297fb1408e480b05d1597ad5e65d5');

Below is a usage example, where two developers modify the same function,
where one of them forgets to commit, causing a difference of
the source code in the database and in the VCS.

DAY 1. You deploy a new function:

SELECT deploy($SQL$
CREATE OR REPLACE FUNCTION calc_value_added_tax(amount numeric) RETURNS NUMERIC AS $$
DECLARE
_vat numeric := 0.25;
BEGIN
RETURN amount * (1 + _vat);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
$SQL$, NULL);

The deploy function returns as text showing any removed,
new or updated/replaced functions caused by your SQL-code.
In this case, we can see a new function was created, like expected:

                      deploy                      
--------------------------------------------------
 +-------------------+                           +
 | Removed functions |                           +
 +-------------------+                           +
                                                 +
                                                 +
                                                 +
 +---------------+                               +
 | New functions |                               +
 +---------------+                               +
                                                 +
 Schema................+ public                  +
 Name..................+ calc_value_added_tax    +
 Argument data types...+ amount numeric          +
 Result data type......+ numeric                 +
 Language..............+ plpgsql                 +
 Type..................+ normal                  +
 Volatility............+ IMMUTABLE               +
 Owner.................+ postgres                +
 Source code (chars)...+ 70                      +
                                                 +
                                                 +
 +-------------------------------+               +
 | Updated or replaced functions |               +
 +-------------------------------+               +
                                                 +
 MD5 of changes: 74e9f61aa5a0ce1c333748ff589a3029
(1 row)

We will now pass the MD5 from the preview as second argument, confirming we want to deploy.

SELECT deploy($SQL$
CREATE OR REPLACE FUNCTION calc_value_added_tax(amount numeric) RETURNS NUMERIC AS $$
DECLARE
_vat numeric := 0.25;
BEGIN
RETURN amount * (1 + _vat);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
$SQL$, '74e9f61aa5a0ce1c333748ff589a3029');

The function has now been deployed.

DAY 2. Function is modified:

The functions needs to be modified, VAT should not be added for amounts less than 100.
Some evil consultant is assigned the task and makes the change locally in the database, but forgets to commit to the VCS.
This causes a discrepancy between the source code in the production database and the VCS. Not good at all!

CREATE OR REPLACE FUNCTION calc_value_added_tax(amount numeric) RETURNS NUMERIC AS $$
DECLARE
_vat numeric := 0.25;
BEGIN
IF amount < 100 THEN
    RETURN amount;
ELSE
    RETURN amount * (1 + _vat);
END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

DAY 3. You modify the function.

You are assigned the task to change the VAT from 25% to 12.5%.
You edit the function in the VCS, and change the single line of code,
and use the deploy function to make the deployment.

SELECT deploy($SQL$
CREATE OR REPLACE FUNCTION calc_value_added_tax(amount numeric) RETURNS NUMERIC AS $$
DECLARE
_vat numeric := 0.125;
BEGIN
RETURN amount * (1 + _vat);
END;
$$ LANGUAGE plpgsql IMMUTABLE;
$SQL$, NULL);

In the output from the deploy function you notice the change is not the single line
you were expecting, but a much bigger change!

                      deploy                      
--------------------------------------------------
 +-------------------+                           +
 | Removed functions |                           +
 +-------------------+                           +
                                                 +
                                                 +
                                                 +
 +---------------+                               +
 | New functions |                               +
 +---------------+                               +
                                                 +
                                                 +
                                                 +
 +-------------------------------+               +
 | Updated or replaced functions |               +
 +-------------------------------+               +
                                                 +
 Schema................: public                  +
 Name..................: calc_value_added_tax    +
 Argument data types...: amount numeric          +
 Result data type......: numeric                 +
 Language..............: plpgsql                 +
 Type..................: normal                  +
 Volatility............: IMMUTABLE               +
 Owner.................: postgres                +
 3 c _vat numeric := 0.25;                       +
 3 c _vat numeric := 0.125;                      +
                                                 +
 5 c IF amount < 100 THEN                     +
 5 c RETURN amount * (1 + _vat);                 +
                                                 +
 6 -     RETURN amount;                          +
 6 -                                             +
                                                 +
 7 - ELSE                                        +
 7 -                                             +
                                                 +
 8 -     RETURN amount * (1 + _vat);             +
 8 -                                             +
                                                 +
 9 - END IF;                                     +
 9 -                                             +
                                                 +
                                                 +
                                                 +
 MD5 of changes: 162d4dcc113345c71f6c9bc4448534aa
(1 row)

You realize someone must have made a local change without committing to the VCS!

The deployment is aborted, the deploy function saved the day!

https://github.com/joelonsql/PgDeploy