Обсуждение: Are rules transaction safe?
Hi all,
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.
The following example might explain what I'm trying to find out:
-------------------------------------------------------------
Two tables, "a" and "b". "b" is referencing "a" via "a_id"...
CREATE TABLE a
(
id serial,
foo varchar(255),
CONSTRAINT aid PRIMARY KEY (id)
);
CREATE TABLE b
(
id serial,
a_id int4 not null,
foo varchar(255),
CONSTRAINT bid PRIMARY KEY (id),
CONSTRAINT bfk FOREIGN KEY (a_id)
REFERENCES a (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
...a view "ab" for a combination of the before mentioned tables...
CREATE OR REPLACE VIEW ab AS
SELECT a.id AS main_id, a.foo AS from_a, b.foo AS from_b
FROM a, b
WHERE a.id = b.a_id;
...and a rule "ab_insert"...
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'));
);
-------------------------------------------------------------
As you can see, the "ab_insert" rule inserts into "a" first and than
takes the current value of "a.id"'s sequence to set the reference "a_id"
in "b".
Can I assume that this will always work as expected or is it possible
that in a multi-user scenario two or more concurrent inserts on the view
will lead to undesirable results?
As mentioned in the beginning, putting BEGIN; and COMMIT; didn't work.
Is this kind of creating a relation between two or more tables and
relying on a sequence generally a good practice?
Regards,
Oliver
Oliver Fürst <ofuerst@tsak.net> writes: > Hi all, > > 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. -Doug
Hi Doug,
On 27.01.2006 21:01, Doug McNaught wrote:
> Oliver Fürst <ofuerst@tsak.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
Oliver Fürst <ofuerst@tsak.net> writes: > Basically I'm worried that the whole "relying on the last value of a > sequence" isn't such a great idea. 'currval()' is specifically written to Do The Right Thing. See the docs. -Doug
Hi Doug, thanks for your answers so far. I think I should try to discuss that matter in [pgsql-sql] instead. On 27.01.2006 21:21, Doug McNaught wrote: > Oliver Fürst <ofuerst@tsak.net> writes: > >>Basically I'm worried that the whole "relying on the last value of a >>sequence" isn't such a great idea. > > 'currval()' is specifically written to Do The Right Thing. See the > docs. My problem isn't answered in the manual, otherwise I wouldn't have asked. I know that sequences are working correct for transactions (either implicit or explicit). But is nowhere stated if (multiple) commands inside a rule are treated as an implicit transaction as a whole. Regards, Oliver
Oliver Fürst <ofuerst@tsak.net> writes: > But is nowhere stated if (multiple) commands inside a rule are treated > as an implicit transaction as a whole. If you don't specifically open a transaction at the top level (i.e. in 'psql' or SQL from your application's code), PG will encapsulate every query you execute within its own transaction. The statements in any rules called will execute in the context of that transaction, along with any other operations such as trigger calls, table updates, whatever. If something in that implicit transaction fails, it will be rolled back; otherwise, it will be committed once the statement is finished. Also, you need to think of rules as 'query rewrites' rather than 'code that executes', because that's what they are. I hope that helps. -Doug
Oliver Fürst wrote:
>> 'currval()' is specifically written to Do The Right Thing. See the
>> docs.
>
> My problem isn't answered in the manual, otherwise I wouldn't have
> asked. I know that sequences are working correct for transactions
> (either implicit or explicit).
>
> But is nowhere stated if (multiple) commands inside a rule are treated
> as an implicit transaction as a whole.
Sequences aren't related to transactions the way you seem to think.
Every time you request a nextval, no matter in what transaction you are,
you get a new value (higher, with the exception of the wraparound case).
I suppose currval always matches the last result of nextval in your
transaction (as manipulated by your queries). Can't say I'm certain, but
that's something that can easily be tested.
I don't know the implementation details of sequences in PostgreSQL, but
that's how sequences are supposed to work. They do (as stated before)
"The Right Thing". You don't need to worry about them.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//