Thread: Affected # of Rows After TRIGGER/RULE Return
Hi, I'm trying to fake DELETEs to a table using below methods: CREATE OR REPLACE FUNCTION mobileunit_fake_delete() RETURNS trigger AS $$ BEGIN UPDATE mobileunit SET networkid = OLD.networkid + OLD.muid * 100000000000, groupid = 146688, plate = 'DELETED_' || OLD.plate WHERE muid = OLD.muid; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER mobileunit_fake_delete BEFORE DELETE ON mobileunit FOR EACH ROW EXECUTE PROCEDURE mobileunit_fake_delete(); or CREATE RULE mobileunit_fake_delete AS ON DELETE TO mobileunit DO INSTEAD UPDATE mobileunit SET networkid = CAST(OLD.networkid AS numeric(20)) + OLD.muid * 100000000000, groupid = 146688, plate = 'DELETED_' || OLD.plate WHERE muid = OLD.muid; But unfortunately, both solutions make DELETE FROM mobileunit WHERE muid = ... queries return 0 as # of affacted rows. And this causes JDBC applications (specifically Hibernate) ROLLBACK query as some failure occured. At least, shouldn't the latter one return 1 as # of affected rows? Any ideas to fix this problem? Regards. P.S. Yep, I know code sucks badly. Trying to migrate a Microsoft SQL Server application to PostgreSQL.
On Wednesday 14 May 2008 02:59:27 Volkan YAZICI wrote: > RETURN NULL; <snip> > queries return 0 as # of affacted rows. > And this causes JDBC > applications (specifically Hibernate) ROLLBACK query as some failure > occured. At least, shouldn't the latter one return 1 as # of affected > rows? Any ideas to fix this problem? > > > Regards. > > P.S. Yep, I know code sucks badly. Trying to migrate a Microsoft SQL > Server application to PostgreSQL. something to try (whether it's acceptable or not will depend on requirements that you didn't mention in your original post). Instead of trying to update the row in place, insert the row again, but with the field you need to mangle (in your example, "plate", in my example below, "k") already mangled. this only works if the field you're mangling is the primary key. if some other field is the primary key, you will need to mangle that too, if possible. since you'll have already inserted the row, just return OLD and let the old row actually be deleted. there are other options but the above works pretty well unless you have constraints that make it impossible. here's an example: drop table t cascade; create table t(k text primary key,oldk text); create or replace function t_trig_func() RETURNS TRIGGER as $$ BEGIN raise notice '%',old.k; insert into t(k,oldk) values (OLD.k||'-DEL',OLD.k); return OLD; END; $$ language plpgsql; create trigger t_trig AFTER delete on t for each row execute procedure t_trig_func(); insert into t values ('1','1'); insert into t values ('2','2'); insert into t values ('3','3'); insert into t values ('4','4'); delete from t where k=4; select * from t; when I run that, the delete gives: NOTICE: 4 DELETE 1 Time: 0.888 ms (the notice is from the raise notice in the trigger function). tiger -- Gerald Timothy Quimpo bopolissimus@gmail.com It is no measure of health to be well adjusted to a profoundly sick society. -- Krishnamurti
On Wed, 14 May 2008, Gerald Quimpo <bopolissimus.lists@gmail.com> writes: > Instead of trying to update the row in place, insert the row again, > but with the field you need to mangle (in your example, "plate", > in my example below, "k") already mangled. this only works if > the field you're mangling is the primary key. if some other field > is the primary key, you will need to mangle that too, if possible. > since you'll have already inserted the row, just return OLD and let > the old row actually be deleted. I've considered that too. But the problem is that there are nearly 50-60 tables referencing to the related row about the be deleted. Therefore, if I'd return OLD from the trigger, all other rows referencing to OLD will get deleted because of ON DELETE CASCADE. But, if there would be some way to tell the ON DELETE CASCADE constraints that "Hey, don't move yet. I'll INSERT a new row with what you thought to be missing previously." there won't be a problem. I hope I understand you correctly. Did I miss anything? Any ideas? Regards.
On Wednesday 14 May 2008 19:10:18 Volkan YAZICI wrote: > On Wed, 14 May 2008, Gerald Quimpo <bopolissimus.lists@gmail.com> writes: > > Instead of trying to update the row in place, insert the row again, > > but with the field you need to mangle (in your example, "plate", > > in my example below, "k") already mangled. this only works if > > the field you're mangling is the primary key. if some other field > > is the primary key, you will need to mangle that too, if possible. > > since you'll have already inserted the row, just return OLD and let > > the old row actually be deleted. > > I've considered that too. But the problem is that there are nearly 50-60 > tables referencing to the related row about the be deleted. Therefore, > if I'd return OLD from the trigger, all other rows referencing to OLD > will get deleted because of ON DELETE CASCADE. But, if there would be > some way to tell the ON DELETE CASCADE constraints that "Hey, don't move > yet. I'll INSERT a new row with what you thought to be missing > previously." there won't be a problem. > > I hope I understand you correctly. Did I miss anything? Any ideas? Not really :-). I was just looking at the simplest possible thing that could work. I've looked at versioned/temporal databases. But you probably can't go there since it definitely adds a lot of complexity to your app and queries. Are you able to change your representation of "deleted" rows? e.g., can you have an is_deleted boolean not null default false column there? that's how i'd have gone, myself, instead of mangling the plate number. good luck. tiger -- Gerald Timothy Quimpo bopolissimus@gmail.com What we call Progress is the exchange of one nuisance for anothe nuisance. -- Havelock Ellis
On Wed, 14 May 2008, Gerald Quimpo <bopolissimus.lists@gmail.com> writes: > Not really :-). I was just looking at the simplest possible thing that could > work. I've looked at versioned/temporal databases. But you probably > can't go there since it definitely adds a lot of complexity to your app > and queries. Are you able to change your representation of "deleted" > rows? e.g., can you have an > > is_deleted boolean not null default false > > column there? that's how i'd have gone, myself, instead of mangling > the plate number. That's really how it should be done. But there are really significant design mistakes made years ago, and I gave up messing with them. Will just accomplish migration and that's all. Regards.