Could be improved point of UPSERT

From: Yourfriend <doudou586(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Could be improved point of UPSERT
Date: 2015-07-12 11:09:39
Message-ID: CABL_R4OgUN7+qgKvV0Jvn0pKBn+1dZWnch3y0S0zJBfGDRZ2MA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2015-07-12 12:16:38 Re: drop/truncate table sucks for large values of shared buffers
Previous Message Pavel Stehule 2015-07-12 09:35:01 Re: [PATCH] Generalized JSON output functions