Re: BUG #13846: INSERT ON CONFLICT consumes sequencers onconflicts

On Wed, Jan 6, 2016 at 9:00 PM, Paul <paul@salesintel.com> wrote:
> As the raw event data is processed, I want to do an UPSERT into the devic=
e
> class table, using only the class name to resolve conflicts, to get it=E2=
=80=99s
> surrogate key, which is then used while inserting the related event recor=
d
> linking that event record to the particular device class.  Since device
> classes are rarely added, it will be the common case that an actual INSER=
T
> is almost never performed.


Fwiw while the implementation of UPSERT is fairly efficient it's
probably still too high an overhead to handle this kind of case.
Ingesting high volume of sensor data like this you want to be very
fast with little extra work. It's perfectly sensible to use UPSERT to
insert sensor ids but you wouldn't want to do so on every single
sensor datum or you'll end up spending more time and I/O doing that
than just ingesting your raw data.

Luckily in a case like that you don't expect the sensor ids to be
updated or deleted so you aren't really concerned about concurrency
except for a race between multiple insertions. So simply keeping a
list of known sensors and using upsert whenever a new sensor id is
seen (and periodically refreshing the list) would work perfectly well.
And independently of how sequences work you would want to be doing
that anyways for performance.

I'm more sympathetic to your concern about storage density than Peter
and Andres seem to be but unfortunately the reality is that it's much
harder to implement what you describe than it might appear. That often
happens in Postgres because it's a general purpose flexible platform
and has to handle whatever setup users create. So often solutions that
make perfect sense for a typical setup actually don't work in general.
Implementing UPSERT correctly for general case was actually really
really hard already so avoiding extra complications for features like
this is an important strategy for getting things done. I'm sure you'll
agree that we're better off with UPSERT with this limitation than no
UPSERT at all...

Fwiw this is the kind of feedback for which there are beta releases.
We need users to try to develop applications using new features before
they're released to find exactly these types of mismatches with user
expectations. Even if someone wanted to work on this now it wouldn't
happen until 9.6 which means any application that it would have helped
would probably already have run into the problem and had to adjust
already.

--=20
greg