Re: deadlock detected when calling function (Call function_name) - Mailing list pgsql-performance

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@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Andreas
Tille
Sent: Tuesday, July 31, 2007 10:10 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] 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