[BUGS] 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_idoid 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