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

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
>