Re: BUG #14756: Inserting row with PK IDENTITY column fails 1st time

On 7/23/17 21:25, zam6ak(at)gmail(dot)com wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14756
> Logged by: zam zam
> Email address: zam6ak(at)gmail(dot)com
> PostgreSQL version: 10beta2
> Operating system: docker for windows 17.06 ce
> Description:
>
> - table with PK IDENTITY COLUMN
> - insert 1st row and specify PK value (OVERRIDING)
> - insert 2nd row using defaults (fails)
> - try same command again (succeeds)
>
> The failure is only on the 1st attempt.

This is working correctly, AFAICT. When you insert an explicit value
into the identity column, the sequence is not advanced.

> -- but now, neither of these works ("ERROR: duplicate key value violates
> unique constraint "audit_pk"")
> -- THEY ONLY DON"T WORK 1st TIME you try (either statement),
> -- on 2nd attept each succeeds (My guess 1st time sequence is
> updated/corrected so 2nd time it goes through)

That is correctly observed.

> INSERT INTO public.audit VALUES (DEFAULT, 'public.audit'::regclass,
> 'public', 'audit');
> INSERT INTO public.audit (relation_id, schema_name, table_name) VALUES
> ('public.audit'::regclass, 'public', 'audit');
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services