multiple calls to dblink_connect from within pl/pgSQL function exhaust connection limit

From: Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: multiple calls to dblink_connect from within pl/pgSQL function exhaust connection limit
Date: 2009-09-23 11:11:28
Message-ID: db471ace0909230411pb80f750hfbbd31820b89a7c3@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I'm writing a pl/pgSQL function that connects to multiple PostgreSQL
databases and accumulates data from all of them into a temporary
table.

I use the dblink contrib module to do so. I loop through some records,
form a connection string from those records, and do this:

PERFORM dblink_connect(conn_str);
....

Making many successive calls to this function of mine eventually
exhausts the connection limit of my remote DBs (although I think that
the default connection limit is something like 5 or 10, and it takes
more calls than that). I understood from the dblink docs that calling
this particular overload of dblink_connect wouldn't do so, because
each subsequent call to it disconnects the last, because an "unnamed
connection is opened, replacing any existing unnamed connection".

This is unlikely to be a concern in my production system, because
users will connect to my main db, execute this function and disconnect
(dblink connections only persist for as long as the connection that
originated them, I also understand from the docs). Nonetheless, I'd
like to know what's happening here. Why the apparent resource leak?

Thanks,
Peter Geoghegan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martin Gainty 2009-09-23 11:26:18 Re: Re: PL/PG SQL: select count(*) into <var> from <ANY VIEW> where <cond> - does not compute the where cond - returns always total count
Previous Message Raymond O'Donnell 2009-09-23 10:28:24 Re: Where can I find detail information about constraint ?