On Wed, Oct 1, 2014 at 2:42 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> GiST supports exclusion constraints. That is one of the main reasons I want
>> to do promise tuples, instead of locking within the indexam: to support this
>> feature with exclusion constraints.
>
> That does sound interesting, but I am concerned the semantics may cause issues.
>
> If I go to insert a row for 'UK' and find an existing row for
> 'Europe', do we really want to update the population of Europe to be
> the population of the UK, simply because the UK and Europe have an
> exclusion conflict?
>
> Please give some concrete examples of a business request that might be
> satisified by such a feature.
The ON CONFLICT UPDATE semantics don't seem particularly useful for
exclusion constraints. However, a reasonable business request for
exclusion constraints would be to have a "boss mode" for the canonical
room reservation example - an INSERT that is guaranteed not to fail by
either deleting conflicting rows or updating them so the exclusion
constraints don't overlap (e.g. truncate the time intervals) or the
rows fail the index predicate (e.g. soft delete). AFAICS this is
currently not possible to implement correctly without a retry loop.
The hypothetical ON CONFLICT REPLACE and ON CONFLICT
UPDATE-AND-THEN-INSERT modes would also make sense in the unique index
case.
Not saying that I view this as necessary for the first cut of the
feature, just providing an example where it could be useful.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de