[ADMIN] Is there any support like "for update wait N" in PostgreSQL?

[fanlijing]

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:***@wien.gv.at]

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

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

pgsql-***@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

[Albe Laurenz]

Post by fanlijing
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...

Post by fanlijing
Is there any support like "for update wait N" in PostgreSQL?
I'm waiting for your reply.
Thank you so much any way.

########################################################################
####

Post by fanlijing
Following is my porting method, although it avoid the dead-lock

problem, but it affect the

Post by fanlijing
functionality.
SELECT DOMAIN_ID, DOMAIN_NAME...FOR UPDATE WAIT 10 ORDER BY ...
->
SELECT DOMAIN_ID, DOMAIN_NAME...ORDER BY ... FOR UPDATE

[...]

Post by fanlijing
queryRunner.update(conn, "set statement_timeout = 10000");

[...]

Post by fanlijing
Following is the exception when run my program...

[...]

Post by fanlijing
....***SqlException: ERROR: canceling statement due to statement

timeout Query: SELECT... ORDER BY ... FOR

Post by fanlijing
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

[fanlijing]

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:***@wien.gv.at]
Sent: Wednesday, December 07, 2011 4:08 PM
To: fanlijing *EXTERN*; pgsql-***@postgresql.org
Subject: RE: Is there any support like "for update wait N" in PostgreSQL?

Post by fanlijing
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...

Post by fanlijing
Is there any support like "for update wait N" in PostgreSQL?
I'm waiting for your reply.
Thank you so much any way.

########################################################################
####

Post by fanlijing
Following is my porting method, although it avoid the dead-lock

problem, but it affect the

Post by fanlijing
functionality.
SELECT DOMAIN_ID, DOMAIN_NAME...FOR UPDATE WAIT 10 ORDER BY ...
->
SELECT DOMAIN_ID, DOMAIN_NAME...ORDER BY ... FOR UPDATE

[...]

Post by fanlijing
queryRunner.update(conn, "set statement_timeout = 10000");

[...]

Post by fanlijing
Following is the exception when run my program...

[...]

Post by fanlijing
....***SqlException: ERROR: canceling statement due to statement

timeout Query: SELECT... ORDER BY ... FOR

Post by fanlijing
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