1

I am making a simple note app, and I want to create a table to track a note's edit history and save them with an incrementing version as a composite key with the note's id. So for example:

note_id | version | content
----------------------------
    1   |    1    | some_text
    1   |    2    | some_text 
    2   |    1    | some_text
    2   |    2    | some_text 
    1   |    3    | some_text 
    3   |    1    | some_text 
    1   |    4    | some_text

The composite key is made up of note_id and version I want to increment version for each note_id Is there a way to automate this? I have seen some other posts, and experimented with triggers and functions, but I have not yet been able to get anything to work. Any advice is greatly appreciated!

Improve this question

2 Answers 2

Reset to default
0

Keep in mind with this approach the id's and versions have to be searched for each insert, so it's good to have an index to speed that up. To avoid constant searches of the main table, you can maintain a table with latest version for each note_id. The search still has to take place, but there's less to search through: demo

create table latest_note_versions(
  note_id int primary key,
  version int default 1          );

create function tgf_notes_version() returns trigger language plpgsql as $f$ 
begin 
  with version_upsert as (
      insert into latest_note_versions AS lnv 
      values (NEW.note_id)
      on conflict(note_id) do update set version=lnv.version+1
      returning version)
  select version from version_upsert into NEW.version;
  return NEW;
end $f$;

create trigger tg_a_gen before insert on notes
for each row execute function tgf_notes_version();

Now you can insert nothing or anything into the version column and the trigger will take care of it:

insert into notes values 
  (1,999,'my_own_text11'), 
  (1,999,'my_own_text12'), 
  (3,123,'my_own_text31'), 
  (3,321,'my_own_text32'), 
  (3,0,'my_own_text33')
returning *;
note_id version content
1 1 my_own_text11
1 2 my_own_text12
3 1 my_own_text31
3 2 my_own_text32
3 3 my_own_text33

You need to formulate your own rules on what to do if someone comes in and deletes or updates something in the table, or remember not to rely on the continuity of version sequences - which is actually a good advice for any sequence due to caching and transaction behaviour.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

You are aware that multiple inserts can get the same version number?
You're right. Two simultaneous sessions will see the same snapshot and fetch the same maximum. This would quickly go sideways combined with a possible primary key (note_id,version).
@FrankHeikens When the comment arrived I was in the process of adding the version with the support table and an upsert as an alternative, but after your remark I figured it's better to replace that initial version with it entirely. This one was meant to only be faster, but turns out it's also more valid (as compared to not at all). Thanks for the catch
0

Cool I actually got this to work, hopefully this can help someone else in the future. Thanks to this answer which I made some slight tweaks too, but mostly it just worked.

--Logic to track and automatically increment a note's version number in the notes_history table
--A table to track the current version of each note
CREATE TABLE notes_version_counter(
  note_id UUID PRIMARY KEY REFERENCES users(id),
  current_version INTEGER NOT NULL
);

--A function to calculate the next version for a note
CREATE FUNCTION next_version(p_note_id UUID)
  RETURNS INTEGER
AS
$$
  INSERT INTO notes_version_counter (note_id, current_version)
  VALUES (p_note_id, 1)
  ON CONFLICT (note_id)
  DO UPDATE
    SET current_version = notes_version_counter.current_version + 1
  RETURNING current_version;
$$
language sql
volatile;

--A function to increment the version of a note
CREATE FUNCTION increment_version()
  RETURNS TRIGGER
AS
$$
BEGIN
  new."note_version" := next_version(new."note_id");
  RETURN new;
END;
$$
language plpgsql;

--A trigger when inserting a note into the history table to call the increment_version function
CREATE TRIGGER TR_notes_history_increment_version
  BEFORE INSERT ON notes_history
  FOR EACH ROW
  EXECUTE PROCEDURE increment_version();

And for reference, here are my main tables

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  username varchar(20) NOT NULL UNIQUE,
  password varchar(20)
);

CREATE TABLE notes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES users(id) NOT NULL,
  title VARCHAR(100),
  note VARCHAR(5000),
  tag VARCHAR(100),
  created TIMESTAMPTZ NOT NULL,
  edited TIMESTAMPTZ NOT NULL
);

CREATE TABLE notes_history (
  note_id UUID REFERENCES users(id) NOT NULL,
  note_version INTEGER NOT NULL,
  title_historic VARCHAR(100),
  note_historic VARCHAR(5000),
  tag_historic VARCHAR(100),
  edited TIMESTAMPTZ NOT NULL,
  PRIMARY KEY(note_id, note_version)
);
Improve this answer

2 Comments

The DDL's make sense as an attachment to the question, not as a big reveal in a self-answer closing the thread :) It seems like you've ended up with pretty much the exact same idea, although I'm not sure why a_horse_with_no_name set up the increment function separately from the trigger, other than narrative purposes.
@Zegarek Yes, pretty similar. I had tried more like your way first but kept running into syntax issues. Cool to see other approaches to the same problem. Thanks for your input to this!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.