Database Design Advice

[Philip Skinner]

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