CODEWITHSUNDEEP
postgresqldblink
Julio Martins
Julio Martins

Reputation: 39

PostgreSQL - dblink_exec statement_timeout

I have a function that used dblink_exec inside. Sometimes this execution takes a long time and in some cases produces deadlocks. I know that I can set local lock_timeout and local statement_timeout but when I do this inside a function, It doesn't take any effect over dblink_exec.

Is that any way to set those parameters on dblink_connect? I know that I can set these two parameters on .conf file but I want to do this locally (per connection).

Is this possible?

Thanks!

Julio

Upvotes: 0

Views: 628

Answers (1)

jjanes
jjanes

Reputation: 44363

It is quite ugly, but you can specify the statement timeout (to be implemented on the remote side) in the connection string, using the options construct.

create or replace function dblink_timeout() returns int language plpgsql as $$ 
  declare xx int;  
begin 
  select x into xx from 
    dblink(
      'dbname=jjanes options = ''-c statement_timeout=500 -c lock_timeout=100''',
      'select count(*) from pgbench_accounts'
    ) as (x int);
  return xx; 
end 
$$;

Upvotes: 2

Related Questions