Re: BUG #11107: UPDATE violates table check constraint

On Fri, Aug 1, 2014 at 10:01 AM, Marko Tiikkaja <marko@joh.to> wrote:

> On 8/1/14 5:59 PM, David G Johnston wrote:
>
>> Tom Lane-2 wrote
>>
>>> Sorry, but this check constraint has entirely undefined behavior, as do=
es
>>> any check constraint that refers to data rows other than the one that i=
s
>>> being checked.
>>>
>>
>> Which is why PostgreSQL has CREATE TRIGGER functionality...
>>
>
> Which does absolutely nothing to get rid of the race conditions.
>
>
=E2=80=8BTrue enough - but at least it would theoretically work in their ab=
sence=E2=80=8B.

Two more useful model designs to consider:

1) Make the FK composite
2) Remove the duplicate PK dependent data from the foreign table

Though given that we only have a toy model to work, and no problem/domain
description, all suggestions are thought-provoking only.

Lacking a model change you could encapsulate both constraints and races
within a function and disallow direct updates to the key-related columns.

David J.