I have a web application in which data can be changed concurrently by the users. At the moment I include the old row values in each form and update the row only if the data is the same. With SQLite this to be the only option. This is ugly and I consider switching to another SQL database if it would provide a better way to do this. Do PostgreSQL or MySQL have implicit row timestamps or version numbers which one could use instead?
Asked
Active
Viewed 1,010 times
3 Answers
3
Using a numerical counter is better than using a timestamp. However exact the timestamp, it's possible that two versions of the data could be committed at the same time and get the same timestamp. By using a numerical counter (e.g. update mytable set counter=counter+1, data=? where id=? and counter=?) then each time the row gets changed it gets a unique counter value. (Supply the original counter value in the where clause, if the data has been changed by someone else then no rows will be matched.)
Although this is not an "implicit" solution, I think it's OK. Libraries such as Hibernate have facilities to let you do this sort of thing automatically, so your code doesn't have to worry about it.
Adrian Smith
- 17,236
- 11
- 71
- 93
2
MySQL has a TIMESTAMP data type that can be used for this purpose, when combined with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP constraints.
In PostgreSQL, there is a "hidden field" on every table called xmin that can be used to determine the row version.
Eric Petroelje
- 59,820
- 9
- 127
- 177
-
Is the `xid` updated when a transaction is in progress and updated the row or only when the transaction was committed and the row was changed. – Jan 31 '11 at 18:17
-
@ott - I believe that is updated only after the transaction is committed. – Eric Petroelje Jan 31 '11 at 18:20
-
It seems also that `xid`s can potentially overflow while the data is modified (very unlikely, but it has to be considered), so I'm not sure whether one should rely on them. – Jan 31 '11 at 21:47
-
xid's wrap around, so you have to know how the rules for comparing them (e.g. using the `age()` function). If you update a row, you will see the new `xmin` on the new version of the row immediately, not just after the end of the transaction. – araqnid Feb 01 '11 at 11:11
0
AFAIK, getting an update timestamp in Postgres requires a trigger, see this very similar question:
Update timestamp when row is updated in PostgreSQL
That question (and Eric's answer) point out that MySQL supports this w/o a trigger.
-
Timestamps are generally not an option. I could do this in SQLite too. Versioning should be the task of the database, not the application. – Jan 31 '11 at 18:17