Re: [GENERAL] Are rules transaction safe?
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 [EMAIL PROTECTED] 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// ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Are rules transaction safe?
Oliver Fürst <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Are rules transaction safe?
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 <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Are rules transaction safe?
Oliver Fürst <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Are rules transaction safe?
Hi Doug,
On 27.01.2006 21:01, Doug McNaught wrote:
Oliver Fürst <[EMAIL PROTECTED]> 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Are rules transaction safe?
Oliver Fürst <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
