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.
