> 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
