implicit lock in RULE ?
От | Fritz Lehmann-Grube |
---|---|
Тема | implicit lock in RULE ? |
Дата | |
Msg-id | 3EA80719.9000205@math.tu-berlin.de обсуждение исходный текст |
Ответы |
Re: implicit lock in RULE ?
|
Список | 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
В списке pgsql-novice по дате отправления: