When i try to delete rows from a table, it does not work. I used the standard DELETE FROM statement, using tag_id=1 The first statement is returning Success, so i expect that the rows are deleted. However, the rows are not deleted. And i can't seem to figure out why.
The image below has the specific query statements, and the return result.
There are several possibilities:
There is a trigger on the table that prevents the rows from being deleted.
Have a look at
\d project_tag
Your index is corrupted. Try
REINDEX TABLE project_tag;
NULL from the DELETE, which is the condition for it to skip the operation: postgresql.org/docs/15/trigger-definition.html I did that with an accidental RETURN NEW instead of RETURN OLD.My issue was different (in that the DELETE appeared to have worked), but this question was the number one search result and phrased exactly like my issue. I want to say:
AUTOCOMMIT
Some DB connections have autocommit as an option during their connection profile. Checking that solved my issue.
autocommit=False, hence, I have to call session.commit() to make it executed against the database.If none of the other answers work, I would recommend trying explain on the delete query. In my case it turned out to be a faulty RLS policy (more details below). The output of explain showed a suspicious Filter: (false AND (id='...'::uuid)), where, the false was being introduced by the RLS policy.
Details about RLS fault: I had included only a single policy for delete and it was a restrictive policy. After carefully reading the RLS docs, I found out that there must be at least one permissive policy for the restrictive policies to make sense. If not, then no records will be accessible.
