Could be improved point of UPSERT
| От | Yourfriend | 
|---|---|
| Тема | Could be improved point of UPSERT | 
| Дата | |
| Msg-id | CABL_R4OgUN7+qgKvV0Jvn0pKBn+1dZWnch3y0S0zJBfGDRZ2MA@mail.gmail.com обсуждение исходный текст | 
| Ответы | Re: Could be improved point of UPSERT | 
| Список | pgsql-hackers | 
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.
В списке pgsql-hackers по дате отправления: