On Wed, Jan 6, 2016 at 5:14 PM, Paul <paul@salesintel.com> wrote:
> If I had a BEFORE INSERT trigger, it would only execute for records that
> were actually going to be inserted. However, you=E2=80=99re now stating t=
hat the
> INSERT ON CONFLICT will execute a BEFORE INSERT trigger even for records
> that are not inserted?? That doesn=E2=80=99t seem quite logical, and cont=
rary to
> =E2=80=98old fashioned=E2=80=99 upsert logic. Does this also mean that th=
e BEFORE UPDATE
> trigger is always called as well, or is it never called?
>
=E2=80=8BThis seems to boil down to the two possible ways of manually imple=
menting
UPSERT:
UPDATE, if not present, INSERT
INSERT, if failing, UPDATE
In the later the before insert trigger fires and influences whether the
insert=E2=80=8B
=E2=80=8Bfails. In the former you are already pretty certain the insert wi=
ll
=E2=80=8Bsucceed because the UPDATE found no records.
We've implemented INSERT, if failing UPDATE. The insert has to be
attempted and right now there is no concept of targeted partial
deferrability when constructing the record to be inserted.
To solve this situation it is likely that some form of "UPDATE ON MISSING
INSERT" would need to be designed. The insert portion would specify
"DEFAULT" for sequence columns and would execute nextval() only if the ON
MISSING portion is executed.
Fundamentally, the difference is that ON MISSING is considerably less
complicated than ON CONFLICT. What is wanted here is an ON MISSING
interpretation but what we've implemented is ON CONFLICT. It seems that
the hackers are in agreement that our implementation of ON CONFLICT is
consistent with its definition. That it doesn't efficiently solve problems
better handled by ON MISSING - while unfortunate - doesn't constitute a
bug: only an opportunity for future enhancement.
David J.
=E2=80=8B