Usuwanie rekordow z CTA z dwoch table.

Witam,

Czy mozna przepisac takie zapytanie na bezpieczniejsze? I szybsze?

WITH _customers_ AS (
    SELECT u.user_id
    FROM users u
    JOIN orders o ON o.user_id = u.user_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE u.user_id = 9999
),
_del1_ AS (
    DELETE FROM table_1 AS a
    USING _customers_
    WHERE _customers_.user_id = a.user_id
    RETURNING *
),
_del2_ AS (
    DELETE FROM table_2 AS a
    USING _customers_
    WHERE _customers_.user_id = a.user_id
    RETURNING *
)
SELECT * FROM _del1_ LIMIT 1;

Przecież to co chcesz zrobić, to jest kaskadowe usuwanie przy FOREIGN KEY

Zgadza sie, tylko, ze w parent tabeli nie moge usunac recordu. W tej tabeli jest kolumna removed. I tak naprawde w systemie nie mozna usunac tego rekordu, jedynie jest mozliwa jego aktualizacja. I tutaj wywoluje to zapytanie. Jak ktos aktualizuje rekord to chce wszystko usunac z dwoch poznizszych tabel.

Sprobuje tego:

 on update cascade on delete cascade

Jakiś dziwny konstrukt. Mam wątpliwości czy ta sekcja z del2 zawsze się wykona. Teoretycznie optymalizator mógłby ją pominąć (jak nie dziś, to w kolejnych wydaniach silnika?), bo nie wnosi nic do konstrukcji zapytania.

Co jest złego w wywołaniu kilku operacji DELETE? Przykładowo:

DELETE FROM TABLE_1 WHERE 
USER_ID=:x  AND EXISTS (
  SELECT 1 FROM users u
    JOIN orders o ON o.user_id = u.user_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE u.user_id = :x
);

@Marcin.Miga Obawiam sie ze nie da rady przejsc tego przez FK. Musialbym je potworzyc na istniejacych tabelach. A tak brakuje sporo danych. I FK juz nie chce sie tak utworzyc. I niezabardzo moge usuawac o tak sobie dane z tabel. Wiec klucze obce bardzo dobre rozwiazanie, ale nie w tym przypadku.

@yarel Mozesz miec racje. Wczesniej czy pozniej zapytanie z CTA moze nie usuwac wszystkiego. Przerobie to jakos. Wiedzialem, ze cos jest nie tak z tym CTA'em.

Dzieki

@poniatowski: Skoro nie możesz utworzyć FK, to ssprawdz to:

WITH c AS (
    SELECT u.user_id
    FROM users u
    JOIN orders o ON o.user_id = u.user_id
    JOIN order_items oi ON oi.order_id = o.order_id
    WHERE u.user_id = 9999
),
d1 AS (
    DELETE FROM table_1 AS a
    USING c
    WHERE c.user_id = a.user_id
    RETURNING *
),
d2 AS (
    DELETE FROM table_2 AS a
    USING c
    WHERE c.user_id = a.user_id
    RETURNING *
)
SELECT * FROM d1
UNION
SELECT * FROM d2;

@Marcin.Miga: Ja chce tylko usunac rekordy z dwoch tabel. Nie chce niczego zwracac. Tylko zastanawialem sie nad bezpieczenstwiem CTA. Czy takie kombinacje CTA i dwoma DELETE sa bezpieczne.

Tak. Jest bezpieczne tak samo jak pojedynczy DELETE. Ale musisz coś zwracać, bo inaczej zapytania kasujące się nie wykonają

Nie moge po prostu zwrocic jeden wiersz SELECT * FROM _del1_ LIMIT 1;? Takie zapytanie dziala, usuwa wszystki. Nie rozsypalo sie. A wydaje sie, ze latwiej i wydajniej jest zwrocic jedna krotke niz wszystkie? Popraw mnie jezeli sie myle?

Wydaje mi się, ze przy takim zapytaniu końcowym optymalizator zignoruje ten drugi DELETE, ale musiałbym sprawdzić. A jak nie chcesz zwracać wszystkich rekordów, to możesz zwrócić np:

SELECT Count(*) FROM 
(... co tam chcesz ... twoje lub moje zapytanie)

Wtedy będziesz dodatkowo miał w wyniku ilość zwróconych rekordów.

Ok, teraz rozumiem o co Ci chodzilo. Obawiales sie o drugi DELETE, bo nie byl uzyty. Pomimo, ze drugi DELETE nie byl uzyty w zapytniu nizej, bo CTA wykonuje wszystkie DELETE.

@Marcin.Miga: Jak zwykle dzieki za cenne uwagi i pomoc :) Milego weekendu ludki :)