ansaurus

Question

Does Posgresql guarantee unique timestamps?

Answer 1

+1 A:

Even if it does, it seems dangerous to rely on the values being unique. If you need a unique key you are better off using an alternative such as a serial column.

Justin Ethier 2010-08-09 17:11:44

I'd love to do that - but my use case seems to require that: I want to use a version field in JPA that is globally at least mostly ascending, to improve performance (not just within an entity). But JPA unfortunately doesn't allow me to use a serial column, a sequence or a sequence table for a version field. But it does allow me to use a timestamp, and with JPA, I can safely assume the version field to be ascending (for an entity), as long as the database guarantees uniqueness.

Chris Lercher 2010-08-09 17:17:55

Answer 2

+1 A:

Yes, precision depends on the OS. Within a single transaction the timestamps are all the same when using CURRENT_TIMESTAMP, when using CLOCK_TIMESTAMP they might be different. Different transactions can have different CURRENT_TIMESTAMPS, depends on how fast you can insert/update.

If you need uniqueness, add a unique constraint.

Frank Heikens 2010-08-09 17:13:22

The unique constraint is a good idea, but one transaction can insert multiple instances in a table - so it would violate the uniqueness. It's ok for my application to have the same timestamp, but not when inserted by different transactions.

Chris Lercher 2010-08-09 17:22:29

Do some tests to see if you can insert/update fast enough to get problems.

Frank Heikens 2010-08-09 17:27:45

@Frank: Yeah, this is really a situation, where I'd have a simple, super-fast solution (for the version management in my distributed app), but at the price of a small chance of errors. Or on the other hand a solution, which would make the app much slower, and increase traffic by a significant factor - but it would be more correct...

Chris Lercher 2010-08-09 17:34:29

Answer 3

+4 A:

No.

I did a small test, inserting current_timestamp from 5 parallell clients in a table, 3 of 3463 records got the same timestamp.

nos 2010-08-09 17:21:06

Ok, that's the definite answer - I'll have to look for a different solution then. Thanks!

Chris Lercher 2010-08-09 17:23:30

Answer 4

+1 A:

Luckily no, since that would mean a "please make this slower than it has to be" option.

If you really need what is really a serial, but which looks like a timestamp, then add some microseconds on based on the result of a serial!

Jon Hanna 2010-08-09 17:27:14

Good point, and a good idea. I wish, JPA would allow me to do that (it manages the timestamps for version fields itself).

Chris Lercher 2010-08-09 17:30:31

Why does it insist that a version is unique. That seems like it's doing two jobs from the same thing. Could you add your own version and identity columns yourself and ignore this version+identity feature?

Jon Hanna 2010-08-09 17:45:40

@Jon: I hope, I'll find a way to extend/replace JPA's versioning with my own versioning, but I don't think it's easy to do, because it's an integral part of JPA.

Chris Lercher 2010-08-09 18:22:10

Do JPA have a way where they completely manage versioning themselves? In which case, just use an integral type.

Jon Hanna 2010-08-09 19:43:09

@Jon: Yes, JPA offers integral versioning and timestamp versioning. The problem with integral versioning is, that JPA starts counting at 1 for every entity in the table separately. So I can't do a query `from t where version > 578`, when I want to retrieve all entities that have changed since the last time I checked. Using a sequence (or setting the value myself) would be great, but JPA doesn't seem to allow that.

Chris Lercher 2010-08-09 20:51:44

That's a different requirement to versioning though (though slightly overlapping, and sometimes we can do both from the same value). Maybe have a version and a last-mod as separate columns?

Jon Hanna 2010-08-09 21:02:16

Answer 5

+1 A:

I agree with the recommendation of adding a serial. But if what you are trying to do doesn't merit a schema change, you could use the system column xmin to separate rows with the same timestamp but from different transactions.

  SELECT ts, xmin, count(1)
  FROM foo
  GROUP BY ts, xmin

Scott Bailey 2010-08-09 18:27:00

ansaurus

tags:

views:

answers:

Does Posgresql guarantee unique timestamps?

related questions