Rules and locking within a transaction?...

From: "Net Virtual Mailing Lists" <mailinglists(at)net-virtual(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Rules and locking within a transaction?...
Date: 2004-10-09 23:50:17
Message-ID: 20041009235017.25998@mail.e-na.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

If I have a rule like this:

CREATE OR REPLACE RULE sometable_update AS ON UPDATE TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_insert AS ON INSERT TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_delete AS ON DELETE TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='sometable';

Then I begin inserting a bunch of records within a transaction into
table2, like this:

BEGIN;
INSERT INTO table2 (val1, val2) VALUES ('hello','world');
...(etc)
COMMIT;

... It appears that there is a row lock in cache table for the duration
of the transaction....

First of all, is my premise correct or is there some hidden problem I'm
not seeing?...

Secondly, if there is no hidden problem, is there some way (short of
dropping and recreating the rule) to make it not perform the row lock and
execute the rule at the end of the transaction (if the transaction
succeeds of course)?...

Thanks!

- Greg

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Parker 2004-10-10 01:52:36 external function libraries on non-build machine
Previous Message Bernard Clement 2004-10-09 20:25:22 Re: install problem