How to rollback the autogenerated sequence id after a failed constraint?

Questions / Help
1

I wonder if there is an idiomatic way for that.

2

You mean like pg sequence? That would be a very bad idea to do in production but you can use:
https://www.postgresql.org/docs/current/static/sql-altersequence.html And you really really do not want to do this. If your goal is gapless sequence it’s not really doable in PG unless you roll your own implementation that would basically rely on a table for the value and you would have to place EXCLUSIVE lock on the table in every transaction basically making all transaction using the sequence execute is serial manner.

1 Like
3

Oh, I see. And there is no way to not increase the id on a failed insert (because of a unique constraint, for example), I guess? Could a big gap between ids in my table hurt performance in any way? Right now I have this (from pg_dump):

71	...	2016-09-16 20:06:42
72	...	2016-09-16 20:06:42
73	...	2016-09-16 20:06:42
74	...	2016-09-16 20:06:42
3374	...	2016-10-10 16:37:04
3369	...	2016-10-10 16:37:04
3370	...	2016-10-10 16:37:04
4

Maybe a better question would be, does it matter? If you absolutely need sequential id’s (e.g. order numbers) then you can also make a separate table order_numbers and lock it while you increase the number.

Flow

  • get the record (based on your critera like per shop or something)
  • get the next_number field
  • update the order with the correct number
  • lock the table
  • increased the number
  • update the record

You can do this with Ecto.Multi and then your last step would be to create the order number

1 Like
5

It does not affect performance in any way, on the other hand running transactions serially will have a large negative impact on performance

2 Likes
6

@andre1sk @hlx thanks!

1 Like
7

np :slight_smile: