Database Design Advice

[Smylers]

Dagfinn Ilmari Mannsåker writes:

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

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

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

Cheers

Smylers
-- 
The UK gov's proposed gagging law will suppress protest and campaigning by
charities and political groups. Read more: http://civilsocietycommission.info/
Please sign this petition: http://38d.gs/1cJ9ViA
Oxfam • BHA • Quakers * Countryside Alliance • League Against Cruel Sports • CND