| From: | Andreas Karlsson <andreas(at)proxel(dot)se> |
|---|---|
| To: | Dinesh Chandra 12108 <Dinesh(dot)Chandra(at)cyient(dot)com>, John Gorman <jgorman(at)eldocomp(dot)com> |
| Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, "pgsql-performance-owner(at)postgresql(dot)org" <pgsql-performance-owner(at)postgresql(dot)org> |
| Subject: | Re: Auto generate number in Postgres-9.1. |
| Date: | 2017-03-20 15:40:50 |
| Message-ID: | efd88f08-1a8e-c883-9842-38fe63588c18@proxel.se |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | James Parks | 2017-03-21 19:24:52 | Optimizing around retained tuples |
| Previous Message | Dinesh Chandra 12108 | 2017-03-20 14:08:07 | Re: Auto generate number in Postgres-9.1. |