Thread: insert rule instead oddity
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)
On Tue, Mar 13, 2007 at 02:15:01PM +0200, Sim Zacks wrote: > 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. 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, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
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. Sim Martijn van Oosterhout wrote: > On Tue, Mar 13, 2007 at 02:15:01PM +0200, Sim Zacks wrote: >> 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. > > 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,
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. > Martijn van Oosterhout wrote: >> On Tue, Mar 13, 2007 at 02:15:01PM +0200, Sim Zacks wrote: >>> 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. >> >> 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,
Martijn van Oosterhout <kleptog@svana.org> writes: > I don't think rules can do what you want. A trigger might work, ignoring the problem of race conditions between two inserts happening concurrently (which a rule can't handle either). regards, tom lane
A trigger did work, thank you for that suggestion. In a race situation, there really is no manual solution either I sent in a bug report for the rule problem that I experienced. Sim Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: >> I don't think rules can do what you want. > > A trigger might work, ignoring the problem of race conditions between > two inserts happening concurrently (which a rule can't handle either). > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >