Unique_constraint/3 and how it affects db

Phoenix Forum Questions / Help
1

I run unique_constraint/3 on username and email for my user registration process. unique_contraint/3 does it checks on the insert level in the process and not actually during validation, as I understood the docs :neutral_face:

This causes some interesting stuff in the db and I wonder if this is something i should care about or if it is known in the elixir world:

Screen Shot 2018-02-18 at 22.49.44.png1365×79 15.3 KB

As you can see, after the user kalle@kalle.se registered, there were a failed registration attempt. Then it succeded, but got id 17 instead of expected 16. In theory, could someone mess up the app by spamming the registration form? And can I somehow prevent this from happening?

2

Sequences are not transactional, so there will be gaps in the user ids when user registrations fail.

Does your application require gapless sequences? I tend to use the uuid type for primary keys.

2 Likes
3

Ecto relies more on Postgres for validation, while Active record uses callback.

Active record uniqueness does not impose constraint to the database, instead, it checks first…

Ecto just send to postgres and get the return value.

But as @mbuhot said, if You are afraid of exhausting ids, You can use uuid.

A bigger limitation is if You have multiple unique_constraints, You will get only the first failing…

4

Ah ok, thanks for the answers :slight_smile: . Except for my ocd there shouldnt be a problem. First time i see this, so better be sure.

I do i have two unique constraints, username and email. But im ok that one might validate on first try and the other on second. Gonna look up uuid as ids, sounds interesting. :stuck_out_tongue:

5

Databases can skip autoincrementing integer primary keys for many different reasons. Don’t assume they are strictly sequential with no empty spots. It won’t affect your application and if you use bigint you shouldn’t run out of numbers either.

1 Like
6

I have been looking at uuids for ecto 2.0 and it seems pretty straight forward to implement. What was not covered in those topics was why?

Is there any direct benefits using uuids instead of integers?

7

Two things that I know of:

  1. Your IDs are not sequential-ish, if you don’t want them to be. For example users cannot predict them as they are totally random (enough). This might also have benefits and drawbacks for indexing in DB, but I don’t know the specifics.
  2. A client can generate an UUID on the client side as collisions are almost nonexistent (of course it’s not that simple in practice but that’s one thing that I’ve read can be done).
1 Like
8

The big one for me is the ability to move data between databases and not worry about ID collisions. For example when migrating data from a legacy system into a new one you can just bulk insert the records preserving the IDs.

1 Like