Are rules transaction safe?

From: Oliver Fürst <ofuerst(at)tsak(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Are rules transaction safe?
Date: 2006-01-27 20:48:29
Message-ID: 43DA871D.8000704@tsak.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Doug McNaught 2006-01-27 21:01:25 Re: Are rules transaction safe?
Previous Message Tom Lane 2006-01-27 20:43:51 Re: WAL questions