Обсуждение: [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

On 7/23/17 21:25, zam6ak@gmail.com wrote:
> 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.

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


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