Cascading deletes with rules in 7.0.3: why doesn't this work?
От | Jeremy Radlow |
---|---|
Тема | Cascading deletes with rules in 7.0.3: why doesn't this work? |
Дата | |
Msg-id | 4.2.2.20010402022030.0282b100@galaga.dreamhost.com обсуждение исходный текст |
Ответы |
Re: Cascading deletes with rules in 7.0.3: why doesn't this work?
|
Список | pgsql-general |
In the following example, I'm trying to log all deletions from any of three tables. The only information I'm interested in recording is the index from table A which the deleted row referenced. When I try to delete a row from A or B: testdb=> delete from b; ERROR: ExecAppend: Fail to add null value in not null attribute ax testdb=> delete from c; DELETE 1 testdb=> delete from b; DELETE 1 What's really odd is that if I drop the *_delete rules, and try 'delete from a' in the same psql session, I get that ExecAppend error. But 'delete from a' works in a new session: testdb#1=> delete from a; ERROR: ExecAppend: Fail to add null value in not null attribute ax testdb#1=> drop rule a_delete; DROP testdb#1=> drop rule b_delete; DROP testdb#1=> drop rule c_delete; DROP testdb#1=> [1]+ Stopped psql -d testdb -U tester postgres# psql -d testdb -U tester testdb#2=> delete from a; DELETE 1 testdb#2=> \q postgres# fg testdb#1=> insert into a values (1); INSERT 1961282 1 testdb#1=> insert into b values (1,1); INSERT 1961283 1 testdb#1=> insert into c values (1,1); INSERT 1961284 1 testdb#1=> delete from a; ERROR: ExecAppend: Fail to add null value in not null attribute ax testdb#1=> Does anyone know what the problem is? Thanks, Jeremy -- cut here -- create table a ( ax serial, primary key (ax) ); create table b ( bx serial, ax int references a on delete cascade, primary key (bx) ); create table c ( cx serial, bx int references b on delete cascade, primary key (cx) ); create table delete_log ( ax int not null default '0', delete_timestamp timestamp default current_timestamp ); create rule a_delete as on delete to a do insert into delete_log (ax) values (old.ax); create rule b_delete as on delete to b do insert into delete_log (ax) values (old.ax); create rule c_delete as on delete to c do insert into delete_log (ax) values ((select ax from b where b.bx = old.bx)); insert into a values (1); insert into b values (1,1); insert into c values (1,1);
В списке pgsql-general по дате отправления: