Re: Are rules transaction safe?

Hi Doug,

On 27.01.2006 21:01, Doug McNaught wrote:
> Oliver Fürst <ofuerst(at)tsak(dot)net> writes:
>
>>I have a question regarding rules on views. Are the commands inside a
>>ON INSERT (or UPDATE) DO INSTEAD (implicit) transactions for postgres?
>>I tried to put BEGIN; and COMMIT; around the commands in a ON ... DO
>>INSTEAD ( ) block, but keep getting a syntax error.
>
> Everything that happens in Postgres is inside either an implicit or
> explicit transaction, so you can't do BEGIN/COMMIT inside rules or
> functions.
>
> You might be able to use savepoints, depending on what you're actually
> trying to do.

Actually I'm just worried that something like the ON INSERT ... DO
INSTEAD rule on a view (as stated in my example)...

>> CREATE OR REPLACE RULE ab_insert AS ON INSERT TO ab DO INSTEAD (
>> INSERT INTO a (foo) VALUES (new.from_a);
>> INSERT INTO b (foo,a_id) VALUES (new.from_b,currval('a_id_seq'));
>> );

...could yield unexpected results. Imagine two overlapping inserts on
the view in a heavy load situation. (C1, C2 -> two concurrent connections):

C1 -> INSERT INTO ab (from_a,from_b) VALUES ('foo','bar');
C2 -> INSERT INTO ab (from_a,from_b) VALUES ('hello','world');

...should translates to...

C1 -> INSERT INTO a (foo) VALUES ('foo'); -- id == 1
C1 -> INSERT INTO b (foo,a_id) VALUES ('bar',1);

C2 -> INSERT INTO a (foo) VALUES ('hello'); -- id == 2
C2 -> INSERT INTO b (foo,a_id) VALUES ('world',2);

...but could translate to...

C1 -> INSERT INTO a (foo) VALUES ('foo'); -- id == 1
C2 -> INSERT INTO a (foo) VALUES ('hello'); -- id == 2

C1 -> INSERT INTO b (foo,a_id) VALUES ('bar',2);
C2 -> INSERT INTO b (foo,a_id) VALUES ('world',2);

Basically I'm worried that the whole "relying on the last value of a
sequence" isn't such a great idea.

(By the way, did I pick the wrong mailing list for that topic and should
I move to pgsql-sql?)

Regards,
Oliver