From: | zam6ak(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | zam6ak(at)gmail(dot)com |
Subject: | BUG #14756: Inserting row with PK IDENTITY column fails 1st time |
Date: | 2017-07-24 01:25:17 |
Message-ID: | 20170724012517.1455.83097@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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.
-- 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');
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2017-07-24 04:53:07 | Re: PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING |
Previous Message | Tom Lane | 2017-07-21 17:09:11 | Re: PgFDW connection invalidation by ALTER SERVER/ALTER USER MAPPING |