Delete dependents that does not have a primary key

[aelgn]

Very nice procedure 👍

However, after testing it for my use case I realized that it will ignore dependant tables that themselves does not declare a primary key. Thus, they are not deleted and the transaction rolls back due to fk constraint violation.

Would be really nice to be able to delete dependants without their own pk.

[trlorenz]

Hey, @aelgn. Thanks for the comment. Be first advised that I haven't had the opportunity to work with PG in almost two years (and don't even have a local install ATM). My time isn't really my own anymore :/

BUT... a couple questions: What's your PG version? And what's your situation? You've got an FK pointing at a unique key and not a PK?

[aelgn]

Hi @trlorenz - no worries, I know the feeling :)
Using psql 11.13 with the following schema

create table A (
    id          serial primary key,
);
create table B (
    id          integer generated always as identity primary key,
    a_id        int4 not null references A,
);
create table C (
    b_id        int4 not null references B,
    a_id        int4 null references A
);

recursive_delete('A') fails since fk relations in C are not deleted. I'm guessing it is because C does not have a declared PK.

[trlorenz]

Understood, and you're correct -- it's because C has no PK.

Tables involved in the recursive deletion query are strung together by their foreign keys and the primary/unique keys that they reference. In your example, table B isn't important. Let's look at the situation with tables A and C, but let's give C a primary key:

DROP TABLE IF EXISTS c;
DROP TABLE IF EXISTS a;

CREATE TABLE a (id SERIAL PRIMARY KEY);

-- CREATE TABLE c (a_id int4 NULL REFERENCES a);
CREATE TABLE c (id SERIAL PRIMARY KEY, a_id int4 NULL REFERENCES a);

INSERT INTO a VALUES (99);
INSERT INTO c (a_id) VALUES (99);

SELECT recursively_delete('A', 99);

As expected, that works:

INFO:  DAMAGE PREVIEW (recursively_delete v0.1.5)
INFO:  
INFO:          1     a
INFO:          1 a   | c.["a_id"]
INFO:  
 recursively_delete 
--------------------
                  0
(1 row)

Here's the recursive query itself:

WITH 
 "del_0$ROOT" AS (
   DELETE FROM a WHERE (id) IN (
     WITH RECURSIVE
     self_ref (id) AS (
       SELECT id FROM a WHERE (id) IN (99)
         UNION ALL
       SELECT NULL
     )
     SELECT id FROM self_ref
   ) RETURNING *
 )
,
 "del_1$13320130" AS (
   DELETE FROM c WHERE (id) IN (
     WITH RECURSIVE
     self_ref (id) AS (
       SELECT id FROM c WHERE (a_id) IN (SELECT id FROM "del_0$ROOT")
         UNION ALL
       SELECT NULL
     )
     SELECT id FROM self_ref
   ) RETURNING *
 )
SELECT '0' AS queue_i, count(*) AS n_del FROM "del_0$ROOT" UNION ALL SELECT '1' AS queue_i, count(*) AS n_del FROM "del_1$13320130"

Notice that each auxiliary query begins by saying, 'DELETE FROM <TABLE> WHERE <KEY_COLS> IN (...)...'. That's the lynchpin of the whole thing as currently written, relating childrens' FK's to parents' PK's to arbitrary depth. So far, I can think of two possible ways to address your issue, but both would involve pretty substantial rewrites.

It's an interesting problem, and now that you've put it in my head, my OCD will force me to think about it. (Damn you!) But I can't promise an implemented solution real soon.

I have to ask: Can you throw a PK on that table? If this deletion you're doing is a one-off, you could even drop it after you were done. Or maybe (a_id, b_id) is unique and deserves permanent PK status anyway.

[aelgn]

Ah yes I see now, thank you for the writeup. I did add identity pk on the problematic binding tables and was able to recursively delete 👍