Auto-increment a double column primary key, something like:
uid int(11) not null auto_increment, revision int(11) not null
auto_increment, primary key(uid, revision)
; works in mysql. You won't have your concurrent query issues there, for
the most part anyway. Maybe marking a records as tainted.
On 11/18/2013 10:25 AM, Smylers wrote:
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
