Forum OpenACS Q&A: Bizarre Postgres Behavior

I find this behavior very odd and am concerned that it's a deep flaw in postgres:

lta=# begin;
BEGIN
lta=# select count(*) from sec_sessions where user_id = 71;
 count 
-------
     2
(1 row)

lta=# delete from sec_sessions where user_id = 71;
DELETE 0
lta=#

So the WHERE clause returns two rows from a SELECT but deletes zero rows! Anyone have any ideas?

Thank you,

Carl C-M

Are you sure sec_sessions is not a VIEW (can't check right now)? In can't remember if that threw an error or not in Postgres.

It is a table. and the rows still exist.  That is why I am very perplexed.

cheers,

There is an "on delete cascade" for the foreign key you are referring to, meaning that when the key is deleted from sec_sessions, the corresponding rows are also deleted from sec_session_properties (silently; AFAIK there is no indication that another table has been effected in what postgres returns).  If you try to delete a row is referenced by a FK w/o the cascade, you will get an error.

It turns out there there was an ON DELETE trigger on the row that looked a little like this:

create trigger clean_up_other_tables
before delete on sec_sesssions
execute clean_up_other_tables_tr;

Where clean_up_other_tables_tr looked a little like this:

delete from foo where session_id = OLD.session_id;
return NULL;

-----------------
So the return null was breaking the delete and when I think about it, It makes some sense.

Cheers,

Carl C-M