Обсуждение: 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.