Thread: Is there any support like "for update wait N" in PostgreSQL?
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’m 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 …
->
SELECT DOMAIN_ID, DOMAIN_NAME...ORDER BY … FOR UPDATE
return (List) queryBean.getQueryRun().query(queryBean.getConn(), strSql, new String[]{domain_nm,server_flag}, srHandler);
->
Connection conn = queryBean.getConn();
QueryRunner queryRunner = queryBean.getQueryRun();
queryRunner.update(conn, "set statement_timeout = 10000");
return (List) queryRunner.query(conn, strSql, new String[]{domain_nm,server_flag}, srHandler);
############################################################################
Following is the exception when run my program…
11/12/06 15:18:17 ***: APL: INFO : …
….***SqlException: ERROR: canceling statement due to statement timeout Query: SELECT… ORDER BY … FOR UPDATE Parameters: [yun_SF_18, 0]
…
…
11/12/06 15:18:17 ***: APL: ERROR: [ID:flj777] … : !! Exception [class ….***SqlException
…
…
]
############################################################################
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
fanlijing wrote: > 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'm 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 ... > -> > SELECT DOMAIN_ID, DOMAIN_NAME...ORDER BY ... FOR UPDATE [...] > queryRunner.update(conn, "set statement_timeout = 10000"); [...] > Following is the exception when run my program... [...] > ....***SqlException: ERROR: canceling statement due to statement timeout Query: SELECT... ORDER BY ... FOR > UPDATE Parameters: [yun_SF_18, 0] There is no "WAIT n" clause in PostgreSQL, and using statement_timeout as you did is the only way to get a similar functionality. But you seem unhappy with this approach. How does it affect the functionality? If you use "WAIT n" in Oracle, you will get an error message (ORA-30006) similar to using statement_timeout in PostgreSQL. Yours, Laurenz Albe
Hello Laurenz Thank you for your rapid reply. Using statement_timeout is indeed the only-way to get a similar functionality as "for update wait N" in oracle. It doesn't affect the functionality at all. As I said in the previous mail that this porting method affects the functionality, it is finally verified as a bug in my program. In a work, the porting method has no problem. Sorry for interrupting you so many times. Best Regards from a Chinese girl~ (*^__^*) Yours, Fan LiJing（范丽菁） 2011-12-9 -----Original Message----- From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] Sent: Wednesday, December 07, 2011 4:08 PM To: fanlijing *EXTERN*; pgsql-admin@postgresql.org Subject: RE: Is there any support like "for update wait N" in PostgreSQL? fanlijing wrote: > 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'm 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 ... > -> > SELECT DOMAIN_ID, DOMAIN_NAME...ORDER BY ... FOR UPDATE [...] > queryRunner.update(conn, "set statement_timeout = 10000"); [...] > Following is the exception when run my program... [...] > ....***SqlException: ERROR: canceling statement due to statement timeout Query: SELECT... ORDER BY ... FOR > UPDATE Parameters: [yun_SF_18, 0] There is no "WAIT n" clause in PostgreSQL, and using statement_timeout as you did is the only way to get a similar functionality. But you seem unhappy with this approach. How does it affect the functionality? If you use "WAIT n" in Oracle, you will get an error message (ORA-30006) similar to using statement_timeout in PostgreSQL. Yours, Laurenz Albe