Optimistic Concurency and Audits using PostgresSQL

Introduction

This article will show how to implement concurrency with audit tables using PostgreSQL.

Background

Teo wrote about an Optimistic Concurrency control in the article ASP.NET Optimistic Concurrency Control. I decided to do the same for PostgreSQL to see how it was done in that database but also expanding on my last article using audits.

Using the Code

As was outlined by Teo, you can use a field to determine if an update has been done to your record since you read it last. In his article, he used a date column; I use an int and just increment the value for each update. If the record of the same integer value is there, then no one has changed it since it was read. I have modified the tables since the last article in that I added a Mod_User field. This will show who was the last person who modified the data. This is very important to companies who cater to Sarbanes-Oxley, or HIPAA as they are required to keep track of who changed what.

//
// The main data table along with the audit table
//
CREATE TABLE MinUser (
    Mod_User            varchar (50) NOT NULL,
    User_Id               UUID NOT NULL PRIMARY KEY,
    User_Currency            int not null, 
    User_Name             varchar (50) NOT NULL,
    User_Password            varchar (50) NOT NULL,
    UNIQUE (User_Name)
) WITH (OIDS=FALSE);


CREATE TABLE MinUser_Audit (
    AuditUser_Id               Serial PRIMARY KEY,
        operation                 char(1)   NOT NULL,
        stamp                    timestamp NOT NULL,
    Mod_User            varchar (50) NOT NULL,
    User_Id               UUID NOT NULL,
    User_Currency            int not null, 
    User_Name             varchar (50) NOT NULL,
    User_Password            varchar (50) NOT NULL
) WITH (OIDS=FALSE);

Points of Interest

The new function will take Mod_User and update the audit record to keep track of who modified what. In the last article, I used the function user which returns the current role who is executing the function. Since we logged into the server as the admin account (postgres), all of the records will show postgres as the person who modified the data. If we were to create a new role for each user who used the system, that would be fine. Since we are going to use the connection string to log into the database and the login account information will give us the permissions, using the database login information is unacceptable. We need to keep track of the exact person who logged into the system and modified the data.

CREATE OR REPLACE FUNCTION MinUser_audit() RETURNS TRIGGER AS $usr_audit$
    BEGIN
        --
        -- Create a row in MinUser_Audit to reflect the operation performed on MinUser,
        -- make use of the special variable TG_OP to work out the operation.
        --
    INSERT INTO MinLog (Log_Text) Values ('Trigger');
        IF (TG_OP = 'DELETE') THEN
        INSERT INTO MinLog (Log_Text) Values ('Delete');
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'D', now(), OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO MinLog (Log_Text) Values ('UPDATE');
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO MinLog (Log_Text) Values ('INSERT');
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'I', now(), NEW.*);
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$usr_audit$ LANGUAGE plpgsql;

The update Stored Procedure will try and update the record and increment the User_Currency value when it saves it back to the database. The WHERE clause is searches for the exact record we first retrieved, if we were able to update the record fine; otherwise, we see if the record exists for any currency value. If it does not exist, then the record was deleted. I used the same return values as Teo has in his article to make life simple.

CREATE OR REPLACE FUNCTION Update_MinUser(
UUID,        -- User_Id
INT,        -- User_Currency
varchar,    -- User_Name
varchar,    -- User_Password
varchar         -- Mod_User
)
RETURNS int AS '
BEGIN
    
    UPDATE
        MinUser 
    SET
        User_Name = $3,
        User_Password = $4,
        User_Currency = $2 + 1,
        Mod_User = $5
    WHERE
        User_Id = $1 AND User_Currency = $2;

    IF found THEN
        RETURN 0;        -- The record could be updated
    ELSE
        IF EXISTS( SELECT User_Name FROM MinUser WHERE User_Name = $3 ) THEN
            RETURN 2;    -- Concurrency conflict
        ELSE
            RETURN 1;    -- The record has been deleted
        END IF;
    END IF;

END;
' LANGUAGE 'plpgsql' volatile;

Now, to test the procedures, we can execute the two commands below. The first will create the record, and the second will update the same record hopefully.

Create_MinUser('1DC422DB-7A82-410c-9465-98BBB692D1C8', 'donsw', 'password', 'don')
Update_MinUser('1DC422DB-7A82-410c-9465-98BBB692D1C8', 1, 'donsw', 'password1', 'hazel')

Now, we query the database to see the result and if the records were actually updated. Doing a Select on the data table, you can see the last update. The second image is the audit table.

History

• 27 January 2008 - Initial release.