Hi, Hackers,
The feature of UPSERT was my most interested one of 9.5, I really like need it.
I have test the different usages for this features like one record input, multi records input,
and also more than 10,000 records upserting, all look great, thanks for your work.
When I checked my records from these tests, I found that there was one result that might be
improved, the issue is, although there is no new records added to the table when conflict happens,
but the sequence for this table had been touched, so when a new record is actually added after that,
the sequence will skip the numbers when it was touched, then we get a not reasonable result (from my opinion). The scenario is as following:
1, System: PostgreSQL 9.5 Alpha + Win7 X64
2, Enter pgadmin:
3, create table foobar (
sysid serial,
theprovince varchar(20),
themonth varchar(7),
therevenue integer default 0,
primary key (sysid),
unique (theprovince,themonth)
)
4, insert into foobar values
('BeiJing','2015-01',1234),
('ShangHai','2015-01',1927)
5, select * from foobar ;
sysid theprovince themonth therevenue
1 Beijing 2015-01 1234
2 ShangHai 2015-01 1927
6, insert into foobar values
('BeiJing','2015-01',1999),
('ShangHai','2015-01',1988)
on conflict (theprovince,themonth) do update set therevenue=excluded.therevenue;
7, select * from foobar ;
sysid theprovince themonth therevenue
1 Beijing 2015-01 1999
2 ShangHai 2015-01 1988
8, insert into foobar values
('TianJing','2015-01',1888)
9, select * from foobar ;
sysid theprovince themonth therevenue
1 Beijing 2015-01 1999
2 ShangHai 2015-01 1988
5 TiangJing 2015-01 1888
---------------------------------------------------------------------------
Not reasonable result or issue:
The third record of "TianJing" in the table gets the SYSID of 5, as the sequence was accessed
twice by the step of 6.
Suggestion: When a conflict was found for UPSERT, don't access the sequence, so users can have a reasonable list of ID.
Regards,
Daojing Zhou.