Click here to Skip to main content
11,637,176 members (69,913 online)
Click here to Skip to main content

Optimistic Concurency and Audits using PostgresSQL

, 26 Jan 2009 CPOL 9.9K 9
Rate this:
Please Sign up or sign in to vote.
Discussion of concurency and audit trails using the Postrgres database.


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


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
    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)

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

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 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;
        INSERT INTO MinLog (Log_Text) Values ('UPDATE');
            INSERT INTO MinUser_audit VALUES (DEFAULT, 'U', now(), NEW.*);
            RETURN NEW;
        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
$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.

UUID,        -- User_Id
INT,        -- User_Currency
varchar,    -- User_Name
varchar,    -- User_Password
varchar         -- Mod_User
        User_Name = $3,
        User_Password = $4,
        User_Currency = $2 + 1,
        Mod_User = $5
        User_Id = $1 AND User_Currency = $2;

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

' 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.




  • 27 January 2008 - Initial release.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Web Developer
United States United States
I am a Director of Engineering, have an MBA and work in C# forms, Asp.Net and I have been writing Windows program since windows 3.0. I am currently working in the Healthcare industry.

I enjoy reading, music (most types), cars, and cigars. I am involved in opensource projects at codeplex.

My linkedin link is

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150728.1 | Last Updated 26 Jan 2009
Article Copyright 2009 by Donsw
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid