Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 743DAA8E4B5 for ; Tue, 6 Dec 2011 21:39:12 -0400 (AST) Received: from [222.73.24.84] (helo=song.cn.fujitsu.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1RY6TU-0007wX-EA for pgsql-admin@postgresql.org; Wed, 07 Dec 2011 01:39:11 +0000 Received: from tang.cn.fujitsu.com (tang.cn.fujitsu.com [10.167.250.3]) by song.cn.fujitsu.com (Postfix) with ESMTP id 614A417011E; Wed, 7 Dec 2011 09:38:42 +0800 (CST) Received: from jfttmail.jftt.cn.fujitsu.com (tang.cn.fujitsu.com [127.0.0.1]) by tang.cn.fujitsu.com (8.14.3/8.13.1) with ESMTP id pB71cfT2016507; Wed, 7 Dec 2011 09:38:41 +0800 Received: from fanlijing (unknown [10.167.14.111]) by jfttmail.jftt.cn.fujitsu.com (Postfix) with ESMTP id 6F33C822C; Wed, 7 Dec 2011 09:40:38 +0800 (CST) X-SecurityPolicyCheck-FJ: OK by FujitsuOutboundMailChecker v1.4.0 Received: from fanlijing[10.167.14.111] by fanlijing (FujitsuOutboundMailChecker v1.4.0/9992[10.167.14.111]); Wed, 07 Dec 2011 09:39:03 +0800 (JST) From: "fanlijing" To: "'Albe Laurenz'" , "'fanlijing'" , References: <19472E8115934C528372F048DEB29E99@fanlijing> Subject: Is there any support like "for update wait N" in PostgreSQL? Date: Wed, 7 Dec 2011 09:38:57 +0800 Message-ID: <262B52C2DEF844DFB1CB5C81EB02101A@fanlijing> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0009_01CCB4C4.0AC341E0" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcyGWZEyayt1FUq5R0q6Cy1384894wAw3tOgAABa7SAABrDHEAAHteQwC+AYzPAAltXn4A== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 In-Reply-To: <19472E8115934C528372F048DEB29E99@fanlijing> X-Host-Lookup-Failed: Reverse DNS lookup failed for 222.73.24.84 (failed) X-Pg-Spam-Score: 1.8 (+) X-Archive-Number: 201112/40 X-Sequence-Number: 38798 This is a multi-part message in MIME format. ------=_NextPart_000_0009_01CCB4C4.0AC341E0 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable Hello, Laurenz =20 I'm sorry for replying you so late.. Thank you for your advice below.=20 Finally we decide to port it as a shell using the copy command. =20 Actually, now I'm confused with another problem, that is: Oracle: for update wait 10 PostgreSQL: no support for the parameter "wait" =20 What should I do when I'm doing porting on this point? If I get rid of the parameter "wait", there would be a dead lock in my program... Is there any support like "for update wait N" in PostgreSQL? =20 I=A1=AFm waiting for your reply. Thank you so much any way. #########################################################################= ### Following is my porting method, although it avoid the dead-lock problem, = but it affect the functionality. =20 SELECT DOMAIN_ID, DOMAIN_NAME...FOR UPDATE WAIT 10 ORDER BY =A1=AD -> SELECT DOMAIN_ID, DOMAIN_NAME...ORDER BY =A1=AD FOR UPDATE =20 =20 return (List) queryBean.getQueryRun().query(queryBean.getConn(), strSql, = new String[]{domain_nm,server_flag}, srHandler); -> Connection conn =3D queryBean.getConn(); QueryRunner queryRunner =3D queryBean.getQueryRun(); queryRunner.update(conn, "set statement_timeout =3D 10000"); return (List) queryRunner.query(conn, strSql, new String[]{domain_nm,server_flag}, srHandler); #########################################################################= ### Following is the exception when run my program=A1=AD =20 11/12/06 15:18:17 ***: APL: INFO : =A1=AD =A1=AD.***SqlException: ERROR: canceling statement due to statement = timeout Query: SELECT=A1=AD ORDER BY =A1=AD FOR UPDATE Parameters: [yun_SF_18, = 0] =A1=AD =A1=AD 11/12/06 15:18:17 ***: APL: ERROR: [ID:flj777] =A1=AD : !! Exception = [class =A1=AD.***SqlException =A1=AD =A1=AD ] #########################################################################= ### =20 Best Regards! -----Original Message----- From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]=20 Sent: Monday, October 10, 2011 10:26 PM To: Albe Laurenz; fanlijing *EXTERN*; pgsql-admin@postgresql.org; pgsql-general@postgresql.org Subject: RE: [GENERAL] how to save a bytea value into a file? =20 I wrote: [fanlijing wants to write bytea to file] > A simple > COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. =20 Corrections: a) "binary" must be surrounded by single quotes. b) that won't dump just the binary data - you would have to remove the first 25 bytes and the last 2 bytes... =20 So maybe using the functions I mentioned would be the best way after all. =20 You could also write your own user defined function in C. =20 Yours, Laurenz Albe =20 ------=_NextPart_000_0009_01CCB4C4.0AC341E0 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable

Hello, = Laurenz

 

I'm sorry for replying you so = late..

Thank you for your advice below. =

Finally we decide to port it as a = shell using the copy command.

 

Actually, now = I'm confused with another problem, that is:

Oracle: for update wait 10

PostgreSQL: no support for the parameter = "wait"

 

What should I do when I'm doing = porting on this point?

If I get rid of the parameter = "wait", there would be a dead lock in my program...

Is = there any support like "for update wait N" in = PostgreSQL?

 

I=A1=AFm waiting for your = reply.

Thank you so much any = way.

##################################= ##########################################

Following is my porting = method, although it avoid the dead-lock problem, but it affect the = functionality.

 

SELECT DOMAIN_ID, = DOMAIN_NAME...FOR UPDATE WAIT 10 ORDER BY = =A1=AD

->

=

SELECT DOMAIN_ID, = DOMAIN_NAME...ORDER BY =A1=AD FOR = UPDATE

 

 

return = (List) queryBean.getQueryRun().query(queryBean.getConn(), strSql, new = String[]{domain_nm,server_flag}, srHandler);

->

Connection conn =3D = queryBean.getConn();

QueryRunner queryRunner =3D queryBean.getQueryRun();

queryRunner.update(conn, = "set = statement_timeout =3D = 10000");

return (List) = queryRunner.query(conn, strSql, new String[]{domain_nm,server_flag}, = srHandler);

##################################= ##########################################

Following is the exception when run my program=A1=AD

 

11/12/06 15:18:17 = ***: APL: INFO : = =A1=AD

=A1=AD.***SqlException: ERROR: canceling statement due to statement = timeout Query: SELECT=A1=AD = ORDER BY =A1=AD FOR UPDATE = Parameters: [yun_SF_18, 0]

     &nbs= p;  =A1=AD

     &nbs= p;  =A1=AD

11/12/06 15:18:17 ***: APL: = ERROR: [ID:flj777] =A1=AD : !! Exception = [class =A1=AD.***SqlException

     &nbs= p;  =A1=AD

     &nbs= p;  =A1=AD

]

##################################= ##########################################

 

Best = Regards!

-----Original = Message-----

From: Albe Laurenz = [mailto:laurenz.albe@wien.gv.at]

Sent: Monday, October 10, 2011 10:26 = PM

To: Albe Laurenz; fanlijing *EXTERN*; pgsql-admin@postgresql.org;

pgsql-general@postgresql.org=

Subject: RE: [GENERAL] how to save a bytea = value into a file?

 

I wrote:

[fanlijing wants to write bytea to = file]

> A simple

>    COPY (SELECT byteacol = WROM mytab WHERE ...) TO 'filename' (FORMAT

binary)

> should do the = trick.

 

Corrections:

a) "binary" must be surrounded by = single quotes.

b) that won't dump just the binary data - you = would have

   to remove the first 25 bytes and = the last 2 bytes...

 

So maybe using the functions I mentioned = would be the

best way after = all.

 

You could also write your own user defined = function in C.

 

Yours,

Laurenz Albe

 

------=_NextPart_000_0009_01CCB4C4.0AC341E0--