track revisions in postgresql

Question

I have to keep track of revisions of records in a table. What I've done is create a second table that inherits from the first and adds a revision counter.

CREATE TABLE A (
id SERIAL,
foo TEXT,
PRIMARY KEY (id));

CREATE TABLE B (
revision INTEGER NOT NULL) INHERITS (A);

Then I created a trigger that would update table B every time A is inserted/updated. What I can't figure out is how to make B.revision keep an individual "sequence" for each id.

Example: table A has 2 rows, i & j.
i has been updated 3 times and should have 3 revisions: (1, 2, 3).
j has been updated 2 times and should have two revisions: (1, 2).

Here is what I have so far, maybe I'm going down the wrong path and someone can help me!

CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
    DECLARE
        last_revision INTEGER;
    BEGIN
        SELECT INTO last_revision MAX(revision) FROM B WHERE id = NEW.id;

        IF NOT FOUND THEN
            last_revision := 0;
        END IF;

        INSERT INTO B SELECT NEW.*;

        RETURN NEW;
    END;
$table_update$ LANGUAGE plpgsql;

CREATE TRIGGER table_update
AFTER INSERT OR UPDATE ON A
    FOR EACH ROW EXECUTE PROCEDURE table_update();

Laomedon

Answer 1

If you need the version numbers just for ordering, and don't specifically need them to be an integer that increase by one for each identifier, the easiest way to do it is to use a sequence for the revision and just let it do the tracking for you:

CREATE TABLE A (
    id SERIAL,
    foo TEXT,
    PRIMARY KEY (id)
);

CREATE TABLE B ( revision SERIAL NOT NULL) INHERITS (A);

CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
    BEGIN
        INSERT INTO B SELECT NEW.*;
        RETURN NEW;
    END;
$table_update$ LANGUAGE plpgsql;

CREATE TRIGGER table_update
AFTER INSERT OR UPDATE ON A
    FOR EACH ROW EXECUTE PROCEDURE table_update();

Then do the inserts as usual:

    try=# insert into a (foo) values ('bar');
    INSERT 0 1
    try=# insert into a (foo) values ('bar');
    INSERT 0 1
    try=# update a set foo = 'you' where id = 1;
    UPDATE 2
    try=# select * from b;
     id | foo | revision 
    ----+-----+----------
      2 | bar |        2
      1 | you |        1
      1 | you |        3
    (3 rows)

So you can get all revisions for a given row like so:

    try=# select * from b where id = 1 order by revision;
     id | foo | revision 
    ----+-----+----------
      1 | you |        1
      1 | you |        3
    (2 rows)

Starrstarred

This makes lot of sense. It would be best that the OP changes his requirements to make room for this, because else things would require locking as you mention. – Photography

Hrm. And I just noticed that it doesn't show the actual revision information. I inserted the record at r1 as "bar" and updated it in r3 as "you", but the results in that last query show "you" for both revisions. To fix that, B should not inherit from A. use LIKE instead of INHERITS to decouple them: CREATE TABLE B ( LIKE A, revision serial NOT NULL);. – Starrstarred

Or use the "only" keyword. But yes, it might be less confusing to just use separate tables. – Untouched

Well, you could use a window function on PostgreSQL 8.4 to get revisions as sequences of numbers: select *, rank() over (partition by id order by revision) as version from b where id = 1;. – Starrstarred

Yes, and mind the ONLY keyword, solves your second comment, as small_duck says. – Photography

I really like this solution, and the serial for table B is what i was doing, but needed to have a revision # sequence unique to each row in table A. I didn't know about the rank() function - which would solve my problem, except I'm stuck on vrs 8.2.9 and upgrading is not in my control. Is there anything similiar to that function i could look into w/ my current version? – Laomedon

Answer 2

Here's my suggestion:

CREATE OR REPLACE FUNCTION table_update() RETURNS TRIGGER AS $table_update$
DECLARE
    last_revision INTEGER;
BEGIN
    SELECT INTO last_revision coalesce(MAX(revision), 0) FROM B WHERE id = NEW.id;

    INSERT INTO B SELECT NEW.*, last_revision + 1;

    RETURN NEW;
END;
$table_update$ LANGUAGE plpgsql;

I changed the "if not found" into a coalesce, that will pick the "0" if there is no existing revision. Then, I insert in B the row, with the incremented revision.

Be careful with your inheritance: you will need to use the "only" keyword to limit yourself to the A table when selecting and updating, as such:

select * from only A
update only A set foo = ... where id = ...

Untouched

This solution has a race condition. In order to avoid the race condition, you'd have to lock all the records in B with id = NEW.id before you could do the insert. The use of a sequence avoids the race condition and therefor requires no lock. – Starrstarred

So you should add a SELECT FOR UPDATE * FROM B WHERE id = NEW.id , also it should be COALESCE(MAX(revision)+1,0) to get a new revision id instead of the same. – Photography

Agreed, this code would not work with concurrent access. If wanting to deal with race conditions, we would lose the ability to have a single consecutive sequence per id. – Untouched

Answer 3

--THIS TABLE AUTOMATICALLY INCREMENT THE COLUMN VALUES USING TRIGGER
CREATE TABLE emp_table(
  emp_id int not null,
  emp_name varchar not null,
  emp_rollno int not null,
  primary key(emp_id)
);

--Now create table with three column and emp_id is primary key
--and emp_rollno both are automatically increment in trigger is fired
CREATE or REPLACE FUNCTION emp_fun() RETURNS TRIGGER AS $BODY$
--creating function emp_fun()
DECLARE
BEGIN
  IF(tg_op='INSERT') THEN
    NEW.emp_id=COALESCE((SELECT MAX(emp_id)+1 FROM emp_table), 1);
    NEW.emp_rollno=COALESCE((SELECT MAX(emp_rollno)+1 FROM emp_table), 1);
    --trigger is fired values is automatically increment
END IF;

IF tg_op='DELETE' THEN RETURN OLD; ELSE RETURN NEW; END IF;
END; $BODY$LANGUAGE PLPGSQL

CREATE TRIGGER emp_fun BEFORE INSERT ON
  emp_table FOR EACH ROW EXECUTE PROCEDURE emp_fun();

INSERT INTO emp_table(emp_name) VALUES('BBB');
--insert the value tanle emp_table
SELECT * FROM emp_table
-- Check the result

Derna

please take a look at stackoverflow.com/editing-help#code and try to edit your answer, so that code sticks out. I didn't know how to edit your answer, because I couldn't follow what you are doing... – Intuitivism

Answer 4

Here is a feature rich Aduit package for postgres that I've used in the past: Audit Trigger. It tracks the type of update (insert, update, delete) as well as the before and after values for the update.

Beene