Hi list,
PostgreSQL 9.0 introduced the "join removal" feature for cases where
an left join can't return more than one output row. This feature
relies on checking whether a UNIQUE constraint exists on the joined
column in the referenced table.
However, there was another new feature in 9.0: deferrable unique
constraints. It seems that the join removal code currently doesn't
check whether the constraint is deferrable or not. Since deferrable
unique constraints may contain duplicate rows in the course of a
transaction, join removal changes the results returned from a query.
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
An inner join performs as expected:
marti=# select count(*) from uniq a inner join uniq b using (i);
count
-------
4
----
Attached is a patch with an attempt to fix it. I'm not at all sure
this is the right approach, but it seems the cheapest way to make sure
is walk through *all* rows in pg_constraint for the given table, since
there is no index on pg_constraint.conindid. I'm not sure whether the
cost of this check outweighs the usefulness of this patch.
Catalog changes are a no-no for backported patches, right?
Or should I just go ahead and create this index, and not worry about
backporting?
I'm also adding lots of includes to this file. Maybe
unique_index_is_consistent() should be moved to another file instead?
While passing by, I also added an unrelated check to
check_functional_grouping() for the validity of the constraint. This
isn't necessary for now (unique constraints are always valid), but
seems useful just in case this changes in the future.
Regards,
Marti