Fix optimization of foreign-key on update actions

Поиск
Список
Период
Сортировка
От Peter Eisentraut
Тема Fix optimization of foreign-key on update actions
Дата
Msg-id 3326fc2e-bc02-d4c5-e3e5-e54da466e89a@2ndquadrant.com
обсуждение исходный текст
Ответы Re: Fix optimization of foreign-key on update actions  (Andrew Gierth <andrew@tao11.riddles.org.uk>)
Список pgsql-hackers
I came across an edge case in how our foreign key implementation works
that I think is not SQL conforming.  It has to do with how updates to
values that "look" different but compare as equal are cascaded.  A
simple case involves float -0 vs. 0, but relevant cases also arise with
citext and case-insensitive collations.

Consider this example:

create table pktable2 (a float8, b float8, primary key (a, b));
create table fktable2 (x float8, y float8,
    foreign key (x, y) references pktable2 (a, b) on update cascade);

insert into pktable2 values ('-0', '-0');
insert into fktable2 values ('-0', '-0');

update pktable2 set a = '0' where a = '-0';

What happens now?

select * from pktable2;
 a | b
---+----
 0 | -0
(1 row)

-- buggy: did not update fktable2.x
select * from fktable2;
 x  | y
----+----
 -0 | -0
(1 row)

This happens because ri_KeysEqual() compares the old and new rows and
decides that because they are "equal", the ON UPDATE actions don't need
to be run.

The SQL standard seems clear that ON CASCADE UPDATE means that an
analogous UPDATE should be run on matching rows in the foreign key
table, so the current behavior is wrong.

Moreover, if another column is also updated, like update pktable2 set a
= '0', b = '5', then the old and new rows are no longer equal, and so x
will get updated in fktable2.  So the context creates inconsistencies.

The fix is that in these cases we have ri_KeysEqual() use a more
low-level form of equality, like for example record_image_eq does.  In
fact, we can take the same code.  See attached patches.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Вложения

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

Предыдущее
От: Andrew Dunstan
Дата:
Сообщение: Re: fast defaults in heap_getattr vs heap_deform_tuple
Следующее
От: Andrew Gierth
Дата:
Сообщение: Re: Fix optimization of foreign-key on update actions