Thread: Rule system and unsucessful updates.

I'm having trouble getting the rule system to work on updates that do
not match the where clause.  Perhaps I'm doing this wrong, but I can't
find any docs that explain this very clearly.

Here what I would like to do:

CREATE OR REPLACE RULE
  insertAcctUpdate
AS ON UPDATE TO
  accounting_tab
WHERE
  NEW.type <> 'new'
AND
  NOT EXISTS (
    SELECT
      sessionID
    FROM
      accounting_tab
    WHERE
      sessionID = NEW.sessionID
  )
DO INSTEAD
INSERT INTO accounting_tab (
  sessionID,
  type
) values (
  NEW.sessionID,
  NEW.type
);

Basically when I get an update that doesn't have a row to update (due to
the sessionID missing) do an insert instead.  For some reason it just
won't work, however the opposite (check for the insert and instead update):

CREATE OR REPLACE RULE
  insertAcctUpdate
AS ON INSERT TO
  accounting_tab
WHERE
  NEW.type <> 'new'
AND
  EXISTS (
    SELECT
      sessionID
    FROM
      accounting_tab
    WHERE
      sessionID = NEW.sessionID
  )
DO INSTEAD
UPDATE
  accounting_tab
set
  (updates to columns)
where
  type = NEW.type,
and
  sessionID = NEW.sessionID;

Works just fine.  The only thing I can think of is that the rule system
doesn't process the rule when it finds that the update modified 0 rows.
 Anyone know why the first rule doesn't work but the second one does?

Thanks,
schu

Matthew Schumacher <matt.s@aptalaska.net> writes:
> I'm having trouble getting the rule system to work on updates that do
> not match the where clause.

You did not say what you mean by "doesn't work", but what I suspect you
are getting bit by is that ON UPDATE rules fire before the original
query is done.  By the time the original query executes, you've inserted
a row into accounting_tab and so its added condition fails.

            regards, tom lane

Tom Lane wrote:
> Matthew Schumacher <matt.s@aptalaska.net> writes:
>
>>I'm having trouble getting the rule system to work on updates that do
>>not match the where clause.
>
>
> You did not say what you mean by "doesn't work", but what I suspect you
> are getting bit by is that ON UPDATE rules fire before the original
> query is done.  By the time the original query executes, you've inserted
> a row into accounting_tab and so its added condition fails.
>
>             regards, tom lane

Thanks for your reply Tom, your help is always appreciated...

By doesn't work I am saying that I never get a row inserted.  Here is a
complete example of what I'm talking about:

create table test (id int, data varchar(100));

CREATE OR REPLACE RULE
  testUpdate
AS ON UPDATE TO
  test
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      test
    WHERE
      id = NEW.id
  )
DO INSTEAD
INSERT INTO test (
  id,
  data
) values (
  NEW.id,
  'test'
);

update test set id = 1, data = 'test';

As you can see, this is a simple matter of inserting instead of updating
where the key (id) is missing from the table.  But the insert never happens.

Your comment about ON UPDATE rules firing before the original query is
desired in this case because the conditions should be met (id doesn't
exist) then the insert query processed.  I have to be missing something
here but I just can't see why it won't insert.

If I do the same thing except update when I see an insert query it
correctly updates the rows, but inserts new ones too despite me
declaring DO INSTEAD.  Here is an example:

create table test (id int, data varchar(100));

CREATE OR REPLACE RULE
  testUpdate
AS ON INSERT TO
  test
WHERE
  EXISTS (
    SELECT
      id
    FROM
      test
    WHERE
      id = NEW.id
  )
DO INSTEAD
UPDATE
  test
SET
  id = NEW.id,
  data = 'test';

insert into test (id, data) values (1, 'test');
insert into test (id, data) values (2, 'test');

Select * from test;

 id | data
----+------
  2 | test
  2 | test

I really don't see why this isn't working as expected either.  Wouldn't
DO INSTEAD cause it to omit the original insert query?

Thanks,

schu

Matthew Schumacher <matt.s@aptalaska.net> writes:
> update test set id = 1, data = 'test';

The above is a pretty bad idea in any case --- think about what happens
when you have some data in the table.  It'll set *every row* to id = 1
and data = 'test'.  The reason nothing happens when there is nothing in
the table is that there is no row that can be updated.  Taking an action
"instead of" an action that doesn't happen still doesn't happen.

Any practical application using UPDATE is going to say UPDATE ... WHERE
to limit the set of rows that get changed, and what you have to think
about is whether you need a rule to do anything in that situation.

For what I think you want this application to do, it'd make more sense
for the application to say "INSERT some-data", and for you to have a
rule that changes that into an UPDATE if there is a pre-existing row
with matching key columns.

            regards, tom lane

Tom Lane wrote:
> The above is a pretty bad idea in any case --- think about what happens
> when you have some data in the table.  It'll set *every row* to id = 1
> and data = 'test'.

Your right, DUH, I forgot my where clause in my example.  It is in the
real query though, perhaps I didn't get enough coffee this morning.

The reason nothing happens when there is nothing in
> the table is that there is no row that can be updated.  Taking an action
> "instead of" an action that doesn't happen still doesn't happen.
>
> For what I think you want this application to do, it'd make more sense
> for the application to say "INSERT some-data", and for you to have a
> rule that changes that into an UPDATE if there is a pre-existing row
> with matching key columns.

I'll go with updating instead of inserting in the rule, however I am
curious, is there a way to make an ON UPDATE rule work regardless if the
original query updated rows or not?  I was under the impression that the
rule engine just looked at the query syntax not what it did.

schu