BUG #14291: Sequence ID gets modified even for "on conflict" update
The following bug has been logged on the website:
Bug reference: 14291
Logged by: Maddy Jones
Email address: mitramaddy@gmail.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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
mitramaddy@gmail.com writes:
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.
This is not a bug. See previous discussions at, eg,
/messages/by-id/20160105150227.1117.51692@wrigleys.postgresql.org
/messages/by-id/20160506065528.2693.64808@wrigleys.postgresql.org
The core reason why it's not a bug is that the INSERT is attempted in full
and only after detecting a conflict in the attempted unique-index
insertion does the code fall back to the ON CONFLICT path.
More generally, though, it's not a terribly good idea to assume that the
sequence of numbers obtained from a sequence object has no holes in it.
The description of nextval() at
https://www.postgresql.org/docs/9.5/static/functions-sequence.html
specifically disclaims this:
Important: To avoid blocking concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never
rolled back; that is, once a value has been fetched it is
considered used and will not be returned again. This is true even
if the surrounding transaction later aborts, or if the calling
query ends up not using the value. For example an INSERT with an
ON CONFLICT clause will compute the to-be-inserted tuple,
including doing any required nextval calls, before detecting any
conflict that would cause it to follow the ON CONFLICT rule
instead. Such cases will leave unused "holes" in the sequence of
assigned values. Thus, PostgreSQL sequence objects cannot be used
to obtain "gapless" sequences.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs