Re: BUG #14952: COPY fails to fill in IDENTITY column default value

On 2/1/18 20:49, Michael Paquier wrote:
> Wrapping again my mind on this one... On top of the comment for
> identitySequence, I think that it is important to mention that the
> sequence name and a RangeVar are basically safe as they get created
> hence they are not visible to other sessions yet. 0001 and 0002 should
> be merged.

Committed with more comments and as one patch.

> By the way, on HEAD with two sessions it is easy to bump into sequence
> name conflicts with identity columns:
> * Session 1:
> begin;
> create table itest13 (a int);
> * Session 2:
> create sequence itest13_b_seq;
> * Session 1:
> alter table itest13 add columns b int generated by default as identity; --blocks
> * Session 2:
> commit;
>
> And then session 1 reports that:
> ERROR: 23505: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
> DETAIL: Key (typname, typnamespace)=(itest13_b_seq, 2200) already exists.
> SCHEMA NAME: pg_catalog
> TABLE NAME: pg_type
> CONSTRAINT NAME: pg_type_typname_nsp_index
> LOCATION: _bt_check_unique, nbtinsert.c:434

I think this is equivalent to pre-existing behavior with serial:

S1: begin;
S2: begin;
S2: create sequence t1_a_seq;
S1: create table t1 (a serial, b text); --blocks
S2: commit;
S1: ERROR

I suspect you can also construct situations like this with other
implicitly created objects such as triggers.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services