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

The following bug has been logged on the website:

Bug reference: 14756
Logged by: zam zam
Email address: zam6ak@gmail.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.

-- verify version
SELECT version();
--"PostgreSQL 10beta2 on x86_64-pc-linux-gnu, compiled by gcc (Debian
6.3.0-18) 6.3.0 20170516, 64-bit"

-- create test table
DROP TABLE public.audit
CREATE TABLE public.audit
(
--id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
relation_id oid NOT NULL,
schema_name text NOT NULL,
table_name text NOT NULL,
CONSTRAINT audit_pk PRIMARY KEY (id)
)

-- insure table is clean
TRUNCATE TABLE public.audit RESTART IDENTITY CASCADE;

-- initial inserts
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');

SELECT * FROM public.audit;
--1;16403;"public";"audit"
--2;16403;"public";"audit"

-- insert and specify PK value (works)
INSERT INTO public.audit OVERRIDING SYSTEM VALUE VALUES (3,
'public.audit'::regclass, 'public', 'audit');
INSERT INTO public.audit VALUES (3, 'public.audit'::regclass, 'public',
'audit');

SELECT * FROM public.audit;
--1;16403;"public";"audit"
--2;16403;"public";"audit"
--3;16403;"public";"audit"

-- 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)
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');

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs