The topic sounds funny. But yeah, the delete row command was not deleting the row as i expected it to be. Luckily i found the solution for the same.
But first, let me post my problem.
I was doing a simple delete operation over some table..
Say for example,
select count(*) from some_table_x where some_col_name = 'some_value'
This query was returning me a value of 1 which meant is exactly one row that matches this clause. I tried firing a delete query like the one below,
delete from some_table_x where some_col_name = 'some_value'
Ofcourse, the output i expected was that the row to get deleted. But instead, i was informed by postgres as "0 rows deleted. Query executed successfully"
This was very annoying as postgres was not throwing any kind of error and my delete query had the 'where' clause same as the select query i used. The only possible place where the problem might haved occured was the triggers. Even constraints can prevent the row from getting deleted. But the delete operation would have intimated me some kindof error.
So, i concluded only triggers may have this issue. When i went through my trigger, it had a code something like this,
CREATE TRIGGER sample_trig
BEFORE INSERT OR UPDATE OR DELETE
ON some_table_x
FOR EACH ROW
EXECUTE PROCEDURE "TRGR_SAMPLE"();
And the trigger function was something like,
CREATE OR REPLACE FUNCTION "TRGR_SAMPLE"()
RETURNS "trigger" AS
$BODY$
BEGIN
IF TG_OP='DELETE'
THEN
-- some operations
END IF;
IF TG_OP='UPDATE'
THEN
-- some operations
END IF;
IF TG_OP='INSERT'
THEN
-- some operations
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
The trigger was a
BEFORE EACH ROW trigger. It will be fired everytime when one of insert or delete or update operation occurs on
some_table_x. The bug in this code was that i was returning NEW at the end of the trigger statement. Whereas in oracle, no one need to worry about what a trigger returns, only need to worry about what a trigger does. But not so in Postgres, you have to write a trigger that calls a trigger function which ofcourse should return either NULL or NEW or OLD.
Since a delete operation does not have a NEW RECORD type, returning NEW does not make any sense at all during a delete operation. So, a small addition to the code really solved the issue.
CREATE OR REPLACE FUNCTION "TRGR_SAMPLE"()
RETURNS "trigger" AS
$BODY$
BEGIN
IF TG_OP='DELETE'
THEN
-- some operations
RETURN OLD;
END IF;
IF TG_OP='UPDATE'
THEN
-- some operations
END IF;
IF TG_OP='INSERT'
THEN
-- some operations
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Although the problem and the solution sounded very simple, finding out that sucked up a lot of time as there was no error been thrown by postgres.
For projects involving small to medium sized databases one may require to copy the remote (or production) database onto local environment. I was earlier doing this for my production application using custom pg_dump and then restoring with pg_restore. It was relatively straightforward but still consumed good amount of time. I wanted to automate this using capistrano and this is how I did it
