Re: [HACKERS] 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 BEFORE INSERT and >> BEFORE UPDATE 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
Re: [HACKERS] 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 BEFORE INSERT and BEFORE UPDATE 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
