Trigger.sgml

On 1/28/16 8:02 PM, Tatsuo Ishii wrote:

I am working as a volunteer to translate docs to Japanese. I have been
having hard time to parse the following sentence in
doc/src/sgml/trigger.sgml.

--------------------------------------------------------------------
The possibility of surprising outcomes should be considered when there
are both <literal>BEFORE</> <command>INSERT</command> and
<literal>BEFORE</> <command>UPDATE</command> row-level triggers that
both affect a row being inserted/updated (this can still be
problematic if the modifications are more or less equivalent if
they're not also idempotent).
--------------------------------------------------------------------

Especially I don't understand this part:

(this can still be problematic if the modifications are more or less
equivalent if they're not also idempotent).

It would be great if someone could enligntend me.

I believe the idea here is that thanks to UPSERT you can now get very
strange behavior if you have BEFORE triggers that aren't idempotent. IE:

CREATE TABLE test(
a int PRIMARY KEY
);

BEFORE INSERT a = a - 1
BEFORE UPDATE a = a + 1

INSERT (1) -- Results in 0
INSERT (2) -- Results in 1

Now if you try to UPSERT (1), the before insert will give you a=0, which
conflicts. So then you end up with an UPDATE, which gives you a=1 again.
Things are even worse when you try to UPSERT (2), because the insert
conflicts but then you try to update a row that doesn't exist (a=2).

Obviously this is a ridiculous example, but hopefully it shows the problem.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 1/28/16 8:02 PM, Tatsuo Ishii wrote:

I am working as a volunteer to translate docs to Japanese. I have been
having hard time to parse the following sentence in
doc/src/sgml/trigger.sgml.

--------------------------------------------------------------------
The possibility of surprising outcomes should be considered when there
are both <literal>BEFORE</> <command>INSERT</command> and
<literal>BEFORE</> <command>UPDATE</command> row-level triggers that
both affect a row being inserted/updated (this can still be
problematic if the modifications are more or less equivalent if
they're not also idempotent).
--------------------------------------------------------------------

Especially I don't understand this part:

(this can still be problematic if the modifications are more or less
equivalent if they're not also idempotent).

It would be great if someone could enligntend me.

I believe the idea here is that thanks to UPSERT you can now get very
strange behavior if you have BEFORE triggers that aren't
idempotent. IE:

CREATE TABLE test(
a int PRIMARY KEY
);

BEFORE INSERT a = a - 1
BEFORE UPDATE a = a + 1

INSERT (1) -- Results in 0
INSERT (2) -- Results in 1

Now if you try to UPSERT (1), the before insert will give you a=0,
which conflicts. So then you end up with an UPDATE, which gives you
a=1 again. Things are even worse when you try to UPSERT (2), because
the insert conflicts but then you try to update a row that doesn't
exist (a=2).

Obviously this is a ridiculous example, but hopefully it shows the
problem.

Thank you for the info!

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers