Обсуждение: multiple calls to dblink_connect from within pl/pgSQL function exhaust connection limit

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

Peter Geoghegan <peter.geoghegan86@gmail.com> writes:
> 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".

What PG version is this?  8.4 has a connection leak in some cases:
http://archives.postgresql.org/pgsql-committers/2009-09/msg00125.php

This fix hasn't made any released version yet, but you could apply the
source patch shown there.

            regards, tom lane

Hi Tom,


> What PG version is this?  8.4 has a connection leak in some cases:
> http://archives.postgresql.org/pgsql-committers/2009-09/msg00125.php
>
> This fix hasn't made any released version yet, but you could apply the
> source patch shown there.

This is 8.4.0 on Windows. Looks like it's the bug you mention. I guess
I'll just upgrade to 8.4.1.

Thanks,
Peter Geoghegan