From: | Fritz Lehmann-Grube <lehmannf(at)math(dot)TU-Berlin(dot)DE> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | implicit lock in RULE ? |
Date: | 2003-04-24 15:47:37 |
Message-ID: | 3EA80719.9000205@math.tu-berlin.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hallo,
Are tables, that are accessed by a rule, implicitely locked ?
I tried
CREATE RULE new_vc_thread AS ON INSERT TO images
WHERE new.vc_thread = 0
DO
(
BEGIN;
LOCK vc_threads;
INSERT INTO vc_threads(name) VALUES(new.name);
UPDATE images SET vc_thread = currval('vc_threads_id_seq') WHERE vc_thread=0;
COMMIT;
);
but got a syntax error - OK.
But how can I work around it ?
See my code, especially the comments in the CREATE RULE statement:
-----------------------------------------------------------------------
/*-----------------------------------------------
Scenario:
------------------------------------------------*/
CREATE TABLE vc_threads(
id serial primary key,
name varchar(100) DEFAULT NULL
);
INSERT INTO vc_threads(id,name) VALUES(0,'DEFAULT VC_THREAD');
CREATE TABLE images(
id serial PRIMARY KEY,
vc_thread int REFERENCES vc_threads(id) NOT NULL DEFAULT 0,
name varchar(80) NOT NULL
);
/*-----------------------------------------------
The RULE in question:
I want to have a new vc_thread, any time an image
without specified "vc_thread" column is inserted.
------------------------------------------------*/
CREATE RULE new_vc_thread AS ON INSERT TO images
WHERE new.vc_thread = 0
DO
(
-- the insert on "images" is now already made
INSERT INTO vc_threads(name) VALUES(new.name);
-- this insert on "vc_threads" implicitly calls nextval('vc_threads_id_seq'),
-- such that
UPDATE images SET vc_thread = currval('vc_threads_id_seq') WHERE vc_thread=0;
-- should set images.vc_thread to the new vc_thead's id
-- UNLESS (!!) any concurrent client calls nextval('vc_threads_id_seq') between the
-- INSERT and the UPDATE. Can that happen ?
);
-- Tests:
INSERT INTO images(name) VALUES('bild1');
-- INSERT 101487 1
INSERT INTO images(vc_thread,name) VALUES(null,'bild2');
-- psql:test.sql:27: ERROR: ExecInsert: Fail to add null value in not null
attribute vc_thread
INSERT INTO images(vc_thread,name) VALUES(0,'bild3');
-- INSERT 101489 1
INSERT INTO images(vc_thread,name) VALUES(1,'bild4');
-- INSERT 101491 1
INSERT INTO images(id,name) VALUES(15,'bild5');
-- INSERT 101492 1
-- works correct, but is it safe ?
--------------------------------------------------------------
Sorry, I couldn't find the answer in the docs.
Thanks, Fritz
From | Date | Subject | |
---|---|---|---|
Next Message | David Olbersen | 2003-04-24 17:21:31 | ODBC & Access [Try 2] |
Previous Message | Tom Lane | 2003-04-24 15:42:42 | Re: [NOVICE] Postgres Remote Syslog |