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