Обсуждение: BUG #13584: Upsert. The sequence value is increased.

The following bug has been logged on the website:

Bug reference:      13584
Logged by:          jedi
Email address:      av.kostromin@gmail.com
PostgreSQL version: 9.5alpha2
Operating system:   Windows 7
Description:

Hello, I have the following bug.
I have created the following sequence:
CREATE SEQUENCE public.t_tags_ident_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 41
  CACHE 1;
ALTER TABLE public.t_tags_ident_id_seq
  OWNER TO postgres;

and table:

CREATE TABLE public.t_tags_ident
(
  id smallint NOT NULL DEFAULT nextval('t_tags_ident_id_seq'::regclass),
  tag_name character varying(100) NOT NULL,
  comment text,
  reserve character varying(250) DEFAULT NULL::character varying,
  CONSTRAINT t_tags_ident_pk PRIMARY KEY (tag_name)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.t_tags_ident
  OWNER TO postgres;

When I execute the query

INSERT INTO t_tags_ident (tag_name, "comment")
 VALUES ('tag1','comment1'), ('tag2','comment2'), ('tag3','comment3'),
('tag4','comment4')
 ON CONFLICT (tag_name)
 DO UPDATE SET "comment" = EXCLUDED.comment
 RETURNING id
there is the following problem.  The sequence value is increased by the
quantity of updated (conflict) rows, i.e. when I insert the new row its id
is equal to the quantity of updated rows + 1. Is it OK for upsert?

On Sat, Aug 22, 2015 at 11:25 AM,  <av.kostromin@gmail.com> wrote:
> INSERT INTO t_tags_ident (tag_name, "comment")
>  VALUES ('tag1','comment1'), ('tag2','comment2'), ('tag3','comment3'),
> ('tag4','comment4')
>  ON CONFLICT (tag_name)
>  DO UPDATE SET "comment" = EXCLUDED.comment
>  RETURNING id
> there is the following problem.  The sequence value is increased by the
> quantity of updated (conflict) rows, i.e. when I insert the new row its id
> is equal to the quantity of updated rows + 1. Is it OK for upsert?

This is the expected behavior. It is not a bug.

--
Peter Geoghegan