3

I create the following table

CREATE TABLE dogs (
  id serial,
  name VARCHAR(15),
  age integer;

I have the table looking like this

                                  Table "public.birds"
 Column  |         Type          |                     Modifiers                      
---------+-----------------------+-------------------------------------    
 id      | integer               | not null default nextval('birds_id_seq'::regclass)
 name    | character varying(25) | 
 age     | integer               |

I insert two rows

INSERT INTO dogs (name, age) 
VALUES ('puffy', 13),
 ('fluffy', 15);

The table now looks like this

  id |  name  | age 
 ----+--------+-----
   1 | puffy  |  13
   2 | fluffy |  15
 (2 rows)

Then I delete the row with id = 2

DELETE FROM dogs WHERE id = 2;

And add another row instead

INSERT INTO dogs (name, age) VALUES('mimi', 20);

The table is

  id | name  | age 
 ----+-------+-----
   1 | puffy |  13
   3 | mimi  |  20
 (2 rows)

My question is why the next number for id in the second row is not 2 but 3? I guess that somewhere underneath something stores the last value in a memory and it doesn't matter that the row with that id was deleted. I know I can insert value for id explicitly if I need to. But I would like to be clear why it happens in this case. And what functionality or feature is responsible for that? How does it work?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
buterfly85
  • 163
  • 3
  • 11
  • 4
    That is how sequences work in postgres. In the other case they could cause transactions to lock, waiting for the update (decrement) of the sequence (or worse: picking one from a free list) – wildplasser Oct 26 '16 at 23:21
  • 2
    This is not limited to Postgres. Nearly all RDMS's behave that way with autonumber fields. Any used ids in deletes or failed commits are not reused unless you re-create table. Finally the sequential numbers is purely aesthetic and should not be used or viewed in application layer. – Parfait Oct 27 '16 at 02:00

1 Answers1

7

PostgreSQL makes no effort to keep track of deleted sequence IDs. It just uses a counter to get the next ID to generate.

Gaps will also appear if you generate values then ROLLBACK a transaction, the client connection crashes before committing, or the server crashes.

The only property you can rely on from generated IDs is uniqueness. You cannot even rely on them appearing in the table in the same order they're generated, since commit order isn't necessarily the same as ID allocation order.

If you need gapless sequences there are ways to implement them, but they have terrible performance in concurrent write loads. That's why PostgreSQL does things the way it does.

For more info, Google "gapless sequence postgresql" and read the documentation chapter on sequences and the "nextval" function.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778