Обсуждение: Trigger.sgml
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. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
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
> 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