Dependent SERIAL column in PostgreSQL

Question

I want to get this result in my table contacts:

 |contact_id |  user_id         |  user_contact_id     |
 +-----------+------------------+----------------------+
 |  1        |  1               |          1           |
 +-----------+------------------+----------------------+
 |  2        |  1               |          2           |
 +-----------+------------------+----------------------+
 |  3        |  1               |          3           |
 +-----------+------------------+----------------------+
 |  4        |  2               |          1           |
 +-----------+------------------+----------------------+
 |  5        |  2               |          2           |
 +-----------+------------------+----------------------+
 |  6        |  2               |          3           |
 +-----------+------------------+----------------------+
 |  7        |  3               |          1           |
 +-----------+------------------+----------------------+

I'm going to insert only user_id.

INSERT INTO contacts (user_id) VALUES ($user_id);

The contact_id will auto-increment because it's a serial. I want user_contact_id to also populate automatically by the DB itself, so it is 100% stable with concurrent writes.

Follow-up to this previous, related question. http://stackoverflow.com/questions/30469032/postgresql-inner-queries-with-prepared-statements — Erwin Brandstetter, May 28 '15 at 01:29
This is **not trivial** due to concurrency issues. You need to be very precise. Please clarify *in your question*: Your version of Postgres? Does `(user_id, user_contact_id)` have to be unique or are occasional dupes ok - which can be be distinguished by `contact_id`. (Would make it *a lot* simpler.) How to deal with gaps in `user_contact_id` due to `DELETE` and/or `UPDATE`, or is it `INSERT`-only? Are gaps in the sequence ok? You are aware that gaps are *to be expected* in `serial` columns under concurrent load, right? And is this use case for *few* or *many* users? — Erwin Brandstetter, May 28 '15 at 02:07
@ErwinBrandstetter : Im using Postgres 9.4.1, and dupes are no excuse, I need a strict lineup, because Im going to use contact_user_id in combination of user_id so it must be always unique — CodeRows, May 28 '15 at 05:13
Am I reading your short comment correctly: you are not going to clarify your question any further? — Erwin Brandstetter, May 28 '15 at 14:48
@ErwinBrandstetter I used another oprion with inner query to select max valud of the coolumn and + 1 to that — CodeRows, May 28 '15 at 14:52

Dondi Michael Stroma · Answer 1 · 2015-05-28T20:45:01.553

0

INSERT INTO CONTACTS
    (user_id, user_contact_id)
VALUES
    ($user_id, (SELECT COALESCE(MAX(user_contact_id), 0) + 1 FROM CONTACTS WHERE user_id = $user_id))

edited May 28 '15 at 20:45

answered May 27 '15 at 22:28

Dondi Michael Stroma

4,668
18
21

1

Michael - thats cool, but how about concurrency, Im going to use this where I have lots of clients inserting records into the DB – CodeRows May 28 '15 at 05:31
2

This also doesn't work if there is a new user since MAX will return null and null+1 == null. – Sami Kuhmonen May 28 '15 at 06:05
@SamiKuhmonen thats true, I agree, do you have any workaround for this? – CodeRows May 28 '15 at 06:15
1

@CodeRows One could use `COALESCE` for this, using `COALESCE(MAX(user_contact_id), 0)+1` – Sami Kuhmonen May 28 '15 at 06:16
@SamiKuhmonen ah, I didnt know anything about COALESCE is it possible for you to frame a complete query? apologies Im not very sure about the syntax, so I can try if you post me here – CodeRows May 28 '15 at 06:19
@SamiKuhmonen wow, just read the dcumentationa and got it running, you are awesome, thanks a lot- is there a minute for you to let me know about the concurrency with these inner queries? Im not an expecrt on DB, so trying to know the most the I can :) – CodeRows May 28 '15 at 06:24
1

@CodeRows Your concurrency concerns were right. You shouldn't use it - it's race vulnerable. – Radek Postołowicz May 28 '15 at 15:05
@CodeRows first you should have a unique constraint on (user_id, user_contact_id). Then you can decide whether you want to fail and retry on a unique key violation or you can lock the table first to prevent it from happening to begin with. But the bigger question is, why do you want to do this? It's not a good design. – Dondi Michael Stroma May 28 '15 at 20:55

score 0 · Answer 2 · answered May 28 '15 at 15:33

As other users suggested only sequence-s or serial type are guaranteed to be concurrent safe.

If you really need to have user_contact_id "restarted" every user_id maybe you could use following view:

create view contacts_v as
select
  contact_id,
  user_id,
  rank() over (partition by user_id order by contact_id) as user_contact_id
from contacts;

score -2 · Answer 3 · answered May 27 '15 at 22:25

You should use a sequence as a default value for your user_contact_id. And this is exactly what the SERIAL column type is doing.

http://www.postgresql.org/docs/9.1/static/datatype-numeric.html http://www.postgresql.org/docs/9.4/static/sql-createsequence.html

Sequences are safe with concurrent writes.

CREATE TABLE contacts (
  contact_id      SERIAL PRIMARY KEY,
  user_id         INTEGER,
  user_contact_id SERIAL
);

INSERT INTO contacts (user_id) VALUES
  (1), (1), (1), (2), (2), (2), (3);

And here are the results:

> SELECT * FROM contacts;
 contact_id | user_id | user_contact_id
------------+---------+-----------------
          1 |       1 |               1
          2 |       1 |               2
          3 |       1 |               3
          4 |       2 |               4
          5 |       2 |               5
          6 |       2 |               6
          7 |       3 |               7

> \d contacts
                                    Table "public.contacts"
     Column      |  Type   |                             Modifiers
-----------------+---------+--------------------------------------------------------------------
 contact_id      | integer | not null default nextval('contacts_contact_id_seq'::regclass)
 user_id         | integer |
 user_contact_id | integer | not null default nextval('contacts_user_contact_id_seq'::regclass)
Indexes:
    "contacts_pkey" PRIMARY KEY, btree (contact_id)

My understanding of the original question is that OP wants `user_contact_id` to have independently increasing values per `user_id`. — fvu, May 27 '15 at 22:30
Oh, I didn't noticed that sorry, I'll just delete this answer if that's the case — Clément Prévost, May 27 '15 at 22:47

Dependent SERIAL column in PostgreSQL

3 Answers3