<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.6000.16544" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV>Each manager thread has its own Connection, Transaction, and
TQuery.</DIV>
<DIV>Each manager can handle potentially thousands of sockets</DIV>
<DIV>Each server instance (1 process) can have up to ~100 manager
threads.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><BR></DIV>
<DIV>Each Manager executes socket "commands" that translate at some point,
into a Database, Select, Update, or Delete.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><BR></DIV>
<DIV>My current problem: </DIV>
<DIV>If a user updates a table by uploading large a video (say about 200+MB)
the sql server locks pretty much every table and prevents other sockets
which during the SQL execution. This is true for Delete and Update as of
v9.1.</DIV></BLOCKQUOTE>
<DIV><FONT face=Arial color=#0000ff size=2><SPAN class=328595907-22062012>Are
the tables locked or is the server just slow? What does 'select * from
pg_catalog.pg_locks where granted is false' return? For a query that lists
interdependent lock information see <A
href="http://wiki.postgresql.org/wiki/Lock_dependency_information"><FONT
face="Times New Roman"
size=3>http://wiki.postgresql.org/wiki/Lock_dependency_information</FONT></A>.</SPAN></FONT><BR></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV>Implications. Parallel commands executed on other threads aren't
able to execute b/c the postgresql server just sits there waiting for the
update transaction to complete. PostgreSQL is locking all
subsequent calls to even other tables.</DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT><FONT face=Arial
color=#0000ff size=2></FONT><BR></DIV>
<DIV>Remediation: Memory barriers are already in place to ensure that other
threads don't access the TConnection,TQuery or other things during the
problematic transaction. Is there a way to use specify a SHARE mode in
transactions for TPostgres component?</DIV></BLOCKQUOTE>
<DIV dir=ltr><SPAN class=328595907-22062012></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>T<SPAN class=328595907-22062012>hat I don't
understand. You started with each thread having its own
Tconnection,TTransaction, Tquery. Here you say you put a memory barrier to stop
access to Tconnection,Tquery from other threads.
</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=328595907-22062012>"During the problematic transaction": is this a one
query transaction or do you have a lot of queries on multiple tables in the
transaction, triggers included? What is your isolation level for these
transactions?</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=328595907-22062012>What exactly do you want to share in transactions
for TPostgres component? How are TPostgres components related to a server
locking tables? </SPAN><SPAN
class=328595907-22062012> </SPAN></FONT></FONT></FONT><BR></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV>How have anyone who've encountered this problem solved this issue?
Any feedback is welcome.<SPAN class=328595907-22062012><FONT face=Arial
color=#0000ff size=2> </FONT></SPAN></DIV>
<DIV><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV></BLOCKQUOTE>
<DIV dir=ltr><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2>I haven't run into this problem yet but some general
advise:</FONT></SPAN></DIV>
<DIV dir=ltr><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2>1) Determine what the exact cause of the lock is before implementing
remedies. </FONT> </SPAN></DIV>
<DIV dir=ltr><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2>2) The postgresql components make a lot of connections. When
serving that many clients, you will probably benefit from connection pooling.
Look at something like pgpool-II <A
href="http://pgpool.projects.postgresql.org/">http://pgpool.projects.postgresql.org/</A>.</FONT></SPAN></DIV>
<DIV dir=ltr><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2>3) Putting 200MB files in a database is a bad idea</FONT></SPAN></DIV>
<DIV dir=ltr><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV dir=ltr><SPAN class=328595907-22062012><FONT face=Arial color=#0000ff
size=2>Ludo</FONT></SPAN></DIV></BODY></HTML>