Thread: additional question about postgresql 10 's new identity column
postgresql version we're using in windows 10:
PostgreSQL 10.0, compiled by Visual C++ build 1800, 64-bit
we have the following table named subclass:
\d stk_m.subclass
Table "stk_m.subclass"
Column | Type | Collation | Nullable | Default
---------------+---------------+-----------+----------+------------------------------
subclass_id | integer | | not null | generated always as identity
subclass_code | character(3) | | |
subclass_desc | character(30) | | |
class_code | character(1) | | |
class_id | integer | | |
Indexes:
"subclass_pkey" PRIMARY KEY, btree (subclass_id)
"uq_subclass_code_class_id" UNIQUE CONSTRAINT, btree (subclass_code, class_id)
Foreign-key constraints:
"subclass_class_class_id_fk" FOREIGN KEY (class_id) REFERENCES stk_m.class(class_id)
the combined values for subclass_code and foreign key class_id must be unique tablewise.
at primary key subclass_id = 260,
when we do two successive inserts that violate the unique constraint, then do a valid insert,
we notice that the successful insert had subclass_id value of 263, so even tho the prior two inserts failed, they consumed the 261 and 262 values for the primary key.
is this the expected behavior? anything we can do or configure so that failed inserts wont advance the nextval for the primary key?
thanks for any info / help!
On Friday, December 15, 2017, john snow <ofbizfanster@gmail.com> wrote:
anything we can do or configure so that failed inserts wont advance the nextval for the primary key?
No. PostgreSQL favors concurrency in this. If you want to go gap-less you will need to write it up yourself.
David J.
thanks!
On Sat, Dec 16, 2017 at 11:27 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, December 15, 2017, john snow <ofbizfanster@gmail.com> wrote:anything we can do or configure so that failed inserts wont advance the nextval for the primary key?No. PostgreSQL favors concurrency in this. If you want to go gap-less you will need to write it up yourself.David J.