Rép. : Re: dblink locked query
| От | Francois Rigaudie |
|---|---|
| Тема | Rép. : Re: dblink locked query |
| Дата | |
| Msg-id | s56589a5.063@gw-tdf1s.tdf.fr обсуждение исходный текст |
| Список | pgsql-hackers |
I was just testing the statement_timeout solution. Here are my strange results :
- the distant database uses a specific user, indeed, but the problem occurs when that database server falls down. So my problem is to control my local query execution.
- we set up a benchmark where 2 distinct postgres (a 8.0 and a 7.4.9) run together on the same server. The first one is used as the local machine, the second one as the distant database. We launched on the first one the procedure with the dblink and we tortured the link between the two dbs like that :
1°/ - stopping postgres 7.4.9 : the local query detects it and stops with an SQL error - so the query is not locked - X(
2°/ - kill -9 on the 7.4.9 baby process, same thing, different SQL error - X(
3°/ - we block during the query the Postgres 7.4.9 TCP incoming port : the local query hangs like we want !!! If nothing is done the query is blocked for at least 10 minutes. If we set a value for the statement_timeout (tested with 30 and 3 seconds) then the query stops with an SQL error !!! Do we got it ??? Yes and No...
Yes because we can now stop the blocked query (heeeeeeeee ya !!!!!)
No because it does not stops after 30 or 3 seconds. Something more happends and the statement_timeout delay is not respected. Something more strange (yes) : if we release again the TCP blocked port, the local query finish it's job normally after a certain time without any error, even if by that time we stopped and started again the postgres 7.4.9, including existing connexions !!! I like magic. Do you ?
So it seems that dblink connexions are really resiliant and able to reconnect quite well. Unfortunately this behaviour is not the one we had on our servers. In our case our local querys never recover (after 2 days once) even when the network link and the distant server were up again.
Anyway I think we will add the statement_timeout setting in our procedure. Thanks a lot to you and to Tom Lane <tgl@sss.pgh.pa.us> for your answers.
Have a nice day (or night),
François
>>> "Albe Laurenz" <all@adv.magwien.gv.at> jeudi 23 novembre 2006 >>>
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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
В списке pgsql-hackers по дате отправления: