Thread: Using EXECUTE in a function
Hi, I have found under http://www.physiol.ox.ac.uk/Computing/Online_Documentation/postgresql/plpgsql.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
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
Sachchida Ojha wrote:
>
> 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.
What Postgres version is this?
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
8.2.3 Thanks Regards Sachchida N Ojha sojha@secure-elements.com Secure Elements Incorporated 198 Van Buren Street, Suite 110 Herndon Virginia 20170-5338 USA http://www.secure-elements.com/ 800-709-5011 Main 703-709-2168 Direct 703-709-2180 Fax This email message and any attachment to this email message is intended only for the use of the addressee(s) named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering the message to the intended recipient(s), please note that any distribution or copying of this communication is strictly prohibited. If you have received this email in error, please notify me immediately and delete this message. Please note that if this email contains a forwarded message or is a reply to a prior message, some or all of the contents of this message or any attachments may not have been produced by the sender. -----Original Message----- From: Alvaro Herrera [mailto:alvherre@commandprompt.com] Sent: Tuesday, July 31, 2007 11:28 AM To: Sachchida Ojha Cc: Andreas Tille; pgsql-performance@postgresql.org Subject: Re: [PERFORM] deadlock detected when calling function (Callfunction_name) Sachchida Ojha wrote: > > 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. What Postgres version is this? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
On 7/31/07, Andreas Tille <tillea@rki.de> wrote: http://www.physiol.ox.ac.uk/Computing/Online_Documentation/postgresql/plpgsql.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). If I read the documentation correctly, EXECUTE is not needed because query plans are generally cached within pl/pgsql after the first execution of the function. > 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. prepared statements are the fastest possible way to execute queries but generally that extra speed is not measurable or only useful under specific conditions. also, prepared statements can't be folded into queries the way functions can: select xml_data(foo, bar) from baz; so, I'd stick with the function approach (I think that's what you are asking). If you are doing XML work you may be interested in the upcoming xml features of 8.3: http://developer.postgresql.org/pgdocs/postgres/functions-xml.html merlin