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
