Postgres Pro
Downloads
Home   >   mailing lists

Thread: dblink locked query

dblink locked query

From
"Francois Rigaudie"
Date:
Hi,
 
I am working on a server that send queries to a distant database based on a PHP script executed twice a second.
 
Sometimes, if the distant server goes down the local SELECT dblink query can keep running indefinitly. This is strongly disturbing my server if by night the daily server FULL VACUUM falls on it. Then my whole database access is locked and I find myself with a magnificient DOS of the death.
 
Since there is no timeout possibility with dblink I look for a solution to that problem.
 
Does anyone of yours ever had that kind of trouble ?
 
Is there any [easy] way to stop my local infinite query ? The only way we found for the moment was to kill it (with a SIGQUIT signal) but this unfortunately forces Postgres to restart quite ungracefully for the other connections on the server (too bad).
 
Any idea, testimony, pray, support will be appreciated.
 
Thanks,
 
Francois - Paris.
 
 

Re: dblink locked query

From
Tom Lane
Date:
"Francois Rigaudie" <francois.rigaudie@tdf.fr> writes:
> Since there is no timeout possibility with dblink I look for a solution to =
> that problem.

Doesn't statement_timeout work?
        regards, tom lane

Re: dblink locked query

From
"Albe Laurenz"
Date:
Tom Lane answered to a question from Francois Rigaudie:
>> Since there is no timeout possibility with dblink I look for a
solution to
>> that problem.
>
> Doesn't statement_timeout work?

To expand on this:
dblink will login as a certain user on the remote database.
If your design is good, this is a dedicated user only for this
purpose.

You can then
ALTER ROLE <user> SET statement_timeout = 10000
for that user on the remote database to have all statements
canceled when they take longer than 10 seconds.

Yours,
Laurenz Albe