Rules and locking within a transaction?...
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
Hello,
I have a table that uses tsearch2 and, of course, and index and trigger
to keep everything updated..... Something like:
CREATE TABLE sometable (
id SERIAL,
someinteger INTEGER
sometext TEXT,
sometext2 TEXT,
sometext3 TEXT,
sometext_fti TSVECTOR
);
UPDATE sometable SET sometext_fti=to_tsvector('default',
COALESCE(sometext, ''));
CREATE INDEX sometext_fti_idx ON sometable USING gist(sometext_fti);
CREATE TRIGGER text_update BEFORE UPDATE OR INSERT ON sometable FOR EACH
ROW EXECUTE PROCEDURE tsearch2(sometext_fti, sometable);
There are two issues:
#1. I need a way to be able to do a full-text search any combination of
sometext, sometext2, and sometext3... So far the only way I've been able
to figure out to do this is to actually create additional fields and
indexes for all combinations (a total of 7), like the following:
CREATE TABLE sometable (
id SERIAL,
someinteger INTEGER,
sometext_fti TSVECTOR,
sometext TEXT,
sometext_fti TSVECTOR,
sometext2 TEXT,
sometext2_fti TSVECTOR,
sometext3 TEXT,
sometext3_fti TSVECTOR
sometext1_2_fti TSVECTOR
sometext1_3_fti TSVECTOR
sometext2_3_fti TSVECTOR
sometext1_2_3_fti TSVECTOR
);
UPDATE sometable SET sometext_fti=to_tsvector('default',
COALESCE(sometext, ''));
CREATE INDEX sometext_fti_idx ON sometable USING gist(sometext_fti);
CREATE TRIGGER sometext_update BEFORE UPDATE OR INSERT ON sometable FOR
EACH ROW EXECUTE PROCEDURE tsearch2(sometext_fti, sometable);
UPDATE sometable SET sometext2_fti=to_tsvector('default',
COALESCE(sometext2, ''));
CREATE INDEX sometext2_fti_idx ON sometable USING gist(sometext2_fti);
CREATE TRIGGER sometext2_update BEFORE UPDATE OR INSERT ON sometable FOR
EACH ROW EXECUTE PROCEDURE tsearch2(sometext2_fti, sometable);
UPDATE sometable SET sometext3_fti=to_tsvector('default',
COALESCE(sometext3, ''));
CREATE INDEX sometext3_fti_idx ON sometable USING gist(sometext3_fti);
CREATE TRIGGER sometext3_update BEFORE UPDATE OR INSERT ON sometable FOR
EACH ROW EXECUTE PROCEDURE tsearch2(sometext3_fti, sometable);
UPDATE sometable SET sometext1_2_fti=to_tsvector('default',
COALESCE(sometext1, '')||' '||COALESC(sometext2, ''));
CREATE INDEX sometext1_2_fti_idx ON sometable USING gist(sometext1_2_fti);
CREATE TRIGGER sometext1_2_update BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(sometext_1_2_fti, sometable);
UPDATE sometable SET sometext1_3_fti=to_tsvector('default',
COALESCE(sometext1, '')||' '||COALESC(sometext3, ''));
CREATE INDEX sometext1_3_fti_idx ON sometable USING gist(sometext1_3_fti);
CREATE TRIGGER sometext1_3_update BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(sometext1_3_fti, sometable);
UPDATE sometable SET sometext2_3_fti=to_tsvector('default',
COALESCE(sometext2, '')||' '||COALESC(sometext3, ''));
CREATE INDEX sometext2_3_fti_idx ON sometable USING gist(sometext2_3_fti);
CREATE TRIGGER sometext2_3_update BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(sometext2_3_fti, sometable);
UPDATE sometable SET sometext1_2_3_fti=to_tsvector('default',
COALESCE(sometext1, '')||' '||COALESCE(sometext2, '')||'
'||COALESC(sometext3, ''));
CREATE INDEX sometext1_2_3_fti_idx ON sometable USING gist(sometext1_2_3_fti);
CREATE TRIGGER sometext1_2_3_update BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(sometext1_2_3_fti, sometable);
... of course, this creates an enormous load on the server for any
update/delete operation... I tried just creating 3 indexes (one for each
field) and doing a query like:
SELECT * FROM sometable WHERE sometext1 @@ to_tsquery('default',
'postgres') OR sometext2 @@ to_tsquery('default', 'postgres') OR
sometext3 @@ to_tsquery('default', 'postgres') -- but found this to be
enormously expensive for queries....
.. with the arrangement above, I make some decision as to which field to
query, for example:
SELECT * FROM sometable WHERE sometext1_2_3 @@ to_tsquery('default',
'postgres'); -- this executes MUCH faster
.. is there some way to get the best of both worlds here or am I trying
to jam a square peg into a round hole?...
#2. When doing an update on the above table, such as: "UPDATE sometable
SET someinteger=0", it ends up firing off the triggers which of course
takes a long time to update... Is there someway to make the trigger only
fire if a the field it is tied to is updated?.... This is not a
Thanks as always!
- Greg
On Fri, 15 Oct 2004, Net Virtual Mailing Lists wrote:
Hello,
I have a table that uses tsearch2 and, of course, and index and trigger
to keep everything updated..... Something like:CREATE TABLE sometable (
id SERIAL,
someinteger INTEGER
sometext TEXT,
sometext2 TEXT,
sometext3 TEXT,
sometext_fti TSVECTOR
);There are two issues:
#1. I need a way to be able to do a full-text search any combination of
sometext, sometext2, and sometext3... So far the only way I've been able.. is there some way to get the best of both worlds here or am I trying
to jam a square peg into a round hole?...
Hmm, probably :)
There is a hint on http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
under section "Restricted search".
#2. When doing an update on the above table, such as: "UPDATE sometable
SET someinteger=0", it ends up firing off the triggers which of course
takes a long time to update... Is there someway to make the trigger only
fire if a the field it is tied to is updated?.... This is not a
no idea, sorry
Thanks as always!
- Greg
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Hello,
Thank you to Oleg for your help with this earlier! It resolved it very
nicely!
I still have one remaining issue which I can't figure out, perhaps best
explained with an example:
CREATE TABLE sometable (
title TEXT,
body TEXT,
footer TEXT,
all_fti TSVECTOR
);
UPDATE sometable SET all_fti = setweight(to_tsvector(title), 'A') ||
setweight(to_tsvector(body),'B') || setweight(to_tsvector(footer), 'C');
INSERT INTO sometable (title, body, footer) VALUES ('something in the
title', 'something in the body', 'something in the footer');
INSERT INTO sometable (title, body, footer) VALUES ('anything in the
title', 'anything in the body', 'anything in the footer');
INSERT INTO sometable (title, body, footer) VALUES ('whatever in the
title', 'whatever in the body', 'whatever in the footer');
.. What I can't figure out is how to make those last 3 inserts
automatically update all_fti via a trigger... The documentation would
show something like:
CREATE TRIGGER sometable_update_fti BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(all_fti, title, body, footer);
.. but as expected, this does not take into account the result of the
"setweight" function.... Unfortunately, I am not very skilled with
Postgres's triggers (yet) and I can't find any documentation on how to go
about this...
Any thoughts?.....
- Greg
Import Notes
Reply to msg id not found: 20041015130203.23817@mail.e-na.netReference msg id not found: 20041015130203.23817@mail.e-na.net | Resolved by subject fallback
On Sat, 16 Oct 2004, Net Virtual Mailing Lists wrote:
Hello,
Thank you to Oleg for your help with this earlier! It resolved it very
nicely!I still have one remaining issue which I can't figure out, perhaps best
explained with an example:CREATE TABLE sometable (
title TEXT,
body TEXT,
footer TEXT,
all_fti TSVECTOR
);UPDATE sometable SET all_fti = setweight(to_tsvector(title), 'A') ||
setweight(to_tsvector(body),'B') || setweight(to_tsvector(footer), 'C');INSERT INTO sometable (title, body, footer) VALUES ('something in the
title', 'something in the body', 'something in the footer');
INSERT INTO sometable (title, body, footer) VALUES ('anything in the
title', 'anything in the body', 'anything in the footer');
INSERT INTO sometable (title, body, footer) VALUES ('whatever in the
title', 'whatever in the body', 'whatever in the footer');.. What I can't figure out is how to make those last 3 inserts
automatically update all_fti via a trigger... The documentation would
show something like:CREATE TRIGGER sometable_update_fti BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(all_fti, title, body, footer);.. but as expected, this does not take into account the result of the
"setweight" function.... Unfortunately, I am not very skilled with
Postgres's triggers (yet) and I can't find any documentation on how to go
about this...Any thoughts?.....
You're right, tsearch2 trigger doesn't supports tsvector as argument yet.
The problem is in rather complex argument recognition. I don't know
any workaround, so take a look on sources or wait until we'll have spare
time to implement it.
- Greg
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83