pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there any support like "for update wait N" in PostgreSQL?
3+ messages / 2 participants
[nested] [flat]
* Is there any support like "for update wait N" in PostgreSQL?
@ 2011-12-07 01:38 fanlijing <fanlijing@cn.fujitsu.com>
0 siblings, 1 reply; 3+ messages in thread
From: fanlijing @ 2011-12-07 01:38 UTC (permalink / raw)
To: 'Albe Laurenz' <laurenz.albe@wien.gv.at>; 'fanlijing' <fanlijing@cn.fujitsu.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is there any support like "for update wait N" in PostgreSQL?
@ 2011-12-07 08:08 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: fanlijing <fanlijing@cn.fujitsu.com>
0 siblings, 1 reply; 3+ messages in thread
From: Albe Laurenz @ 2011-12-07 08:08 UTC (permalink / raw)
To: fanlijing *EXTERN* <fanlijing@cn.fujitsu.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is there any support like "for update wait N" in PostgreSQL?
@ 2011-12-09 03:17 fanlijing <fanlijing@cn.fujitsu.com>
parent: Albe Laurenz <laurenz.albe@wien.gv.at>
0 siblings, 0 replies; 3+ messages in thread
From: fanlijing @ 2011-12-09 03:17 UTC (permalink / raw)
To: 'Albe Laurenz' <laurenz.albe@wien.gv.at>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-12-09 03:17 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-07 01:38 Is there any support like "for update wait N" in PostgreSQL? fanlijing <fanlijing@cn.fujitsu.com>
2011-12-07 08:08 ` Albe Laurenz <laurenz.albe@wien.gv.at>
2011-12-09 03:17 ` fanlijing <fanlijing@cn.fujitsu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox