Баг с некорректным выполнением UPDATE при использовании в where секционированной таблицы #110

siv163 · 2017-08-08T00:14:57Z

Апдейт имел такой вид:

UPDATE обычная_таблица a
SET поле = значение 
WHERE not exists (select null
                  from секционированная_таблица b
                  where b.owner_id = a.id);

условие where не срабатывало, и обновлялось больше чем нужно.

хотя запрос вида:

SELECT *
FROM обычная_таблица a
WHERE not exists (select null
                  from секционированная_таблица b
                  where b.owner_id = a.id);

отдавал все верно.

поведение повторяется. Могу, при необходимости, попробовать написать скрипт.

funbringer · 2017-08-08T07:39:56Z

Добрый день, @siv163

Спасибо за отзыв.

Какую версию pg_pathman вы используете?
Вы пробовали последнюю версию (1.4.2)?

Да, скрипт был бы очень полезен для воспроизведения проблемы.

siv163 · 2017-08-08T07:50:41Z

VERSION "1.4" пишет постгрес, выкачивали версию от 31.07.2017

funbringer · 2017-08-08T07:54:21Z

Создаю две таблицы:

create table abc (id int not null, val int);
insert into abc values (1,1), (2,2);

create table test(id int not null);
select create_range_partitions('test', 'id', 1, 10, 10);
insert into test values (1);

Смотрим, что получается:

explain (analyze, costs off, timing off)
select * from abc where not exists (select from test where test.id = abc.id);
                          QUERY PLAN                           
---------------------------------------------------------------
 Hash Anti Join (actual rows=1 loops=1)
   Hash Cond: (abc.id = test_1.id)
   ->  Seq Scan on abc (actual rows=2 loops=1)
   ->  Hash (actual rows=1 loops=1)
         Buckets: 32768  Batches: 1  Memory Usage: 257kB
         ->  Append (actual rows=1 loops=1)
               ->  Seq Scan on test_1 (actual rows=1 loops=1)
               ->  Seq Scan on test_2 (actual rows=0 loops=1)
               ->  Seq Scan on test_3 (actual rows=0 loops=1)
               ->  Seq Scan on test_4 (actual rows=0 loops=1)
               ->  Seq Scan on test_5 (actual rows=0 loops=1)
               ->  Seq Scan on test_6 (actual rows=0 loops=1)
               ->  Seq Scan on test_7 (actual rows=0 loops=1)
               ->  Seq Scan on test_8 (actual rows=0 loops=1)
               ->  Seq Scan on test_9 (actual rows=0 loops=1)
               ->  Seq Scan on test_10 (actual rows=0 loops=1)
 Planning time: 0.282 ms
 Execution time: 0.138 ms
(18 rows)

set enable_hashjoin = f;

explain (analyze, costs off, timing off)
select * from abc where not exists (select from test where test.id = abc.id);
                          QUERY PLAN                           
---------------------------------------------------------------
 Merge Anti Join (actual rows=1 loops=1)
   Merge Cond: (abc.id = test_1.id)
   ->  Sort (actual rows=2 loops=1)
         Sort Key: abc.id
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on abc (actual rows=2 loops=1)
   ->  Sort (actual rows=1 loops=1)
         Sort Key: test_1.id
         Sort Method: quicksort  Memory: 25kB
         ->  Append (actual rows=1 loops=1)
               ->  Seq Scan on test_1 (actual rows=1 loops=1)
               ->  Seq Scan on test_2 (actual rows=0 loops=1)
               ->  Seq Scan on test_3 (actual rows=0 loops=1)
               ->  Seq Scan on test_4 (actual rows=0 loops=1)
               ->  Seq Scan on test_5 (actual rows=0 loops=1)
               ->  Seq Scan on test_6 (actual rows=0 loops=1)
               ->  Seq Scan on test_7 (actual rows=0 loops=1)
               ->  Seq Scan on test_8 (actual rows=0 loops=1)
               ->  Seq Scan on test_9 (actual rows=0 loops=1)
               ->  Seq Scan on test_10 (actual rows=0 loops=1)
 Planning time: 0.302 ms
 Execution time: 0.096 ms
(22 rows)

set enable_mergejoin = f;

explain (analyze, costs off, timing off)
select * from abc where not exists (select from test where test.id = abc.id);
                        QUERY PLAN                         
-----------------------------------------------------------
 Nested Loop Anti Join (actual rows=1 loops=1)
   ->  Seq Scan on abc (actual rows=2 loops=1)
   ->  Custom Scan (RuntimeAppend) (actual rows=0 loops=2)
         Prune by: (test.id = abc.id)
         ->  Seq Scan on test_1 (actual rows=0 loops=2)
               Filter: (id = abc.id)
               Rows Removed by Filter: 0
 Planning time: 0.566 ms
 Execution time: 0.098 ms
(9 rows)

Можно заметить, что во всех случаях SELECT был запланирован и выполнен правильно. Теперь попробуем сделать то же самое с UPDATE:

explain (analyze, costs off, timing off)
update abc set val = 4 where not exists (select from test where test.id = abc.id);
                             QUERY PLAN                              
---------------------------------------------------------------------
 Update on abc (actual rows=0 loops=1)
   ->  Hash Anti Join (actual rows=1 loops=1)
         Hash Cond: (abc.id = test_1.id)
         ->  Seq Scan on abc (actual rows=2 loops=1)
         ->  Hash (actual rows=1 loops=1)
               Buckets: 32768  Batches: 1  Memory Usage: 257kB
               ->  Append (actual rows=1 loops=1)
                     ->  Seq Scan on test_1 (actual rows=1 loops=1)
                     ->  Seq Scan on test_2 (actual rows=0 loops=1)
                     ->  Seq Scan on test_3 (actual rows=0 loops=1)
                     ->  Seq Scan on test_4 (actual rows=0 loops=1)
                     ->  Seq Scan on test_5 (actual rows=0 loops=1)
                     ->  Seq Scan on test_6 (actual rows=0 loops=1)
                     ->  Seq Scan on test_7 (actual rows=0 loops=1)
                     ->  Seq Scan on test_8 (actual rows=0 loops=1)
                     ->  Seq Scan on test_9 (actual rows=0 loops=1)
                     ->  Seq Scan on test_10 (actual rows=0 loops=1)
 Planning time: 3.251 ms
 Execution time: 0.546 ms
(19 rows)

select * from abc;
 id | val 
----+-----
  1 |   1
  2 |   4
(2 rows)

set enable_hashjoin = f;

explain (analyze, costs off, timing off)
update abc set val = 6 where not exists (select from test where test.id = abc.id);
                             QUERY PLAN                              
---------------------------------------------------------------------
 Update on abc (actual rows=0 loops=1)
   ->  Merge Anti Join (actual rows=1 loops=1)
         Merge Cond: (abc.id = test_1.id)
         ->  Sort (actual rows=2 loops=1)
               Sort Key: abc.id
               Sort Method: quicksort  Memory: 25kB
               ->  Seq Scan on abc (actual rows=2 loops=1)
         ->  Sort (actual rows=1 loops=1)
               Sort Key: test_1.id
               Sort Method: quicksort  Memory: 25kB
               ->  Append (actual rows=1 loops=1)
                     ->  Seq Scan on test_1 (actual rows=1 loops=1)
                     ->  Seq Scan on test_2 (actual rows=0 loops=1)
                     ->  Seq Scan on test_3 (actual rows=0 loops=1)
                     ->  Seq Scan on test_4 (actual rows=0 loops=1)
                     ->  Seq Scan on test_5 (actual rows=0 loops=1)
                     ->  Seq Scan on test_6 (actual rows=0 loops=1)
                     ->  Seq Scan on test_7 (actual rows=0 loops=1)
                     ->  Seq Scan on test_8 (actual rows=0 loops=1)
                     ->  Seq Scan on test_9 (actual rows=0 loops=1)
                     ->  Seq Scan on test_10 (actual rows=0 loops=1)
 Planning time: 0.779 ms
 Execution time: 0.290 ms
(23 rows)

select * from abc;
 id | val 
----+-----
  1 |   1
  2 |   6
(2 rows)

set enable_mergejoin = f;

explain (analyze, costs off, timing off)
update abc set val = 8 where not exists (select from test where test.id = abc.id);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Update on abc (actual rows=0 loops=1)
   ->  Nested Loop Anti Join (actual rows=1 loops=1)
         ->  Seq Scan on abc (actual rows=2 loops=1)
         ->  Custom Scan (RuntimeAppend) (actual rows=0 loops=2)
               Prune by: (test.id = abc.id)
               ->  Seq Scan on test_1 (actual rows=0 loops=2)
                     Filter: (id = abc.id)
                     Rows Removed by Filter: 0
 Planning time: 0.795 ms
 Execution time: 0.210 ms
(10 rows)

select * from abc;
 id | val 
----+-----
  1 |   1
  2 |   8
(2 rows)

funbringer · 2017-08-08T07:55:48Z

выкачивали версию от 31.07.2017

В этот день мы не выпускали официального релиза. Вы используете master?

siv163 · 2017-08-08T08:01:11Z

я понимаю что не выпускали) я не нашел где посмотреть минорную версию, и решил уточнить дату выкачки.
"Вы используете master?" думаю что да, завозили админы, могу уточнить.

funbringer · 2017-08-08T08:10:25Z

"Вы используете master?" думаю что да, завозили админы, могу уточнить.

Это очень важно, потому что мы не поддерживаем master.

Вы вольны использовать pg_pathman каким угодно образом, в том числе модифицировать код и т.д., но если вы ставите master, вся ответственность за сломанные обновления ложится на вас. Все дело в том, что обновления plpgsql-части pg_pathman инкрементальные, и могут быть корректно наложены только на предыдущий базовый релиз (т.е. 1.4, 1.3, 1.2 и т.д.).

В промежуточных коммитах мы можем менять уйму вещей, и единственный способ обновиться с мастера - дропнуть pg_pathman и поставить его с нуля, за исключением случаев, когда мы явно заявляем об отсутствии проблем с совместмостью.

При удалении расширения, например, ломается hash-партицирование (т.к. check constraints завязаны на функции pg_pathman и удаляются при удалении расширения), поэтому за этой проблемой ожидаемо следует проблема снятия бэкапов партицированных таблиц при помощи pg_dump.

я не нашел где посмотреть минорную версию

Попробуйте выполнить функцию get_pathman_lib_version.

funbringer · 2017-08-08T08:11:46Z

я понимаю что не выпускали) я не нашел где посмотреть минорную версию

Более того, мы не обновляем патч-версию каждый коммит, поэтому не факт, что ваша версия отражает суть.

funbringer · 2017-08-08T08:14:08Z

Если вы хотите ставить именно стабильные релизы, крайне советую взглянуть на PGXN. У них есть клиент для установки обновлений.

siv163 · 2017-08-08T08:23:19Z

Спасибо большое, будем разбираться.
'get_pathman_lib_version'
'10401'

funbringer · 2017-08-08T08:25:56Z

Я могу вас немного утешить)

Я более чем уверен, что если вы соберете 1.4.2 со страницы релизов, то никаких проблем при эксплуатации и обновлении у вас не возникнет, а вы при этом получите последнюю стабильную версию.

Вам не потребуется делать drop extension, просто установите новую версию библиотеки и перезапустите кластер.

funbringer · 2017-08-08T08:28:45Z

Если у вас останутся проблемы с запросами, прикладывайте explain analyze, примерную схему таблиц, либо тестовый скрипт.

Жду подтверждения решения проблемы.

siv163 · 2017-08-08T14:01:12Z

    drop table if exists entity;

    CREATE TABLE entity
    (
      id integer,
      active smallint
    );

    insert into entity (id, active)
    select v.id,
           v.active
      from (values
    (13533565,3),
    (14227974,3),
    (14227975,3),
    (14230556,3),
    (14230557,3)) v(id, active);


    drop table if exists log cascade;

    CREATE TABLE log
    (
      date timestamp without time zone NOT NULL,
      entity_id integer
    );


    insert into log (date, entity_id)
    select v.date::timestamp,
           v.entity_id
      from (values
    ('2017-02-16 09:52:40',13533565),
    ('2017-07-25 08:43:06',14227974),
    ('2017-07-25 08:43:06',14227975),
    ('2017-08-07 08:11:42',14230556),
    ('2017-08-07 08:20:27',14230557)) v(date, entity_id);


    SELECT create_range_partitions('public.log', 'date', '2017-01-01 00:00:00'::timestamp, 
    '1 month'::interval, null, true);

    select count(*)  from  log;
    select count(*)  from only log;
    
    -- что должно обновиться 
    select e.id
      from entity e
       where active <> 4
         and not exists (select null
                           from log l
                          where l.entity_id = e.id
                            and l.date > '2017-07-25 10:24:24.327106+00');

    -- что обновилось по факту
    begin;
    with cte as 
           (update entity e
               set active = 4
             where e.active <> 4
               and not exists (select null
                                 from log l
                                where l.entity_id = e.id
                                  and l.date > '2017-07-25 10:24:24.327106+00')
                returning id)
    select *
      from cte

-- у меня обновляются:
entity_id
13533565
14227974
14227975
+
14230556
14230557

последние 2 не должны попадать

funbringer · 2017-08-08T14:24:46Z

Вот что я вижу у себя:

select e.id
from entity e
where active <> 4 and
      not exists (select null
                  from log l
                  where l.entity_id = e.id and
                        l.date > (now() - make_interval(days := 14)));
    id    
----------
 13533565
 14227974
 14227975
(3 rows)

with cte as (
    update entity e
    set active = 4
    where e.active <> 4 and
          not exists (select null
                      from log l
                      where l.entity_id = e.id and
                            l.date > (now() - make_interval(days := 14)))
    returning id)
select * from cte;
    id    
----------
 13533565
 14227974
 14227975
(3 rows)

Какую версию PostgreSQL вы используете?

funbringer · 2017-08-08T14:26:50Z

Пожалуйста, покажите ваш explain analyze для UPDATE:

explain (analyze, costs off, timing off)
with cte as (  
    update entity e
    set active = 4
    where e.active <> 4 and
          not exists (select null
                      from log l
                      where l.entity_id = e.id and
                            l.date > (now() - make_interval(days := 14)))
    returning id)
select * from cte;
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 CTE Scan on cte (actual rows=3 loops=1)
   CTE cte
     ->  Update on entity e (actual rows=3 loops=1)
           ->  Hash Anti Join (actual rows=3 loops=1)
                 Hash Cond: (e.id = l.entity_id)
                 ->  Seq Scan on entity e (actual rows=5 loops=1)
                       Filter: (active <> 4)
                 ->  Hash (actual rows=2 loops=1)
                       Buckets: 8192  Batches: 1  Memory Usage: 65kB
                       ->  Append (actual rows=2 loops=1)
                             ->  Seq Scan on log_1 l (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                             ->  Seq Scan on log_2 l_1 (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                                   Rows Removed by Filter: 1
                             ->  Seq Scan on log_3 l_2 (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                             ->  Seq Scan on log_4 l_3 (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                             ->  Seq Scan on log_5 l_4 (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                             ->  Seq Scan on log_6 l_5 (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                             ->  Seq Scan on log_7 l_6 (actual rows=0 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
                                   Rows Removed by Filter: 2
                             ->  Seq Scan on log_8 l_7 (actual rows=2 loops=1)
                                   Filter: (date > (now() - '14 days'::interval))
 Planning time: 1.607 ms
 Execution time: 0.535 ms
(30 rows)

siv163 · 2017-08-08T15:02:27Z

    QUERY PLAN
    CTE Scan on cte (actual rows=5 loops=1)
      CTE cte
        ->  Update on entity e (actual rows=5 loops=1)
              ->  Hash Anti Join (actual rows=5 loops=1)
                    Hash Cond: (e.id = l.entity_id)
                    ->  Seq Scan on entity e (actual rows=5 loops=1)
                          Filter: (active <> 4)
                    ->  Hash (actual rows=0 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 8kB
                          ->  Seq Scan on log l (actual rows=0 loops=1)
                                Filter: (date > (now() - 14 days::interval))
    Planning time: 0.108 ms
    Execution time: 0.062 ms

funbringer · 2017-08-08T15:07:46Z

И все-таки, какую версию PostgreSQL вы используете?

siv163 · 2017-08-08T15:37:21Z

select version();
'PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit'

siv163 · 2017-08-08T15:38:04Z

Извините, пропустил ваш вопрос

funbringer · 2017-08-08T15:44:20Z

'PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit'

Хорошо, у вас есть две возможности:

Обновиться до 9.6, потому что в этой версии были внесены изменения, которые значительно помогли нам решить проблемы с многими запросами. Для версии 9.5 используется более старый код, с которым все еще наблюдаются проблемы.
Помочь нам протестировать ветку master_hotfix_delete_using, которая содержит некоторые исправления, которые мы пока не готовы включить в новую версию.

siv163 · 2017-08-08T15:48:24Z

давайте посмотрим как я вам смогу помочь с тестированием.

funbringer · 2017-08-21T10:45:17Z

Добрый день, @siv163

Извините за ожидание. Я подготовил ветку master_hotfix_issue_110, на которой вы сможете протестировать ваши запросы. Ветка совместима с последним стабильным релизом, DROP EXTENSION + CREATE EXTENSION делать не нужно.

funbringer · 2017-08-21T10:46:41Z

Мы решили отключить некоторые опасные оптимизации для UPDATE и DELETE, если расширение установлено на PostgreSQL 9.5.

siv163 · 2017-08-21T13:26:34Z

Спасибо большое!
На pg_pathman 10402 (ветка master_hotfix_issue_110)
и PostgreSQL 9.5.8 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
ошибка не повторяется!

Только сегодня занялись проверкой, и к сожалению, не успели попробовать повторить ошибку на ветке master_hotfix_delete_using.

Но выполнили следующие проверки:
PostgreSQL 9.5.8 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
pg_pathman 10402 (ветка master) баг повторяется.

PostgreSQL 9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit
pg_pathman 10402 (ветка master) баг НЕ повторяется.

funbringer · 2017-08-21T16:59:57Z

Спасибо!

Я думаю, master_hotfix_delete_using тестировать уже не нужно, я замержил ее в master и удалил.

Если есть возможность и время, лучше попробуйте больше разных запросов, чтобы помочь нам протестировать исправление в ветке master_hotfix_issue_110.

funbringer · 2017-08-24T10:52:42Z

Вышла версия 1.4.3 с исправлением.

siv163 · 2017-08-24T12:28:42Z

Спасибо большое!

funbringer added the help wanted label Aug 8, 2017

funbringer self-assigned this Aug 8, 2017

funbringer added the bug label Aug 10, 2017

funbringer added this to the Release 1.4.3 milestone Aug 14, 2017

funbringer added a commit that referenced this issue Aug 20, 2017

disable dangerous query optimizations on PostgreSQL 9.5 (issue #110)

6a6ee15

funbringer closed this as completed Aug 24, 2017

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Баг с некорректным выполнением UPDATE при использовании в where секционированной таблицы #110

Баг с некорректным выполнением UPDATE при использовании в where секционированной таблицы #110

siv163 commented Aug 8, 2017 •

edited by funbringer

Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017

funbringer commented Aug 8, 2017 •

edited

Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017 •

edited

Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017 •

edited

Loading

funbringer commented Aug 8, 2017

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017 •

edited

Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 21, 2017

funbringer commented Aug 21, 2017

siv163 commented Aug 21, 2017

funbringer commented Aug 21, 2017

funbringer commented Aug 24, 2017

siv163 commented Aug 24, 2017

Баг с некорректным выполнением UPDATE при использовании в where секционированной таблицы #110

Баг с некорректным выполнением UPDATE при использовании в where секционированной таблицы #110

Comments

siv163 commented Aug 8, 2017 • edited by funbringer Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017

funbringer commented Aug 8, 2017 • edited Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017 • edited Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017 • edited Loading

funbringer commented Aug 8, 2017

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017 • edited Loading

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 8, 2017

siv163 commented Aug 8, 2017

funbringer commented Aug 21, 2017

funbringer commented Aug 21, 2017

siv163 commented Aug 21, 2017

funbringer commented Aug 21, 2017

funbringer commented Aug 24, 2017

siv163 commented Aug 24, 2017

siv163 commented Aug 8, 2017 •

edited by funbringer

Loading

funbringer commented Aug 8, 2017 •

edited

Loading

funbringer commented Aug 8, 2017 •

edited

Loading

siv163 commented Aug 8, 2017 •

edited

Loading

siv163 commented Aug 8, 2017 •

edited

Loading