Повернутися значення стовпців перед UPDATE за допомогою SQL Only - версія PostgreSQL

I have a related question, but this is another part of MY puzzle.

Я хотів би отримати старе значення колонці з рядка, яке було UPDATEd - без використання тригерів (ні збережених процедур, ні будь-яких інших додаткових, не-SQL/-прописних сутностей).

Мій запит виглядає так:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
    WHERE trans_nbr IN (
                        SELECT trans_nbr
                          FROM my_table
                         GROUP BY trans_nbr
                        HAVING COUNT(trans_nbr) > 1
                         LIMIT our_limit_to_have_single_process_grab
                       )
RETURNING row_id;

Якщо б я міг зробити "ЗА UPDATE ON my_table" в кінці підзапиту, це буде девін (і виправити моє інше питання/проблему). Але, це не спрацює: не може бути цього AND "GROUP BY" (що необхідно для з'ясування COUNT trans_nbr's). Тоді я міг би просто взяти ці trans_nbr і виконати запит спочатку, щоб отримати колишні значення (formerly to-be-) former_process_by.

Я намагався зробити так:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table old_my_table
     JOIN (
             SELECT trans_nbr
               FROM my_table
           GROUP BY trans_nbr
             HAVING COUNT(trans_nbr) > 1
              LIMIT our_limit_to_have_single_process_grab
          ) sub_my_table
       ON old_my_table.trans_nbr = sub_my_table.trans_nbr
    WHERE     my_table.trans_nbr = sub_my_table.trans_nbr
      AND my_table.processing_by = old_my_table.processing_by
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by

Але це не може працювати; old_my_table не відображається поза об'єднанням; Застереження RETURNING є сліпим до нього.

Я давно втратив кількість всіх спроб, які я зробив; Я досліджую це буквально годинами.

Якщо б я міг знайти куленепробивний спосіб блокування рядків у моєму підзапиті - і ТІЛЬКИ ці рядки, а КОЛИ відбувається підзапит - всі проблеми паралелізму, які я намагаюся уникнути, зникнуть ...


UPDATE: [WIPES EGG OFF FACE] Okay, so I had a typo in the non-generic code of the above that I wrote "doesn't work"; it does... thanks to Erwin Brandstetter, below, who stated it would, I re-did it (after a night's sleep, refreshed eyes, and a banana for bfast). Since it took me so long/hard to find this sort of solution, perhaps my embarrassment is worth it? At least this is on SO for posterity now... :>

Те, що я зараз маю (це працює), виглядає так:

   UPDATE my_table
      SET processing_by = our_id_info -- unique to this worker
     FROM my_table AS old_my_table
    WHERE trans_nbr IN (
                          SELECT trans_nbr
                            FROM my_table
                        GROUP BY trans_nbr
                          HAVING COUNT(*) > 1
                           LIMIT our_limit_to_have_single_process_grab
                       )
      AND my_table.row_id = old_my_table.row_id
RETURNING my_table.row_id, my_table.processing_by, old_my_table.processing_by AS old_processing_by

COUNT (*) - це пропозиція від Flimzy у коментарі до іншого (зв'язаного вище) питання. (Я був більш конкретним, ніж потрібно. [У цьому випадку.])

Please see my other question for correctly implementing concurrency and even a non-blocking version; THIS query merely shows how to get the old and new values from an update, ignore the bad/wrong concurrency bits.

31
Чому ви не можете використовувати правило або тригер?
додано Автор Flimzy, джерело
Найкращим способом IMHO є перетворення старих рядків на історичні, або явним SQL, або через правило перезапису або тригер.
додано Автор wildplasser, джерело
@wildplasser: Мета полягає в тому, щоб повернутися (1) що було змінено і (2) що було до зміни (принаймні). Це зручно для робочих місць, де те, що буде змінено, не є явним відомим на передньому плані, але програма повинна знати, що для обробки старі валети. (Виведення до/після для пошуку несправностей, наприклад). Історичні рядки - непотрібні безладдя, правила та тригери не тільки суворі (для цього випадку використання), але також потребують "більше" (безпека, доступ і т. Д.). Для тих, хто не має/хочуть/не потребує цього, це рішення є найкращим.
додано Автор pythonlarry, джерело
@Flimzy: 1. Якщо хтось не має доступу до таких речей (хоча I do), якщо він може бути виконаний виключно в SQL/єдиному запиті ... 2. Правила/тригери є цілим 'ofter debug enchilada. 3. Тримаючи це простим, прямим SQL, і маючи один запит, щоб зробити все, це робить K.I.S.S. красиво Дякую, ще раз, для нагадування Count (*), Tho! :>
додано Автор pythonlarry, джерело

4 Відповіді

Проблема

The manual explains:

Необов'язковий пункт RETURNING викликає UPDATE для обчислення та повернення   значення (и) на основі кожного фактично оновленого рядка. Будь-який вираз із використанням   стовпці таблиці та/або стовпці інших таблиць, згаданих у FROM , can   бути обчислено Значення нових (після оновлення) стовпців таблиці є   використаний . Синтаксис списку RETURNING ідентичний списку   вивести список SELECT .

Наголос мій. Немає доступу до старого рядка в пункті RETURNING . Ви можете це зробити за допомогою тригера або окремого SELECT перед кодування UPDATE , загорнутий у транзакції, як @Flimzy та @wildplasser, або Загорнуті в CTE як @MattDiPasquale розміщені.

Рішення

Проте те, що ви намагаєтеся досягти працює відмінно , якщо ви приєднаєтесь до іншого екземпляра таблиці в розділі FROM :

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

Повертає:

 old_id | old_name | tbl_id |  name
--------+----------+--------+---------
  3     | Old Guy  | 23     | New Guy

SQL Fiddle

Я протестував це з версією PostgreSQL з 8.4 до 9.6.

Це відрізняється для INSERT :

Робота з одночасним завантаженням запису

Існує кілька способів уникнути перегонів з паралельними операціями запису. Простий, повільний та надійний (але дорогий) спосіб полягає в тому, щоб запустити транзакцію за рівнем ізоляції SERIALIZABLE .

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ..;
COMMIT;

Але це, ймовірно, надмірно. І ви повинні бути готові повторювати операцію, якщо ви отримаєте серійний збій.
Простіше і швидше (і настільки ж надійним при одночасному завантаженні запису) це явний замок на рядок one , який потрібно оновити:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name, x.tbl_id, x.name;

Більше пояснень, прикладів і посилань за цим пов'язаним питанням:

53
додано
@ErwinBrandstetter, як використовувати підзапит, як я пропоную? stackoverflow.com/a/22734004/242933
додано Автор ma11hew28, джерело
@ErwinBrandstetter Чому вам потрібно WHERE x.tbl_id = y.tbl_id ?
додано Автор ma11hew28, джерело
Дуже класний трюк.
додано Автор a_horse_with_no_name, джерело
Працює також на 9.1 (я б був дуже здивований, якщо цього не зробив).
додано Автор a_horse_with_no_name, джерело
дуже хороша. Дякую!
додано Автор Daniel Shin, джерело
Це добре працює, поки це не відбудеться. Для мене це відбувається при запуску одного і того ж запиту з різними параметрами з досить невеликою затримкою (близько 10 мілісекунд). У цьому випадку наступні запити матимуть старе значення за одним із наступних запитів (читаються як "об'єкт, що має інший ключ"), а не виконується. Використовуйте це з обережністю у випадках із важким навантаженням!
додано Автор JLarky, джерело
@ a_horse_with_no_name: Ви, мабуть, мали кластери 9.1, де можна було б запустити цей невеликий тест? Щоб все завершилось .. :)
додано Автор Erwin Brandstetter, джерело
@ a_horse_with_no_name: Дякую! Те саме тут, але знаючи >> припускаючи. :)
додано Автор Erwin Brandstetter, джерело
@PythonLarry: ця редакція повинна допомогти песимістам серед нас. :)
додано Автор Erwin Brandstetter, джерело
@MattDiPasquale: ми приєднуємося до другого примірника тієї ж таблиці під іншим псевдонімом. Нам потрібний приєднаний стан або це буде перехресний зв'язок (картезіанський продукт).
додано Автор Erwin Brandstetter, джерело
@JLarky: Хороший момент. Я додав рішення для цього.
додано Автор Erwin Brandstetter, джерело
Привітання @ Ервіна Брандтететера. Наступним кроком буде створення рекурсивної версії цієї краси ;-) Я боюся, що це не дозволено мати побічні ефекти в рекурсивному CTE, хоча.
додано Автор wildplasser, джерело
Не збирати ніздрів, але я можу запропонувати редагувати вашу відповідь, @ErwinBrandstetter, як перехожий може припинити читати верхню частину (" це не може бути зроблено ") і пропустити повністю rockin 'доказ що це може ! Незалежно, дякую, ще раз! : o)
додано Автор pythonlarry, джерело

Ви можете використовувати підзапит SELECT .

Приклад: Оновлення електронної пошти користувача RETURNING старе значення.

  1. RETURNING Subquery

    UPDATE users SET email = '[email protected]' WHERE id = 1
    RETURNING (SELECT email FROM users WHERE id = 1);
    
  2. PostgreSQL WITH Query (Common Table Expressions)

    WITH u AS (
        SELECT email FROM users WHERE id = 1
    )
    UPDATE users SET email = '[email protected]' WHERE id = 1
    RETURNING (SELECT email FROM u);
    

    This has worked several times on my local database without fail, but I'm not sure if the SELECT in WITH is guaranteed to consistently execute before the UPDATE since "the sub-statements in WITH are executed concurrently with each other and with the main query."

6
додано
Чи обидва рішення постійно працюють під великим навантаженням? Якщо так, доведіть це. Якщо ні, то як вони можуть бути модифіковані так, щоб вони робили це?
додано Автор ma11hew28, джерело
Схоже, це звучить, мабуть, це питання. Ви можете завжди посилатися на цей для контексту ...
додано Автор Erwin Brandstetter, джерело
@ 2: "Всі твердження виконуються з тим самим знімкам (див. Розділ 13), тому вони не можуть" бачити "ефекти один одного на цільових таблицях. Це полегшує наслідки непередбачуваності фактичного порядку оновлень рядків та засобів що дані RETURING є єдиним способом спілкування змін між різними субпозиціями з СВ та основним запитом ". - postgresql.org/docs/9.3/static/queries-with. html
додано Автор moi, джерело

Варіант CTE також повинен бути запропонованим @MattDiPasquale .
З затишними засобами CTE я б більш чітко вказав:

WITH sel AS (
   SELECT tbl_id, name FROM tbl WHERE tbl_id = 3  -- assuming unique tbl_id
   )
, upd AS (
   UPDATE tbl SET name = 'New Guy' WHERE tbl_id = 3
   RETURNING tbl_id, name
   )
SELECT s.tbl_id AS old_id, s.name As old_name
     , u.tbl_id, u.name
FROM   sel s, upd u;

Без тестування я стверджую, що це працює: SELECT і UPDATE див. Той самий знімок бази даних. SELECT зобов'язується повернути старі значення (навіть якщо ви розмістите CTE після CTE за допомогою UPDATE ), тоді як UPDATE повертає нові значення за визначенням. Войла

Але це буде повільніше, ніж моя перша відповідь.

4
додано
@calebboyd: Єдиний негайний "випуск": це швидше, ніж варіант CTE. Також розглянемо варіант захисту від можливих питань паралельності, обговорених в іншій відповіді.
додано Автор Erwin Brandstetter, джерело
Ви бачите будь-які безпосередні проблеми за допомогою методу підзапиту? (продуктивність чи інше)
додано Автор calebboyd, джерело
Я думаю, це може бути те, що мені було цікаво - чи знаєте ви, чи підзапит буде виявляти подібні проблеми паралельності? - може бути окремим питанням ...
додано Автор calebboyd, джерело

Коли зіткнулася з цією дилемою, я додав небажані стовпці до таблиці, а потім я копіював старі значення в небажані стовпці (які потім я повернув), коли я оновлю запис. це роздуває стіл трохи, але дозволяє уникнути необхідності об'єднань.

0
додано