Dagfinn Ilmari Mannsåker writes: > Smylers <smyl...@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