Re: [PATCH] Deferrable unique constraints vs join removal -- bug?

On Wed, Oct 19, 2011 at 7:35 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> This probably doesn't affect many real-world applications, but it
> seems wrong that a performance feature can affect results returned by
> a query.
>
> Test case:
>
> create table uniq (i int unique deferrable initially deferred);
> begin;
> insert into uniq values(1),(1);
> select count(*) from uniq a left join uniq b using (i);
>  count
> -------
>     2

Yuck. Well, that's certainly a bug. What's weird is that I thought
we had put logic into the join removal code to ignore deferrable
constraints. Apparently not. I think maybe what we should do is add
an "immediate" field to IndexOptInfo, mirroring the existing unique
flag, and have get_relation_info() populate it from indimmediate, and
then make relation_has_unique_index() disqualify any non-immediate
index.

has_unique_index() arguably needs a similar fix, although at present
that appears to be used for only statistic purposes, so maybe it's OK.
A comment update might be a good idea, though.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company