How to get the next number in a sequence for PostgreSQL

Question

I am trying to do this for PostgreSQL. I understand in PostgreSQL Writers don't block readers. So how can I safely increment a sequence in a table. I am aware that PostgreSQL has a sequence feature but I am not looking for this because then I'd have thousands of sequences created in the database.

I have a table like this:

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
+----+-----------+------+-------+--+

How can I insert another record with the next seq after Stomach for Model 3. So here is what the new table suppose to look like:

+----+-----------+------+-------+--+
| id | Part      | Seq  | Model |  |
+----+-----------+------+-------+--+
| 1  | Head      | 0    | 3     |  |
| 2  | Neck      | 1    | 3     |  |
| 3  | Shoulders | 2    | 29    |  |
| 4  | Shoulders | 2    | 3     |  |
| 5  | Stomach   | 5    | 3     |  |
| 6  | Groin     | 6    | 3     |  |
+----+-----------+------+-------+--+

Is there a way to craft an insert query that will give the next number after the highest seq for Model 3 only. Also, looking for something that is concurrency safe.

There are [table locks](http://www.postgresql.org/docs/current/static/sql-lock.html) and [advisory locks](http://www.postgresql.org/docs/9.1/static/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS) if you really need them — Ihor Romanchenko, Apr 02 '16 at 19:04

redbmk · Answer 1 · 2016-04-03T15:55:47.897

You can query the table in the insert statement using the as keyword. You can find the full syntax here, but in this case the syntax you need is insert into <table_name> (<columns...>) <query>.

In order to make sure other sessions cannot run a similar query, it sounds like you'll need a SHARE ROW EXCLUSIVE (SRE) lock. According to the documentation:

This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time.

An SRE lock runs into a conflict with other ROW EXCLUSIVE (RE) locks, which are acquired during any insert, but will still allow for reading from the table. An RE lock on its own will not prevent other sessions from doing a simultaneous insert however, so a simple insert will not be enough to protect against concurrent changes.

An SRE lock is normally obtained automatically by altering a table or creating a trigger, but can also be created manually. You'll want to obtain the lock just before inserting, and then release it by committing your change as soon as possible.

For example:

begin work;
    lock table my_table in share row exclusive mode;

    insert into my_table
        (id, part, seq, model) as
    select
        max(id) + 1 as id,
        'Next Body Part' as part,
        max(seq) + 1 as seq,
        4 as model
    from
        my_table;
commit work;

A caveat to look out for is that this wouldn't prevent an application from fetching the max sequence (before or while the above statement is running), then processing that and creating a new insert statement using that variable. (e.g. ? as seq with a bind param of $max_sequence + 1). To make sure it stays concurrent, you'll want to make sure you get the sequence number in the same insert statement, or at the very least while the application has an SRE lock.

Is there a way to lock specific rows? I have a big table and don't want to lock the whole table. I want to prevent reads and writes when the rows are locked. — Luke101, Apr 02 '16 at 23:10
Well, it sounds like you might want a SHARE EXCLUSIVE LOCK instead. I've updated the post to reflect how that would work. You would still be able to read, but you shouldn't have a problem with concurrency if you mind the caveat mentioned at the end. — redbmk, Apr 03 '16 at 15:57

How to get the next number in a sequence for PostgreSQL

1 Answers1