On 08/25/2016 10:08 PM, Peter Geoghegan wrote:
On Thu, Aug 25, 2016 at 11:49 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
I think the point is that given the way he's set up the test case,
there should be no duplicate violation in the plain unique index
unless there is one in the arbiter index. So assuming that INSERT
tests the arbiter indexes first, there shouldn't be an error.
Maybe it doesn't do that, but it seems like it would be a good idea
if it did.
Oh, yeah. This is arguably an example of inference failing to infer
multiple unique indexes as arbiters. Inference could, in principle,
recognize that the second unique index is equivalent to the first, but
doesn't. (I don't think that it matters which order anything is tested
in, though, because not finding a dup value in the arbiter index does
not guarantee that there won't be one in the other index. There is no
locking when no conflict is initially found, and so no guarantees
here.)
Anyway, I don't have a lot of sympathy for this point of view, because
the scenario is completely contrived. You have to draw the line
somewhere.
I do not think that this scenario is completely contrived: the cases when a
table has more than one primary key are quite common.
For example, "user" may have unique e-mail address, phone number and login.
Also, as far as I know, this is not an artificial example, but real case taken
from customers application...
I am not sure weather it's really bug or feature, but the user's intention was
obvious: locate record by one of the unique keys and if such record already
exists,
then increment counter (do update instead of insert). But there are also good
arguments why upsert may report conflict in this case...
If such UPSERT behavior is assumed to be correct, what is the best workaround
for the problem if we really need to have to separate indexes and want to
enforce unique constraint for both keys?
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
