Re: [HACKERS] Could be improved point of UPSERT
For the most cases I mentioned, we don't request a strict gapless sequence for the Invoice ID, the essential requirement is unique. We just hope that there is no obviously gap in most situations. From the test of UPSERT, there are quite a few chances to generate a big gap when UPSERT multi records. However, the result of UPSERT is acceptable, and I do love this function. So, it's a suggestion only. Anyway, thanks a lot for the detail explanation. Regards, Daojing Zhou. On Wed, Jul 15, 2015 at 3:23 PM, Peter Geoghegan p...@heroku.com wrote: On Wed, Jul 15, 2015 at 12:01 AM, Yourfriend doudou...@gmail.com wrote: for example, SO201507_1001, PO201503_1280, etc. As these IDs would be the most important attribute to the business, so, we hope there is no gap for the IDs. That's a requirement I've heard a number of times before. If you're relying on a sequence for this purpose, your application is already broken [1]. UPSERT need not be involved at all. [1] http://www.varlena.com/GeneralBits/130.php -- Peter Geoghegan
Re: [HACKERS] Could be improved point of UPSERT
In my example, I just give each record a different ID to access it efficiently. In our business cases, some times, we also use some prefix letter like 'SO', PO' combining with the current year, month and then a sequence to make a invoice ID, for example, SO201507_1001, PO201503_1280, etc. As these IDs would be the most important attribute to the business, so, we hope there is no gap for the IDs. Regards, Daojing Zhou. On Wed, Jul 15, 2015 at 2:33 AM, Peter Geoghegan p...@heroku.com wrote: On Sun, Jul 12, 2015 at 4:09 AM, Yourfriend doudou...@gmail.com wrote: Suggestion: When a conflict was found for UPSERT, don't access the sequence, so users can have a reasonable list of ID. This is not technically feasible. What if the arbiter index is a serial PK? The same thing can happen when a transaction is aborted. SERIAL is not guaranteed to be gapless. -- Peter Geoghegan
Re: [HACKERS] Could be improved point of UPSERT
On Wed, Jul 15, 2015 at 12:01 AM, Yourfriend doudou...@gmail.com wrote: for example, SO201507_1001, PO201503_1280, etc. As these IDs would be the most important attribute to the business, so, we hope there is no gap for the IDs. That's a requirement I've heard a number of times before. If you're relying on a sequence for this purpose, your application is already broken [1]. UPSERT need not be involved at all. [1] http://www.varlena.com/GeneralBits/130.php -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Could be improved point of UPSERT
On Sun, Jul 12, 2015 at 4:09 AM, Yourfriend doudou...@gmail.com wrote: Suggestion: When a conflict was found for UPSERT, don't access the sequence, so users can have a reasonable list of ID. This is not technically feasible. What if the arbiter index is a serial PK? The same thing can happen when a transaction is aborted. SERIAL is not guaranteed to be gapless. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Could be improved point of UPSERT
On Tuesday 14 July 2015 11:33:34 Peter Geoghegan wrote: On Sun, Jul 12, 2015 at 4:09 AM, Yourfriend doudou...@gmail.com wrote: Suggestion: When a conflict was found for UPSERT, don't access the sequence, so users can have a reasonable list of ID. This is not technically feasible. What if the arbiter index is a serial PK? The same thing can happen when a transaction is aborted. SERIAL is not guaranteed to be gapless. Could there be a version of UPSERT where an update is tried, and if 0 records are modified, an insert is done? Just wondering, I haven't got am use-case for that. I don't mid gaps in sequences. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Could be improved point of UPSERT
On Tue, Jul 14, 2015 at 11:40 AM, Gianni nasus.maxi...@gmail.com wrote: Could there be a version of UPSERT where an update is tried, and if 0 records are modified, an insert is done? Just wondering, I haven't got am use-case for that. I don't mid gaps in sequences. Perhaps, if you don't mind having a severely restricted set of qualifications in the UPDATE, which the existing command effectively has anyway. That would be a very odd thing. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Could be improved point of UPSERT
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.
