Delete with sorting in PostgreSQL

Question

I am trying to delete rows in a specific order. I tried the suggestions in the post How do I delete a fixed number of rows with sorting in PostgreSQL?, but nothing worked. I know they did not work because I had a trigger on delete and seeing which one is deleted and it is never doing it in the order that I want it to.

The tests that I wrote are like this:

create table test1 (
    th text,
    co text,
    ty text);

CREATE OR REPLACE FUNCTION testfunc() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$

DECLARE modified_row test1%ROWTYPE;
BEGIN

    IF TG_OP = 'DELETE' THEN
    modified_row := OLD;
ELSE
    modified_row := NEW;
END IF;
raise notice 'opearation: %, co: %', TG_OP, modified_row.co;
    RETURN modified_row;
END;
$$;

CREATE TRIGGER trigger_test1
BEFORE DELETE OR INSERT on test1
FOR EACH ROW
EXECUTE PROCEDURE testfunc();

insert into test1 (th, co, ty) values ('t1', 'c1', 'p'), ('t2', 'c2','p'), ('t2', 'c3','p'), ('t2', 'c4','p');
delete from test1 where ctid  in (select ctid from test1 order by co desc);

The delete statement above is the highest voted and accepted answer in the above post. But for me, the output has always been:

NOTICE: opearation: DELETE, co: c1

NOTICE: opearation: DELETE, co: c2

NOTICE: opearation: DELETE, co: c3

NOTICE: opearation: DELETE, co: c4

which indicates that the deletes are not done in desc order of 'co' that I am trying to do. Am I missing something here?

NOTE: forget about the insert order, I am only concerned about the delete order. And I could not ask this question in the above post because I do not have enough reputation to put a comment.

If you use LIMIT 1 in your delete subselect, you will delete the last one first. If you do that in a loop, you will get the order you desire. — hruske, Mar 02 '16 at 22:43
okay, I will try those. But there is no way to sort the deletes then? — ski, Mar 03 '16 at 00:15
@ski: Unfortunately not. Even if you find something which *seems* to work now, the planner might (for no obvious reason) choose to change the order next time you run it. I think a `DELETE ... WHERE CURRENT OF ` in a [`DO` block](http://www.postgresql.org/docs/current/static/sql-do.html) is the best way to do it. — Nick Barnes, Mar 03 '16 at 04:46
_Why_ are you trying to delete rows in a certain order? What is the underlying problem that you are trying to solve with that? — , Mar 03 '16 at 06:56
@a_horse: Presumably to avoid deadlocks. I can't think of many other situations where you'd even *notice* the deletion order. — Nick Barnes, Mar 03 '16 at 08:11

pumbo · Accepted Answer · 2016-03-03T05:35:37.657

The only way I can think of is delete in a loop. Here's an example using a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;
    DECLARE cur CURSOR FOR SELECT * FROM test1 ORDER BY co DESC FOR UPDATE;

BEGIN
    FOR test1_row IN cur 
    LOOP
        DELETE FROM test1 WHERE CURRENT OF cur;
    END LOOP;
END $$

An example without a cursor:

DO $$
DECLARE 
    test1_row test1%ROWTYPE;    
BEGIN
    FOR test1_row IN SELECT * FROM test1 ORDER BY co DESC
    LOOP
        DELETE FROM test1 WHERE th = test1_row.th AND co = test1_row.co AND ty = test1_row.ty;
    END LOOP;
END $$

Note: the WHERE condition in DELETE statement above is not reliable because test1 table does not have a primary/unique key. In other cases, you could just use: WHERE id = row.id

Delete with sorting in PostgreSQL

1 Answers1

Linked