Database Design Advice

Smylers Smylers at
Mon Nov 18 09:25:43 GMT 2013

Dagfinn Ilmari Mannsåker writes:

> Smylers <Smylers at> 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.

Ah, thank you for pointing that out.

In this case I think having a collision, and hence an error, may
actually be desirable: the application can detect the error and warn the
user that another change has already been made.

> If you want both to succed, with the last one winning, you can do it
> by keeping the current version in the document table,

I had been thinking that doing that would be bad, because it's
redundant, even though having it looks like it'd be convenient in a few

> and making the trigger update it:

But that sounds quite nice, the duplication seems liveable-with when
it's managed with a trigger.

>   ALTER TABLE document
>     ADD COLUMN current_version INTEGER NOT NULL DEFAULT 0;
>   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;

I do like Postgres's RETURNING clauses — they seem so much more flexible
and elegant than other ways of achieving the same ends.


The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more:
Please sign this petition:
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND

More information about the mailing list