473,598 Members | 3,369 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Rules and locking within a transaction?...

Hello,

If I have a rule like this:
CREATE OR REPLACE RULE sometable_updat e AS ON UPDATE TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='some table';
CREATE OR REPLACE RULE sometable_inser t AS ON INSERT TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='some table';
CREATE OR REPLACE RULE sometable_delet e AS ON DELETE TO table2 DO UPDATE
cache SET updated_dt=NULL WHERE tablename='some table';
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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #1
2 3318
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('defa ult',
COALESCE(somete xt, ''));
CREATE INDEX sometext_fti_id x ON sometable USING gist(sometext_f ti);
CREATE TRIGGER text_update BEFORE UPDATE OR INSERT ON sometable FOR EACH
ROW EXECUTE PROCEDURE tsearch2(somete xt_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_f ti TSVECTOR

);

UPDATE sometable SET sometext_fti=to _tsvector('defa ult',
COALESCE(somete xt, ''));
CREATE INDEX sometext_fti_id x ON sometable USING gist(sometext_f ti);
CREATE TRIGGER sometext_update BEFORE UPDATE OR INSERT ON sometable FOR
EACH ROW EXECUTE PROCEDURE tsearch2(somete xt_fti, sometable);

UPDATE sometable SET sometext2_fti=t o_tsvector('def ault',
COALESCE(somete xt2, ''));
CREATE INDEX sometext2_fti_i dx ON sometable USING gist(sometext2_ fti);
CREATE TRIGGER sometext2_updat e BEFORE UPDATE OR INSERT ON sometable FOR
EACH ROW EXECUTE PROCEDURE tsearch2(somete xt2_fti, sometable);

UPDATE sometable SET sometext3_fti=t o_tsvector('def ault',
COALESCE(somete xt3, ''));
CREATE INDEX sometext3_fti_i dx ON sometable USING gist(sometext3_ fti);
CREATE TRIGGER sometext3_updat e BEFORE UPDATE OR INSERT ON sometable FOR
EACH ROW EXECUTE PROCEDURE tsearch2(somete xt3_fti, sometable);

UPDATE sometable SET sometext1_2_fti =to_tsvector('d efault',
COALESCE(somete xt1, '')||' '||COALESC(some text2, ''));
CREATE INDEX sometext1_2_fti _idx ON sometable USING gist(sometext1_ 2_fti);
CREATE TRIGGER sometext1_2_upd ate BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(somete xt_1_2_fti, sometable);

UPDATE sometable SET sometext1_3_fti =to_tsvector('d efault',
COALESCE(somete xt1, '')||' '||COALESC(some text3, ''));
CREATE INDEX sometext1_3_fti _idx ON sometable USING gist(sometext1_ 3_fti);
CREATE TRIGGER sometext1_3_upd ate BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(somete xt1_3_fti, sometable);

UPDATE sometable SET sometext2_3_fti =to_tsvector('d efault',
COALESCE(somete xt2, '')||' '||COALESC(some text3, ''));
CREATE INDEX sometext2_3_fti _idx ON sometable USING gist(sometext2_ 3_fti);
CREATE TRIGGER sometext2_3_upd ate BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(somete xt2_3_fti, sometable);

UPDATE sometable SET sometext1_2_3_f ti=to_tsvector( 'default',
COALESCE(somete xt1, '')||' '||COALESCE(som etext2, '')||'
'||COALESC(some text3, ''));
CREATE INDEX sometext1_2_3_f ti_idx ON sometable USING gist(sometext1_ 2_3_fti);
CREATE TRIGGER sometext1_2_3_u pdate BEFORE UPDATE OR INSERT ON sometable
FOR EACH ROW EXECUTE PROCEDURE tsearch2(somete xt1_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('def ault',
'postgres') OR sometext2 @@ to_tsquery('def ault', 'postgres') OR
sometext3 @@ to_tsquery('def ault', '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('def ault',
'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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2
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/oddmus...earch_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: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
2596
by: Alban Hertroys | last post by:
Good day, I have a number of threads doing inserts in a table, after which I want to do a select. This means that it will occur that the row that I want to select is locked (by the DB). In these cases, I believe I receive an OperationalError (or is it an InterfaceError?). Is it possible (and if so - how?) to verify that the exception occured because of row locking, so that I can wait and try again?
0
1471
by: Wayne Smallman | last post by:
OK, anyone have some advice on implementing the likes of SELECT ... FOR UPDATE / SELECT ... LOCK IN SHARE MODE? I'm trying to get it working in conjunction with the transaction facilities built into Pear DB. Obvious answer to obvious question: yes, I'm using InnoDB-formatted tables within MySQL. So far, all of the stuff I've put in place certainly isn't stopping me
9
5060
by: john smile | last post by:
Hi All, I want to lock 2 tables on 2 servers using TABLOCKX hint. These tables function as semaphores in my application. It means when the tables are locked then other users will not be able to access them and automatically they can not continue their works. I have tried using the following code, but it does not work. I always got the error :
3
2609
by: heynothanks | last post by:
(MSSQL2000) I have read the transaction/locking sections in the MS-help, online and several books. What I want to understand is the transaction behavior in single statements . If I have a Table: "Letters" with 1 column "L" and the table presently has rows{A,B,C,D} Case 1 (Insert): First start transaction T1 "SELECT * FROM Letters" Next start transaction T2 "INSERT INTO Letters
16
8917
by: Nid | last post by:
How do I do row-level locking on SQL Server? Thanks, Nid
3
6085
by: mahajan.sanjeev | last post by:
Hi All, I am using a SQLTransaction to insert records into a table. At one time, there are 5000 or more records to be inserted one by one. It takes some 20-25 mins for the entire process to run. Another application accesses the same table. As long as the insert process within the transaction isn't completed, the second application is not getting any response from the server. I
2
2099
by: Matt | last post by:
I run SQL Server 2000 and use thier database maintenance plans to backup my databases. My questions is what happens if a change is being made to a database table while a backup is running? Should I be locking the databases before the backup begins? Scenario: 1) Database Plan begins backup at 7:00 PM. 2) At 7:01 PM a web user updates their password while the backup is taking place.
8
3524
by: dan.c.roth | last post by:
Hi I am having a debate with one of the Postgres developers (Tom Lane) according to him ms-sql does not implement predicate locking(SERIALIZABLE). Where predicate locking is defined as (from the postgres help): "12.2.2.1. Serializable Isolation versus True Serializability"
6
4089
by: shaanxxx | last post by:
I have global variable which is being shared between threads (problem is not connected with thread). Without using any mutex i have do some operation global variable in *consistent* way. consider following code : int i = 0 fun (int j)
0
7991
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
7902
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8395
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8050
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
5438
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3898
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3939
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1504
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1250
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.