From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Tatsuo Ishii <ishii(at)postgresql(dot)org>, <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Trigger.sgml |
Date: | 2016-02-01 15:03:35 |
Message-ID: | 56AF73C7.6070904@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
--
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2016-02-01 15:43:00 | Re: Template for commit messages |
Previous Message | Jim Nasby | 2016-02-01 14:49:55 | Re: statistics for shared catalogs not updated when autovacuum is off |