From: | mitramaddy(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #14291: Sequence ID gets modified even for "on conflict" update |
Date: | 2016-08-20 22:17:08 |
Message-ID: | 20160820221708.1517.56191@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: 14291
Logged by: Maddy Jones
Email address: mitramaddy(at)gmail(dot)com
PostgreSQL version: 9.5.4
Operating system: Windows 10
Description:
Hi, Here are the steps to replicate the bug:
Step 1: Create a simple table
CREATE TABLE public.test
(
username text NOT NULL,
fullname text,
id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
CONSTRAINT primary_test PRIMARY KEY (username)
)
Step 2 - Here is the sequence ID:
CREATE SEQUENCE public.test_id_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
Step 3 - Run the following simple UPSERT SQL command:
INSERT INTO test (Username,FullName) VALUES ('john','John H') ON
CONFLICT(Username) DO Update set FullName='John P' where
test.Username='john';
Step 4 - This will increase the "Start at" value in step 2 above for
test_id_seq (since we are doing a brand new insert).
Step 5 - Now run the EXACT same UPSERT command in step 3 for five times (or
more). In table test, it will change fullname to "John P".
However there is a bug in test_id_seq backend
Expected result: Since we are only doing updates in step 5, the "start at"
for test_id_seq should remain at 2.
Actual Result: Even though there are no inserts, the "start at" for
test_id_seq increases to 6.
Summary: It seems that Postgresql first updates test_id_seq even though
there are no actual inserts happening. It should only increment test_id_seq
when it does an actual insert.
I have a SQL UPSERT command which runs around 1000 times. What is happening
is id for brand new insert is 1. Then run the upsert command 1000 times. Now
do a brand new insert. The id for this should be 2. But it is actually 1001.
This huge difference in id in just two rows seems out of place.
Tested in Postgresql 9.5.4 on Windows 10.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-08-21 00:06:29 | Re: BUG #14291: Sequence ID gets modified even for "on conflict" update |
Previous Message | Kevin Grittner | 2016-08-19 15:27:48 | Re: Postgresql Performance Issue |