Hello,
I am getting following error from my application. Can any body tell me
how to find the process name and transaction details when the deadlock
occurred?
This problem did not occur consistently.
Error log
2007-07-30 19:09:12,140 ERROR [se.em.asset.persistence.AssetUpdate]
SQLException calling procedure{? = call update_asset_dependents(?,?,?)}
for asset id 36
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 21172 waits for ShareLock on transaction 5098759;
blocked by process 21154.
Process 21154 waits for ShareLock on transaction 5098760; blocked by
process 21172.
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecu
torImpl.java:1548)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImp
l.java:1316)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
191)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdb
c2Statement.java:351)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stateme
nt.java:344)
at
org.jboss.resource.adapter.jdbc.CachedPreparedStatement.execute(CachedPr
eparedStatement.java:216)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(Wrapped
PreparedStatement.java:209)
at
se.em.asset.persistence.AssetUpdate.callProcedure(AssetUpdate.java:1751)
at
se.em.asset.persistence.AssetUpdate.updateAsset(AssetUpdate.java:1028)
at
se.em.asset.service.AssetService.updateAsset(AssetService.java:3843)
at
se.em.asset.service.AssetService.process(AssetService.java:1042)
at sun.reflect.GeneratedMethodAccessor669.invoke(Unknown Source)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessor
Impl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at
se.em.framework.service.ServiceAbstract.process(ServiceAbstract.java:163
)
at
se.em.framework.service.ServiceAbstract.process(ServiceAbstract.java:58)
at
se.em.commwebservice.webservice.AssetDataHandler.getandCallService(Asset
DataHandler.java:1810)
at
se.em.commwebservice.webservice.AssetDataHandler.run(AssetDataHandler.ja
va:487)
Thanks
Regards
Sachchida N Ojha
-----Original Message-----
From: pgsql-performance-owner@xxxxxxxxxxxxxx
[mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Andreas
Tille
Sent: Tuesday, July 31, 2007 10:10 AM
To: pgsql-performance@xxxxxxxxxxxxxx
Subject: Using EXECUTE in a function
Hi,
I have found under
http://www.physiol.ox.ac.uk/Computing/Online_Documentation/postgresql/pl
pgsql.html#PLPGSQL-OVERVIEW
Note: The PL/pgSQL EXECUTE statement is not related to the
EXECUTE
statement supported by the PostgreSQL server. The server's
EXECUTE
statement cannot be used within PL/pgSQL functions (and is
not needed).
I'm especially stumbling over the "is not needed" part. My plan
is to write a server side function (either SQL or pgsql) that wraps
the output of a PREPAREd statement but I have no idea how to do this.
The final task is to obtain some XML for of my data via a simple shell
script
that contains
psql -t MyDatabase -c 'SELECT * FROM MyFunction ($1, $2);'
The task of MyFunction($1,$2) is to wrap up the main data into an XML
header (just some text like
<?xml version="1.0" encoding="ISO-8859-1"?>
...
) around the real data that will be obtained via a PREPAREd statement
that is
declared like this
PREPARE xml_data(int, int) AS ( SELECT ... WHERE id = $1 AND source
= $2 );
where "..." stands for wrapping the output into xml format.
I don't know whether this is a reasonable way. I know how to solve this
problem when using a pgsql function and preparing the output as a text
string but I learned that PREPAREd statements might be much more clever
performance wise and thus I wonder whether I could do it this way.
Kind regards and thanks for any help
Andreas.
--
http://fam-tille.de
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
