Thread: implicit lock in RULE ?

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

Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE> writes:
> 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 ?

Drop the BEGIN, the LOCK, and the COMMIT.  Read the discussion of
sequence functions at
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-sequence.html
to see why you don't need any LOCK to protect the currval() value.

A more serious problem with the above is that it will fail to do what
you want for multiple-row insertion commands --- the INSERT will
correctly insert multiple rows into vc_threads, but when control comes
to the UPDATE, all of the freshly added images rows will be updated to
link to the last of those vc_threads rows, because you only have one
currval() value to work with.

You'd be better off doing this as a trigger, not a rule.  The syntax
hurdle is a bit higher (you need to learn a little bit of pl/pgsql)
but the mental model of what's going on is far simpler.  Triggers
work on one row at a time --- rules don't.

            regards, tom lane

Fritz Lehmann-Grube <fritzlg@gmx.de> writes:
>> You'd be better off doing this as a trigger, not a rule.  The syntax
>> hurdle is a bit higher (you need to learn a little bit of pl/pgsql)

> I know, but my "contract" tells me to produce code "as standard SQL as
> possible" (sorry. They think we might want to be able to port to oracle
> or something - though we can't, we're open source. See www.mumie.net or
> www.math.tu-berlin.de/multiverse )- TRIGGERS are, as much as RULES, but
> pl/pgsql is not. (Am I right ? I'd be glad to use more pl/pgsql)

I would think you'd have a better shot at porting triggers to Oracle
than rules.  pl/pgsql is a shameless imitation of Oracle's PL/SQL, so
that part is no problem.  There are syntactical differences in the way
you set up a trigger, but AFAIK the functionality is comparable.
On the other hand, I don't believe there is anything similar to PG's
rule system in any other DBMS.

            regards, tom lane

Thanks Tom,
that solves my problem...

Tom Lane schrieb:
>
> Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE> writes:
> > 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 ?
>
> Drop the BEGIN, the LOCK, and the COMMIT.  Read the discussion of
> sequence functions at
> http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-sequence.html
> to see why you don't need any LOCK to protect the currval() value.

 I had hoped for something exactly like this. Though I had searched for
it in the RULE system.
So SEQUENCES are threadsafe inside sessions !

>
> A more serious problem with the above is that it will fail to do what
> you want for multiple-row insertion commands --- the INSERT will
> correctly insert multiple rows into vc_threads, but when control comes
> to the UPDATE, all of the freshly added images rows will be updated to
> link to the last of those vc_threads rows, because you only have one
> currval() value to work with.

I must admid, I hadn't thought about that. But it's no problem, because
I can guarantee SINGLE ROW inserts at a time at least per session.

>
> You'd be better off doing this as a trigger, not a rule.  The syntax
> hurdle is a bit higher (you need to learn a little bit of pl/pgsql)

I know, but my "contract" tells me to produce code "as standard SQL as
possible" (sorry. They think we might want to be able to port to oracle
or something - though we can't, we're open source. See www.mumie.net or
www.math.tu-berlin.de/multiverse )- TRIGGERS are, as much as RULES, but
pl/pgsql is not. (Am I right ? I'd be glad to use more pl/pgsql)

> but the mental model of what's going on is far simpler.  Triggers
> work on one row at a time --- rules don't.
>
>                         regards, tom lane

thank you, Fritz Lehmann-Grube