First of all, it seems that the Bug Reporting form is having problems.
I tried submitting this on that form and it told me my email address
was invalid even though I tried a number of different valid email
addresses.
An insert instead rule with a condition that does an update if the row
already exists does both the insert and the update. This is probably
because it puts the condition on both the insert and the update. After
it does the insert it checks if the condition exists and it does
(because the row was just inserted) so it applies the update. When the
condition is met it actually does the update instead of the insert. I
would think that an Instead means that it will either do the statement
executed or, if it meets the condition it will do the rule statement
but it should never do both.
In debugging this, I also tried using the sequence of the inserted
statement in the condition to see if the row that it found was the
same row it just inserted and I found that the new.(serial field) was
incrementing by 3 (though it is defined to increment by 1) each time
the rule statement was executed, whether or not the insert actually
happened or not. In the case where the row was inserted, new.(serial
field) did not reflect the value of the inserted row, but rather a
value 2 higher then it.
Below are the emails with more explicit details of what I was trying
to do and exactly what failed and where that I sent to pgsql.general
and the response of Martijn van Oosterhout as he tried to explain that
I misunderstood how rules work.
---First Email---
select version()
"PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1,
ssp-3.3.2-3, pie-8.7.7.1)"
I am sure that I must have missed something here because I read the
documentation and searched the forums and it all seems fairly
straightforward.
I am doing an Insert Rule with Instead that if the row already exists
then it should update a field instead of Inserting the row. This works
great when the row exists, but when the row doesn't exist it is doing
both the insert and the update (in other words I'm getting double
value in the field that is supposed to be updated when the row is
found.
I put the notice into the function just to ascertain that it was
actually calling it after doing the insert.
create or replace function func_rul_insertstock(v_stock int,v_pnid
int,v_stocklocationid int) returns void as
$$
begin
Update stock set stock=stock+v_stock where pnid=v_pnid and
coalesce(stocklocationid,-1)=coalesce(v_stocklocationid,-1);
raise notice 'Doing function';
return;
end;
$$ language 'plpgsql';
create or replace rule rul_insertstock as on insert
to stock where exists(select stockid from stock where pnid=new.pnid
and ownerid=new.ownerid and
coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1))
Do Instead
select func_rul_insertstock(new.stock,new.pnid,new.stocklocationid);
insert into stock(partid,pnid,ownerid,stock,stocklocationid)
values(1036,9243,10,150,1)
---Martijn's response---
Rules don't work the way you think they do. They're sort of macro
expansions. What's ahppening when you insert is the rule splits it into
two statements, one insert and one update, with the where conditions
adjusted. Depending on the order I imagine that it could do both.
I don't think rules can do what you want. What you need in the SQL
MERGE command, but postgresql doesn't support that. A stored procedure
could do it.
Have a nice day,
--My response--
I would think the rule qualification would tell the system not to run
the rule in the event that it does not meet the qualification. According to the
docs:
What is a rule qualification? It is a restriction that tells when
the actions of the rule should be done and when not. This
qualification can only reference the pseudorelations NEW
and/or OLD, which basically represent the relation that was given
as object (but with a special meaning).
In the event that the qualification is met, the database only does the
rule and not the insert, as is directed by the Instead keyword and as
I am expecting. However, in the event that the qualification is not
met then I would expect it to not do the rule as I understand it to
say in the docs.
--My response after more testing--
I misunderstood you.
What you are saying is that since the insert is done first and then
the update when it gets to the update it checks the rule and even
though it wasn't valid when the statement was executed, it is valid at
the time of execution and therefore it runs.
I have tried another test, with the assumption that the new.stockid
(serial field) would be the number of the inserted row, but it's not.
create or replace rule rul_insertstock as on insert
to stock where exists(select stockid from stock where pnid=new.pnid
and ownerid=new.ownerid and
coalesce(stocklocationid,-1)=coalesce(new.stocklocationid,-1)
and stockid<>new.stockid)
Do Instead
select func_rul_insertstock(new.stock,new.pnid,new.stocklocationid,new.stockid);
the stockid<>new.stockid should check if the insert statement happened
or not. However, the new.stockid jumps 3 numbers every time an insert
statement is called, whether or not the insert actually happens.
For example, if the sequence is at 100 and the insert statement does
not meet the rule, meaning that it will do an insert, the record is
inserted with a stockid of 101 and the new.stockid shows 103 and the
sequence start is at 103.
If the insert statement meets the rule and does not do an insert then
the new.stockid and the sequence start is still at 103.
In the case that the insert statement works, the update statement is
still run because the new.stockid does not reflect the actual
new.stockid.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
