Re: [PERFORM] Auto generate number in Postgres-9.1. - Mailing list pgsql-performance

On 03/20/2017 03:08 PM, Dinesh Chandra 12108 wrote:
> But on deletion it's not automatically re-adjusting the id's.
>
> Do I need to create trigger for this??

It is possible to do but I advice against adjusting the IDs on DELETE
due to to do so safely would require locking the entire table in the
trigger.

Note that serial columns will also get holes on ROLLBACK. In general I
think the right thing to do is accept that your ID columns can get a bit
ugly.

For example:

CREATE TABLE t (id serial);

INSERT INTO t DEFAULT VALUES;

BEGIN;

INSERT INTO t DEFAULT VALUES;

ROLLBACK;

INSERT INTO t DEFAULT VALUES;

Gives us the following data in the table:

  id
----
   1
   3
(2 rows)

Andreas