Duplicate unique key values in inheritance tables

Поиск
Список
Период
Сортировка
От Richard Guo
Тема Duplicate unique key values in inheritance tables
Дата
Msg-id CAMbWs481Dwk2J1Ya1OFByyW96LnBcdKm213Po0TkU99NJcPZfw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Duplicate unique key values in inheritance tables
Список pgsql-hackers
I came across a query that returned incorrect results and I traced it
down to being caused by duplicate unique key values in an inheritance
table.  As a simple example, consider

create table p (a int primary key, b int);
create table c () inherits (p);

insert into p select 1, 1;
insert into c select 1, 2;

select a, b from p;
 a | b
---+---
 1 | 1
 1 | 2
(2 rows)

explain (verbose, costs off)
select a, b from p group by a;
              QUERY PLAN
--------------------------------------
 HashAggregate
   Output: p.a, p.b
   Group Key: p.a
   ->  Append
         ->  Seq Scan on public.p p_1
               Output: p_1.a, p_1.b
         ->  Seq Scan on public.c p_2
               Output: p_2.a, p_2.b
(8 rows)

The parser considers 'p.b' functionally dependent on the group by
column 'p.a' because 'p.a' is identified as the primary key for table
'p'.  However, this causes confusion for the executor when determining
which 'p.b' value should be returned for each group.  In my case, I
observed that sorted and hashed aggregation produce different results
for the same query.

Reading the doc, it seems that this is a documented limitation of the
inheritance feature that we would have duplicate unique key values in
inheritance tables.  Even adding a unique constraint to the children
does not prevent duplication compared to the parent.

As a workaround for this issue, I'm considering whether we can skip
checking functional dependency on primary keys for inheritance
parents, given that we cannot guarantee uniqueness on the keys in this
case.  Maybe something like below.

@@ -1421,7 +1427,9 @@ check_ungrouped_columns_walker(Node *node,
        Assert(var->varno > 0 &&
               (int) var->varno <= list_length(context->pstate->p_rtable));
        rte = rt_fetch(var->varno, context->pstate->p_rtable);
-       if (rte->rtekind == RTE_RELATION)
+       if (rte->rtekind == RTE_RELATION &&
+           !(rte->relkind == RELKIND_RELATION &&
+             rte->inh && has_subclass(rte->relid)))
        {
            if (check_functional_grouping(rte->relid,

Any thoughts?

Thanks
Richard



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Peter Smith
Дата:
Сообщение: Re: Logical Replication of sequences
Следующее
От: Michael Paquier
Дата:
Сообщение: Re: Injection point locking