[GENERAL] getting lock information

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

>"Himanshu Baweja" <[hidden email]> writes
>I wanted to see which tables/transaction have acquired or are waiting for
which locks....
>as far as i know there are two ways to do it....
>
>1) pg_locks ::: need to write trigger... and have high overhead...
>

"select * from pg_locks" has trivial impact on the server. pg_locks is a
view

test=# \d pg_locks;
    View "pg_catalog.pg_locks"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 relation    | oid     |
 database    | oid     |
 transaction | xid     |
 pid         | integer |
 mode        | text    |
 granted     | boolean |
View definition:
 SELECT l.relation, l."database", l."transaction", l.pid, l."mode",
l.granted
   FROM pg_lock_status() l(relation oid, "database" oid, "transaction" xid,
pid
integer, "mode" text, granted boolean);

and it retrives data from server internal lock data structure via
pg_lock_status() function.

What do you mean by "write trigger"?

>2) trace_locks,trace_lwlocks ... etc etc.... ::: well for this my server
says undefined parameters... >so as far as i know i need to rebuild the
server with some flag...
>

Yes, trace_locks is used when LOCK_DEBUG is defined, so you got to rebuild
your server.

Regards,
Qingqing





---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

"Qingqing Zhou" <[hidden email]> writes:
>> "Himanshu Baweja" <[hidden email]> writes
>> 1) pg_locks ::: need to write trigger... and have high overhead...

> What do you mean by "write trigger"?

I'm sure he's imagining that he can create a trigger on pg_locks and
thereby capture lock-related events :-(

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]

Himanshu Baweja wrote:
> is there any other better way by which i can get a list of locks
> acquired and waited for during entire run of my application

Hacking the backend would be the easiest route, I think. Why do you need
this information -- what are you trying to do?

-Neil

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

hacking the backend... can u elaborate a little bit
more... i want to write a small utility which other
ppl can use to see when the locks are acquired etc
etc... so that they can optimise their application
using that information... in my application we have
have 4 processes doing almost the same thing ie they
do the same quey at exactly the same time... and if i
can the complete lock information... i can change the
application so that they dont have to wait for each
other....

thx
Himanshu

--- Neil Conway <[hidden email]> wrote:

               
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new Resources site
http://smallbusiness.yahoo.com/resources/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

> >> 1) pg_locks ::: need to write trigger... and have
high overhead...
>
> > What do you mean by "write trigger"?
>
> I'm sure he's imagining that he can create a trigger
on pg_locks and
> thereby capture lock-related events :-(

well that was wht i was thinking.... got it now... i
will recompile with LOCK_DEBUG
thx
Himanshu


>
> regards, tom lane
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com 

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq