Database Design Advice

[Dagfinn Ilmari Mannsåker]

Smylers <Smylers at stripey.com> writes:

> Here it is in Postgres's own function language, which goes by the
> awkwardly written name PL/pgSQL:
>
>   CREATE OR REPLACE FUNCTION set_document_version_num() RETURNS TRIGGER AS
>   $FN$
>     BEGIN
>       NEW.version := 1 +
>           MAX(version)
>           FROM document_version
>           WHERE document_id = NEW.document_id;
>       RETURN NEW;
>     END;
>   $FN$ LANGUAGE plpgsql;
>
>   CREATE TRIGGER insert_document_version_num
>   BEFORE INSERT ON document_version FOR EACH ROW
>   EXECUTE PROCEDURE set_document_version_num();

This trigger works fine, until you get concurrent saves of the same
document, in which case one of the transactions will get a duplicate key
violation. If you want both to succed, with the last one winning, you
can do it by keeping the current version in the document table, and
making the trigger update it:

  ALTER TABLE document
    ADD COLUMN current_version INTEGER NOT NULL DEFAULT 0;

  -- If you already have document versions in the database
  UPDATE document
    SET current_version = v.max_version
    FROM (
      SELECT document_id, MAX(version) max_version
      FROM document_version group by document_id
    ) v
    WHERE document.id = v.document_id;

  REPLACE FUNCTION set_document_version_num() RETURNS TRIGGER AS
  $FN$
    BEGIN
      UPDATE document
        SET current_version = current_version + 1 
        WHERE id = NEW.document_id
        RETURNING current_version
        INTO NEW.version;
      RETURN NEW;
    END
  $FN$ LANGUAGE plpgsql;

-- 
- Twitter seems more influential [than blogs] in the 'gets reported in
  the mainstream press' sense at least.               - Matt McLeod
- That'd be because the content of a tweet is easier to condense down
  to a mainstream media article.                      - Calle Dybedahl